| |
Single Quotes In A Query Issue
By Raymond Camden
Expert Author
Article Date: 2007-10-08
Alex had a problem with his SQL.
This is actually a frequently asked question and I've covered it here before (I think so anyway), but I thought I'd mention it again.
It comes up from time to time as people forget. Anyway - his question: I'm using a web service to retrieve zip codes within a given radius from another zip code.
The service sends me back a string that is formatted to use in a SQL WHERE clause:
xxx="10001" or xxx="10002" or xxx="10003" ...
I am using MySQL as the DB and the double quotes in the return string above won't work in the WHERE clause, so I am using a simple CF replace() function to replace the double quotes with single quotes (the zip field in my DB is setup as a string) for processing.
For some reason, after I replace the quotes, the SQL statement in my CFQUERY tag includes the double quotes again! I can't figure out why this is happening. There is a simple explanation for this. ColdFusion auto escapes single quotes. Why? Imagine you have a search for, and a user searches for "Ray's Hotness is greater than Paris Hilton". Your sql could do:
where name like '#form.search#'
(Although hopefully you would use cfqueryparam instead.) As you can see - the single quote in my search would break.
So in cases where you need a single quote to be left alone, you tell ColdFusion to stop that change with the preserveSingleQuotes function:
where whatever = #preserveSingleQuotes(somefunc)#
Comments
About the Author:
Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com
Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
|
|
