[Pdx-pm] Escaping strings for SQL insertion
Jeff Zucker
jeff at vpservices.com
Tue Feb 15 17:45:44 PST 2005
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
More information about the Pdx-pm-list
mailing list