Anatomy of a SQL Injection Attack

Anatomy of a SQL Injection Attack

Tom Lofts
August 22, 2018

When discussing common attacks on web sites and web applications, you may have come across the term SQL Injection, but never been clear about exactly what it means or how it happens. This blog post takes you through the types of vulnerability we might find in our Application Security Testing Service and what to do about them.

What is Injection?

Injection in web applications occurs when an attacker can trick the application into taking data provided by the client and running it in some way. This is one of the most common attacks on websites and is considered the single most critical risk in the industry standard OWASP top 10 list of web application risks.

Almost every website or web application will accept data from users – this may be something the user is uploading such as a file or an image, posting a message or entering a username/password. Even actions as simple as entering a search term or clicking a link send data to the web application such as the search term or the page the user would like to visit.

The web application retrieves this data in the form of text or as a file, and decides how to process it. For example, if the user sends a username and password, the application will check these and decide whether to log the user in. The data which is uploaded will normally be in a specific format (e.g. a number of characters for a username), but an attacker will attempt to manipulate this format to upload something which the application allows, but has an unintended affect on the web application. Exactly what the attacker sends will depend on the type of injection they’re trying to perform.

SQL and SQL injection

The majority of web applications use a database to store data used as part of the web application such as users, posts, comments and other data. In order for the application to communicate with the database a programming language called SQL (https://en.wikipedia.org/wiki/SQL) is often used. SQL allows the application to perform actions on the database such as adding, retrieving and updating data.
Originally written as a language to help non-technical users retrieve information from databases, SQL is relatively simple to understand – an example to retrieve the email address for the user admin might be written as:

SELECT email FROM users WHERE username = 'admin'

The application will pass this to the database using the query function with the SQL in double quotes:

$database->query("SELECT email FROM users WHERE username = 'admin'");

Which would return an email address for the admin user such as ‘admin@example.com’.

But in most applications, you wouldn’t just want to retrieve details about the admin, but about any user, so the application needs to substitute ‘admin’ for the correct user they want to retrieve.

So the web application must now be updated to take a user from a variable such as $user and pass it to the database:

$database->query("SELECT email FROM users WHERE username = '" . $user . "'");

In the code above, the visitor to the website supplies the username of the user they want to visit, this is placed in the variable $user e.g. if $user = “admin” the line becomes when the code is run:

$database->query("SELECT email FROM users WHERE username = '" . "admin" . "'");

These parts are joined up to give the same code as before:

$database->query("SELECT email FROM users WHERE username = 'admin'");

And the database would return ‘admin@example.com’ in this case, but were the user to enter a different user e.g. ‘user1’, the database would return their email instead such as ‘user1@example,com’.

The web application is assuming that the user will always supply a valid username and that makes it vulnerable to SQL injection – if an attacker passes a carefully crafted username, it’s able to inject SQL rather than just usernames allowing the attacker to run other SQL commands on the database.

An example SQL injection attack

Looking at selecting a user again, you can see the user to select is surrounded by single quotes with the data entered by the user in yellow:

SELECT email FROM users WHERE username = 'admin'

This indicates to the database that ‘admin’ is the user to search for and not a SQL command.

As the application passes the username directly to this location in the database, an attacker can try to exploit this by including the single quote character in the username, this will trick the database into thinking that it should only look for part of the username provided, and that the database should try to run the rest as a SQL command.

E.g with the username “admin` A command here” the SQL becomes (with the data entered by the attacker in yellow again):

SELECT email FROM users WHERE username = 'admin' A command here'

Which causes an error as this isn’t a valid SQL command.

But an attacker with a knowledge of SQL can send any username at all such as "admin'; DELETE FROM USERS;"

SELECT email FROM users WHERE username = 'admin'; DELETE FROM USERS;'

Here the attacker is using the semicolon to separate SQL commands, some database will then interpret these as 3 separate commands as follows.
SELECT email FROM users WHERE username = 'admin';

Runs the original command

DELETE FROM USERS;

Deletes all users in the database – oh dear…

'

The last single quote is not a valid SQL command, so this will cause an error, but it doesn’t matter to the attacker who has already managed to delete all the users in the database.

As the attacker can attempt to run any SQL command their access is not limited to just deleting data, they may also be able to retrieve, add or modify data they shouldn’t be able to and may also be able to compromise other parts of the website.

Further SQL injection attacks

The attack above is a very simple example, and probably won’t work against the majority of vulnerable applications as it depends on the use of the semi-colon to use split the SQL commands and run the delete command.

Many databases block the running of multiple SQL commands at once, so the command below would either error or just run the ‘select’ command:

SELECT email FROM users WHERE username = 'admin'; DELETE FROM USERS;'

To work around this, attackers can write more complex SQL e.g. the `UNION` command which allows two commands to be joined together.
Let’s say an attacker wants to retrieve usernames and passwords from the database – they set the username to "' UNION SELECT email FROM users" which runs the following:

SELECT email FROM users WHERE username = '' UNION SELECT email from USERS'

This takes the result of the first command where an empty username is supplied (instead of admin in the previous examples, and adds all the email addresses for all users to it including the admin user:

admin@example.com
user1@example.com
user2@example.com

But this only gives the user the email addresses. If they’d like the passwords as well they can retrieve these using the `CONCAT` SQL command, this allows multiple columns to be joined together.

The attacker now sends "' UNION SELECT CONCAT(email, ' ', password) FROM users" which runs the following:

SELECT email FROM users WHERE username = '' UNION SELECT CONCAT(email, ' ', password) FROM USERS'

Which outputs the following:

admin@example.com admin_password
user1@example.com user_1_password
user2@example.com user_2_password

So the attacker now has a list of all usernames and passwords (which should be hashed), and could retrieve any information from the database in this way.

Protecting against SQL injection

Earlier, I mentioned many databases block the running of multiple SQL commands which prevents against the first attack, but it’s not possible for the database to protect itself against the use of UNION commands in this way as there are many legitimate situations where these may need to be used. In addition, there are a number of other ways similar SQL injection attacks can be attempted which don’t use the UNION commands.

Looking at the examples, you might think these attacks could be avoided by preventing the use of the single quote. Were the application able to strip single quotes “admin’; DELETE FROM USERS;” would be sent to the database as:

SELECT email FROM users WHERE username = 'admin; DELETE FROM USERS;'

Which is safe here as the attacker’s command is being used to check against the username now the single quote has been removed.

However, this causes problems as well as there are other ways attacks can recreate the single quote character to circumvent protection you might have applied. The safest way to avoid SQL injection is to use parameters. E.g. in the original example:

SELECT email FROM users WHERE username = 'admin'

The command is updated to use parameters:

SELECT email FROM users WHERE username = %S

And the parameter %s is supplied as “admin”

As the new command does not directly take the attacker input, the attacker can’t use single quotes or any other techniques to inject additional commands – any input they supply is treated as data and sent directly to the database without further manipulation.

The vast majority of databases now support parametrisation in this manner, but there’s still an awful lot of old or invalid code which is still vulnerable just waiting for an attacker to discover it.