User Guide

Enhancing security with cfqueryparam 473
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
</cfquery>
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, you must 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">
</cfquery>
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">
</cfquery>
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'.