COMMAND

    ColdFusion

SYSTEMS AFFECTED

    - 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

PROBLEM

    Following is based on Allaire Security Bulletin.  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.

    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.

SOLUTION

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