Coldfusion blog

Prevent SQL injection and cross-site scripting using ColdFusion

This is an updated version of an article posted on my blog over 3 years ago. This is unfortunately still very relevant as we continue to see SQLi (SQL injection) and XSS (cross site script) attacks hitting some of the more recognizable websites and brands.

This is not a tutorial on XSS and SQLi, just a few tips on how to avoid them when using ColdFusion. However, for the sake of clarity, I will briefly describe what SQLi and 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 the SQL statements that run your site.

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

	select * from tbl where name=""

Open in a new window

Since the input of the form is not cleaned, it is simple to transmit the following

star wars'; DROP table MovieReviews --

Open in a new window

which produces the following statement

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

Open in a new window

This collects star wars reviews, then DELETS your table. This works because many SQL servers allow chained statements. These are usually separated by a semicolon;

The injected string tricks the server into believing that there are 2 distinct instructions by adding; and manipulate quotes.

Now you could say that an attacker will not know the name of your tables. There are several ways for an attacker to obtain this information. One is to guess. It is not that difficult in many cases. Another is incorrect error handling that leaks query and table name in server error messages. Of course the other way is by, yes you guessed it. SQLi. By the time the attacker is in the DROP table part, he already knows the name of your tables.

It should be noted that the DROP Table scenario is, although dramatic, quite rare these days. Most malicious users don’t want you to know that they’ve been messing with your data. These types of attacks are the root of almost all major credit card and personal information hacks.

By the way, it is possible to control the permissions of the various basic SQL commands (SELECT, Create, GRANT, INSERT, DROP, REVOKE, UPDATE, ALTER, Stored Procedures, DELETE) in most DBMS as well as in ColdFusion Administrator. As always with good security practices, only allow commands that really need to be enabled. For a web app, that probably just means select, insert, delete, and update (and possibly access to stored procedures)

It is also possible to disallow chained instructions in some (all?) DBMSs, so unless you really need it, you should disable this capability.

XSS or Cross Site Scripting (Xross Site Scripting?) Is a little different. A malicious user will try to insert something that will not harm your server, but rather harm the users browsing your server. A typical scenario can be found again on our review site which has a form for adding a review.

An unverified query will gladly insert the following into a text field.

I hated this movie!  Don't go see it.

Open in a new window

Since these are all valid text characters, there is no harm to the server or your data. The problem arises when you provide this notice to other users of the site.

While the browser reads the HTML feed, it will see the tags

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

For a detailed explanation of the different attack vectors (and there are a depressing number of them), you should visit OWASP

Now let’s move on to preventing these types of attacks in ColdFusion.

Coldfusion is one of the easiest, if not the simplest, web scripting languages ​​to code safely. Right out of the box, it’s harder to execute an SQLi attack than most. (please … I said louder, not impossible)

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

The bottom line for a CF encoder is that using cfqueryparam will eliminate your SQLi risk. This is a good practice recommended by Adobe

Adobe recommends that you use the cfqueryparam tag in every cfquery tag, in order to secure your databases from unauthorized users. For more information, see Security Bulletin ASB99-04, “Multiple SQL Statements in Dynamic Queries,” in the Security area,, and “Accessing and Retrieving Data” in the ColdFusion Developer’s Guide.

Of Adobelivedocs

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

    select id from table where id =

Open in a new window

Can easily be transformed into

    select id from table where id =; do bad stuff here

Open in a new window

You can also see an improvement in performance by using it.

However, just because you are using cfqueryparam, it is not sufficient. You can still be vulnerable to exploits. XSS is more difficult to manage and although CF has built-in protection that you can turn on, it doesn’t protect you in all cases.

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

It is at “Specify whether to protect Form, URL, CGI, and Cookie scope variables from cross-site scripting attacks”. Great. Check this box and it’s done. Safe and sound? Maybe not as much as you would like.

Submit the following form with Global Script Protection (GSP) disabled. You should see an alert box which means you are hax0red. Activate GSP and you will not receive the alert and you are safe.

However, while GSP is good at protecting against JavaScript hacks like alert (‘Gotcha?’), It won’t do anything to protect you from an IFrame attack. An IFrame attack basically involves the malicious user injecting an IFrame with a source URL that points to a malicious website that can exploit browser bugs. (More than OWASP)

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

To solve this problem, we need to remove the html tags from the entry (which you should not allow to submit under any circumstances) using reReplaceNoCase and the regular expression

As you can see, this eliminates the iFrame problem. It also solves the problem of JS injection even without GSP enabled.

Did you see the JS Alert???
]*>", "", "All")>
I'm an injected iframe
That's bad ;(
]*>", "", "All")>
No iframe here #myvar#

XSS Demo

The form fields are pre-loaded with "malicious" input.

Open in a new window

(Note: my original post used Google as the IFrame source, but since they no longer allow IFrames I switched it to Bing. Yay Bing! I also deleted my iFrame references to IFrame. Silly iEverything more .)

I also have a few free tools to recommend.

SQL injection blocker v.4 by Mary Jo Sminkey

This is a “blacklist” tag that examines common CGI variables and checks them for common SQLi keywords such as insert | delete | select | update etc and for other nasty pieces that do not belong to the submitted form. You just included it in application.cfm or cfc and it should prevent most attacks.

Note: this is not -not- intended to replace anything I have described above. It’s an extra layer. (Since the original post, Mary Jo added an XSS check in v4.0)

While a proper testing regime is beyond the scope of this article, testing for defects is a key part of your job when designing your website, so take a look at all the resources available at OWASP. You can also check out the HP Lab scrawlr for SQLi

“Scrawlr will crawl a website while simultaneously scanning the settings of each individual web page for SQL injection vulnerabilities.”

If you have ever been hacked with an XSS attack, you can use to rub of OWASP to help disinfect your database. This tool scans your database records for and flagging suspicious entries based on rules that you can customize according to your own needs.

The point to remember about this? Always use cfqueryparam, always use rereplacenocase to remove the html code and test, test, test.