Welcome!

ColdFusion Authors: Greg Ness, Liz McMillan, Pat Romanski, Andreas Grabner, David Strom

Related Topics: ColdFusion, Java, AJAX & REA, Web 2.0

ColdFusion: Article

ColdFusion Developer's Journal Special: How to Prevent an SQL Injection Attack

SQL Injection Attacks are One of the Easiest Ways to Hack Into A Website - Learn How to Prevent Them
Article Rating:
August 8, 2008 04:00 PM EDT
Reads:
 5,952

SQL Injection attacks are one of the easiest ways to hack into a website. One recent hack, using a script from verynx.cn, involves injecting SQL into a web form that then appends some JavaScript code into fields in a database that then gets executed on the client side when a user views a database-driven page. To learn more about this hack, go to this link. 

If you're using ColdFusion, to harden your website from sql injection attacks add the following code to your Application.cfm file. If you're not using ColdFusion, you can translate this code into the language you're using and it should still work.

<!--- CREATE SQL REGULAR EXPRESSION--->
<cfset sqlregex = "
(SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+)|
(UPDATE\s[\w]+\sSET\s[\w\,\'\=]+)|
(INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+)|
(DELETE\sFROM\s[\d\w\'\=]+)|
(DROP\sTABLE\s[\d\w\'\=]+)">

<!--- CHECK FORM VARIABLES --->
<cfloop collection="#form#" item="formelement">
         <cfif isSimpleValue(evaluate(formelement)) AND refindnocase(sqlregex, "#evaluate(formelement)#")>
                  <cflocation url="messages.cfm?message=Invalid Input. Possible SQL Injection attack.">
                  <cfset StructClear(form)>
                  <cfabort>
         </cfif>
</cfloop>

<!--- CHECK URL VARIABLES --->
<cfloop collection="#url#" item="formelement">
         <cfif isSimpleValue(evaluate(formelement)) AND refindnocase(sqlregex, "#evaluate(formelement)#")>
                  <cflocation url="messages.cfm?message=Invalid Input. Possible SQL Injection attack.">
                  <cfset StructClear(url)>
                  <cfabort>
         </cfif>
</cfloop>

This code would reside in your Application.cfm file which gets executed every time a ColdFusion file is requested on the server. What it does is it checks all form and URL variables to see if they contain any patterns matching an SQL SELECT, UPDATE, INSERT, DELETE or DROP statement.

If a match is found, the user is redirected to a message page indicating that a possible SQL Injection attack was made and the SQL injection is prevented.


Published August 8, 2008 – Reads 5,952
Copyright © 2008 SYS-CON Media, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.

Comments (6) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

Most Recent Comments
littleviews 05/15/09 01:37:00 AM EDT

I could not make this work in CFMX8. Does anyone have any suggestions?
cmosdall 10/15/08 03:07:54 AM EDT

There’s a very nasty SQL injection attacking our sites at the moment:

DECLARE @S CHAR(4000);
SET @S=CAST(0x44…..72 AS CHAR(4000));
EXEC(@S);

You need to add DECLARE and EXEC to the regex.
I use (exec(|ute)[\s|\(]) which traps EXEC( and EXECUTE
klevenson 09/18/08 12:25:41 PM EDT

If I change sqlregex = "select" for testing the code works fine.

The complete regular expression as listed above doesn't seem to work in CFMX8.
distinct 09/03/08 10:17:09 AM EDT

Dangerous Solution!

Whilst it can be useful to attempt to detect SQL injection; using detection as a defence mechanism is risky.

The only way to really be sure that no SQL injection will be possible in ColdFusion is to ensure all queries use the cfqueryparam tag around user supplied input. Additionally, all user input should be validated server side in order to ensure it matches a specific and expected data type and format.

In programming, one can always prove what is true, but not always prove what is false. Trying to protect a system by determining what user input is bad is shakey. By contrast, protecting a system by determining what user input is good is solid. Essentially, a system should only accept and process user input which adheres to an expected datatype and format. Everything else should be rejected.

Beware!
pgwalters 07/30/08 11:08:54 AM EDT

And, if I may, add TRUNCATE TABLE (or the equivalent for your DBMS)
cfengineers 07/30/08 09:22:00 AM EDT

Dont forget that DECLARE should also be in the list.