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! :)
LVL 1
paddycobbettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.