How can I protect against SQL injection attacks using Perl's DBI?

Question

Is there a function i can use in Perl to sanitize input before putting it into a MySQL db? I don't know regex very well so before I make my own function i was wondering if there was already one made.

Answer 1

How do you call the database?

DBI has support for prepared statements using placeholders. Both DBIx::Class and Rose::DB::Object sanitize values automatically, if you use the "find" method provided by each library.

Answer 2

The proper way to sanitize data for insertion into your database is to use placeholders for all variables to be inserted into your SQL strings. In other words, NEVER do this:

my $sql = "INSERT INTO foo (bar, baz) VALUES ( $bar, $baz )";

Instead, use ? placeholders:

my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";

And then pass the variables to be replaced when you execute the query:

my $sth = $dbh->prepare( $sql );
$sth->execute( $bar, $baz );

You can combine these operations with some of the DBI convenience methods; the above can also be written:

$dbh->do( $sql, undef, $bar, $baz );

See the DBI docs for more information.

Answer 3

In very rare cases you're not able to use placeholders, as described in other answers. But even in such rare case you shouldn't tamper with data by yourself, since it makes a place for a potential bug. It's better to use DBI's quote and quote_identifier methods. Also it makes your code less dependent on a particular RDBMS.

Disclaimer. The following is a dummy example and is not meant to illustrate the very rare case I mentioned.

$dbh->do('INSERT INTO ' . $dbh->quote_identifier($table) . ' (id, name) VALUES '
    '(NULL, ' . $dbh->quote($name) . ')');

Answer 4

Minor (and admittedly pedantic) addendum to the "use placeholders" answers: Parametrized queries are not, strictly speaking, "sanitizing". They do not modify the data in any way to make it safe. Instead, they protect against SQL injection by sending the query structure (commands) and the data by separate channels.

The reason I feel this distinction is significant is because treating sanitizing/quoting/escaping your data and using parametrized queries as the same thing implies that they are interchangeable or, at best, that parameters are just a better way to quote dangerous characters, so it's no big deal if you stick with quoting instead of bothering to figure out that placeholder stuff.

In truth, they are completely different techniques with completely different levels of reliability. Quoting can provide excellent protection against injection, but there is always the chance that a determined attacker could find some corner case which will break or slip through your quoting algorithm and allow them to perform a successful SQL injection. Parametrized queries, on the other hand, provide absolute protection against SQL injection. Because the commands and data are sent separately, there is no way that the database engine can be tricked into executing data as a command.

Unless you're in a case where your language or database engine won't allow you to use a parameter in your query, never quote/escape/sanitize user input as protection against SQL injection. Always use parametrized queries for this purpose if you are able to do so.

And the obligatory link: http://bobby-tables.com/ has examples of how to use parametrized queries in several different languages, including Perl.