Is this Perl database connection vulnerable to SQL Injection

Question

I have this (stripped down) Perl database query, and I wonder if this can be exploited in any way. This is from a challenge, so I know things could be done different, the task is to exploit this.

To my knowledge it uses prepared statements and is therefore considerable safe. However I could find this, regarding problems with quote and param.

if ('POST' eq request_method && param('username') && param('password')){
    my $dbh = DBI->connect( "DBI:mysql:database_name","database_name", "<censored>", {'RaiseError' => 1});
    my $query="Select * FROM users where username =".$dbh->quote(param('username')) . " and password =".$dbh->quote(param('password')); 

    my $sth = $dbh->prepare($query);
    $sth->execute();
    my $ver = $sth->fetch();
    if ($ver){
        print "win!<br>";
        print "here is your result:<br>";
        print @$ver;
    }
    else{
        print "fail";
    }
    $sth->finish();
    $dbh->disconnect();
}

Answer 1

It isn't safe, but I don't think it is exploitable.

The point is that param may return a scalar or a list. In list context, if you pass username=a&username=b to this page, the list will be ("a", "b").

In Perl, if you pass a list to a function, it is interpreted as seperate arguments.

quote("a", "b")

Is equivalent to

@list = ("a", "b")
quote(@list)

So if you call quote(param("username")), the user can supply the second parameter to quote, by passing in two username values. Since the second parameter determines how to do quoting, this can introduce an SQL injection vulnerability.

However, how the second parameter is handled depends on the DBD driver, which is different for each database type. Furthermore, it should actually be a hash reference, but we can only pass in a string. So it may be that a driver exists where passing in something changes the way the escaping is done, but I couldn't find it. The Postgres driver, for example, explicitly states "Second argument to quote must be a hashref".

Answer 2

After recreating the website locally and reviewing the docs

Yes

This Perl program is vulnerable to SQL Injection.

• However this depends on the DBI driver, and could only reproduce this with MySQL

There are 2 flaws with this in $dbh->quote(param('paramater'))

1. You see, param is context-sensitive. In scalar context, if the parameter has a single value (name=foo), it returns that value, and if the parameter has multiple values (name=foo&name=bar) it returns an arrayref.

   From the SO link, the problem with directly calling param() is that it can return an array

2. As a special case, the standard numeric types are optimized to return $value without calling type_info.

   From the DBI docs. Calling quote as a list with SQL_INTEGER as the second parameter, will return an unquoted value.

Since SQL_INTEGER == 4 All it took was this python script:

def vuln(url):
    params={"username": "valid_username", "password": ["'lol' or 1", 4]}
    print(requests.post(url, data=params).text)

Answer 3

In General its a bad idea to use POST variables directly (there client controlled after all). It is better to do the following:

• Limit the size of the input to limit overflows.
• Parse the value, to make sure its of the type you expect.
• Sanatize the parameter to not include any (unescaped) control instructions, the quote does most of this but not all (check the instructions for your Perl version)
• Use proper prepared statements (as @Marc suggests)

For the rest it looks like a good approach but make sure your using uptodate software and libraries. (goes for any Application)