How can I escape single and double quotes in SQL prepared statement?
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);
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.
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.