Allaire Security Bulletin (ASB99-04)
Multiple SQL Statements in Dynamic Queries
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 [4]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:
SELECT * FROM MyCustomerTable
WHERE Customer_ID = #ID_VAR#
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 [5]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 [6]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):
Response to error (e.g. redirection) goes here
SELECT * FROM MyCustomerTable
WHERE Customer_ID = #ID_VAR#
Alternatively, the Val() function can be used inside the query. If the
variable is a number it will return the value, if isn't a number it
will return a zero.
SELECT * FROM MyCustomerTable
WHERE Customer_ID = #Val(ID_VAR)#
Obviously, in this case the record set would not return any rows
assuming there is no Customer_ID 0.
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 [7]secure@allaire.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: [8]http://www.allaire.com/security/
THE INFORMATION PROVIDED BY ALLAIRE IN THIS BULLETIN IS PROVIDED "AS
IS" WITHOUT WARRANTY OF ANY KIND. ALLAIRE DISCLAIMS ALL WARRANTIES,
EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL ALLAIRE
CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER
INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF
BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF ALLAIRE CORPORATION OR
ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR
CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY
NOT APPLY.
< a l l a i r e >
Copyright © 1995-99 Allaire Corp., All rights reserved.
[9]Site problems? [10]Service questions? [11]Privacy Policy
References
1. LYNXIMGMAP:http://www1.allaire.com/handlers/index.cfm?ID=8728&Method=Full#allaireHome
2. LYNXIMGMAP:http://www1.allaire.com/handlers/index.cfm?ID=8728&Method=Full#tools
3. javascript:history.back()
4. http://www1.allaire.com/handlers/index.cfm?id=11069&method=full
5. http://www1.allaire.com/handlers/index.cfm?id=11069&method=full
6. http://www1.allaire.com/handlers/index.cfm?id=11069&method=full
7. mailto:secure@allaire.com
8. http://www.allaire.com/security/
9. mailto:webmaster@allaire.com
10. mailto:info@allaire.com
11. http://www.allaire.com/privacy/