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,290 — Feedback 5
Copyright © 2009 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 »

WEB 2.0 LATEST NEWS

Project Management 2.0

By Andrew Filev

Nowadays we can observe changes going on in management and especially project management in organizations. More and more, organizations are abandoning top-down management style. Among them are the New York Times, Tribune Co., Ernst & Young and many others. Even the world biggest corpor...

Web Apps Will Be Built in the Cloud: Keynote Systems Exec

By Jeremy Geelan

In this Exclusive Q&A with Jeremy Geelan of SYS-CON's Cloud Computing Journal, Rajeev Kutty of Keynote Systems speaks of the factors currently driving companies to increase their effort in monitoring the performance of their Web and mobile applications, and about how Keynote foresees a...

SOA World Expo: Enterprise Mashup Services

By Ric Smith

Since Web 2.0 kicked off scarcely a day goes by without a headline targeting mashups and their enablers, AJAX and Web Services, as the next hot Web technologies. Mashups are Web sites that integrate a variety of services (e.g., news feeds, weather reports, maps, and traffic conditions)...

Web 2.0 Is Dead And It's Time To Get Out Of This Mess!

By Web 2.0 News Desk

Industry blogger Alex Bunardzic writes in his 'Ethical Software by Alex Bunardzic' blog: 'Now that Microsoft has jumped onto the web 2.0 bandwagon, it is more than obvious that Web 2.0 is dead as a doornail. Everyone knows by now that anything Microsoft touches turns into this big slim...

AJAX World RIA Interview: Convergence of Web, Mobile and Desktop Apps is the Next Phase

By Jeremy Geelan

'While the last decade was focused on the Web, the next phase in the evolution of our industry will be on the convergence of Web, mobile and desktop applications and the ability to extend existing applications with these new technologies for a consistent user experience regardless of h...