How can I insert strings with quotes into Perl DBI queries?

Question

What is the preferred way to insert strings that can contain both single and double quotes (",') into MySql using DBI? For example, $val1 and $val2 can contain quotes:

my $dbh = DBI->connect( ... );
my $sql = "insert into tbl_name(col_one,col_two) values($val1, $val2)";
my $sth = $dbh->prepare($sql);
$sth->execute();

Answer 1

Use a bound query using

$sth = $dbh->prepare("insert into tbl_name(col_one,col_two) values(?,?)");
$sth->execute($val1, $val2);

If you use bound variables, everything is escaped for you.

Update: Changed my example to correspond with the example edited into the question.

Update: I don't know why Adam deleted his answer, but if for some reason you can't use bound variables (aka "placeholders"), you can also use $dbh->quote($var) on the variable. For example:

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
    $dbh->quote(q("Don't"));

Answer 2

Use the quote() method. It will intelligently handle the quoting for you. Example from the docs:

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
            $dbh->quote("Don't");

Slightly modified to have both types of quotes:

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
            $dbh->quote(q("Don't"));

Answer 3

One small caveat on the bound placeholders, I build a rather large database-loading script that initially used bound placeholders in an older version of Perl/DBI and found what appears to be a memory leak in the placeholder implementation, so if you're looking at using them in a persistent process/daemon or in a high-volume context you may want to make sure process size doesn't become an issue. Switching over to building the query strings using the quote() method eliminated the issue for me.