SQL Injection

What is SQL Injection?

SQL injection is an attack that allows an attacker to inject or run malicious SQL code via web page input.

SQL injection can be used to expose sensitive information like user's credit card information, email addresses, ... etc. It can even be used to bypass the authentication process and have access to the entire database.

SQL Injection attack is one of the most common website hacking techniques.

When SQL Injection happens?

SQL injection attacks occur when a web application does not validate values received from an input parameter, a web form, and the database server performs them directly. This will allow an attacker to manipulate the input so that the data will be interpreted as code.

The following example creates a SELECT statement by adding a variable (user_id) to a select string.

useId = getRequestString("user_id");
sqlQuery = "SELECT * FROM user WHERE user_id = " + userId;

The rest of this chapter explains the danger of using user input directly without validation in SQL statements.

SQL Injection Based on 1 = 1

The above example code aims to create an SQL statement to select a user by his given user id.

If there is no validation to check what the user has entered, the user can enter some malicious code like the following:

User Id: <input type="text" name="user_id" value="3656 OR 1=1" >

The corresponding SQL statement that will be executed for the above input will be as follows:

SELECT * FROM user WHERE user_id = 3656 OR 1=1;

The SQL statement above is valid and will return all records from the "User" table since OR 1=1 is always TRUE.

The above example is a dangerous code because the attacker can access all information of the "User" table containing passwords and sensitive data.

SQL Injection Based on ""=""

The following example uses an email and password to log-in to a web site:

User email: <input type="text" name="user_email" value="user-example@email.com">

Password: <input type="text" name="user_password" value="password-example">

userEmail = getRequestString("user_email");
userPassword = getRequestString("user_password");

sqlQuery = 'SELECT * FROM user WHERE user_email = " ' + userEmail  + ' " AND user_password = " ' + userPassword + ' " ';

The result after passing the above input data will be as follows:

SELECT * FROM user WHERE user_email = "user-example@email.com" AND user_password = "password-example"

An attacker might have access to user email and passwords in a database by simply inserting " OR ""=" into the user email or password input:

User email: <input type="text" name="user_email" value="" OR ""="">

Password: <input type="text" name="user_password" value="" OR ""="">

The above input will create a valid SQL statement as follows:

SELECT * FROM user WHERE user_email ="" OR ""="" AND user_password ="" OR ""=""

The above SQL statement is valid and will return all records from the "User" table since OR ""="" is always true.

SQL Injection Based on Batched SQL Statements

A batch of SQL statements is a group of at least two SQL statements separated by semicolons.

The following SQL statement will return all records from the "User" table, then delete the "Order" table.

SELECT * FROM user; DROP TABLE order;

Giving the following code example:

useId = getRequestString("user_id");
sqlQuery = "SELECT * FROM user WHERE user_id = " + userId;

And the following input:

User Id: <input type="text" name="user_id" value="3656; DROP TABLE order">

And so the valid SQL statement would be as follows:

SELECT * FROM user WHERE user_id = 3656; DROP TABLE order;

Preventing SQL Injection

Always validate user input and don't trust the user input. Never build SQL statements directly from user input.

To prevent SQL Injection, you can use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time.

PHP Example:

$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id = :userId");
$stmt->bindParam(':userId', $user_id);
$stmt->execute();

The parameters are represented in the SQL statement by a : marker.

Using the SQL parameters, the SQL engine checks each parameter to ensure that it is correct.