Stop SQL Injection Attacks on data-driven websites

January 14, 2010
Sean Cooper

In May of 2008, a SQL injection spread attack across the web compromising sites as illustrious as BusinessWeek.com. This attack, in a nutshell, updated every text field in a database with code to serve a JavaScript popup with a link to a website serving malicious content.

It was a morning in mid-May when I came into the office and scanned through the emails from the night, including all the various errors the sites we were responsible for had generated. One email I looked at seemed kind of funny. The requested URL had a query string on it that looked to be a complete jumble of characters. I thought to myself, "This is probably not good."

After looking at a few more of the emails that came in, I realized that the sites had been attacked. Just how they were attacked, I wasn’t sure. Were we compromised? I hoped not. It was time to get to the bottom of everything. Rob wasn’t too far behind me that day and I showed him what I’d found. I took a quick trip through the sites and it looked like everything was working okay while Rob started deconstructing the attack. Within an hour or two, we’d broken down the attack and figured out our sites were safe. Not that I had any doubt.

We realized that we'd been on the receiving end of a SQL injection attack and had stopped it cold. Had we failed, we figured each product page would have served up a browser-killing 78 popups; each one of them loaded with a malicious payload.

Rob and I gathered the rest of our development team in the main conference room and walked them through the attack and why our development practices kept our sites safe. For most of our developers, a SQL injection attack was purely theoretical. None of them had ever worked on a site where someone had tried to compromise it in such a visible manner. Seeing the attack "in the flesh" as it were served to drive home the reason for some of our methodologies.

For a breakdown of the specific attack and how it worked, check out ASCII Encoded/Binary String Automated SQL Injection Attack. You can also check out this cheatsheet on SQL Injection Attacks put together by someone who actually engages in this kind of activity.

As an aside, the nature of this attack was such that web crawlers like Googlebot and Yahoo’s Slurp actually spread the attack by cataloging and crawling compromised URLs. The geeky part of me found this quite interesting. I have no idea if this was a desired effect or not.

How to Stop SQL Injection Attacks

So, just how did we immunize our websites from this SQL injection attack? We followed a few simple rules and design strategies.

1. Stored Procedures and avoiding dynamic SQL.

   We have a policy that SQL statements will not be in-line with other code. If you’re accessing the database, you’re doing it through a stored procedure. This is a point that is not open for disucssion. Aside from the performance gains offered by properly crafted stored procedures, the enhanced security is key to keeping a site safe.

   You’ll never see anything in our code base that looks like:
   Dim myString as string="Select * from table1 where col1=" & myval

   By passing variables as parameters rather than inserting them inline, the database engine goes looking for the string "'or '1'='1'"rather than creating a true statement in the WHERE clause that's being processed. Additionally, because stored procedure parameters have a specific length associated with them, attempting to inject hundres of additional characters into those parameters will fail before the stored procedure even attempts to execute a single line of SQL.

   I am rabid about this and have actually dismissed programmers for violating this rule. Additionally, any use of dynamic SQL within a stored procedure has to be reviewed and approved. This rule is the primary reason the attack failed. By treating the additional query string data as text to be searched for, rather than SQL statements to be executed, we kept almost a terabyte of data secure and kept the sites running while other sites when down under this attack.

2. Don’t trust the query string.

   The query string is a great place for the inquisitive, young wannabe hacker to play and figure out what he can get your site to give up. As the Bloombit article above showed, it's also a great place for professional hackers to exploit flaws in your site. Rather than passing the contents of the query string variable directly to the stored procedure, assign them to an internal variable first. In our situation, the sites use a numeric category id to dynamically generate a page.

   Rather than passing Request.Querystring("CategoryID").Tostringinto my stored procedure we assigned it to an appropriate variable then pass the variable to the stored procedure.

   Dim catID as Integer = CInt(Request.Querystring("CategoryID").Tostring)

   Should someone attempt to stick extra info such as "; go; select * from systables" onto the CategoryID query string, the variable declaration will fail and we can handle things appropriately. This is one situation where I’m quite happy if my page fails. Better to have the page error out than pollute my databases.

3. Don’t trust user input.

   Everything you allow your user to put into a form must be hygiened. You cannot simply accept user input and place it into your database without cleaning it and checking it first. This attack targeted sites using ASP.Net specifically. .NET offers a number of pre-built data validators as well as the ability to do custom validation on either the client or server side. When it's this easy to implement, there's no excuse for not checking your user's inputs first.

4. Name your form fields smart.

   Naming your form fields with the field names in your database is just giving the resourceful hacker insight as to how to get into your database. If you want to capture a user’s first name and store it in a database field name FirstName , calling your input field txtFirstNamemight not be the smartest idea.

   In the case of the attack we saw in May, this really doesn't apply as the script looked for every text field in every user-defined table in the database. Still, minimizing the amount of help you provide the person trying to compromise your site is always a good idea