cfquery fails with conditional query stored in a variable

[Guest]

Following code doesnt work. Can anyone explain why?

<cfset myquery="select * from employee where id = '10'">
<cfquery name="a" datasource="mydsn">
#myquery#
</cfquery>

[Adam Cameron. · Accepted solution]

What do you mean by "doesn't work"?  You really ought to read this before posting: http://forums.adobe.com/thread/607238.

Is the ID really a string-oriented column?  I would expect it to be an integer, so it oughtn't have the quotes.

Also, if you are doing what you're doing, if your variable has single quotes in it, you'll need to use preserveSingleQuotes() to stop CF escaping them.

--
Adam

[Guest]

My sincere apologies for not reading that thread before posting because of which my post which seemed fine to me doenst seem the same any longer.

Anyway, yes you are right that the column is a string oriented one because of which I am using single quotes. And you are right again about escaping single quotes which was the real problem. Once I used the function mentioned, it worked prefectly.

Thanks again.

[-__cfSearching__-]

Once I used the function mentioned, it worked prefectly.

While PreserveSingleQuotes() is the correct function to use to resolve that error, be sure you understand the security ramifications of using it. ColdFusion automatically escapes single quotes to help combat sql injection.  PreserveSingleQuotes() prevents that from happening.  So if your sql string includes user supplied values, it may be vulnerable to sql injection.

Message was edited by: -==cfSearching==-

[Dan_Bracuk]

Are you sure about that?  All of my sql injection test results suggest otherwise.

[-__cfSearching__-]

All of my sql injection test

results suggest otherwise.

As I mentioned on another thread, a quick search of google will turn up many articles that concur.  So your comments suggest that either your tests are incorrect, or perhaps you have a database that is not capable of executing multiple statements.  Please post more details about which database and what tests you are using.  Since it is rather trivial to reproduce, either something in your tests is very wrong or you are omitting some key details ...

Message was edited by: -==cfSearching==-

Message was edited by: -==cfSearching==-

[Dan_Bracuk]

To summarize, with MSSql, which does allow multiple queries in a cfquery tag, you can get a user entered query to execute if the datatype is numeric.  If the field was char or varchar, the form field would be treated as a single string.   It was awhile ago, but I think with date fields, the query would simply crash.

My tests also include javascript.  To make a long story short, those who rely only on cfqueryparam for security are unprotected.

But then again, maybe my tests were wrong.

[-__cfSearching__-]

To make a long story

short, those who rely only on cfqueryparam for security are

unprotected.

I disagree with that statement, and you do not really explain why you think it is unprotected. Could you provide an example of a query that is unprotected with cfqueryparam?

But then again, maybe my tests were wrong.

But .. the previous conversation was about your disagreement with my statement: "using PreserveSingleQuotes() makes queries insecure." If you are not able to easily reproduce that vulnerability in a simple test against MS SQL, then I would agree. Your tests are wrong.

http://www.coldfusionmuse.com/index.cfm/2008/7/21/query-string-with-cfqueryparam

-Leigh

[Dan_Bracuk]

Regarding:

Could you provide an example of a query that is unprotected with cfqueryparam?

insert into sometable

(somefield)

values

(<cfqueryparam value = "<script>code goes here;</script>">

[-__cfSearching__-]

insert into sometable

(somefield)

values

(<cfqueryparam value = "<script>code goes

here;</script>">

That is not sql injection. It is script injection which requires totally different protection methods.  As I am sure you know.  We were talking about a specific sql injection risk posed by using PreserveSingleQuotes().  http://en.wikipedia.org/wiki/SQL_injection

>> PreserveSingleQuotes() .... it may be vulnerable to sql injection.

...

>>>> Are you sure about that?  All of my sql injection test results

>>>> suggest otherwise.

... Are you suggesting PreserveSingleQuotes() IS safe from your script injection example?