Single quote - double quote problem
Posted on 2013-06-05
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#'" >
<CFSET Rslt=" AND (City = '#CArray#'">
<CFLOOP From = "2" To = "#ArrayLen(CArray)#" Index="i">
<CFSET Rslt=Rslt & " OR City = " & Chr(39) & CArray[#i#] & Chr(39)>
<CFSET Rslt=Rslt & ")">
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 <> ''
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'.
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?