How can I escape single and double quotes in SQL prepared statement?

Question

I have a SQL statement similar to the one shown below in Perl:

my $sql="abc..TableName '$a','$b' ";

The $a is free text which can contain anything including single quotes, double quotes, back- and front-slash characters, etc.

How can these characters be escaped to make the SQL statement work?

Thanks.

Answer 1

You can either use the ->quote method (assuming you're using DBI):

my $oldValue = $dbh->quote('oldValue');
my $newValue = $dbh->quote('newValue');
$dbh->do("UPDATE myTable SET myValue=$newValue where myValue=$oldValue");

Better still, the best practice is to use bind values:

my $sth = $dbh->prepare('UPDATE myTable SET myValue=? WHERE myValue=?');

$sth->execute('newValue','oldValue');

This should also work for stored procedure calls, assuming the statement once the strings have been expanded is valid SQL. This may be driver/DB specific so YMMV.

my $sth = $dbh->prepare("DBName..ProcName ?,? ");
$sth->execute($a, $b);