Tip/Trick: Guard Against SQL Injection Attacks

SQL Injection attacks are really nasty security vulnerabilities, and something all web developers (regardless of platform, technology or data layer) need to make sure they understand and protect themselves against. Unfortunately developers too often neglect putting focused time on this - and leave their applications (and worse their customers) extremely vulnerable.

Michael Sutton recently published a very sobering post about just how widespread this issue is on the public web. He built a C# client application that uses the Google Search API to look for sites vulnerable to SQL Injection Attacks. The steps to achieve this were simple:

1. Look for sites that have querystring values (example: search for URLs with "id=" in the URL)
2. Send a request to the sites identified as dynamic with an altered id= statement that adds an extra quote to attempt to cancel the SQL statement (example: id=6')
3. Parse the response sent back to look for words like "SQL" and "query" - which typically indicate that the app is often sending back detailed error messages (also bad)
4. Review whether the error message indicates that the parameter sent to SQL wasn't encoded correctly (in which case the site is open to SQL Injection Attacks)

Of a random sampling of 1000 sites he found via his Google search, he detected possible SQL Injection Attack vulnerability with 11.3% of them. That is really, really scary. It means hackers can remotely exploit the data in those applications, retrieve any unhashed/encrypted passwords or credit-card data, and potentially even log themselves in as administrators to the application. This is bad not only for the developer who built the application, but even worse for any consumer/user of the application who has provided data to the site thinking it will be secure.
So what the heck is a SQL Injection Attack?
There are a couple of scenarios that make SQL Injection attacks possible. The most common cause are cases where you are dynamically constructing SQL statements without using correctly encoded parameters. For example, consider this SQL query code that searches for Authors based on a social security number provided via a querystring:

Dim SSN as String
Dim SqlQuery as String
SSN = Request.QueryString("SSN")
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

If you have SQL code like the snippet above, then your entire database and application can be hacked remotely. How? Well in the normal scenario users will hit the site using a social security number which will be executed like so:

' URL to the page containing the above codehttp://mysite.com/listauthordetails.aspx?SSN=172-32-9999
' SQL Query executed against the database SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

This does what the developer expected, and searches the database for author information filtered by the social security number. But because the parameter value hasn't been SQL encoded, a hacker could just as easily modify the querystring value to embed additional SQL statements after the value to execute. For example:

' URL to the page containing the above codehttp://mysite.com/listauthordetails.aspx?SSN=172-32-9999';DROP DATABASE pubs --

' SQL Query executed against the database SELECT au_lname, au_fname FROM authors WHERE au_id = '';DROP DATABASE pubs --

Notice how I was able to add the ';DROP DATABASE pubs -- clause to the SSN querystring value and use it to terminate the current SQL statement (via the ";" character), and then add my own malicious SQL statement to the string, and then comment out the rest of the statement (via the "--" characters). Because we are just manually concatenating the SQL statement in our code, we will end up passing this to the database - which will execute first the query against the authors table, and then delete our pubs database table. Bang - it is now gone.
In case you think the idea of anonymous hackers deleting your database tables is bad, that is unfortunately actually one of the better scenarios when a SQL Injection Attack is involved. Rather than just destroy data, a hacker could instead use the above code vulnerability to perform a JOIN that retrieves all of the data within your database and displays it on the page (allowing them to retrieve username/passwords/credit-cards). They could also add UPDATE/INSERT statements to modify product prices, add new admin users, and really screw up your life (imagine auditing your inventory at the end of the month, only to discover that the actual number of products in your warehouse is different then what your accounting system reports...).
How do you protect yourself?
SQL Injection Attacks are something you need to worry about regardless of the web programming technology you are using (all web frameworks need to worry about it). A couple of very basic rules you must always follow:
1) Don't construct dynamic SQL Statements without using a type-safe parameter encoding mechanism. Most data APIs (including ADO + ADO.NET) have support for allowing you to specify the exact type of a parameter being provided (for example: string, integer, date) and can ensure that they are escaped/encoded for you to avoid hackers trying to exploit it. Always use these features.
For example, with dynamic SQL using ADO.NET you could re-write the code above like below to make it safe:

Dim SSN as String = Request.QueryString("SSN")

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)

This will prevent someone from trying to sneak in additional SQL expressions (since ADO.NET above knows to string encode the au_id value), and avoid other data problems (incorrectly type-casting values, etc). Note that the TableAdapter/DataSet designer built-into VS 2005 uses this mechanism automatically, as do the ASP.NET 2.0 data source controls.
One common misperception is that if you are using SPROCs or a ORM you are completely safe from SQL Injection Attacks. This isn't true - you still need to make sure you are careful when you pass values to a SPROC, and/or when you escape or customize a query with an ORM that you do it in a safe way.
2) Always conduct a security review of your application before ever put it in production, and establish a formal security process to review all code anytime you make updates. This later point is super important. Too often I hear of teams that conduct a really detailed security review before going live, then have some "really minor" update they make to the site weeks/months later where they skip doing a security review ("it is just a tiny update - we'll code review it later"). Always do a security review.
3) Never store sensitive data in clear-text within a database. My personal opinion is that passwords should always be one-way hashed (I don't even like to store them encrypted). The ASP.NET 2.0 Membership API does this for you automatically by default (and also implements secure SALT randomization behavior). If you decide to build your own membership database store, I'd recommend checking out the source code for our own Membership provider implementation that we published here. Also make sure to encrypt credit-card and other private data in your database. This way even if your database was compromised, at least your customer private data can't be exploited.
4) Ensure you write automation unit tests that specifically verify your data access layer and application against SQL Injection attacks. This is really important to help catch the "it is just a tiny update so I'll be safe" scenario, and provide an additional safety layer to avoid accidentally introducing a bad security bug into your application.
5) Lock down your database to only grant the web application accessing it the minimal set of permissions that it needs to function. If the web application doesn't need access to certain tables, then make sure it doesn't have permissions to them. If it is only read-only generating reports from your account payables table then make sure you disable insert/update/delete access.