Solved

Single quote - double quote problem

Posted on 2013-06-05
3
417 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sum/Avg of results from a CFSCRIPT 1 54
Coldfusion Report Builder HTML table 2 55
what type of join should I use on these 2 queries 9 92
query of query sort 6 97
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now