Ask a Jedi - Issue with single quotes in a query

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 (13) Oct 5, 2007 11:20 AM Views: 1083

Comments

[Add Comment]
Man I don't know if I like any service doing formatting for me. I'd rather get a list of data then have them try to monkey with formatting.
# Posted By todd sharp | 10/5/07 11:40 AM
when will people learn to just use cfqueryparam and be done with this.
# Posted By tony petruzzi | 10/5/07 12:14 PM
For Alex or anyone else:
Having the service give you SQL isn't that bad but I would prolly take the string they give me, parse it into a structure/array and then use queryparams. Queryparams are more then just a security measure (although this is the most vital reason to use them). I'm not going to go into that, there are a great many articles out there on the subject.
# Posted By Tyler Clendenin | 10/5/07 12:14 PM
Tony - cfqueryparam doesn't imply here. In this case his str is SQL itself.
# Posted By Raymond Camden | 10/5/07 12:45 PM
Tony,

According to CFMX documentation, you cannot use the cfquery cachedAfter or cachedWithin attributes with cfqueryparam.

So if you want to use cfqueryparam, you can't cache the query results.
# Posted By James Moberg | 10/5/07 1:03 PM
Its worth noting that the limitation James mentions is for ColdFusion versions prior to CF 8.
# Posted By Jason Troy | 10/5/07 1:10 PM
And to add a note to Jason's comment, you certainly can cache the query. You just can't use CF's built in caching. I normally write my own anyway.
# Posted By Raymond Camden | 10/5/07 1:13 PM
cachedWithin and cfqueryparam work together in CF8? That's great to know, but I probably won't notice unless someone actually pointed it out. I haven't worked on CF8 yet as most client applications that I've worked with are still either on CF5, 6 or 7.

Is there a good online resource for identifying the subtle differences and bugfixes between each CF version?

Also, on the topic of CFQUERY, if you are using a UDF to perform any modifications to the data, I've found that CF7 throws an error when combined with preservesinglequotes(). We've had to perform variable modifications separate from the query. This may be fixed in CF8 too... but I don't know because I'm not using it yet.
# Posted By James Moberg | 10/5/07 1:38 PM
James- I blogged on this actually. :) In the PDF Reference there is a section that details changes since MX (v 6).
# Posted By Raymond Camden | 10/5/07 1:40 PM
I think a good rule of thumb here is that if you need to use the preservesinglequotes() function you should probably be writing your query differently - or just be completely aware of the fact that if you're using this function you are opening the door to sql injection and will need to validate the data you're checking yourself.
# Posted By Ken Sykora | 10/5/07 2:15 PM
I would just want to add that using preserveSingleQuotes() without CFQUERYPARAM is a really big security risk. Simple SQL injections that usually fail because the single quote is escaped will have a better chance of working.
# Posted By Shlomy Gantz | 10/5/07 2:56 PM
I agree with you Shlomy - but let me be picky. Afaik, you can't use cfqueryparam with PSQ. Your comment makes it sounds like it is a choice - which it isn't - it is an either/or.
# Posted By Raymond Camden | 10/5/07 3:47 PM
If this is an OR set for a single variable, I'd recommend that you change what is returned by your function to be a list of:

10001,10002,10003,...

and then put this in your query:

xxx IN (<cfqueryparam list="YES" type="CF_SQL_VARCHAR" value="#YourList#" NULL="#NOT Len(YourList)#">)

It would do the same thing and be more secure.
# Posted By Lyle | 10/5/07 6:55 PM
[Add Comment]