SQL Injection: Basics and Defence

Structured Query Language (SQL) is used all over the web and is potentially vulnerable to an injection attack any time that user input is insecurely concatenated into a query. An injection attack allows an attacker to alter the logic of the query and the attack can lead to confidential data theft, website defacement, malware propagation and host/network compromise.

For those less familiar with SQL it is a language used for storing, retrieving, modifying and removing data from a database. It can often also be used to read or modify files on the remote system and execute operating system commands.

There are different query types in SQL, such as SELECT, INSERT, UPDATE and DELETE as well as the idea of “Stored Procedures”. These are used for retrieving data from the database (SELECT), adding new rows to a table (INSERT), taking a table row and modifying it (UPDATE) or removing rows from a table (DELETE). There are many different stored procedures however the most interesting is likely the MSSQL procedures xp_cmdshell which can be used for executing commands on the remote database host.

SELECT Statements retrieve data from the data base and look like:

SELECT column FROM table WHERE condition is true

For example the following could be used for a login system:

SELECT username,password FROM users WHERE username='$INPUT1$' AND password='$INPUT2$';

Where $INPUT1$ and $INPUT2$ is text taken from the user of the application. Statements are separated by semi-colons, so two statements could be “stacked” and executed one after the other by separating them with a semi-colon. Developers can add comments into a statement by preceding the comment with a “” before the comment, which makes the SQL parser ignore any following text.

Taking this into account an attacker could utilize the following payload for INPUT1 to cause a login bypass: x’ OR 1=1 —

This input would change the command to the following:

SELECT username,password FROM users WHERE username='x' OR 1=1 -- ' AND password='$INPUT2'

As you can see from the above, the double dash has caused the password check to be “commented out” so that it has no effect. Also if you remember back to the first SQL command I showed highlights that the SELECT statement evaluates the WHERE clause to determine if it is a Boolean true. That’s what the OR 1=1 part of the input achieves, as one always equals one. The effect on many applications that are vulnerable to SQL injection in the login form is that all account data will be returned and the database will simply log in the attacker in as the first user in the database.

As you can see the attacker has altered the intended logic of the statement and they could use this to bypass authentication, cause the database to leak confidential information or even execute functions such as executing operating system commands.

Defending against Injection

As I said at the start of this article the issue really is that user input is insecurely concatenated into a query. So there’s two things to note here, the first is concatenation and the fix here is instead to use “parametrized” or “prepared” statements, these are available in all modern languages and frameworks, these effectively separate the query from the user input so that the database cannot mix the two up and effectively stop SQL injection attacks on their own. An example for PHP can be found in the PHP documentation here, which gives a flavour of the general idea.

However another thing to consider, to stop other kinds of injection and web application attack, it’s a good idea to consider filtering all user input. I’ve written about user input filtering tactics here.

Read More