On Coldfusion And SQL Injection Attacks: Metalunderground.com As A Case Study
posted Aug 19, 2008 at 10:35:35 PM by Doug Gibson.
To follow up on my previous message about my site, Metalunderground.com, being "hacked," I thought I'd go into more detail on the attack and what web developers and site owners can do against it. To clarify, I put quotes around "hacked" because it's not really so much hacking as it is a scripted attack, perpetrated by some script kiddie in China, not a "real hacker."
If I wasn't so busy of late, I'd have noticed the many blog posts about SQL Injection attacks hitting ColdFusion sites.
It seems a similar attack swept through MySQL-powered sites earlier in the year, but the recent bout of attacks affecting ColdFusion sites using MS SQL Server hit hard in July with a few as early as June from what I have read. There's a particularly nasty version of the attack (seemingly largely perpetrated by a botnet)
that appends a cross-site scripting exploit (XSS) into EVERY varchar, nvarchar, text and ntext column in your database. Doing so not only corrupts your data, but can result in data loss beyond cleanup.
Fortunately, the attack on Metalunderground.com appears to be more targeted. The attacker injected a 24KB payload of XSS and spam links into each of nearly 40,000 news records. The focus of the injection actually made finding the problem very difficult.
To reiterate what's been said around the Coldfusion development community, absolutely make sure you use CFQUERYPARAM on EVERY variable going into your queries - especially URL, FORM, SESSION and COOKIE and CGI variables, which can all be corrupted. You might as well just make it standard practice, because often variables in the local, request, or other scopes are often dumped from cookies or sessions as well (in application code, that is), meaning any tampering could trickle down into those scopes.
Also, do not rely on the CFADMIN checkbox for "Global script protection" or any other built-in security. I've always been very skeptical of the built-in protection in ColdFusion or any other black-box code like this, and for good reason. This particular attack uses a DECLARE statement and encoded SQL and payload, failing to trigger the catch-words to invoke the protection.
Read on to learn from my experience gained from this attack.
Troubleshooting the Issue
I first noticed something was wrong when I got a down-notice from my server monitor, Pingdom one day near the end of July around the end of the work day. I could still remote into my server, but web pages were not being served. The issue seemed very weird and I began troubleshooting all sorts of stuff from Apache to ColdFusion and the JVM. Sifting through logs, I saw lots of timeouts from CF and eventually reinstalled CF to no avail.
After extensive troubleshooting and research online, I was convinced I was under a Denial of Service attack. As soon as I would start up the server, the TCP connections would queue up at a rate of 5-10 per second. At about 700-800 TCP connections, the server would hang and/or boot me off of my remote connection, requiring a hard reboot to connect again.
A few days went by and I had made little progress after tons more reading. Finally, I noticed that my database files were massive, sitting at 2.6GB. I knew they were only a few hundred megabytes not too long ago, and that's when I started looking more closely and discovered that it was the database that was attacked.
Backup Your Data...AND Verify It!
At this point I went to check out my backups, only to realize they had not been firing for a couple of weeks. And the ones that were firing before that were timing out for some reason and contained only partial backups, never getting to my database that was affected. I pretty much shit a brick, thinking I had lost a ton of data back to late May, when my last good backup was from.
SQL To The Rescue?
After much research, I found a number of stored procs and queries to help me track down the injected text. I wrote a script to clean out the affected table in ColdFusion. All seemed well - the data could be scrubbed, only ntext fields were affected (in my case), it was unlikely that there was any data truncation, and I could close the security holes in my code and be up and running again.
I did this, but the database's file size did not change, even after shrinking it multiple times and trying all the options to shrink it. I started the server up again but its performance was still severely degraded. After more research, I found a neat query that displays the physical size of every table in a database.
SQL: Dead and Bloated
The news table that was attacked still had a gigabyte of empty space in it and there was no way I knew of to fix it. This was presumably the cause for the performance degradation, especially since news is the main feature of Metalunderground.com.
Fortunately, I located a backup of the database I had made manually just a day before the attack. I would lose a little data, but not much by restoring that, so that's what I did. The .mdf file was a mere 360MB for over seven years' worth of data, as opposed to 2.6GB.
Plugging the Holes with CFQUERYPARAM
In my early days as a CF developer, I did not use CFQUERYPARAM. The code base for Metalunderground.com is largely over 8 years old. The main barriers to using CFQUERYPARAM are remembering how the data types map (I still don't remember when to use date and timestamp sometimes) and dealing with NULLs and data constraints. These issues are fairly trivial once you go down that road though. CFQUERYPARAM is a necessity these days. Learn to deal with this slightly less friendly tag or you will be dealing with SQL Injection attacks at some point.
I had actually updated a good bit of my code recently, and using CFQUERYPARAM was a major priority. But there were probably one or two spots where I still did not use CFQUERYPARAM and that's all it took to leave a loophole open. The main place was on a dynamic ORDER BY clause, because you cannot parameterize those values. The real solution to that case is to validate the data more strictly. Since you are likely passing a alphanumeric tablename and "ASC" or "DESC," validation of the data is fairly straight forward.
Pete Freitag has a great writeup on CFQUERYPARAM and how to validate the data in those places where CFQUERYPARAM cannot be used. Additionally, RIA Forge has a project to scan your codebase for queries using variables without CFQUERYPARAM. It can give an enormous number of false positives because it will display every query that has hash signs that are not in a CFQUERYPARAM - even those properly validated, in places where CFQUERYPARAM doesn't work, or even commented out! But false positives are better than missing one and leaving a security hole, so I'd say it's worth the time to check out anyway.
It Can Happen To You!
Don't think because you're a small blog that you won't get attacked. Many blogs have been attacked. Remember that those tools used to scan for vulnerabilities can be automated by hackers to check for vulnerable sites as well. No one is safe, unless your application is airtight.
Further Reading
2 Reader Comments
Hey John. Personally I hate using Stored Procs at all and having that part of the logic removed from the application codebase. We're versioning everything at work and even though the database is versioned as well, it is not as clean an implementation as the code itself, which is yet another reason I prefer to have queries in the codebase.
The benefits of Stored Procs have been vastly overblown, IMO, and the main reason I see for using them is for sharing of those standard queries and datasets across apps and/or platforms. Short of that, I prefer to use regular CFQUERY.
To minimize comment spam/abuse, comments are closed on articles over a month old.
My rule number TWO is, "absolutely make sure you use CFQUERYPARAM."
My rule number ONE is, "absolutely make sure you use CFSTOREDPROC."
If you can follow rule number one, you'll never need rule number two.
Thoughts?