[Pdx-pm] Escaping strings for SQL insertion

[Jeff Zucker]

Roderick A. Anderson wrote:

> While testing an older application I entered single quotes ( 
> apostrophes ) in a string and Win32::ODBC mostly choked.

SQL should look like this:  WHERE col_name = 'string_value', in other 
words, there should be single quotes around the string _value.  If that 
value contains a single quote, everything gets confused because you end 
up with WHERE col_name ='O'Reilly' and there's no way to know where the 
value ends.  Most dialects of SQL use two single-quotes to escape an 
embedded quote, though some use backslash or other things.  So proper 
SQL should look like WHERE col_name='O''Reilly'.  DBI provides the 
quote() method to escape the embedded single quotes for you, but it is 
essentially just putting an escape character in front for you.  When you 
get to DBI, use placeholders instead, but for Win32::ODBC, either write 
your own quote-escaping  or better, just copy the quote() method from 
DBD::ODBC.

> Win32::ODBC doesn't have ( from what I can tell ) quote/unquote 
> functions and DBI only has quote.  I'll be converting to DBI shortly 
> and would bet I could kludge something together to unquote my strings 
> before passing them back


You don't need to reverse the quoting.  When you insert 'O''Reilly' you 
are really inserting O'Reilly and that's how it will come back from the 
database.

-- 
Jeff