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/