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

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.

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.

1 +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? ) – Sinan Ünür Feb 20 '10 at 12:58.

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.

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

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.) – Dave Sherohman Feb 20 '10 at 10:45 2 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. – codeholic Feb 20 '10 at 12:17 Ah, OK. Yes, I misread that statement as being an advantage of ->quote(_identifier)?

Over placeholders, not over roll-your-own sanitizing regexes. – Dave Sherohman Feb 20 '10 at 22:04.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.