SQL injection attacks

One of the more devastating attacks on a web application is also one of the most common: SQL injection. This technique allows an attacker to gain access to the database that underlies many web sites and read and potentially modify data that is not meant to be available to users of that site. This article provides an overview of how SQL injection works and what can be done to avoid it.

A classic example of SQL injection starts with a query that looks something like:

    SELECT id FROM users WHERE name='$name' AND pass='$pass';

This query might be used to authenticate users when they log in to a web site. If it returns a row, the user id returned is considered to be authenticated and the application proceeds to serve the correct page for that user. In this case, the $name and $pass variables would come from a login form that might look something like:

    <form method="post" action="login.php">
        <input type="text" name="name">
        <input type="password" name="pass">
        <input type="submit" value="login">
    </form>

If the login.php program in this example blindly sets the variables to the values that come from the user, a malicious user can bypass the authentication. Consider the following inputs:

    $user = "' OR 1=1 ";
    $pass = "' OR 1=1 LIMIT 1";

This results in a query that is completely different from what the web programmer expected:

    SELECT id FROM users WHERE name='' OR 1=1 AND pass='' OR 1=1 LIMIT 1;

This query will always return one row (unless the table is empty) and it is likely to be the first entry in the table. For many applications, that entry is the administrative login; the one with the most privileges.

This simple example barely scratches the surface of the kinds of attacks that can be made using SQL injection. Depending on the DBMS, it may be possible to do multiple queries via an injection by separating each with a semicolon:

    SELECT id FROM users WHERE name='' AND pass=''; DROP TABLE users;

which is, of course, a rather destructive injection. MySQL does not allow multiple queries in a statement, but PostgreSQL is susceptible to this technique.

Web site and/or database search functions are particularly dangerous because they display their output; if a malicious user can inject any query they choose, they can capture the entire contents of the database. The UNION keyword can turn a query such as:

    SELECT city, state FROM users WHERE name LIKE '%$search%';

into:

    SELECT city, state FROM users 
           WHERE name LIKE '%%' UNION 
                 SELECT name, pass FROM users 
			WHERE name LIKE '%%';

And instead of just printing the city and state of users that match the input string, we are also printing the username and password of every user in the system.

A certain amount of guessing column names and types is required if an attacker does not have access to the database schema, but they are often not very hard to guess given some understanding of the application. Some database systems, notably Microsoft SQL Server, seem to deliberately shoot themselves in the foot by providing the schema for all tables in a generally accessible database, thereby removing all the guesswork.

Injection also requires a certain amount of imagination to visualize the kinds of queries that might be going on behind the input boxes of a web form. It requires quite a bit of trial and error unless one has access to the source; this is why the majority of reported SQL injections are in free software or open source web applications.

Note that it is not only web forms using the POST method that are vulnerable, many web applications that use the GET method are vulnerable to injections via the URL:

    http://vulnerablewebapp.com/login.php?\
           name=%27%20OR%201%3D1%20&pass=%27%20OR%201%3D1%20LIMIT%201

Like many other web vulnerabilities, SQL injection stems from insufficient filtering of user input. Unfortunately, it is sometimes difficult to determine what kinds of input should be accepted (for example the password "' OR 1=1" would not necessarily seem illegal) and using various filtering functions provided by the language may not actually prevent injections. The PHP addslashes() function is often used to sanitize user input because it will put a backslash in front of single quotes which will stop the kinds of injections described above. Unfortunately, there are techniques to circumvent this particular 'fix' as well.

Probably the simplest way to protect queries from SQL injection is by using prepared statements with placeholders. Any reasonable database interface will provide a way to use this functionality and in many cases, it is fairly portable between languages and DBM systems.

Instead of directly interpolating string values into query strings, a query is prepared using '?' as a placeholder for the variables as shown in the following pseudocode:

    $sth = prepare("SELECT id FROM users WHERE name=? AND pass=?");
    execute($sth, $name, $pass);

This has a number of advantages: the DBMS library is responsible for properly quoting the values and because of the way the variables are bound to the query, they can never be treated as anything other than data for the particular place they have in the prepared statement. This effectively turns the injection attempt above into a query like:

    SELECT id FROM users WHERE name='\' OR 1=1 ' AND pass='\' OR 1=1 LIMIT 1';

which is unlikely to authenticate.

Another way to defend against injections is by ensuring that all user input is passed through a database specific quoting function before being used in a query:

    $name = db_quote($name);
    $pass = db_quote($pass);
    SELECT id FROM users WHERE name=$name AND pass=$pass;

Depending on the language and database API, this method may also be fairly portable.

The final recommended technique is also the most complicated; but it can provide an additional level of security if stored procedures are available for the DBMS. Stored procedures are queries (and more complicated functions) that are created by the database administrator and stored with the database. These procedures are then called by the application code to do any queries that they require. The equivalent of the prepare functionality is done on the procedures at the time they are stored and with proper coding, this will prevent injections. One of the main advantages is that these procedures run with the privileges of the user that stored them, instead of the user invoking them and this allows the application to have a much more limited set of privileges than it would normally require. The upshot is that it can protect the database from reading or writing even if the application is subverted in some way.

SQL injections are clearly a serious security problem, but one that can be thwarted relatively easily once one understands the problem and the ways to program around it.

---

(Log in to post comments)