Yes, Using CFQueryParam Can Protect Your Database From SQL Injection (Even With Strings!)

There have been a flurry of blog posts recently regarding SQL injection attacks and ideas on how to combat the evil-doers who create and use these malicious scripts.

Inevitably, CFQUERYPARAM is brought forward as a tool ColdFusion programmers can use to reduce the risk of being affected by a hacker's efforts. (If you don't know what I'm talking about, in essence, "these hackers" try to use malicious SQL statements by appending them to existing URL, FORM and other variables used in SQL queries.)

I also mentioned using CFQUERYPARAM in a previous post and showed some very simple examples of how I might use it along with other techniques to test for data-validity before passing the information to the database. However, after reading the responses on other posts, I believe there is still some confusion on how to use CFQUERYPARAM, especially as it pertains to strings.

When you use CFQUERYPARAM, you can:

  • Verify the data type of a query parameter
  • Bind variables in the SQL statement (for databases that support bind variables)
  • Specify other optional parameters for data validation

The above list items are accomplished by using the available attributes of CFQUERYPARAM, such as value, CFSQLType, maxLength, and others. Probably one of the most important attributes is the CFSQLType (which defaults to CF_SQL_CHAR). Using CFSQLType, you can bind a variable to a parameter type such as date, time, money, blob, clob, longvarchar, etc. Thus, if a variable does not match the parameter type, ColdFusion will generate an error and will not pass the data to the database. In addition, the maxLength attribute is used to set a maximum length of a variable. By using maxLength, ColdFusion checks the string length before it's sent to the database which also helps to prevent the use of malicious strings.

As long as you're using CFQUERYPARAM when inserting the data to your database, you can specify the data type for each form field in the SQL insert statement. Here's a listing of the available SQL type attributes available in CFQUERYPARAM to test the value attribute against:

BIGINT

BIT

CHAR

DATE

DECIMAL

DOUBLE

FLOAT

IDSTAMP

INTEGER

LONGVARCHAR

MONEY

MONEY4

NUMERIC

REAL

REFCURSOR

SMALLINT

TIME

TIMESTAMP

TINYINT

VARCHAR

By the way, according to livedocs, specifying the CFSQLType attribute can also greatly enhance performance.

So What Happens When I Use CFQUERYPARAM?
This is one of the best parts of using this tag, CFQUERYPARAM escapes the string with single-quotation marks so that it appears as a single value to the database. This is why it is so important to avoid using PreserveSingleQuotes() wherever possible, and is exactly why you will read that by using PreserveSingleQuotes() your application will become vulnerable to SQL injection attacks.

This is cool because let's say for example, an evil-doer is filling out a contact form and enters 'Withington;DROP%20DATABASE%20foo' into the last name field, ColdFusion will escape the string and the last name entry will appear as one long (and funky) last name (assuming the maxLength attribute was set to allow for such a long last name to begin with).

Here's a (very) simple contact form with the processing at the top of the page (not necessarily how I would do this, but it gets the point across nonetheless) to illustrate using CFQUERYPARAM and other data-validation techniques:


<cfsilent>
<!---------------------------------------------------------------------------------------------------------------

Document:        contactform.cfm
Author:            http://www.stephenwithington.com (Steve Withington)
Creation Date:    July 23, 2008

Purpose:        A (Very) Simple Contact Us form.

Revision Log:    
MM/DD/2008 - sjw - notes.

---------------------------------------------------------------------------------------------------------------->

</cfsilent>

<!--- scope default form values --->
<cfparam name="welcomeMessage" default="Welcome." />
<cfparam name="FORM.ContactName" default="" />
<cfparam name="FORM.ContactPhone" default="" />
<cfparam name="FORM.ContactEmail" default="" />
<cfparam name="FORM.ContactComments" default="" />

<!--- track form validation errors --->
<cfset REQUEST.errors=ArrayNew(1) />

<!--- If FORM has been submitted, then process the contents --->
<cfif IsDefined("InsertRecord") AND InsertRecord EQ "form1">

    <!--- validate required form field entries --->
    <cfif NOT len(trim(ContactName))>
        <cfset ArrayAppend(request.errors, "Name is required.") />
        <cfset welcomeMessage = 'Ooops!' />
    </cfif>
    <cfif NOT isValid("telephone", ContactPhone)>
        <cfset ArrayAppend(request.errors, "A valid Phone is required.") />
        <cfset welcomeMessage = 'Ooops!' />
    </cfif>
    <cfif NOT isValid("email", ContactEmail)>
        <cfset ArrayAppend(request.errors, "A valid Email is required.") />
        <cfset welcomeMessage = 'Ooops!' />
    </cfif>

    <!--- as long as there are no errors, process the form contents --->
    <cfif NOT ArrayLen(REQUEST.errors)>

    <cftry>
        <cfquery datasource="#APPLICATION.dsn#">
        INSERT INTO tblContactUs (ContactName
                , ContactPhone
                , ContactEmail
                , ContactComments)
        VALUES (
            <cfif IsDefined("ContactName") AND len(ContactName)>
            <cfqueryparam value="#ContactName#" cfsqltype="cf_sql_varchar" maxlength="50" />
            <cfelse>''</cfif>
            , <cfif IsDefined("ContactPhone") AND len(ContactPhone) AND isValid("telephone", ContactPhone)>
            <cfqueryparam value="#ContactPhone#" cfsqltype="cf_sql_varchar" maxlength="20" />
            <cfelse>''</cfif>
            , <cfif IsDefined("ContactEmail") AND len(ContactEmail) AND isValid("email", ContactEmail)>
            <cfqueryparam value="#ContactEmail#" cfsqltype="cf_sql_varchar" maxlength="255" />
            <cfelse>''</cfif>
            , <cfif IsDefined("ContactComments") AND len(ContactComments)>
            <cfqueryparam value="#ContactComments#" cfsqltype="cf_sql_clob" maxlength="1073741823" />
            <cfelse>''</cfif>
        )
        </cfquery>
        <cfcatch><cfoutput>#cfcatch.Message#</cfoutput></cfcatch>
    </cftry>
    
    <cfset welcomeMessage = 'Your information has been submitted. Thank you.' />
    
    </cfif>

</cfif>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Simple Contact Form Example</title>
</head>

<body>
<div id="contactForm">
<cfoutput><h3>#welcomeMessage#</h3></cfoutput>
    <cfform action="#CGI.SCRIPT_NAME#"
            format="html"
            enctype="multipart/form-data"
            method="post"
            name="form1"
            id="form1">

    <table align="center">
        <!--- if there are errors, then display them --->
        <cfif ArrayLen(REQUEST.errors)>
            <cfoutput>
                <tr>
                    <td align="right"> </td>
                    <td><h4>Please review the following issues:</h4>
                        <ul>
                            <cfloop index="intError" from="1" to="#ArrayLen(REQUEST.errors)#" step="1">
                                <li>#REQUEST.errors[intError]#</li>
                            </cfloop>
                        </ul></td>
                </tr>
            </cfoutput>
        </cfif>
        <tr>
            <td align="right"><label for="ContactName">Name:</label></td>
            <td><cfinput     type="text"
                            name="ContactName"
                            id="ContactName"
                            value="#FORM.ContactName#"
                            size="45"
                            required="yes"
                            message="Name is required."
                            tooltip="Enter your name." />
</td>
        </tr>
        <tr>
            <td align="right"><label for="ContactPhone">Phone:</label></td>
            <td><cfinput     type="text"
                            name="ContactPhone"
                            tooltip="Enter your phone number."
                            validate="telephone"
                            id="ContactPhone"
                            value="#FORM.ContactPhone#"
                            size="20" />
</td>
        </tr>
        <tr>
            <td align="right"><label for="ContactEmail">Email:</label></td>
            <td><cfinput     type="text"
                            name="ContactEmail"
                            id="ContactEmail"
                            value="#FORM.ContactEmail#"
                            size="45"
                            required="yes"
                            message="Please enter a valid Email Address."
                            validate="email"
                            tooltip="Enter your email address." />
</td>
        </tr>
        <tr>
            <td align="right"><label for="ContactComments">Comments:</label></td>
            <td><cftextarea name="ContactComments" cols="40" rows="5" id="ContactComments" tooltip="Enter your comments." value="#FORM.ContactComments#"></cftextarea></td>
        </tr>
        <tr>
            <td align="right"> </td>
            <td><cfinput type="submit" name="btnSubmit" label="Submit" value="Submit" validate="submitonce" />
                 
                <cfinput type="reset" name="btnReset" label="Reset" value="Reset" /></td>
        </tr>
    </table>
    <cfinput type="hidden" name="InsertRecord" id="InsertRecord" value="form1" />
    </cfform>
    <script type="text/javascript" language="JavaScript">
        document.forms['form1'].elements['ContactName'].focus();
    </script>
</div>
</body>
</html>

By the way, if you need help with query string concatenation, Mark Kruger talks about fixing concatenated query strings in his recent post on Combining Query Strings and CFQUERYPARAM, for those who might be interested. There's also a decent post at rabidGadfly about using CFQUERYPARAM with LIKE and IN Clauses.

Don't Stop There
As you'll find in the example above, I like to use both client and server side validation on form submissions. For example, when using CFForm, I'll use the 'validate' attribute of a CFInput field to test whether or not the actual input passes through the first door. Then on the server side, I'll check the form fields again using functions such as IsValid, IsNumeric, IsBoolean, etc. to check the input again for both required fields as well as data-validity.

Also, does it really hurt to wrap your SQL statements that contain the CFQUERYPARAM with CFIF statements to test again for data-validity before even passing the data in for execution? Some might see it as additional code to maintain, but I see it as one more layer of protection as long as it's used properly. Call me paranoid.

Don't Forget to Test
In my previous post on protecting your database from SQL injection, I also recommend testing as an additional step to reduce your risk of being affected by SQL injection efforts and mentioned HP's Scrawlr as a tool to help detect vulnerabilities. Since my previous post, I've learned about an additional tool from Brad Wood (aka Coder's Revolution) called QueryParam Scanner by Peter Boughton that is also worth investigating. Don't forget to simply test the application yourself, or better yet, have others test it too. Try hacking your own application. That's essentially what some of the automated tools are trying to do, exposing the weaknesses or vunlerabilities of your application so you can then take the appropriate steps to strenghten it or close the holes so to speak.

At the End of the Day
If there's a database behind your web site, as a programmer, I believe you have a responsibility to your clients to exercise due-diligence in your efforts to reduce the possibility of being affected by hackers efforts to effectively use malicious scripts against your sites. I understand the demands of project deadlines and the impact of implementing some best practices as it pertains to this topic. However, I believe it is irresponsible for programmers to simply ignore these potential risks and refuse to perform any data validation before passing the information on to the database.

If you take anything away from this article, please keep in mind there are a number of stops along the path of handling dynamic data in which you can control whether or not data continues to be forwarded or not, be it good, or bad.

Protect Your Database From SQL Injection

Mark Kruger aka ColdFusion Muse, wrote a couple of good articles regarding SQL injection and shows a few examples of how you can be attacked. I fully agree with his recommendation to always, always, always, use CFQUERYPARAM. I also take it another step further (which he also recommends) by validating the input before it's even passed to the SQL code itself.

For example, if your using a URL.id variable to check for a detailed record, assuming your using a .CFC to store your query and then using CFINVOKE on the details page/template, I would do something like:

  • Wrap the WHERE clause in my SQL SELECT statement with a CFIF statement
  • And, use CFQUERYPARAM
  • Then, on the details page, use another CFIF statement to validate the URL (or form, etc.) being input

Code in CFC:


<cffunction name="getNews"
            displayname="Get record details from db"
            access="public"
            returntype="query"
            output="no">


    <cfargument name="id"
                type="numeric"
                required="no"
                default="1" />
    

    <cfset var returnNews = "" />

    <cfquery name="returnNews" datasource="#application.dsn#">
    SELECT tblNews.NewsID
        , tblNews.NewsTitle
    FROM tblNews
    WHERE 0 = <cfqueryparam value="0" cfsqltype="cf_sql_numeric" />
    <cfif isDefined("ARGUMENTS.id") and isNumeric(ARGUMENTS.id)>
        AND tblNews.NewsID = <cfqueryparam value="#ARGUMENTS.id#" cfsqltype="cf_sql_numeric" />
    </cfif>
    </cfquery>

    <cfreturn returnNews />

</cffunction>

Using this code on the details page would help to prevent the component from even being invoked if being attacked:


<cfif isDefined("URL.id") AND isNumeric(URL.id)>
    <cfinvoke    component="extensions.components.newsqueries"
                method="getNews"
                returnvariable="returnNews">

        <cfinvokeargument name="id" value="#URL.id#" />
    </cfinvoke>
</cfif>

Using this code on the details page (option 2) could fire the component but not pass in the argument:


<cfinvoke    component="extensions.components.newsqueries"
            method="getNews"
            returnvariable="returnNews">

<cfif isDefined("URL.id") AND isNumeric(URL.id)>
        <cfinvokeargument name="id" value="#URL.id#" />
</cfif>
</cfinvoke>
</cfif>

Then, you could simply display some sort of friendly error message if no record is returned.

By using these methods, if someone were to play around with the the URL.id by entering 'http://www.yourdomain.com/details/?id=1;DROP%20DATABASE%20foo' (you get the idea), then the variable wouldn't even be passed in the first place.

In addition to protecting your SQL, I highly recommend testing for vulnerabilities too. There are a number of ways to do this up to and including:

  • manually typing in code in your URL's to try and inject SQL into your own site
  • paying a third-party to do testing for you
  • using specially-designed software

Sometimes I get leary of directing people to any 'automated' solutions because it can lead some to a false sense of security by relying on the software to catch everything. That being said, the smart people at HP have been putting their big brains to work trying to come up with a way to address the issue of SQL Injections - spurred on by our friends at Microsoft.

The result is a product called 'Scrawlr'. Scrawlr is short for SQL Injector and Crawler. It will crawl your website simultaneously analyzing the parameters of each page for vulnerabilities. They have a pretty decent white paper at http://products.spidynamics.com/asclabs/sql_injection.pdf. You can download Scrawlr at https://download.spidynamics.com/Products/scrawlr/. I've personally used it and was surprised at how quickly the software traverses the site exploring for 'injection opportunities.' I purposely created a page where I did not properly 'protect' my SQL and sure enough, Scrawlr found it.

Once again, do not only rely on software to detect your vulnerabilities. Used properly, this could be one more tool in your developer tool box to help combat those evil SQL Injectors.

More information about scrawlr can be found at http://www.communities.hp.com/securitysoftware/blogs/spilabs/
archive/2008/06/23/finding-sql-injection-with-scrawlr.aspx?jumpid=reg_R1002_USEN

Microsoft SQL Server Script for the CFWACK OWS Database

I thought it might be helpful to other readers of the ColdFusion 8 Web Application Construction Kit Series to have a Microsoft® SQL Server script for the Orange Whip Studios (OWS) database. You can download it here.

Hope it helps!

© 2023, Stephen J. Withington, Jr.  |  BlogCFC was created by Raymond Camden – Version 5.9.004

Creative Commons License  |  This work is licensed under a Creative Commons Attribution 3.0 Unported License.  |  Hosted by Hostek.com