Single quote - double quote problem

I have a query which needs parameters depending on which cities the user selected previously.  I have a function to create that part of the SQL

<CFFUNCTION Name="GetCitySQL" ReturnType="String">
<CFARGUMENT Name="CityStr" Datatype="String" Required="Yes">
<CFSET var Rslt="">
<CFSET var CArray = ListToArray(CityStr,"*")>
<CFIF ArrayLen(CArray) EQ 1>
<CFSET Rslt=" AND City = '#CArray[1]#'" >
<CFELSE>
<CFSET Rslt=" AND (City = '#CArray[1]#'">
<CFLOOP From = "2" To = "#ArrayLen(CArray)#" Index="i">
<CFSET Rslt=Rslt & "  OR City = " & Chr(39) & CArray[#i#] & Chr(39)>
</CFLOOP>
<CFSET Rslt=Rslt & ")">
</CFIF>
<CFRETURN Rslt>
</CFFUNCTION>

As you will see I have used single quotes, and when I cfoutput the result of the function the resultant string has single quotes

AND (City = 'Christchurch' OR City = 'Ashburton' OR City = 'Rangiora' OR City = 'Kaiapoi')  

BUT when I use that string in the query

AND Gone = 0
  AND Deceased = 0
  AND NDM = 0
  AND Warning = 0
  AND Email <> ''
  #CitySql#

the query crashes, and the sql in the error report shows that the single quotes have somehow become double quotes.

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'Christchurch'.  
<snip>
AND Gone = 0 AND Deceased = 0 AND NDM = 0 AND Warning = 0 AND Email <> '' AND (City = ''Christchurch'' OR City = ''Ashburton'' OR City = ''Rangiora'' OR City = ''Kaiapoi'')

What is going on here? and how do I fix it?
LVL 3
jdthedjAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
It's because CF escapes single quotes whenever it sees dynamic sql. It does this to protect you against sql injection.

           <cfquery ...> #bigStringOfSQL#</cfquery>

The only way to suppress it is by using PreserveSingleQuotes.

           <cfquery ...> #PreserveSingleQuotes(bigStringOfSQL)#</cfquery>

But using it leaves you vulnerable to sql injection.  So personally I wouldn't recommend it. I'd refactor the code to use cfqueryparam instead. CF9 has more support for adding params dynamically

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ffb.html
0
 
jdthedjAuthor Commented:
Thanks _agx_  Would it still be a problem if I surround it with cfqueryparam?
0
 
jdthedjAuthor Commented:
OOPS sorry  - you had already answered that.  Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.