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

sql perl string sybase quotes
Sam picture Sam · Jan 5, 2010 · Viewed 21.2k times · Source

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

mopoke picture mopoke · Jan 5, 2010

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

Related questions

How can I print the SQL query executed after Perl's DBI fills in the placeholders?

I'm using Perl's DBI module. I prepare a statement using placeholders, then execute the query. Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders? Thanks

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More