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:
       <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 [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):
<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>

   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.
<CFQUERY DATASOURCE="MySQLDB" NAME="Example">
        SELECT * FROM MyCustomerTable
        WHERE Customer_ID = #Val(ID_VAR)#
</CFQUERY>

   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/