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.

Comments
<cfparam name="FORM.ContactName" type="string" default="" />

validate the string ...
# Posted By Jenny | 5/2/11 8:58 PM
For you here we are having one of the best platform where you can have all those ways by which you can smoothly fix windows 10 bluetooth audio devices and wireless displays connections.

© 2019, 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