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

Archived Comments

Comment 1 by todd sharp posted on 10/5/2007 at 8:40 PM

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.

Comment 2 by tony petruzzi posted on 10/5/2007 at 9:14 PM

when will people learn to just use cfqueryparam and be done with this.

Comment 3 by Tyler Clendenin posted on 10/5/2007 at 9: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.

Comment 4 by Raymond Camden posted on 10/5/2007 at 9:45 PM

Tony - cfqueryparam doesn't imply here. In this case his str is SQL itself.

Comment 5 by James Moberg posted on 10/5/2007 at 10:03 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.

Comment 6 by Jason Troy posted on 10/5/2007 at 10:10 PM

Its worth noting that the limitation James mentions is for ColdFusion versions prior to CF 8.

Comment 7 by Raymond Camden posted on 10/5/2007 at 10:13 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.

Comment 8 by James Moberg posted on 10/5/2007 at 10:38 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.

Comment 9 by Raymond Camden posted on 10/5/2007 at 10:40 PM

James- I blogged on this actually. :) In the PDF Reference there is a section that details changes since MX (v 6).

Comment 10 by Ken Sykora posted on 10/5/2007 at 11:15 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.

Comment 11 by Shlomy Gantz posted on 10/5/2007 at 11:56 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.

Comment 12 by Raymond Camden posted on 10/6/2007 at 12:47 AM

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.

Comment 13 by Lyle posted on 10/6/2007 at 3:55 AM

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.