Developing ColdFusion MX Applications with CFML
Accessing and Retrieving Data

Enhancing security with cfqueryparam

Some Database Management Systems (DBMSs) let you send multiple SQL statements in a single query. In many development environments-including ColdFusion, ASP, and CGI-URL or form variables in a dynamic query can append malicious SQL statements to existing queries. Be aware that there are potential security risks when you pass parameters in a query string.

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="CompanyInfo">

  SELECT FirstName, LastName, Salary
  FROM Employee
  WHERE LastName='#Form.LastName#'
</cfquery>

Someone could call this page with the following malicious URL:

http://myserver/page.cfm?Emp_ID=7%20DELETE%20FROM%20Employee

The result is that ColdFusion tries to execute the following query:

<cfquery name="GetEmployees" datasource="CompanyInfo">

  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 CompanyInfo data source is an integer, you specify a cfsqltype of cf_sql_integer:

<cfquery name="EmpList" datasource="CompanyInfo">

  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 containing a string to a query, specify a cfsqltype of cf_sql_char, as in the following example:

<cfquery name = "getFirst" dataSource = "cfsnippets">

  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 quotes so that it appears as a single value to the database. Even if you pass a bad URL, it appears as follows:

  WHERE LastName = 'Anwar 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:

BIGINT	BIT	CHAR	DATE
DECIMAL	DOUBLE	FLOAT	IDSTAMP
INTEGER	LONGVARCHAR	MONEY	MONEY4
NUMERIC	REAL	REFCURSOR	SMALLINT
TIME	TIMESTAMP	TINYINT	VARCHAR

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6

Comments are no longer accepted for ColdFusion MX. ColdFusion 8 is the current version.

Comments

Add Comment

No screen name said on Aug 9, 2004 at 12:56 PM :

I tried this and it does not work

<cfset name = "rpt_mem">
<cfset str = name.recordCount>

rpt_mem is a name of an existing query. But it denies it saying that coldfusion does not recognize the parameter.

halL said on Aug 10, 2004 at 1:03 PM :

Your error is in surrounding rpt_num in quotation marks.
The correct form is
<cfset name = rpt_mem>
The problem is that in your example the variable name is being assigned a string value, not a reference to the query object.

qwicker said on Sep 21, 2004 at 9:49 AM :

i noticed that the initial query has single quotes around the value to be inserted (WHERE LastName='#Form.LastName#'). would this not prevent the sql injection from appearing as 2 queries like the value returned using the cfqueryparam tag?

WHERE LastName = 'Anwar DELETE FROM MyCustomerTable'.

i think i will test this theory. i am trying to decide if the cfqueryparam tag is necessary for us - i have heard that it is a significant hit on processing time.

sweetnevil said on Oct 4, 2004 at 10:56 AM :

Is there a known map for DBMS-specific datatypes to cfSqlType values? Specifically, I'm wondering about date, time, and timestamp datatypes in the cfSqlType parameter against a datetime field in an MS SQL Server database. I don't suppose an answer will arrive here quickly, therefore I shall test... and post what I find. Thanks!

extdw_doc said on Oct 5, 2004 at 10:39 AM :

You can find a link to a matrix that maps
cfsqltype values to DBMS-specific data types at:
http://www.mail-archive.com/cf-talk@houseoffusion.com/msg157220.html

shawnse said on Oct 28, 2004 at 11:13 AM :

Trusting that the cfsqltypes types are based on JDBC SQL types (from the above posting), you can also get general information on most of the cfsqltypes at:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html

bizarrojack said on Feb 23, 2005 at 8:37 AM :

Qwicker - The cfqueryparam is obviously a little more work for the coldfusion engine, but do not let that fool you into thinking that using cfqueryparam is going to be slower. Indeed, it is possible, but is often just the opposite because of time saved by your RDBMS. I know this is true with oracle, but YMMV with another RDBMS, so I recommend investigating this: By using cfqueryparam, subsequent queries after the first one will be sped up significantly by eliminating time used by the query optimizer. cfqueryparam effectively puts "?" in the query, instead of submitting what is a unique query, in the eyes of the optimizer, so the optimizations are cached and this process can be skipped.

Clearly this depends on a number of factors, but I have seen, without question, a marked improvement in overall performance with cfqueryparam.

e.g.
select 'foo' from table etc. etc.
and
select 'bar' from table etc. etc. run through the query optimizer twice,
but wth cfqueryparam it will be
select '?' from table etc. etc., once with foo and once with bar, and the latter query will not have to be processed by the query optimizer. This is not the best example, because optimizing this query is trivial, but I hope it conveys the general idea.

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/queryDB6.htm

Add Comment