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

Aug. 8, 2008 04:00 PM

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.


<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\'\=]+)">


<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>


<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 Aug. 8, 2008— Reads 3,029 — Feedback 5
Copyright © 2008 SYS-CON Media. All Rights Reserved.

YOUR FEEDBACK

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

Cliff Mosdall wrote: 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

read more & respond »

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

Keith Levenson wrote: 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.

read more & respond »

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

Christopher Cundill wrote: 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!

read more & respond »

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

Peter Walters wrote: And, if I may, add TRUNCATE TABLE (or the equivalent for your DBMS)

read more & respond »

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

Ernest Breau wrote: Dont forget that DECLARE should also be in the list.

read more & respond »

CFDJ LATEST STORIES . . .

Adobe Comes Up Short, Cans 600

By Maureen O'Gara

As fate would have it, Adobe picked the worst possible quarter to roll out the great update to its flagship Creative Suite widgetry. Because the economy is tanking, it hasn’t been selling the way it was supposed to. As a result, revenues came up short in the November quarter and Adob...

An Introduction to Ant

By John Ashenfelter

Writing shell scripts to automate the build and deploy process for ColdFusion applications is not very much fun. The Jakarta Ant project is an open-source, cross-platform alternative that makes it easy to automate the build and deploy process.

ARM to Get Flash

By Maureen O'Gara

Adobe and ARM are gonna put Flash Player 10 and AIR, the stuff of web video and rich Internet apps, on ARM widgets by the second half of next year. They mean phones, set-tops, MIDs, TVs, car mojo and personal media devices, which have so far only had access to Flash Lite, not the best ...

It's Saturday Evening, and I'm in San Francisco

By Yakov Fain

Of all domestic air carriers, I like Continental the most. They showed Mamma Mia and the food was bearable. Last month, I was in the air for 14 hours flying to Japan, and now the trip across the USA is a piece of cake. I have only carry luggage with me. This small bag has all the cloth...

MAX is One of the Best Places to Improve Your Technical Flex Skills

By Yakov Fain

I’ll just give you one example. Last week my colleague and I were running a private Flex workshop for software architects of a large corporation who are about to start development with Flex. Needless to say that they are smart and experienced software professionals. Some of them alre...

The Cloud Comes This Week to San Jose

By Cloud Computing News Desk

A round-up of the many themes and topics of interest to infrastructure architects, developers and IT managers featuring at SYS-CON's Cloud Computing Expo being held November 19-21, 2008 at The Fairmont Hotel in San Jose, California. The conference is expecting a record turnout of senio...