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);

Answer 2

Use a prepared statement. Replace the variable with a ?. To crib an example from DBI manpages:

$sql = 'SELECT * FROM people WHERE lastname = ?';
$sth = $dbh->prepare($sql);
$sth->execute($user_input_here);

Interpolating user input into your SQL is asking for security holes.

Answer 3

If you use query parameter placeholders, you don't have to escape the content of the strings.

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

If the DBI is using true query parameters, it sends the parameter values to the RDBMS separately from the SQL statement. The values are never combined with the SQL statement string, therefore the values never have an opportunity to cause SQL injection.

If the DBI is "emulating" prepared statements by interpolating the variables into the query string, then DBI should handle the correct escaping logic so you don't have to. Let the experts (those who write and test DBI) worry about how to do it.

Answer 4

If you don't want to use ->quote (for some reason, this function doesn't run on my version of DBI) then try this:

$query=~s/\"/\\\"/g;

I tend to do the same with single quotes and commas too just to be safe.

Seems to work fine for me...!