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

mysql perl input-sanitization
cskwrd picture cskwrd · Feb 20, 2010 · Viewed 23.1k times · Source

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

friedo picture friedo · Feb 20, 2010

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.

Related questions

How do I know how many rows a Perl DBI query returns?

I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID. This search can return no rows, but it can also return one. I have the following code: …

Read More
Perl DBI fetchall_hashref

Consider the following table: mysql> select * from vCountryStatus; +-------------+------------+------+---------+--------+-----------------+ | CountryName | CountryISO | Code | Status | Symbol | CurrencyName | +-------------+------------+------+---------+--------+-----------------+ | Brazil | BR | 55 | LIVE | BRL | Brazilian Real | | France | FR | 33 | offline | EUR | Euro | | Philippines | PH | 63 | …

Read More
How can I print the SQL query executed after Perl's DBI fills in the placeholders?

I'm using Perl's DBI module. I prepare a statement using placeholders, then execute the query. Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders? Thanks

Read More