J.Doe J.Doe - 1 month ago 6
Perl Question

Is perl function dbh->quote still secure?

I am currently writing a small script in perl to connect to my db, retrieve some data, and show it to the user. The data retrieved depends of parameters given by the user.

I am using

dbh->quote
to escape the quotes:

...
my $dbh=DBI->connect(***);

my $myquery="SELECT * FROM customers WHERE clientName =".$dbh->quote(param('name')) . " AND pass =".$dbh->quote(param('pass'));
my $sth=$dbh->prepare($myquery);
$sth->execute();

my $output=$sth->fetch();
if ($output){
print @$output;
}
...


A friend told me that it might not be secure, and that he read that someone found a vulnerability. I am just beginning with perl, but I would like to understand what is that vulnerability.

After some digging I found this document (pdf) that seems to talk about it, but I am not able to reproduce the bug.

Apparently, one could add some
'
by editing the request to something like this:

name=George&pass=qwerty -> name=George'&name=2&pass=qwerty

Answer

The problem mostly doesn't lie with quote per se. quote is secure if used properly (although it's not the best choice in this situation). However, if param is the param from CGI.pm, or from anything else that has similar behavior, you have a big problem.

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. In list context, it returns a list of values, regardless of how many there are. The argument list to a method (such as quote) is a list context. That means that someone using your app can cause quote to receive two values, and quote's optional second argument is an SQL data type that the first argument should be treated as. If the data type is a non-string type like NUMERIC, then quote will pass its first argument through without any quoting. This constitutes an opportunity for SQL injection.

Recommendations:

  1. Although quote is safe when used properly, placeholders are better, safer, and harder to use wrong. Use DBI placeholders whenever possible, instead of quote.

  2. Don't use CGI's param in argument lists, hash constructors, or any other place where it could return an unexpected number of items and ruin your day. Either put scalar out front, assign to a scalar, or assign to an array. Or, better yet, avoid CGI.pm and workalike interfaces entirely.

  3. Don't store passwords as plaintext in the database. If anyone ever does get access to parts of your database, your users' passwords will be exposed to them. Passwords should be hashed and there are good, easy-to-use Perl modules for doing so (Authen::Passphrase comes to mind).

  4. Don't pass passwords as URL parameters. URLs are easily leaked through HTTP referers, browser history, careless copy/paste, etc. Passwords should be POSTed in forms, preferably over a secure connection.