mysql - 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

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.