Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Coldfusion: problem generating CFQUERY SQL

I seem to occasionally have problems when executing dynamically generated SQL in a CFQUERY. I've usually found work arounds in each case (without trying to understand the cause) but can't in this case, and it's about time i got to the bottom of this! Let me simplify with this case:


<CFQUERY datasource="XXX" name="qry">
SELECT * FROM web_data
</CFQUERY>

... Works fine!


<CFSET j="SELECT * FROM web_data">
<CFQUERY datasource="XXX" name="qry">
#j#
</CFQUERY>

.. Works fine!


<CFQUERY datasource="XXX" name="qry">
SELECT * FROM web_css_rules WHERE selector IN ('ul', 'ul li')
</CFQUERY>

.. Works fine!


<CFSET j="SELECT * FROM web_data WHERE selector IN ('ul', 'ul li')">
<CFQUERY datasource="XXX" name="qry">
#j#
</CFQUERY>

.. throws a SQL error!

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'ul'.  

SQL: SELECT * FROM web_data WHERE selector IN (''ul'', ''ul li'')

Notice that coldfusion is inserting an additional single-quote (') for each single-quote occurrance.

Can anyone explain what is going on here? I need to dynamically generate an IN condition which could have any number of list entries. If any one can shed any light and/or provide a solution it would be much appreciated! :)
0
paddycobbett
Asked:
paddycobbett
  • 2
  • 2
1 Solution
 
MarcLightCommented:
just try to replace each single quote with doubble quote and double with single

<CFSET j='SELECT * FROM web_data WHERE selector IN ("ul", "ul li")'>
<CFQUERY datasource="XXX" name="qry">
#j#
</CFQUERY>

-or-

use PreserveSingleQuotes(Variable)
it is for SQL statements
0
 
paddycobbettAuthor Commented:
Your first suggestion didn't work for me but PreserveSingleQuotes(Variable) worked a dream :)

Thanks alot!
0
 
_agx_Commented:
> Notice that coldfusion is inserting an additional single-quote (') for each single-quote occurrance.

That's for your protection. CF automatically escapes single quotes to protect against a common type of sql injection. PreserveSingleQuotes() dismantles that protection.  Unless you are using MS Access, PreserveSingleQuotes() is a _very_ bad idea because it makes the db vulnerable to sql injection.

You really should be using cfqueryparam for a few reasons

a) you don't have to worry about quoting
b) you mitigate sql injection
c)  performance benefits for db's that support bind variables
d) extra type checking

Simply write your query with cfqueryparam

<!--- change cfsqltype if needed --->
SELECT * FROM web_css_rules
WHERE selector IN
( <cfqueryparam value="#listOfValues#" cfsqltype="cf_sql_varchar" list="true" /> )
0
 
paddycobbettAuthor Commented:
Thanks _agx_, i did always suspect that Coldfusion had a very good reason for behaving that way and was likely related to sql injection attacks, now i understand perfectly why! Will rearrange my code to use <cfqueryparam>, must admit i haven't used that tag but will from now on! Thanks again
0
 
_agx_Commented:
Yes, it confused me at first too. I really wish the docs would make a point of emphasizing it is *deliberate*.. so we developers would know straight away it is a security feature _not_ a bug. (And I do not mean the bad kind of feature either ;-)

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.

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