Solved

Single quote - double quote problem

Posted on 2013-06-05
3
424 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 500 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: 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.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

690 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