Tuesday, March 3, 2009

SQL Injection in ColdFusion

Registry software
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.

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:

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).

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:

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.