Don't get stung by SQL Injection attacks

Takeaway: SQL Injection is no new arrival on the scene, yet high-profile attacks still occur. Alfonso Barreiro lists the basic measures to take to help mitigate the threat.

Despite being known for some time, SQL Injection attacks continue to be one of the most frequent ways a website can be compromised. It has been highly publicized recently as the main tool used in the attacks of high profile organizations such as Sony, PBS, and HBGary, but many misconceptions about it still remain.

What is SQL Injection?

SQL Injection basically inserts (”injects”) a SQL command to be run at a backend database by using input data that has not been properly validated in an application. For example, a login web page usually requires a username and a password. The web application uses a SQL command to validate the username/password combination and provides the user with information. The normal SQL command could take the following form:

SELECT * FROM Table WHERE UserName = ‘$User' AND Password = ‘$Pass';

In the previous example, $User is the username and $Pass is the password submitted on the web page by a user. The quotes surrounding both inputs are part of the query and set them up as literal text strings.

A regular user entering the username of “Michael” with the password “password” would result in the following command being executed:

SELECT * FROM Table WHERE UserName = ‘Michael' AND Password = ‘password';

The command would return all fields from the record in the table where the username and password match. An attacker however, could enter a single quote (´) in the username field and since the input is actually being parsed literally, the resulting command would be executed instead:

SELECT * FROM Table WHERE UserName = ´´´ AND Password = ‘';

This would then generate a syntax error. This is the proof the attacker would need to confirm that this particular login web page is vulnerable to injection attacks. The attacker could then insert the following in the username field:

hacker' OR 1=1; --

The resulting command would then become:

SELECT * FROM Table WHERE UserName = ‘hacker' OR 1=1; --' AND Password = ‘';

Here the pair of hyphens (–) represent the start of a comment, so from that point forward the text is ignored. The real command would be:

SELECT * FROM Table WHERE UserName = ‘hacker' OR 1=1;

The expression 1=1 is always evaluated as true for all records and since a logical OR expression is always evaluated as true when at least one of the components of the expression is true, this SQL command will always return data from the table (assuming it’s not empty).

This is a basic example, but an attacker could be far more creative and include different SQL commands to be executed such as insert, update or delete.

How to protect your applications

There are several ways to reduce your web applications’ exposure to SQL injection attacks, but as with many security measures, a layered approach in which all of the available mitigations are in place would be ideal.

Configure error handling

In the previous example, a syntax error revealed that the web form could be vulnerable to SQL Injection. Ensuring that error messages are consistent and do not contain debugging information (the default for many programming frameworks) could deny an attacker the information needed to refine an attack.

However, by only using generic error messages and not applying other defense mechanisms, it only makes a SQL injection attack more difficult, not impossible. This is known as Blind SQL Injection, which is identical to a normal SQL Injection attack, but it does not rely on error feedback from the server for refinement. This may slow down an attacker, but the end result is the same and just as dangerous.

Sanitize the input

Ensure that the user inputs do not contain dangerous characters or code. The recommended method is to create a “whitelist” of accepted inputs, depending on what should be the expected response. This not only helps in protecting against SQL Injection, but also avoids typos or other errors from being stored in your database. Note that this is not always practical since sometimes there are some fields where a “dangerous” character could be valid. For example, in a “last name” field, O’Brian includes a quote, but in the context of the entry, it’s a valid character.

Use parameterization

Though input sanitation helps, it still means that the user input could be considered part of a SQL command. Using prepared statements, parameterized queries or stored procedures (which are all supported in some way by essentially all databases), allows for a clear separation between data (the user input) and the command itself (the SQL programming). The input would be considered just “data” and some proper validation would be applied to it.

When using stored procedures remember that within it you can still create SQL commands dynamically, defeating its purpose in protecting against SQL Injection.

Apply the principle of least privilege and role separation

Whenever possible, create accounts that have the minimum amount of privileges needed for the task they need to perform. It shouldn’t be necessary to run a database application during day to day operations as a database administrator. This way a successful attack will not immediately grant access to the rest of the data or the server environment.

This is not a comprehensive guide on SQL Injection, as there are other potential avenues for attacks. The mitigation techniques described here are specific for countering SQL Injections, but as always, do not underestimate other basic layers of security to protect your data.