A SQL Injection Vulnerability allows attackers to execute arbitrary SQL commands against your database. The consequences are severe: attackers may steal and modify data, compromise website user accounts, and gain full control of the server or network where the database resides.

How it works

SQL Injections happen when you feed malicious user input to the database as part of a SQL query. For example, one often builds WHERE clauses or INSERT statements out of user-supplied data. But what happens if an evil user enters SQL commands instead of the data you expect? If the input is woven caressly into a query, pain ensues. Here's an example: 

// THIS IS EXAMPLE VULNERABLE CODE, DO NOT USE IT:
private static void LoadProductByName(HttpContext httpContext) {
    string productName = httpContext.Request.QueryString["productName"];

    // THE LINE BELOW IS A SQL INJECTION BUG:
    string selectStatement = "SELECT * FROM Products WHERE Name = '" + productName + "'";

    ExecuteQueryAndOutputJson(selectStatement, httpContext);
}

1
2
3
4
5
6
7
8
9

This web-accessible method retrieves a product by name and outputs the product's data in JSON. The product name is supplied in a web request's HTTP query string (the part of the URL after the ?). Presumably a user enters the product name into an HTML form and an AJAX request is made to the method. Inside the method three pieces are joined to form a SQL query: the first part of a SELECT statement, the product name, and a closing single quote. If the user enters frobnozzle into the HTML form, we get the query: 

SELECT * FROM Products WHERE Name = 'frobnozzle'

Sure enough, the query above would return data for the frobnozzle. The user's input is surrounded by single quotes ('), which is how strings are expressed in SQL. But what if a single quote were entered into the product name itself? Since the programmer made no provisions for that, the extra quote would interfere with the SQL statement. For example, an attacker could enter 

' OR 1 = 1--
as the product name. While that'd be an odd name for a product, it would generate the following valid SQL query: 
SELECT * FROM Products WHERE Name = '' OR 1 = 1--'

This is not what the programmer intended. The query will now return all of the products (since 1=1 is always true) instead of a single product by name. The two dashes (--) at the end of the input are used to start a comment in SQL, thereby hiding the final single quote (the last character in the query) from the SQL parser. This is necessary because the final single quote would otherwise generate a syntax error, and the database would not execute the attacker's command. "So far so good", thinks the free-falling optimist before hitting the ground. Now when an attacker supplies 

' UNION SELECT Id, UserName, Password, 0 FROM Accounts--
for a product name, we get this: 
SELECT * FROM Products WHERE Name = '' UNION SELECT Id, UserName, Password, 0 FROM Accounts--'

That's more interesting. This query would return no products, but it would return all of the rows in the Accounts table. Oops. You might wonder how the attacker could have known about the existence of such a table. Unfortunately the richness of modern database software is a curse in this situation. There are well-known exploit techniques that enable an attacker to do almost anything even in the most contrived SQL Injection scenarios. Subject to the permissions of the database account being used, attackers can:

  • Enumerate tables, columns, stored procedures, and other objects.
  • Steal or modify all of the data in the database.
  • Send emails through the database server.
  • Write to and read from files in the server's file system, which in turn spawns a myriad evils.
  • Run OS commands. Often this leads to immediate game over for your servers and network.

Exploiting

There are two vulnerabilities available for exploitation, both running in ASP.NET backed by a live SQL Server 2005 database. The front page for the app is here. If you find a way to do something nasty in the database, please alert me. Each example has suggestions for inputs, but feel free to try your own. I think that the better you understand the problem, the better you can prevent it.

Victim, Inc. Product Lookup

The first vulnerability is the example from the How It Works section. It is reachable at http://victim.duartes.org/VulnerableService.ashx?LoadProductByName.

Input suggestions:

  • Valid products: Representative, Senator, President
  • ' OR 1 = 1--
  • ' UNION SELECT Id, UserName, Password, 0 FROM Accounts--
  • ' UNION SELECT 0, Name, SCHEMA_NAME(Schema_ID), 0 FROM sys.Tables--
Victim, Inc. Product Lookup
Product Name:

Product Information:

No products selected.

Victim, Inc. Check Password

The second vulnerability is at http://victim.duartes.org/VulnerableService.ashx?CheckPassword. It is a little more realistic in the sense that it does not return whatever data you manage to query for. Instead it only retuns a row count, 1 for a correct user name/password and 0 otherwise. By watching the server response you can see the exact value returned, but the UI only says "yes" or "no".

This yes/no situation is common in real-world injection scenarios. It is exploited by a series of "questions" asked through queries. In this case the UI says "password is correct" if your query returns one or more rows, or "password is wrong" for zero rows. Attackers express general questions about the database in terms of the available query and feedback. The Learn More section links to a paper on exploit techniques.

Input suggestions:

  • Valid accounts: Admin/secret, Anne/apples, Georgie/oranges
  • Find out if there are user names starting with S by entering the following user name: ' OR UserName LIKE '[S]%' --. If there is at least one such user name, you'll get a "password is correct" message.
Victim, Inc. Check Password

Management has decided security is important. Therefore all employees are required to check their passwords once a day to ensure the password has not been forgotten. Type your password below.

User name:
Password:

Naive work around

The 'immediate' solution to SQL Injection vulnerabilities is to be careful when building queries out of strings. This is like being cautious at Russian roulette. You may get by some of the time, but vulnerable queries will inevitably slip by. Since it only takes one vulnerability to nail your app, it's best to seek other defenses.

If you do play Russian roulette, then I think these techniques are useful:

  • Consider all input to be suspect. This article gave you a simple scenario to illustrate the issue, but real-world injections can happen in obscure, convoluted ways. There are injections where attackers will hit a URL in your server to poison your log files in order to exploit a log analysis tool. It goes down from there. The rule here is to shun the habit of trying to decide whether input might be dangerous: always assume it is. Don't let a sleazy spammer own you, treat every string as if it were handed to you by Satan himself.
  • Sanitize input at the very last moment. This may sound odd, but it's the best approach. I've often seen codebases where the responsibility for sanitizing input is spread among different layers of the app. The result is that you're never quite sure whether input has been sanitized. If you spot a string going raw into the database, you have to rummage through various methods trying to decide whether it's a vulnerability. This is Russian roulette with half the chambers filled. The solution is to always sanitize at the last moment, right before a query is executed. Then you have a simple rule for correct code. Programming, code inspections, searching problem spots, and static analysis will all work much better.
  • When filtering input, use white lists. In security, never try to enumerate the bad. Always enumerate the good and throw the rest out. For example, don't worry about black listing every bizarre character. Simply whitelist letters and numbers (or whatever is appropriate), and reject all else. But don't forget last names like O'Brien. ;)
  • To prevent against the 'classic' injection shown in How It Works, you simply need to escape single quotes entered by the user. In most databases you do that by entering two single quotes in a row. The code shown would be fixed thus: 
    string unsafeProductName = httpContext.Request.QueryString["productName"];
string safeProductName = unsafeProductName.Replace("'", "''");
string selectStatement = "SELECT * FROM Products WHERE Name = '" + safeProductName + "'";

    1
2
3
  • The other common injection is for numbers, when no single quotes are involved. If you are going from a numeric type to a string, then presumably there's no way for the attacker to get a SQL command in there. If you have a string representing a number, use a white list to accept only digits and, if applicable, a decimal point and thousand separator.

Defense in depth

By far the best defense are tools that make it impossible to produce a SQL Injection vulnerability. This is easier than it sounds. For example, LINQ-to-SQL queries in C# are immune to SQL Injections. Likewise, most native methods in Ruby on Rails' ActiveRecord are immune (but you are still vulnerable with hand-written queries or conditions). Many languages have libraries ready to save you from yourself. Seek technologies that offer you this protection.

If you cannot switch to a safe technology, then try to build an immune interface using your existing tools. For example, you might have a central class that is responsible for assembling or executing queries, and this central class takes care of preventing injection. The rest of the code is safe by default, even when it's written by people who don't know anything and aren't trying hard enough.

Another essential defense is restricting the permissions for the database account used by your web application. The account should have the minimal permissions required by the application. This technique can sometimes foolproof your database server against SQL Injections, or at least mitigate the damage. A testimony to its power is the fact that you can still read this article (instead of being served, say, the Goatse) despite my wide open database. (Unless you were served the Goatse. If so, please ignore the 'testimony' bit.)

You must also block the execution of operating system commands via the database. Many database systems offer a facility for executing commands in the host operating system (eg, xp_cmdshell in MS SQL Server). Attackers can take advantage of such a facility to compromise the server itself and perhaps - ayeee! - your entire network.

Use a low-privilege OS account to run the database system, so that compromise of the database software does not lead immediately to a full compromise of the server. Command execution is one way attackers do this, but there are many others (eg, writing and reading to the file system, exploiting bugs in your vendor's software). The best protection is to use a low-privilege OS account to run the database software itself.

Lastly, there are code-level techniques to prevent injection. One is to adopt a naming convention that distinguishes safe from unsafe strings. Even better is to create a class (or user-defined type) to hold dangerous strings such that they cannot be readily mixed with regular strings. This class can offer methods to produce SQL-safe, HTML-safe, or URL-safe versions of a string.

Conclusion

SQL Injection vulnerabilities are common and severe. It is futile to try to prevent them if raw queries are built from strings and submitted to the database. You need to solve the problem in a definitive, sweeping manner by relying on injection-proof tools. You should also harden your database against injections.

Learn more

What Now?

rss feed Subscribe to my blog email Email feedback discuss Discuss back to articles index Back to Articles index