Back in February I wrote a blog post on SQL Injection that included an example of how a malicious user might inject into a character field even though ColdFusion escapes single quote marks. The attack involved other forms of escaping single quotes - and was effective against MySQL. This week I stumbled upon (more like a train wreck) an attack that is much more sophisticated - and also involves injection into a character field. I am told that others have discovered and written on this attack over the last few weeks - but I was unaware of it until a customer of ours was victimized. Amazingly, the specific real world attack I discovered and fixed allowed the hacker to append a string to every char column in every table of the database. It was so pervasive it left me wondering if it was SQL injection at all - until I found a URL entry that looked something like this:
someID=129;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x44...
%20AS%20CHAR(4000));EXEC(@S);
Note: in the spot above where it says "CAST(0x44..." I have left out a lengthy string of numbers.
First let me say that this code is ineffective against anyone using cfqueryparam. It is also ineffective against a simple "VAL( )" function in this case (since the user input was numeric - val() would have taken only the first few characters). But in this case the whole string - everything after someID= - was passed into the cfquery. Since the input was not validated the server attempted to execute it as valid SQL. What did it do?
The first part declares a variable as character string of 4000 characters.
DECLARE @S CHAR(4000)The second part "sets" this variable with a unique CAST statement.
SET @S = CAST(0x44...*long string of numbers* AS CHAR(4000))The final command executes the character string as SQL
EXEC(@S)
The trick here is the 0x* syntax inside of the CAST() function. It tells SQL that the values contained are actually ASCII codes and not translated characters. CAST then translates the numbers into actual characters that are subsequently executed by the EXEC() command. This obscures the attack in the logs, but here's how to unpack it. Find the string in the logs and tease out the CAST statement. Then, using Query analyzer try the following:
Note that 44***** is that long string of numbers. This will print out the actual SQL being executed. When I did this for the attack I had just fixed I found the following code being executed - and this resulted in a moment of begrudging awe in spite of my distaste for spammers and hackers.
This code creates a cursor of all the user tables in the database and all the character columns within those tables. Then it appends a string to each of the columns. In this case the string was a link back to a web site, but it could have been much worse. As Guru Scott Krebs said to me, "...at least they had the decency to deallocate their cursor". In any case the result was that every single character column in the DB was infected with the malicious string. Since this was a news site it meant that every story, every title, every comment - virtually every piece of useful content on the site - had an embedded link back to the hacker's site.
If you are using MSSQL you are vulnerable to this specific attack or any attack like it. The fix is to always use CFQUERYPARAM, validate your user input (meaning anything passed on the URL or in the Form scope) to be sure it is what it should be. Never rely on Client side validation for anything more than an enhanced user experience. Always always always write validation routines for form inputs. If any of these steps had been followed this client would not have been hacked successfully.
Recently someone asked me about form validation using Ajax. Form elements can be passed back to the server where they are checked and a result sent back to the browser which can display an error or submit the form accordingly. This can be a useful approach. For example, you can make calls to your database or session, check the user's shopping cart or CC etc. The result however is exactly like client side validation. You can enhance the user experience but you cannot secure user input using Ajax - since the form elements are still within the users control. Moreover, you are opening up an additional avenue of attack - particularly if you are touching the database in your Ajax code. Server side validation means that user input is examined on the server every time it is submitted. You cannot validate using Ajax and then not validate when the submission is made - in that way lies madness.
Yes indeed it does - but I prefere to allow multiple queries in a single connection. This helps me further leverage the DB while minimizing the overall number of connections. The DB is the typically the best and most powerful server in your system. It's use should be maximized, not restricted - at least that is my take.
The point remains that this is not a configuration issue per se. It is failure to write appropriate validation code.
Good stuff. Thought I would add to the discussion but ended up writing an article too: http://www.stephenwithington.com/blog/index.cfm/20...
Thanks for keeping the topic alive.
So if I had a query like...
select cities from locations where state = '#state#'
called by /cities.cfm?state=TX
they could do this? You talk about character fields in the intro, but the example seems to be numeric.
How does this attack get around coldfusion automatically escaping single quotes before sending them to MSSQL?
If it only applies to numerics, but can effect character columns, well, that's always been the case. SQL injection can be way more than just injecting data into a column, they can even execute arbitrary code.
I assume websites that only use stored procedures are not vulnerable to this attack? (cfstoredprocedure)
I think you are right ... In your example CF would indeed add an extra tick at the end and the hack would not succeed. It needs a point of entry like an unqualified unbound numeric value (the ubiquitous url.*something*ID".
For this reason, many people will deny their data source authentication access to stuff like the information_schema tables.
Once an attacker finds a query that will allow for injection, the sky really is the limit as to what SQL code they can dream up to put in there.
IF your site was using cfstoredprocedure then you are probably not vulnerable. However, some SPs that I have seen take a string value that is subsequently executed using sp_execute - or concatenate strings into a long executable piece of SQL... in that case you are still open to attack.
It is also possible to execute stored procs using basic cfquery blocks - so obviuosly that would still leave you open.
-Mark
My numeric stuff is all protected, either using cfqueryparam or by using val() (sometimes we use numbers in varchar fields to accommodate legacy data...)
As for stored procedures, I'd expect they're as vulnerable to this as they are to anything else, which is to say it depends on what you're feeding them. I think Mark's earlier post covered that pretty well -- if you're doing some kind of dynamic query and you feed SQL to a stored procedure instead of simple values, you're asking for it.
http://www.coldfusionmuse.com/index.cfm/2008/2/22/...
http://www.codersrevolution.com/index.cfm/2008/7/1...
@Gary: a website using cfstoredprocedure can still be vunerable to a SQL injectin attack like this IF the stored procedure being called executes dynamic SQL and doesn't use a prepared statement to bind the input parameters.
Hope this help =)
MySQL query is pulling data from the database using cfqueryparam but then I have a customtag that queries that query to sort filter the result for displaying.
Yes it should... but I really hate this question. Why do I always hear the question about exceptions to when it's "ok" to not use CFQUERYPARAM? Why not just make it a hard and fast rule in your development to always use it?
-mk
Yeah... I had 3 hit this blog this morning - and 2 hit my corporate site - so it's definitely making the rounds. It's so pernicious that it is going to force a good many code changes wherever it succeeds - a good thing I think :)
-mk
If you do work in other languages, you might as well get ready for it to hit those soon as well.
http://blog.wired.com/monkeybites/2008/04/microsof...
http://palisade.plynt.com/issues/2006Jun/injection...
Just want to clarify this in case people run to writing out a bunch of cfstoredprocs thinking that they can magically cure everything.
Yes... good point. In my comment above I said "probably" not vulnerable. I meant, if you have created an SP where the variables passed in maintain their status as variables of a particular type and are not strung together as dynamic SQL (most SPs follow this pattern in my experience).
@Emmet,
I'm going to delete your comment and invite you to post it again - this time without the actual URL to the malicious javscript. I don't think we need to give them any more links out there than they already have :)
<cfquery datasource="foo" name="qry_hack_me">
EXECUTE ps_invincible_proc #input1#, #input2#
</cfquery>
If you REALLY wanted to call your proc that way you could. cfqueryparam would be the obvious solution too, but I would hate for someone to think that since they were passing their data into a proc that is somehow made it any better.
Rewrote their procedure to "undo" damage.
DECLARE @T varchar(255)
DECLARE @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+']=replace(['+@C+'],''"></title><InvalidTag src="***********"></script><!--'','''')')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
I like to keep things simple, so here's how I plan to somehow protect my databases agains this kind of attack. I'll just put this line of code in Application.cfm or in onRequestStart method in Application.cfc :
<cfif reFindNocase("declare",cgi.query_string)><cfabort /></cfif>
I'll test this tomorrow (as it's bet time now) but I'm confident it'll work quite well I guess.
this will protect your site against this specific attack... but this is a reactive rather than a pro-active approach (black listing bad tags instead of whitelisting good tags). Yes it will work for THIS attack - until someone finds a way around it :)
That will only catch url attempts. It will not prevent attacks coming from form,cookie, and header variables. Portcullis (portcullis.riaforge.org) can look for these other variables scopes. Just put the 'declare' and 'cast' words in the filter.
Excellent article. I guess most sites were attacked today. Does anyone know of a proxy server/software that can stop those attacks via regular expressions or set of rules?
Thanks Dave for the "undo" code.
I have loads of sites that have a few tables each in one big DB! So this has been a right pain for me to try and sort out! I dont know where to start so just working through adding cfqueryparam.
Am I right in thinking that once I have covered all the leaks and rolled back my DB to a pre-infected backup all will be ok? Or is there more to it!? Thx
There's plenty more to fix or at the very least to check up on. SQL injection is just one type of attack. There's also cross-site scripting (XSS), cross site request forgery, improper error handling, bad session management, weak ssl ciphers, etc. The fun never ends. Cfqueryparam helps with a couple of attack vectors. There several more for you to research.
Yeah, this has definitely opened my eyes to a whole new world. Everything is black & white except for the huge grey bit in the middle!
I'm going to look into the other kinds of attacks you mention instead of taking the fire fighter role!
For now tho... I have made a few attempts to plug the holes in my code and roled back the DB, but it takes just minutes for the injection to take place again! This is really aggressive.
The following text is added into my DB...
"></title><InvalidTag src="http://*******"></script><!--
What should I do 1st to combat this pls guys?
First thing I'd recommend is similar to someone elses post above:
if running Application.cfm:
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string)><cfabort /></cfif>
</cfif>
If running Application.cfc in your onRequestStart block of code
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string)> <cfreturn false></cfif>
</cfif>
Might pay just to test this above locally as I've just typed it straight into this page.
This should give you a breather to revert back to a previous database version have your site up and running and then implement some better security :-)
Am I pushing it now asking what this code actually does??? :-S
This time it got nothing to do with iis or apache?
And it's also not only MS SQL that is affected, also MYSQL?
It's not only updates and inserts, the injection via url params can be made also with selects. Don't really understand this part. Thanks for any information.
i found this patch on the microsoft website:
"MS08-040: Description of the security update for SQL Server 2000 GDR and MSDE 2000: July 8, 2008 http://support.microsoft.com/kb/948110/en-us"
does someone know, if this patch protect the sql server of the injetcion?
thanks for your answers!
Cheers, Martin
explanation
http://products.spidynamics.com/asclabs/sql_inject...
tool
https://download.spidynamics.com/Products/scrawlr/...
Hey numbskull, don't post a link to the malicious code.
This attack uses a network of infected computers to crawl and post - so it's not coming from one source per se (although your attacks may have been).
-mark
Josh, don't call people names douchebag. That site has been disabled since yesterday morning.
I actually think the url should be posted. How else are people going to find information and get help for this particular attack?
ps. I don't really think your a douchebag. Just giving you an example of how nonconstructive your post was.
That's okay, it made me laugh. Point taken.
It will often trap bad code trying to be injected with numeric code, that it doesn't help when malicious content is added to a string. Also, it is not always on the URL, it can be added to a form submit.
For example, you have a contact form that asks for FIRST_NAME, its a string of course. Bad code can be appended to the first name or just used in its place. Sometimes sql injection isn't attacking your database, sometimes is just adding some javascript that when displayed on a web page will run and then cause some problems.
What's discussed here is only part of the bigger picture. Seems nearly impossible to prevent.
Actually the hackers already know how this stuff works. That's their job so-to-speak. Trying to keep the methods somehow secret only makes these things more difficult. SPI Dynamics is a very well known security consulting company. Foundstone is another. They post these examples so people can better understand how the hacks work and know what to look for.
Here's an easy undo if your database was compromised. You'll have to set the char count to 8000 in qry analyzer to get the entire output. Tools/Options/Results Tab, set maximum characters to 8000. This ignores text fields and system databases.
Just paste this in qry analyzer. It will create updates for all your columns. Copy the results into another window and run.
SELECT 'UPDATE [' + table_name + ']
SET ' + column_name + ' = REPLACE(CAST(' + column_name + ' as varchar(8000)), ''"></title><InvalidTag src="http://domain.com/hack.js"></script>...;!--'', '''')
WHERE ' + column_name + ' LIKE ''%"></title><InvalidTag src="http://domain.com/hack.js"></script>...;!--%'''
FROM information_schema.columns where (character_maximum_length is not NULL) AND ([table_name] not like 'dt%') AND ([table_name] not like 'sys%')
Martin
<cfset pageContent = getPageContext().getOut().getString()>
<cfset pageContent = replacenocase(pageContent, "#chr(34)#></title><InvalidTag src=#chr(34)#http://1.verynx.cn/w.js#chr(34)#></script><!--", "", "All")>
<cfcontent reset="yes" type="text/html">
<cfoutput>#pageContent#</cfoutput><cfabort>
I was attacked via this method. Although I did not use cfqueryparam I did have the following code to stop such attacks:
<cftry>
<cfparam name="ThisGroup" type="integer">
<cfcatch type="Any">
<cflocation url="MyHomePage" addtoken="No">
</cfcatch>
</cftry>
I would appreciate if you can tell me how they got by.
Thanks,
ShloimeH
I'm guessing the hackers exploited another variable. Surely your entire site uses more than a single variable.
I would recommend downloading the QueryParam Scanner from RiaForge and adding cfqueryparam to all the cfqueries in your entire site now.
You may be able to get more information about what page the hackers used by examining your web server logs.
@Gary: what you describe is not SQL injection. It is Cross Site Scripting or XSS. It is solved by properly escaping strings when outputting them based on the medium they are being imbedded in. (HTML, JS) You are correct though that cfqueryparam does not make you entirely immune. I blogged about that here:
http://www.codersrevolution.com/index.cfm/2008/7/2...
@Damian: I doubt any patch from Microsoft will protect your database from SQL injection. Good programming practices protect your database. The problem with SQL injection is that your DBMS DOESN'T KNOW the good stuff from the malicious stuff. That's why it is your job as a programmer to tell the database what is SQL and what is data. Parameterized queries do just that.
@Gareth: Apparently you were pushing it to ask. :) The code that was posted by Daniel simply searches the url variables (name, and values) coming into the page for the word "declare" and aborts page processing if it finds it. That sort of code should only be considered a temporary stop gap though. It will NOT stop many attacks, and will probably block legitimate page requests as well since "declare" is an English word that could appear a number of places. That check also wouldn't search form variables.
If anyone is interested, I blogged my experiences using the queryparam scanner from RiaForge here:
http://www.codersrevolution.com/index.cfm/2008/7/2...
There have been many great points and much good advice... I have learnt so much from this, but it still stands that I havent been able to prevent this from happening to my DB!
The g*ts that came up with this must be really pleased with themselves!
e.g. near the top of a form processing page...
<cfset form.email = left(form.email,100)>
<cfset form.firstname = left(form.firstname,40)>
The attack uses a very long string (does anyone know how many chars?) so immediately cutting the data returned from the user down to a reasonable limit will prevent this attack from working?
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="100" value="#form.email#">
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="40" value="#form.firstname#">
I've been doing this as recommended and I haven't had any trouble this week. (Although I've been getting a TON of form validation errors in my logs. ;-)
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
(This one is a bit better than the one I posted in the other thread)
<!--- 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 --->
1 - Update to previous post (onRequestEnd) now using regexp
<cfset pageContent = getPageContext().getOut().getString()>
<cfset pageContent = rereplacenocase(pageContent, "(\#chr(34)#\>\<\/title\>\<InvalidTag src=\#chr(34)#http:\/\/)(.*?)(.verynx.cn/w.js\#chr(34)#\>\<\/script\>\<!--)", "", "All")>
<cfcontent reset="yes" type="text/html">
<cfoutput>#pageContent#</cfoutput><cfabort>
2 - Code to go in onRequest to throw a 403
<cfif findNocase("DECLARE%20@S%20CHAR(4000);", cgi.query_string)>
<cfcontent reset="Yes">
<cfheader statuscode="403" statustext="Access denied">
<cfabort>
</cfif>
A quick and dirty solution to put in place while you're scrambling to add cfqueryparams may be an ISAPI_Rewrite. Check out the following:
RewriteRule .*DECLARE.* http\://yoursite.com/error.html [I,R]
Great article thanks for covering it with such detail.
Does anyone have any other ways of preventing the Declare function from operating?
I have tried adding the following to appication.cfm on all of my sites and have cfqueryparamed everything I can see...
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string)><cfabort /></cfif>
</cfif>
But that did not seem to work. I have heared that cacheing is taking place so making changes at this stage will not help?
RewriteRule .*DECLARE.* http\://yoursite.com/error.html [I,R]
also take care of cookie and form input?
RewriteRule ^(.*)$ http\://yoursite.com/error.cfm
But if you are using inline sql and procs are not an option; this will help you lock out the hackers by diagnosing where you're missing cfqueryparam... http://qpscanner.riaforge.org/
If you are referrring to ColdFusion's cache, then perhaps IF you are using trusted cache AND haven't cleared your template cache OR restarted CF since you modified your code-base.
Chances are, you have a vulnerable page on your site somewhere still. Have you use a scanner tool to check your entire code base for you to see if you missed anything?
It would look for any pound sign (#) inside cfquery tags but not inside a cfqueryparam tag.
Best approach add a filter to your App.cfc onRequestStart or App.cfm. As far as I can tell the remote address is continually changing...
example to look for the exec in the url:
took off brackets due to hack attempt errors
cfif listFind(cgi.SCRIPT_NAME,'execorsomethinginthemalitiousurl',';') gt 0
cfoutputYOU SUCK!cfoutput
cfabort
cfif
Best to use cgi.query_string to eval the url instead of script_name.
Now I just got a new one, in a one minute time span from 4 different IP addresses all over the world (Asia, Australia)
Here it is
http://www.terrapinstats.com/bb/profile.cfm?player...
"playerid" & "addType" are hidden form fields
"remarks" & "username" are text form fields
The error was invalid data on the playerid (I have cfqueryparam'd for integer.. the bot put in a ? and other stuff)
My host has scriptprotection turned on as well
Just very curious as to what they're trying here.
terrapinstats.com/bb/addcontent.cfm?playerid=307&remarks=hgurw+klxmhowvd+ksnwt+sivq+xkjgnfd+ndthz+rtzb&username=fwskyhbz+ogwrqjcd&addType=playerComment
http://www.tennisgear.ws/
http://qpscanner.riaforge.org/