paddycobbett
asked on
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]Incorrec
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! :)
<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]Incorrec
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! :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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"
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"
ASKER
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
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 ;-)
ASKER
Thanks alot!