Originally Posted: February 4, 1999
Last Updated: June 1, 1999

Summary
Some databases, including Microsoft SQL Server and Sybase SQL Server, support the ability to send multiple SQL statements with each query. URL or form variables in a dynamic query in many development environments (e.g. ColdFusion, ASP, CGI, etc.) can, in some cases, be used to append malicious SQL statements to existing queries. Customers can protect themselves with proper coding techniques and database security configuration. This is not a security issue with ColdFusion itself. However, it's a feature of some popular database systems that ColdFusion customers should take measures to address in their applications. Customers using Microsoft Access may be subject to other similar security vulnerabilities created by the appending of VBA commands to SQL strings (see ASB99-09 for more information).

Issue
Many enterprise databases support the ability to send multiple SQL commands in a single query. This feature was originally designed to make it easier to build complex queries.

In a Web application there are often circumstances where queries are built dynamically using a variable that are passed on URLs or in forms. When this is the case, there is a risk that a malicious SQL statement could be appended to the variable.

In ColdFusion, this risk is only an issue if the variable in the query is a number not enclosed in quotations, or if the variable is a string that is processed in the query with the PreserveSingleQuotes() function.

For example, with the query:

       <CFQUERY DATASOURCE="MySQLDB" NAME="Example">
	   SELECT * FROM MyCustomerTable 
	   WHERE Customer_ID = #ID_VAR# 
       </CFQUERY>

The URL could be modified so that it reads:

       http://myserver/page.cfm?ID_VAR=7%20DELETE%20FROM%20MyCustomerTable

The unwanted SQL statement would be appended to the SQL query and passed with the rest of the statement. For obvious reasons, this exposes security issues if the database is not configured properly.

Microsoft Access users may be subject to other vulnerabilities, detailed in ASB99-09.

Affected Software Versions

• ColdFusion Server (all versions and editions) running with Microsoft SQL Server through ODBC
• ColdFusion Server 4.0 Enterprise Edition running with Sybase SQL Server through the native database driver

NOTE: This issue does not affect Microsoft Access users, but it may be an issue with other enterprise databases. Allaire has not investigated all of the possible database configurations that are used with ColdFusion. Access users are subject to a different vulnerability detailed here.

What Allaire is Doing

This issue is the result of a combination of intentional features provided by selected database vendors. Allaire has published an Allaire Technical Brief - Securing Databases for ColdFusion Applications that details some of steps you can take to secure your database. It is the responsibility of developers and DBAs to guard against undesirable SQL statements through coding practice and database security configuration.

What Customers Should Do
Customers need to write their code to validate variables that are passed into SQL statements, configure their database security properly, and use standard database application development practices such as stored procedures where appropriate to protect themselves. These are general requirements of production applications regardless of the development platform.

There are many ways to address the issues raised by the risk of malicious SQL statements being inserted into dynamic queries. For example, numeric variables, such as the one used in the example above, can be validated in several ways. Conditional code can be placed before the query in order to test the variable such as in the example below (note that this example uses CFML 4.0 syntax, but it can be done with other versions of CFML):

<CFIF IsDefined("ID_VAR")>
	<CFIF Not IsNumeric(ID_VAR)>
		Response to error (e.g. redirection) goes here
	</CFIF>
</CFIF>

<CFQUERY DATASOURCE="MySQLDB" NAME="Example">

	SELECT * FROM MyCustomerTable 
	WHERE Customer_ID = #ID_VAR# 
</CFQUERY>

<CFQUERY DATASOURCE="MySQLDB" NAME="Example">
	SELECT * FROM MyCustomerTable 
	WHERE Customer_ID = #Val(ID_VAR)# 
</CFQUERY>

It is important to note that each individual application may require its own particular steps in both coding and database configuration in order to be fully secured. Many of the techniques for securing database applications built with ColdFusion are detailed in the Allaire Technical Brief - Securing Databases for ColdFusion Applications.

Revisions
February 4, 1999 -- Bulletin first released.
June 1, 1999 – updated to reference Access issues addressed in ASB99-09.

Reporting Security Issues
Allaire is committed to addressing security issues and providing customers with the information on how they can protect themselves. If you identify what you believe may be a security issue with an Allaire product, please send an email to secure@macromedia.com. We will work to appropriately address and communicate the issue.

Receiving Security Bulletins
When Allaire becomes aware of a security issue that we believe significantly affects our products or customers, we will notify customers when appropriate. Typically this notification will be in the form of a security bulletin explaining the issue and the response. Allaire customers who would like to receive notification of new security bulletins when they are released can sign up for our security notification service.

For additional information on security issues at Allaire, please visit: http://www.macromedia.com/security/