• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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]#'" >
<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)>
<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?
  • 2
1 Solution
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

jdthedjAuthor Commented:
Thanks _agx_  Would it still be a problem if I surround it with cfqueryparam?
jdthedjAuthor Commented:
OOPS sorry  - you had already answered that.  Thanks for your help
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now