ColdFusion Article

Securing Database Access Using the cfqueryparam Tag

Hardly a day goes by without news of another security problem on the Internet, but many ColdFusion developers tend to think of security as something best left to system administrators.

But while it’s important to securely configure servers, it’s also vitally important to write applications with security in mind. One of the most common and most serious security problems within ColdFusion applications is failing to validate all data sent from a browser. A third party with access to network traffic between the browser and server, or more likely, a malicious end-user can easily tamper any data sent from a browser before it reaches your server. In addition to security concerns, programming best practices ensures that your application receives only acceptable input values.

How can you accomplish this easily? The answer, in most cases, is to use the cfqueryparam tag. This tag, introduced in ColdFusion 4, separates parameters from the surrounding SQL. This has two benefits: it allows the database’s SQL analyzer to more efficiently handle the SQL statement in many cases, and it validates data for the parameters. Think of the cfqueryparam tag as a two-for-one deal benefit. It gives you better performance and better security.

Requirements

ColdFusion MX

• Try
• Buy

What Could Possibly Go Wrong Without Using the cfqueryparam Tag?

It’s pretty easy to demonstrate the security problems inherent in passing unfiltered data to your database. Say you use Microsoft SQL Server and you built a master-detail interface, something common in nearly every ColdFusion application. On the master page, you might have some code that passes a surrogate primary key to the detail page:

<cfoutput query="qGetItems">
  <a href="showitem.cfm?itemid=#qGetItems.ItemID#">
    #qGetItems.ItemName#
  </a><br>
</cfoutput>

Then, on your detail page, you might have a query like this:

<cfquery name="qGetItemDetail" datasource="...">
	SELECT	*
	FROM	Item
	WHERE	ItemID = #URL.ItemID#
</cfquery>

Carefully examining the code above shows that that the detail page expects the variable, URL.ItemID, to be an integer. This is a reasonable expectation, given the construction of the hyperlink on the master page. However, there’s nothing to stop someone from changing the value of URL.ItemID by simply typing in a different value in the browser address:

Figure 1. An end user can tamper the URL parameter value easily by editing the address.

At this point, there are many things that the end user might enter:

• A different valid number
• A number that doesn’t match any item.
• A value that is something altogether different.

The "something altogether different," when the end user intends to attack your database, is commonly referred to as a SQL injection attack. What follows is a common SQL injection attack used against SQL Server:

In the above example, the address has a valid URL parameter for the primary key value, itemid. But its value is a big string of gibberish. The contents instruct the database server to create a new local user account. By calling the xp_cmdshell system stored procedure, the end user instructs SQL Server to do all sorts of wacky things to do anything that you could do from a command prompt. This end users does all of these actions using whatever set of permissions the SQL Server service has, typically LocalSystem! Naturally, this is a very dangerous security breach.

To build this SQL injection attack, the end user uses double quotes instead of single quotes to wrap the command line he wants to execute. He encodes spaces encoded using the appropriate URL encoding (%20). This example uses a URL parameter, but the end user could do the same thing with form data or cookies, if your app uses them within a SQL statement. For example, the text below is the actual text that a browser might send to a server when a browser submits a form:

POST /some_action.cfm HTTP/1.1
Referer: http://www.myserver.com/some_form.cfm
Accept-Language: en-us
Content-Type: application/x-www-form-urlencoded
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; Q312461)
Host: www.myserver.com
Content-Length: 80
Connection: Keep-Alive
Cache-Control: no-cache
Cookie: CFID=54; CFTOKEN=cde27b%2De40fcb11%2D0f2c%2D49ef%2Dbca2%2D74456ee518c5


quantity=1&productID=1&productName=Aero&productPrice=123.0000&method=Add+to+Cart

An end user could also send the same text directly to the server without using a browser at all. Using a common telnet client would be sufficient; the end user could change any information beforehand. In short, a user can change any data from a browser. If you plan to use any of user inputted data in your SQL, sanitize it first.

Using the cfqueryparam Tag

How can you easily prevent the hack I just described above? Use cfqueryparam tag as follows:

<cfquery name="qGetItemDetail" datasource="...">
  SELECT	*
  FROM	Item
  WHERE	ItemID = <cfqueryparam
				  cfsqltype="CF_SQL_INTEGER"
				  value="#URL.ItemID#">
</cfquery>

In the above example, using the cfqueryparam tag ensures that the value sent to the database contains an integer and nothing else. If you enable debug output, the following appears:

Figure 3. Using debut output with the cfqueryparam tag.

Note that the SQL statement has no literal value; a placeholder replaces it. The literal value is listed after the SQL statement, as a parameter. This construction is called a prepared statement and the variables listed at the end are called bind parameters.

What happens now if the end user passes an invalid value? The end user will receive an error message, which is preferable to letting him run SQL commands of his choice in your database.

Basic cfqueryparam Tag Syntax

The syntax for the cfqueryparam tag is pretty straightforward; there are two required attributes which specify the datatype and the value. The cfsqltype attribute specifies the datatype. Set it to one of the types listed in the documentation, such as CF_SQL_INTEGER or CF_SQL_VARCHAR. Set the value attribute to the passed value you want to test.

You can use the list attribute in the cfqueryparam tag to pass lists to your queries; you can use this attribute to filter variables you want to use with an IN or EXISTS SQL clause.

You can use the maxlength attribute in the cfqueryparam tag to specify the maximum length for character strings passed to your queries. When passing floating point values, use the SCALE attribute in the cfqueryparam tag to specify their precision.

One other neat trick you can do with cfqueryparam is using the null attribute to pass null values to the database. Normally, if you wanted to pass a null within your SQL, you might write code like this:

<cfquery ...>
  UPDATE	Item
  SET ItemName = '#Trim(Form.ItemName)#',
	  ItemDesc = <cfif Len(Trim(Form.ItemDesc))>
						'#Trim(Form.ItemDesc)#'
				 <cfelse>
						null
				 </cfif>
  WHERE	ItemID = #Form.ItemID#
</cfquery>

This code would pass a null to the database if the user didn’t enter a value into the form field. Using cfqueryparam, you could rewrite the code as follows:

<cfquery ...>

	UPDATE	Item
	SET	ItemName = <cfqueryparam
					cfsqltype="CF_SQL_VARCHAR"
					value="#Form.ItemName#">,
					ItemDesc = <cfqueryparam
								cfsqltype="CF_SQL_VARCHAR"
								value="#Form.ItemDesc#"
								null = "#YesNoFormat(NOT Len(Trim(Form.ItemDesc)))#">
	WHERE ItemID = <cfqueryparam
					cfsqltype="CF_SQL_INTEGER"
					value="#Form.ItemID#">
</cfquery>

Caching with the cfqueryparam Tag

One limitation of cfqueryparam is that you can’t use the cachedwithin or cachedafter attributes of the cfquery tag when using the cfqueryparam tag. Using that combination will cause an error message. However, you can work around this by caching the Query variable within a persistent memory scope (Session, Application or Server), or by using the Query-of-Query functionality in ColdFusion MX.

Performance and the cfqueryparam Tag

As mentioned earlier, the cfqueryparam tag may enhance the performance of your SQL statements. Many common database servers can more quickly build a plan for executing your SQL when you build a prepared statement, and they may also cache that plan for later use.

Going One Step Further

As you’ve seen, there are very good reasons to use cfqueryparam in any SQL statement you place within your CFQUERY tags. You may want to take this one step further, however, and call stored procedures directly, instead of including any SQL at all within your CFML code. For more on that, read Sam Neff’s article, Learning Stored Procedure Basics in ColdFusion MX. However, the advantage of cfqueryparam is that it lets you generate your SQL with CFML instead of needing to know exactly what that SQL should be in all cases. For example, building the SQL for a query-by-example interface is usually easier within CFML than within a stored procedure.

Ensure that you have secured your database server properly. For example, in an ideal configuration, your SQL Server should not permit anyone to run xp_cmdshell from your CFML pages. Of course, ideal configurations are rarer than they should be, and your application should be responsible for its own security wherever possible.

About the author

Submit feedback on our tutorials, articles, and sample applications.