Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Single quote - double quote problem

Posted on 2013-06-05
3
Medium Priority
?
435 Views
Last Modified: 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[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?
0
Comment
Question by:jdthedj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 39224375
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
 
LVL 3

Author Comment

by:jdthedj
ID: 39224380
Thanks _agx_  Would it still be a problem if I surround it with cfqueryparam?
0
 
LVL 3

Author Comment

by:jdthedj
ID: 39224384
OOPS sorry  - you had already answered that.  Thanks for your help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question