<

Go Premium for a chance to win a PS4. Enter to Win

x

Preventing SQL Injection & Cross Site Scripting using ColdFusion

Published on
19,749 Points
8,149 Views
6 Endorsements
Last Modified:
Awarded
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>

Open in new window

Because the form input is unsanitized, it's a simple thing to pass the following
star wars'; DROP table MovieReviews --

Open in new window

which produces the following statement
      
select * from tbl where name='star wars'; DROP table MovieReviews --'

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.

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>

Open in new window


Can easily be turned into

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

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>

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.
6
Comment
Author:SidFishes
5 Comments
 
LVL 36

Author Comment

by:SidFishes
corrected a last typo
0
 
LVL 38

Expert Comment

by:lherrou
Nice article, very informative. It earned a Yes vote from me!

I am not a CF programmer, but as a general web developer, these principles apply elsewhere as well, and in addition, I have been called in when sites have been compromised, it's always good to know what to look for when you are trying to find out how someone got in, and what backdoors they may have left behind.
0
 
LVL 13

Expert Comment

by:srikanthmadishetti
Good one Sidfishes I really liked ur article.

While displaying to escape characters better to use htmleditformat .

CF10 has more security features implemented especailly for XSS and CSRF like below

¿Handling XSS attacks

  Only the following characters are allowed as values for the attribute name in the tag cfform: alphanumeric characters, _ (underscore), - (hyphen), : (colon), and . (dot). It prevents stored XSS for the scriptsrc field

The following new encoding methods are added to reduce XSS attack vulnerability: encodeForHTML, encodeForHTMLAttribute, encodeForJavaScript, encodeForCSS, and encodeForURL. Encode the user inputs depending on the contexts. To decode the input string, added a method: canonicalize

¿Handling CSRF attacks

CSRFGenerateToken: Returns a random token and stores it in the session

CSRFVerifyToken: Validates the given token and the key against the same stored in the session
0
 
LVL 36

Author Comment

by:SidFishes
Thanks for that update. We're still stuck at 8 but these new features sound great. Just proves my point "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."

:)
0
 
LVL 3

Expert Comment

by:Eric Bourland
Great article!

I have a couple of very small editorial notes which I hope are helpful:

The link to SQL Injection Blocker v.4 by Mary Jo Sminkey seems to be missing. I searched for it:

http://www.cfwebstore.com/index.cfm?fuseaction=blog.search

but did not find it. And it seems like Ms. Sminkey is no longer represented at CFWebStore? I could be wrong?

>>>Check that box and your done.

~you're

Thanks for this great article, Sid.

Eric
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month