Enhancing security with cfqueryparam

Some DBMSs let you send multiple SQL statements in a single query. However, hackers sometimes try to modify URL or form variables in a dynamic query by appending malicious SQL statements to existing parameters. Be aware of potential security risks when you pass parameters in a query string. These risks can exist in many development environments, including ColdFusion, ASP, and CGI. Using the  cfqueryparam  tag can reduce this risk.

About query string parameters

When you let a query string pass a parameter, ensure that only the expected information is passed. The following ColdFusion query contains a WHERE clause, which selects only database entries that match the last name specified in the LastName field of a form:

<cfquery name="GetEmployees" datasource="cfdocexamples"> 
SELECT FirstName, LastName, Salary 
FROM Employee 
WHERE LastName='#Form.LastName#' 

Someone could call this page with the following malicious URL:
The result is that ColdFusion tries to execute the following query:

<cfquery name="GetEmployees" datasource="cfdocexamples"> 
SELECT * FROM Employee 
WHERE Emp_ID = 7 DELETE FROM Employee 

In addition to an expected integer for the Emp_ID column, this query also passes malicious string code in the form of a SQL statement. If this query successfully executes, it deletes all rows from the Employee table'something you definitely do not want to enable by this method. To prevent such actions, evaluate the contents of query string parameters.

Using cfqueryparam

You can use the  cfqueryparam  tag to evaluate query string parameters and pass a ColdFusion variable within a SQL statement. This tag evaluates variable values before they reach the database. You specify the data type of the corresponding database column in the  cfsqltype  attribute of the  cfqueryparam  tag. In the following example, because the Emp_ID column in the cfdocexamples data source is an integer, you specify a  cfsqltype  of cf_sql_integer:

<cfquery name="EmpList" datasource="cfdocexamples"> 
SELECT * FROM Employee 
WHERE Emp_ID = <cfqueryparam value = "#Emp_ID#" 
cfsqltype = "cf_sql_integer"> 

The  cfqueryparam  tag checks that the value of Emp_ID is an integer data type. If anything else in the query string is not an integer, such as a SQL statement to delete a table, the  cfquery  tag does not execute. Instead, the  cfqueryparam  tag returns the following error message:
Invalid data '7 DELETE FROM Employee' for CFSQLTYPE 'CF_SQL_INTEGER'.

Using cfqueryparam with strings

When passing a variable that contains a string to a query, specify a cfsqltype value of cf_sql_char, and specify the maxLength attribute, as in the following example:

<cfquery name = "getFirst" dataSource = "cfdocexamples"> 
SELECT * FROM employees 
WHERE LastName = <cfqueryparam value = "#LastName#" 
cfsqltype = "cf_sql_char" maxLength = "17"> 

In this case,  cfqueryparam  performs the following checks:

  • It ensures that LastName contains a string.
  • It ensures that the string is 17 characters or less.
  • It escapes the string with single-quotation marks so that it appears as a single value to the database. Even if a hacker passes a bad URL, it appears as  follows:WHERE  LastName = 'Smith DELETE FROM MyCustomerTable'.

Using cfSqlType

The following table lists the available SQL types against which you can evaluate the value attribute of the  cfqueryparam  tag:






















Specifying the cfsqltype attribute causes the DBMS to use bind variables, which can greatly enhance performance.


Get help faster and easier

New user?

Adobe MAX 2024

Adobe MAX
The Creativity Conference

Oct 14–16 Miami Beach and online

Adobe MAX

The Creativity Conference

Oct 14–16 Miami Beach and online

Adobe MAX 2024

Adobe MAX
The Creativity Conference

Oct 14–16 Miami Beach and online

Adobe MAX

The Creativity Conference

Oct 14–16 Miami Beach and online