Query Builder Form

[djkhalif]

Hi all,

I built a simple query builder form:

<cfif IsDefined('form.query')>
<cfquery name="queryDemo" datasource="TestProduction">
#form.goQuery#
</cfquery>
</cfif>

FORM

<ul>
<cfform name="QueryForm" action="" method="post">
<li><label>Type Query</label></li>
<li><cftextarea name="goQuery" cols="100" rows="6"></cftextarea></li>
<li><cfinput name="query" type="submit" value="Run Query"/></li>
</cfform>
</ul>
<cfif IsDefined('form.query') and queryDemo.recordCount gt 0>
<cfform action="" name="resultsForm">
<cfgrid query="queryDemo" griddataalign="center" name="QueryResults" width="700" autowidth="yes" height="700">
</cfgrid>
</cfform>
</cfif>

When I run a "where" clause such as where type = 'LCR', I get an error. Has anyone tried this before? Please provide insight.

Thanks.

[Adam Cameron.]

I get an error. Has anyone tried this before? Please provide insight.

Some initial insight:  when saying you get an error, it's always helpful to post the error message to give us some idea of what's going wrong 😉

However I presume the problem is because your WHERE clause has single quotes in it, which <cfquery> will escape, unless you tell it not to by using preserveSingleQuotes().

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6cbf.html

--

Adam

[Owainnorth]

Ah, one of those handy SQL Injection forms, you just don't get them enough these days

Look into the preserveSingleQuotes() function, that might sort your issue.  But unless you let us know what the error actually is...?

[Owainnorth]

Sorry for the repeat post, I was clearly leaning over Adam's shoulder copying his homework.

[djkhalif]

Error Code:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'LCR'.

The error occurred in C:\inetpub\wwwroot\cfAttica\betaFiles\queryDemo.cfm: line 3

1 : <cfif IsDefined('form.query')> 2 : <cfquery name="queryDemo" datasource="TestProduction"> 3 : #form.goQuery# 4 : </cfquery> 5 : </cfif>

VENDORERRORCODE   102 SQLSTATE   HY000 SQL   select item from tlkp_item where type = ''LCR'' DATASOURCE   TestProduction

Resources:

[Owainnorth]

And what happened when you tried my suggestion that Adam copied from me?

[Adam Cameron.]

Cheeky sod.

--
Adam

[Adam Cameron.]

SQL	select item from tlkp_item where type = ''LCR''

Right.  So does that SQL look valid to you?

As Owain told me to say: did you follow up the other half of my reply to you?  You know, the bit that explained the problem and - uncharacteristic for me (it must be the XMas spirit.  Or Xmas spirits) - pretty much gave you the solution?

--

Adam

[djkhalif]

I'm working through the examples. I may need to change my code to suit the example.

[Owainnorth]

djkhalif wrote:

I may need to change my code to suit the example.

Interesting methodology.

[djkhalif]

I get this:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]'#PreserveSingleQuotes' is not a recognized built-in function name.

The error occurred in C:\inetpub\wwwroot\cfAttica\betaFiles\queryDemo.cfm: line 4

2 : <cfset List = "'LCR'"> 3 : <cfquery name="queryDemo" datasource="TestProduction"> 4 : #form.goQuery# 5 : </cfquery> 6 : </cfif>

VENDORERRORCODE   195 SQLSTATE   HY000 SQL   select * from tlkp_itemTest where type IN (#PreserveSingleQuotes(LIST)#) DATASOURCE   TestProduction

[Adam Cameron. · Accepted solution]

OK, that's almost right but not quite.

It's not clear from the docs, but - like I mentioned above - <cfquery> escapes and single quotes in variables with its tags.  So there's no point in using preserveSingleQuote() outside the <cfquery> tag, because it'll have no useful effect because it won't actually achieve anything... <cfquery> will still see a variable, and still escape the single quotes within it.

You need to do this sort of thing:

<cfquery>

#preserveSingleQuotes(yourVar)#

</cfquery>

This will ensure CF won't escape the single quotes within yourVar.

--

Adam

[Dan_Bracuk]

PreserveSingleQuotes is correct, but incomplete.  Other things to consider are:

security - make sure Cold Fusion is using a read-only connection to your db.

Apostrophes - these will mess up your sql

error handling - I am almost adequate at writing queries but I still don't always type it correctly on my first attempt.

[djkhalif]

Dan,

I set CF Admin to Select only.

Thanks,

djkhalif

[ilssac]

djkhalif wrote:

I set CF Admin to Select only.

This is not a lot of security.  All ColdFusion can do is examine the string about to be sent to the database drivers for allowed and unallowed substrings.

Hackers are very good at figuring out ways to use alternate character sets and encodings to send disguised strings.

You really MUST set up very tight security on your database if you MUST allow open SQL like this to be used from a web interface.

[Adam Cameron.]

I would set the user that CF connects to the DB to to be read-only.  As Ian says... the security options in CF are... not particularly secure, and not difficult to defeat.

--

Adam

[Dan_Bracuk]

Server protection is another consideration. 

A slight error in the query can result in a cartestian join returning millions of rows.  Then the user gets impatient and clicks the submit button again, and again, and again.  This can bring your server up to 100% cpu in a hurry and then nobody can do anything.

[djkhalif]

This works!!!

<cfif IsDefined('form.query')>
<cfquery name="queryDemo" datasource="TestProduction">
#preserveSingleQuotes(form.goQuery)#
</cfquery>
</cfif>

Thanks!!!!