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.