Copy link to clipboard
Copied
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.
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
...Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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...?
Copy link to clipboard
Copied
Sorry for the repeat post, I was clearly leaning over Adam's shoulder copying his homework.
Copy link to clipboard
Copied
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> | ||||||||
| ||||||||
Resources: |
Copy link to clipboard
Copied
And what happened when you tried my suggestion that Adam copied from me?
Copy link to clipboard
Copied
Cheeky sod.
--
Adam
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I'm working through the examples. I may need to change my code to suit the example.
Copy link to clipboard
Copied
djkhalif wrote:
I may need to change my code to suit the example.
Interesting methodology.
Copy link to clipboard
Copied
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> | ||||||||
|
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Dan,
I set CF Admin to Select only.
Thanks,
djkhalif
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
This works!!!
<cfif IsDefined('form.query')>
<cfquery name="queryDemo" datasource="TestProduction">
#preserveSingleQuotes(form.goQuery)#
</cfquery>
</cfif>
Thanks!!!!