Best Reason to Use CFQueryParam: Avoid Odd SQL Parsing by ColdFusion
After banging my head against the wall for a couple hours over an odd application error, I've finally come up with the best, most definitive reason to use <cfqueryparam> absolutely 100% of the time: It helps you avoid odd--dare I say erroneous--SQL parsing performed by ColdFusion in the <cfquery> tag.
Huh? Say again?
That's right. In my experience, anyway, the <cfquery> tag will erroneously parse the SQL and tag legitimate content as disallowed SQL. Let's take a look at an example.
Here's a basic query that inserts some HTML content into a field:
-
<cfquery name="insertStuff" datasource="myDS">
-
INSERT INTO myTable (fldTitle,fldContent)
-
VALUES ('Title','· Grant Deadline Extended')
-
</cfquery>
Never mind why you might do this; the point is that it is allowable SQL. There's just an HTML special entity in the string. However, ColdFusion 8 sees the semicolon followed by the word "GRANT", and if the GRANT statement is disallowed for the ColdFusion data source, ColdFusion will throw an error similar to: "Error Executing Database Query. Executing the SQL statement is not allowed."
Of course, there is no error in the SQL, and the same statement executes just fine when you run it at the command line or SQL Query Analyzer. ColdFusion is doing a security check by parsing the SQL and attempting to verify that the SQL statement(s) only use allowed SQL as defined for the data source in ColdFusion Administrator.
If a SQL keyword is not immediately following the semicolon, ColdFusion won't make this mistake. And naturally, if your data source is configured to allow the offending keyword (in my example, "GRANT"), it will execute the SQL and it will execute properly, not actually executing the grant statement.
But if my code had used <cfqueryparam>, this wouldn't have been an issue:
-
<cfquery name="insertStuff" datasource="myDS">
-
INSERT INTO myTable (fldTitle,fldContent)
-
VALUES (
-
<cfqueryparam value="Title" cfsqltype="cf_sql_varchar">,
-
<cfqueryparam value="· Grant Deadline Extended" cfsqltype="cf_sql_varchar">
-
)
-
</cfquery>
This code will happily pass the SQL on to the database server.
This scenario may be a bit apparent when presented cleanly as I have here, but when the string being passed in is 3,000 characters or something similar, you're banging your head on the wall wondering what the heck is wrong with your query, especially when it has been working for years and now fails on just a single particular set of data. To my credit, the offending code is about 6 years old and all of my queries do indeed use <cfqueryparam> now. And more than ever, I'm now convinced to continue that habit. 
This oddity probably comes up rarely, but I'm sure someone else has or will experience this. I hope this post will help you.
October 29th, 2008 at 2:32 pm
Nice article.
Something similar happens on the database side with MySQL. It turns out that MySQL uses the backslash as an escape character so having as an example, C:\some\directory in a string literal in an insert query will cause a similar problem at the database level.
I believe there is a way to escape them, but why put yourself through the hassle of doing something extra when it’s going to make your code less portable (i.e. MySQL-specific).
You could instead use cfqueryparam and that will keep your code portable and take care of the escaping for you.
Or just pick an ORM tool like DataFaucet that will put all the cfqueryparam tags in for you and make the application more portable at the same time.
October 30th, 2008 at 11:41 pm
thanks mate. had the same problem and kept looking in Cf administrator and sql permissions. added queryparam and worked straight away. i’ll use it religiously from nw on. thanks again for the post..
January 12th, 2009 at 4:32 pm
We just saw this issue today after posting a new major revision and doing a import test, looks like someone forgot a cfqueryparam and it almost caused us to roll back the whole site.
August 28th, 2010 at 4:46 am
Thanks for the article … i like the way you write (this is what ive got, this is the answer). We we’re having repeated trouble with our CMS when using basic punctuation, and more and more single quotes would be added each time the item was saved. Using cfqueryparam fixed it nicely, and now we have all our field matched to mysql input types. very good. as you were…
April 11th, 2011 at 8:03 am
forever in your debt for saving my Monday morning!
April 11th, 2011 at 10:37 am
Glad to hear it!! This can be a real tough one to figure out!
April 21st, 2011 at 10:55 am
You just solved my problem! I’m using CF9. So this is still a problem.
April 29th, 2011 at 2:27 pm
Me too! I used it in conjuction with PreserveSingleQuotes() as in
May 18th, 2011 at 9:32 am
I thought this was my solution to a problem where the narrative report of all but one officers is stored correctly in the database, but most of the reports of that single officer just will not save (CF8/MSSQL). Tried the above solution, but still the same results, so still scratching my head!!Suggestions welcome, other than firing that one officer! Obviously I have to sit down with him to try to figure out what he is doing differently than the others.
May 18th, 2011 at 9:42 am
It could be many things, but check to see if s/he is using any weird characters. For instance, sometimes when people type some content in Word and copy/paste it, all of the smart quotes and long dashes and so on get copied in, and sometimes your database or ODBC/JDBC connection won’t like that. Just one thing to check.
September 14th, 2011 at 3:47 pm
Thank you for the tip! Very helpful!!
September 15th, 2011 at 8:59 am
Thanks for posting. We had the same problem – fixing it now.
October 29th, 2011 at 9:28 pm
Thanks for the help. Now go get an ice pack for your head, haha
December 25th, 2011 at 9:06 am
You provide some very useful information. Thank you for sharing.