Mastering ColdFusion's CFQUERYPARAM

Updated July 10, 2025, First Published July 24, 2008 by Pete Freitag

If you haven't been using the cfqueryparam tag, chances are you have learned about it the hard way. As you may have heard, lots of ColdFusion powered sites were heavily targeted by hackers using SQL Injection this week.

Fortunately SQL Injection is very easy to prevent in CFML using the cfqueryparam tag. There are however 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:

SELECT TOP

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

ORDER BY

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 name="people">
    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>
</cfquery>

While the above code would be safe, something about it doesn't smell right to me. Yes having pounds in the query without a cfqueryparam is a pungent code smell to myself and many others. So I prefer to explicitly list out the cases using a switch case statement:

<cfquery name="people">
    SELECT first_name, age, height, weight
    FROM people
    ORDER BY 
    <cfswitch expression="#url.sort_column#">
        <cfcase value="age">
            age
        </cfcase>
        <cfcase value="height">
            height
        </cfcase>
        <cfcase value="weight">
            weight
        </cfcase>
        <cfdefaultcase>
            first_name
        </cfdefaultcase>
    </cfswitch>   
</cfquery>

Using cfqueryparam with lists

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 for a list 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">)
</cfquery>

One thing to be aware of is that depending on the database type you may hit a limit on the number of values that can be in the list. On SQL Server there is a limit of 2100 parameters per query, documented here. This limit applies to more than just the number of list values, it is the total number of parameters, so it counts each list item, and each non list cfqueryparam would also count as one.

Using maxlength

The cfqueryparam tag has a maxlength attribute which accepts an integer. Suppose you have a email field defined as varchar(250), you can specify maxlength="250" when querying this field.

<cfquery>
  SELECT *
  FROM people
  WHERE email = <cfqueryparam value="#form.email#" maxlength="250" cfsqltype="varchar">
</cfquery>

When you exceed the maxlength of the cfqueryparam you'll get an exception like this:

coldfusion.tagext.sql.QueryUtils$InvalidDataException: Invalid data value PassedValue exceeds maxlength setting 250

Or on Lucee you might see something like this:

lucee.runtime.exp.DatabaseException: value [PassedValue] is too large, defined maxlength is [250] but binary length of value is [300] at lucee.runtime.tag.QueryParam.check

If you want to avoid these types of errors you can also use the left function, like this:

<cfquery>
  SELECT *
  FROM people
  WHERE email = <cfqueryparam value="#left(form.email, 250)#" maxlength="250" cfsqltype="varchar">
</cfquery>

In this example the form.email value would be clipped to the first 250 characters. If you do, end up clipping the value with the left function, you'll need to be careful about logical implications of this. If you clip the email in one place or not another this might introduce a flaw, so make sure you are consistent with this approach.

Cached Queries with cfqueryparam

As of ColdFusion 8 and above cfqueryparam works with cached queries. If you are running earlier versions, you won't be able to use cfqueryparam 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">
    ...
  </cfquery>
</cfif>

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

Passing NULL's

The cfqueryparam tag 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">
<cfelse>
  <cfqueryparam null="true" cfsqltype="cf_sql_integer">
</cfif>

The above can also be simplified like this:

UPDATE people
SET age = <cfqueryparam value="#form.age#" cfsqltype="integer" null="#isNumeric(form.age)#">

Finding and Fixing missing cfqueryparam's

Finding and fixing missing cfqueryparam's or or rather code that is vulnerable to SQL Injection can be a tedious task. Fortunately, there are tools which can make this more bearable. My company makes a ColdFusion code scanning tool called Fixinator, which can scan your code to locate code that is vulnerable to SQL Injection.

Not only can Fixinator locate missing cfqueryparam tags in your ColdFusion code, Fixinator can even auto fix them for you. If you have a lot of queries that are missing cfqueryparam tags, Fixinator is a great way to speed up the remediation process.

Tips for cfsqltype

You might find it useful to learn that the cfsqltype attribute is optional, and as of ColdFusion 11 and up the cf_sql_ prefix is optional on the cfsqltype attribute values. So instead of typing cf_sql_integer you can just use integer now.

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.