Second Order SQL Injection (Part 3 of the SQL Series)

Liam Follin

Senior Consultant

Liam is one of the senior consultants at Pentest People, with a wide range of skills and experience from Web Applications to Social Engineering he's able to give great comments and opinions on cybersecurity matters.

Second Order SQL Injection

Second Order Injection

We have spoken before about SQL injection in previous blog posts and touched briefly on Second Order Injection, but what is Second Order Injection and how can we protect against it? (I will give a clue, it begins with a V).

A Second Order Injection is the same as a traditional Injection attack but the payload is already stored in the database, with the payload waiting to be triggered by a user at a later date.

Many developers will use things like PDO to protect against SQL injections, which is great.  Let’s look at an example of user registration (found on a PHP tutorial site!) script and see how easy it to exploit this vulnerability.  

$sql = "INSERT INTO user (username, password)  VALUES (:username, :password)"; $data = [      'username' => $userName,      'password' => $password,      'first_name' => $firstName,      'second_name' => $secondName ]; $stmt = $conn->prepare($sql); $stmt->execute($data);

In this example, the data submitted by the user will be parameterized meaning that a vicious input will not affect the database. Now as the statement is parametrised if the following MySql is submitted

'; DROP TABLE Users; --

it will not affect the database. However, this bit of MySql code is now sat waiting in the user table.

The problem here is if the username is used in another MySql statement that does not use parameterised statements. For example, we have a list user page in the admin panel of an application and for speed the developer wrote a basic select script, skipping the use of parameterized variables as they believed the data must be fine in the following database. The simple MySQL statement:

$sql = "SELECT * FROM user WHERE username = '{$userName}'";$stmt = $conn->query($sql);$user = $stmt->fetch();Now becomes SELECT * FROM user WHERE username = ''; DROP TABLE User; --';

And that is it, the table is dropped.

During testing for the proof of concept for this blog post, I was surprised and worried about how easy it was to exploit the Second Order Injection vulnerability. Obviously this is a simple example but it is possible to create a more complex attack.

It is also possible to inject malicious javascript code into a website using a similar method. It is possible to submit and store the following javascript code as the username or another value.

Web Application Testing Services Find out more about our Throughout the three parts of my posts regarding SQL injection attacks, I have stressed that both inputs and outputs must be validated. Relying on parameterized variables alone is not enough, validation is always needed and it is your responsibility as a developer to make sure this is the case.. Cross-site Scripting attack (You can even split this payload into first name and surname as its not uncommon for these variables to be printed out next to each other on a page.)

Video/Audio Transcript