Inspiring

Answered

Query Builder Form

Forum|Forum|15 years ago
December 21, 2010
4 replies
2686 views

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.

Advanced techniques

This topic has been closed for replies.

Correct answer Adam Cameron.

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:

#preserveSingleQuotes(yourVar)#

</cfquery>

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

--

Adam

D

djkhalifAuthor

Inspiring

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

A

Adam Cameron.Correct answer

Inspiring

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:

#preserveSingleQuotes(yourVar)#

</cfquery>

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

--

Adam

D

Dan_Bracuk

Inspiring

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.

D

djkhalifAuthor

Inspiring

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

Inspiring

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

A

Adam Cameron.

Inspiring

Cheeky sod.

--
Adam

Owainnorth

Inspiring

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

Inspiring

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

A

Adam Cameron.

Inspiring

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

Error Executing Database Query.

Error Executing Database Query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded