How to prevent an SQL Injection attack, e.g. verynx.cn

By Abdullah Yahya

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 http://www.hubshout.com/?SQL-Injection-Attack-using-script-from-verynx.cn%2C-hackers-are-back&AID=23.

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\'\=]+)|
(DECLARE)|
(VARCHAR)|
(CAST)|
(EXECUTE)">

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

About the Author

Abdullah Yahya is the webmaster at Chabot College in Hayward, CA and founder of HangStop.com. He has 7 years of experience building complete web systems from basic static sites to multi-tier, database-driven e-commerce sites. He holds a bachelors in engineering from UC Berkeley, a masters in computer science from San Francisco State University and is a Macromedia Certified Advanced Coldfusion Developer. Abdullah has been coding in Coldfusion since 2001. More information on Abdullah can be found on his website at www.abdullahyahya.com.