?
Solved

Coldfusion: problem generating CFQUERY SQL

Posted on 2010-01-12
5
Medium Priority
?
350 Views
Last Modified: 2012-05-08
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
Comment
Question by:paddycobbett
[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
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
MarcLight earned 2000 total points
ID: 26291473
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
 
LVL 1

Author Comment

by:paddycobbett
ID: 26291709
Your first suggestion didn't work for me but PreserveSingleQuotes(Variable) worked a dream :)

Thanks alot!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 26295588
> 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
 
LVL 1

Author Comment

by:paddycobbett
ID: 26296203
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
 
LVL 52

Expert Comment

by:_agx_
ID: 26297714
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 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