Pete Freitag Pete Freitag


Published on July 24, 2008
By Pete Freitag

If you haven't been using the cfqueryparam tag, chances are you had a baptism by fire this week. As you may have heard, lots of ColdFusion powered sites were targeted by hackers using SQL Injection this week.

Fortunately SQL Injection is very easy to prevent in CFML using the cfqueryparam tag, and many people have pointed out some of the simple use cases for the tag. But there are a few cases where you can't use the cfqueryparam tag. In those cases, ColdFusion might throw an exception that looks like this:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P1'.
[Macromedia][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.

Let's take a look at some of these special cases, and how to get around them:


If you are passing a variable into a SELECT TOP statement, you can't use cfqueryparam, instead consider using the Val function. This is a really handy function that will return 0 whenever it gets a non-numeric value. The Val function still allows decimal values, we can convert those to an integer using the Ceiling function, which rounds up to the nearest integer.

SELECT TOP #Ceiling(Val(url.max_rows))# first_name FROM people


When attempting to use a cfqueryparam tag in the ORDER BY statement you might receive an error such as:

[Macromedia][SQLServer JDBC Driver][SQLServer]The SELECT item identified
by the ORDER BY number 1 contains a variable as part of the expression
identifying a column position. Variables are only allowed when ordering
by an expression referencing a column name.

A good way to get around this limitation is to use the ListFindNoCase function, to limit the sortable column names, for example:

<cfset sortable_column_list = "age,height,weight,first_name">
<cfquery ...>
  SELECT first_name, age, height, weight
  FROM people
  ORDER BY <cfif ListFindNoCase(sortable_column_list, url.sort_column)>#url.sort_column#<cfelse>first_name</cfif>

Passing Value Lists using IN

What to do when your variable contains a list of values to be used with a SQL IN expression? The cfqueryparam actually makes it very easy to pass a list, you don't even need to put single quotes around each element if for text lists, it takes care of that for you. To use cfqueryparam with an IN simply add list="true" to your cfqueryparam tag.

<cfset name_list = "Bob,Fred,Pete">
<cfquery ...>
  SELECT first_name, age, height, weight
  FROM people
  WHERE first_name IN (<cfqueryparam value="#name_list#" list="true" cfsqltype="cf_sql_varchar">)

Cached Queries

ColdFusion 8 now allows cfqueryparam in cached queries, but if you are running earlier versions, you won't be able to use it with cached queries.

If the variables passed into the query are integer only, then you can use the Val function to protect against SQL Injection. Or if the possible string values are limited you can use the ListFindNoCase function as shown above.

The best workaround is to remove the caching, upgrade to CF8, or cache them in the application scope, as follows:

<cfif NOT IsDefined("application.my_cached_query")>
  <cfquery name="application.my_cached_query">

This will keep the query cached until the application is reinitialized, or the variable is overwritten.

Passing NULL's

The cfqueryparam lets you pass null values into your database using the null="true" attribute. For example:

UPDATE people
SET age = <cfif IsValid("integer", form.age) AND form.age NEQ 0>
  <cfqueryparam value="#form.age#" cfsqltype="cf_sql_integer">
  <cfqueryparam null="true" cfsqltype="cf_sql_integer">

Those are some of the more common gotcha's that you will run into with cfqueryparam. Please post a comment with any other cfqueryparam tricks, or special cases.

cfqueryparam cfml coldfusion security sql injection sql cfquery

Mastering CFQUERYPARAM was first published on July 24, 2008.

If you like reading about cfqueryparam, cfml, coldfusion, security, sql injection, sql, or cfquery then you might also like:


The Fixinator Code Security Scanner for ColdFusion & CFML is an easy to use security tool that every CF developer can use. It can also easily integrate into CI for automatic scanning on every commit.

Try Fixinator

The weekly newsletter for the CFML Community


Good post Pete. ColdFusion Muse has a couple of equally good posts on this subject
by William from Lagos on 07/24/2008 at 7:10:19 PM UTC
Thanks William, didn't see those!
by Pete Freitag on 07/25/2008 at 7:47:26 AM UTC
As always, good stuff Pete!

I've also begun adding Val() to my CFQueryParam's. I.e., <cfqueryparam value="#val(form.age)#" cfsqltype="cf_sql_integer">
by Steve Withington on 07/25/2008 at 10:33:44 AM UTC
Thanks Steve, using Val in your cfqueryparam's not a bad idea because it prevents exceptions from being thrown when users try to pass invalid data. I'm sure that's why you are doing it, I just wanted to point that out for everyone else's benefit.

Of course you can/should also validate your data before getting to the query.
by Pete Freitag on 07/25/2008 at 10:45:16 AM UTC
The neater way to use the null attribute is this:

UPDATE people
SET age = <cfqueryparam value="#form.age#" null="#not IsValid("integer", form.age) OR form.age EQ 0)#" cfsqltype="cf_sql_integer" />

If the expression evaluates to TRUE, the null overrides the value.
by Seb on 07/25/2008 at 10:52:52 AM UTC
Neat Trick Seb. I didn't know the null attribute would override the value attribute when true.
by Pete Freitag on 07/25/2008 at 11:34:17 AM UTC
Beware of information exposure. Many times I see sites that pass actual column names on the url string as part of a sort field or a table id column. This just give more information to hackers if they decide to target your site. Instead pass variables that are not the name of tables or columns and us code to handle the rest. ie. Sorting
select *
from products
order by
<cfswitch expression="#url.sortby#">
<cfcase value="pname">
<cfcase value="price">
By doing this your not exposing table or column names to the world. The lesson is to limit your exposure.
Wil Genovese
Sr. Web Application Developer
by WilGeno on 08/07/2008 at 8:42:12 PM UTC
In your code examples you have list="true" and in another place null="true" shouldn't these be list="yes" and in the other place null="yes"? I'm new to this and really don't know. Thanks
by Larry Rogers on 08/24/2008 at 4:32:00 PM UTC
@Larry - I prefer using the string true/false for boolean values. ColdFusion typically allows either yes/no or true/false in boolean values, so it's just a personal preference.
by Pete Freitag on 08/25/2008 at 9:10:53 AM UTC
Good tip about the list="true" flag. Thanks!
by Adam Rifat on 10/26/2009 at 8:28:18 AM UTC
Peter - what is your take on using or not using CFSQLType attribute with CFQueryParam?
by Mike Henke on 07/28/2011 at 4:44:45 PM UTC
Love you Peter. This "Passing Value Lists using IN" part of the article just made my day!
by Rich F on 04/23/2015 at 5:25:27 PM UTC