During the few weeks my work sites were getting hit with a nasty SQL injection that would update all the text fields in our SQL tables. I came up with some code to curb the URL injections as well as form-based attacks. It has been effective, but I wonder if anyone has a better solution.
The attack would append a <script> tag pointing to a .js file usually in China at the end of every text field in all the tables in the database. We have over 300 tables and the script added about 7gb of data to the database.
We have been using the <cfqueryparam> tag for the fields when creating queries, but thought the injection was still slipping through. The logs from our host did not give us an exact point of entry. I decided that we would needed a different method to stop the injection code from being executed.
For the URL variables I created a block code that sits in the onRequestStart function in the Application.cfc. This checks for the existence of a URL variable and if it finds one, then loops through the variable trying to match “4445434C415245″ which is the hex equivalent of “DECLARE”, which was part of all the injection scripts that were attempted. If the pattern was matched it would send an email and abort the page. The script was catching a lot of attempts, so after a few hours we commented off the email response.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <cfif NOT StructISEmpty(URL)> <cfloop list="#StructKeyList(URL)#" index="i"> <cfif URL[i] CONTAINS '4445434C415245'> <cfmail to="webmaster@site.org" from="info@site.org" subject="SQL Injection Attempt" type="html"> <cfdump var="#URL#"> <cfdump var="#CGI#"> </cfmail> <cfabort> </cfif> </cfloop> </cfif> |
The second method we are using obscufates harmful string patterns in text submitted on forms. The script simply looks for four different patterns that were part of the injection attempts and replaces the character with ASCII codes. We don’t have any WYSIWYG editors on the public side of the site, so we were not worried about breaking HTML formatted form content.
The script loops through all of the form fields based on the existence of the generated field list when a form is submitted. Of course, more conditions can be added as needed. This also goes in the onRequestStart function in the Application.cfc.
1 2 3 4 5 6 7 8 | <cfloop list="#FORM.FieldNames#" index="i"> <cfscript > FORM[i] = replace(FORM[i],'<','<','ALL'); FORM[i] = replace(FORM[i],'>','>','ALL'); FORM[i] = replace(FORM[i],'"','"','ALL'); FORM[i] = replace(FORM[i],'''',' ','ALL'); </cfscript > </cfloop> |
Aside from the cfqueryparams and these two methods, we also changed the constraints in SQL to not allow certain text patterns as well as limit permissions to the tables.
Have you been hit by these type of SQL injection attacks? If so, what have you been doing to prevent them? I would like to further protect the tables seeing these to methods aren’t bulletproof.
August 15th, 2008 at 6:07 am
Hi Jason,
A couple of our older CFML sites got hit as well but it appeared to us that the only places where they were getting hit was in queries that were passed variables without being parameterized or cleaned. Meaning places where you had things like
where id=#url.id#
or
where id = #form.id#
If you use cfqueryparam with the right sql type you shouldn’t have had any problem with the injection since cfqueryparam will properly escape any quotes and cause an error if a string is appended to the end of a numeric value (if the sqltype is numeric)
For added security we also use a udf called cleanscopes in some of the newer sites that cleans the url and form scopes. Naturally after the attack we added the udf to some of the older sites too :). I will be happy to share the code with you if you would like.
August 15th, 2008 at 3:43 pm
Hi Gary,
Thanks for commenting. I would like to have a peek at the cleanscopes UDF and other added protection is certainly welcome.
August 28th, 2008 at 8:44 am
[...] quite figure out what was going on. While perusing the web for other ideas, I ran across Jason Bartholme’s blog entry about preventing SQL injection attacks. He has a simple bit of code that he put in onRequestStart() so check if the keyword DECLARE was in [...]
September 18th, 2008 at 12:39 am
one way to avoid the problem if you are able to do so is to create two different SQL server accounts. it only works if the form data and user input you are collecting is not going directly into the database as was the case on one of our client sites… so clearly not every site can take advantage of this solution…
anyhow… we made one SQL account for the public site that could not write to the database. we made a second one for use on the firewall protected intranet that allowed read and write (used by the staff for the CMS tool).
September 21st, 2008 at 3:04 pm
We have noticed several hundred attempts to compromise our system using SQL injection. I would love to see what you may have implemented with your cleanscopes UDF as mentioned. We have always secured our Numeric values but it appears that this type of attack may be executing by compromising user defined text variables as well. Is that true?
September 22nd, 2008 at 3:33 am
You can download the code from my site:
http://www.garyrgilbert.com/tools/coldfusion/cleanScopes.cfm.txt
I should mention that this will help reduce simple xss attacks but more sophisticated attacks need to be mitigated by more sophisticated programming practices like using cfqueryparam, argument validation etc.
The code is provided warranty free, meaning you assume all risks when using it. I do have this code in use on commercial websites and did not get hit by the attack.
Hope it helps.
Gary
September 22nd, 2008 at 8:35 am
Gary,
Thanks for posting your method. It will definitely provide another level of protection.
December 17th, 2009 at 2:21 pm
Hi!
I created a video tutorial about SQL injection.
Take a look:
http://www.webmastervideoschool.com/blog_item.php?id=7