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.

Solution 1

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.

Solution 2

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.

Solution 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) . ')');

Solution 4

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.

View more solutions

Comments

[silbana]

Are you looking for mysql_real_escape_string()? See stackoverflow.com/questions/2207654/…

[Dancrumb]

mysql_real_escape_string is a PHP function, not a Perl function

[Lawrence Hutton]

How do you figure that using placeholders "makes your code... dependent on a particular RDBMS"? In working with PostgreSQL, MySQL, and SQLite, I have encountered differences between them, but, as far as I've seen, placeholders are handled identically by all three. Are there any major RDBMS backends that don't support parametrized queries? (Even if there are, I would expect the corresponding DBD driver to emulate them.)

[codeholic]

You got me wrong. Placeholders are always alright. I compared using quote and quote_identifier against tampering with data by yourself, since various RDBMS have different quote characters.

[silbana]

+1 Good explanation: When people come looking for mysql_real_escape_string(), we sort of know they have SQL injection in mind but a fully fleshed explanation is good to have. (incidentally, are there mysql_fake_escape_string() and `mysql_not_this_one_either_escape_string() as well?)

[Lawrence Hutton]

Ah, OK. Yes, I misread that statement as being an advantage of ->quote(_identifier)? over placeholders, not over roll-your-own sanitizing regexes.