Preventing SQL Injection & Cross Site Scripting using ColdFusion

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and brands.

This is not meant to be a tutorial on XSS & SQLi, merely some tips on how to prevent them when using ColdFusion. However, for the sake of clarity, I'll briefly outline what SQLi & XSS attacks look like.

An SQLi or SQL Injection attack is where a malicious user attempts to modify or obtain information from your database. This is accomplished by manipulating SQL statements that run your site.

A simple form post to search for an item on your hypothetical movie review site looks similar to this

<cfquery...>
                      	select * from tbl where name='#form.name#'
                      </cfquery>

Select all Open in new window

Because the form input is unsanitized, it's a simple thing to pass the following

star wars'; DROP table MovieReviews --

Select all Open in new window

which produces the following statement
      

select * from tbl where name='star wars'; DROP table MovieReviews --'

Select all Open in new window

This retrieves the reviews for star wars and then DELETES your table. This works because many SQL servers allow chained statements. These are usually separated by a semi colon ;

The injected string fools the server into thinking that there are 2 separate statements by adding ; and manipulating the quotes.

Now you might say that an attacker won't know the name of your tables. There are several ways for an attacker to get this information. One is by just guessing. It's not that hard in many cases. Another is through improper error handling which divulges query & table name in server error messages. Of course the other way is by, yes, you guessed it. SQLi. By the time the attacker is at the DROP table part they already know the name of your tables.

It should be pointed out that the DROP Table scenario is, while dramatic, pretty rare these days. Most malicious users don't want you to know they've been messing with your data. These types of attacks have been responsible for almost all of the major credit card and personal information hacks.

On a side note, it is possible to control permissions to the various basic SQL commands ( SELECT, Create, GRANT, INSERT, DROP, REVOKE, UPDATE, ALTER, Stored Procedures, DELETE) in most DBMS's as well as in the ColdFusion Administrator. As always for security best practices only allow those commands that really need to be enabled. For a web app, that probably means just Select, Insert, Delete & Update (and possibly stored procedure access)  

It's also possible to disallow chained statements in some (all?) DBMS's, so unless you really need to have them, you should disable that ability.

XSS or Cross Site Scripting (Xross Site Scripting ?) is a bit different. A malicious user will try to insert something which does no damage to your server, but rather, damages users who browse your server. A typical scenario is again on our review site which has a form to add a review.

An unsanitized query will happily insert the following in to a text field.

I hated this movie! <script>alert('Young Anikin Was A Weenie')</script> Don't go see it.

Select all Open in new window

Since those are all valid text characters, there is no harm to the server or your data. The problem arises when you serve that review to other users of the site.

As the browser is reading the HTML stream, it will see the <script> tags and execute them, popping up an alert for everyone who loads the page. Now this is irritating,
but the real problem is, again, a malicious user will add something far more insidious. The script usually forces the browser to silently load code from a website which may be
able to completely compromise a users system by attacking un-patched or zero-day exploits. The script itself may be able to exploit browser vulnerabilities.

Having everyone who visits your site have their computer p0wnd is not a good way to build audience.

For in-depth explanations on the various attack vectors (and there are a depressingly large number of them), you should visit OWASP

Now, on to preventing these types of attacks in ColdFusion.

Coldfusion is one of, if not the easiest, web scripting language to code securely. Straight out of the box, it’s harder to run an SQLi attack against than most. (please… I said harder, not impossible)

Here’s a pretty good discussion from a couple of years ago about this with several of the top CF Experts at EE

Bottom line for a CF coder is that using cfqueryparam will eliminate your SQLi risk. It’s an Adobe recommended best practice

    Adobe recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see Security Bulletin ASB99-04, “Multiple SQL Statements in Dynamic Queries,” in the Security Zone, www.adobe.com/devnet/security/security_zone/asb99-04.html, and “Accessing and Retrieving Data” in the ColdFusion Developer’s Guide.

From AdobeLiveDocs

And you’re crazy not to use it. One thing to notice is that Adobe recommends the use of cfqueryparam for -all- queries. This is important because many coders believe that only insert & update queries need to be protected. However, the URL scope is just as vulnerable and a simple

<cfquery ...>
                          select id from table where id = #url.id#
                      </cfquery>

Select all Open in new window

Can easily be turned into

<cfquery ...>
                          select id from table where id = #url.id#; do bad stuff here
                      </cfquery>

Select all Open in new window

You may actually see a performance boost using it as well.

However, just because you use cfqueryparam, don’t get smug. You still may be vulnerable to exploits. XSS is harder to deal with and while CF does have some built in protection you can enable, it doesn’t protect you in all cases.

Starting in CF8, CFadmin (ColdFusion Administrator) has a Server Settings > Settings > Enable Global Script Protection.

This is to “Specify whether to protect Form, URL, CGI, and Cookie scope variables from cross-site scripting attacks”. Sounds great. Check that box and your done. Safe and sound? Maybe not as much as you'd like.

Submit the following form with Global Script Protection (GSP) disabled. You should see an alert box which means you’re hax0red. Enable GSP and you won’t get the alert and you’re safe.

 However, while GSP is good for protecting against Javascript hacks like alert(‘Gotcha?’) it -won’t- do anything to protect you against an IFrame attack. An IFrame attack basically involves the malicious user injecting an IFrame with a URL source which points to a malicious website which can exploit browser bugs. (more at OWASP)

 I’ve simulated one here using a visible IFrame. An actual IFrame used in an attack would be hidden. You’ll notice that the injected IFrame shows up whether GSP is enabled or not. This is because an IFrame is not actually a “script” so it’s not filtered.

To fix this we need to strip html tags from input (which you should never allow to be submitted in any case) by using reReplaceNoCase and the regex <[^>]*>

As you can see, this eliminates the iFrame issue. This also defeats the JS inject issue as well even without GSP enabled.

<cfoutput>
                       <cfif structkeyexists(form, "f1")>
                       <div style="margin:50px;padding:35px;border:1px solid;width:350px;">
                       Did you see the JS Alert???<br>
                       #form.f1#<!--- Unsanitized input --->
                       </div>
                       </cfif>
                       <cfif structkeyexists(form, "f2")>
                       <cfset myvar = rereplacenocase(form.f2,"<[^>]*>", "", "All")><!--- Sanitized input --->
                       <div style="margin:50px;padding:35px;border:1px solid;width:350px;">
                       #myvar#
                       </div>
                      </cfif>
                       <cfif structkeyexists(form, "f3")>
                       <div style="margin:50px;padding:35px;border:1px solid;width:350px;">
                       I'm an injected iframe<br>
                       #form.f3#<br><!--- Unsanitized input --->
                       That's bad ;(
                       </div>
                       </cfif>
                       <cfif structkeyexists(form, "f4")>
                       <cfset myvar = rereplacenocase(form.f4,"<[^>]*>", "", "All")><!--- Sanitized input --->
                       <div style="margin:50px;padding:35px;border:1px solid;width:350px;">
                       No iframe here
                       #myvar#
                       </div>
                       </cfif>
                       </cfoutput>
                       <hr>
                       <div style="margin:50px;padding:35px;border:1px solid;width:350px;">
                       <h2>XSS Demo</h2><br>
                      The form fields are pre-loaded with "malicious" input.
                       <form name="1" method="post" action="index.cfm">
                       <input type="text" name="f1" value="<script>alert('Gotcha?')</script>"><br>
                       <input type="text" name="f2" value="<script>alert('I am harmless')</script>"><br>
                       <input type="text" name="f3" value="<iframe src=http://www.bing.com></iframe>"><br>
                       <input type="text" name="f4" value="<iframe src=http://www.bing.com></iframe>"><br>
                       <input type="submit">
                       </form>
                      </div>

Select all Open in new window

(note: my original post used Google as an IFrame source but since they no longer allow IFrames I've switched it to Bing. Yay Bing! I've also de-Apple-fied my references from iFrame to IFrame. Silly iEverything no more.)

I’ve got a couple of free tools to recommend as well.

SQL Injection Blocker v.4 by Mary Jo Sminkey

This is a “blacklist” tag which looks at common CGI variables and checks them for common SQLi keywords like insert|delete|select|update etc and for other nasty bits which don’t belong in form submits. You just cfinclude it in application.cfm or cfc and it should head off most attacks.

Note: this is -not- meant as a replacement for everything I’ve outlined above. It’s an added layer. (Since the original post, Mary Jo has added some XSS checking in v4.0)

While a proper testing regime is beyond the scope of this article, testing for flaws is a key part of your job when designing your website so have a look at all the resources available at OWASP. You also might want to check out HP Lab’s scrawlr for SQLi

"Scrawlr will crawl a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities"

If you have already been hacked with an XSS attack you can use scrubbr from OWASP to help sanitize your db. This tool scans records in your database looking for, and flagging, suspicious entries based on rules you can customize for your own requirements.

The take away on this? Always use cfqueryparam, always use rereplacenocase to remove html and test, test, test.