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
my $myquery="SELECT * FROM customers WHERE clientName =".$dbh->quote(param('name')) . " AND pass =".$dbh->quote(param('pass'));
name=George&pass=qwerty -> name=George'&name=2&pass=qwerty
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.
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
quote will pass its first argument through without any quoting. This constitutes an opportunity for SQL injection.
quote is safe when used properly, placeholders are better, safer, and harder to use wrong. Use DBI placeholders whenever possible, instead of
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.
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).
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.