SQL Injection

SQL injection flaws are very critical as they allow a remote attacker to gain access to the underlying database. In the worst case scenario, the attacker can read, write and delete content in the database.

What can happen?

The attacker can gain access to all data stored on the system. This makes it possible to read, create and delete data. Popular attack techniques include stealing passwords and changing the website’s content. Under some circumstances, remote command execution might also be possible. In 2009, the Heartland Payment Systems was compromised by an SQL injection attack that resulted in a leak of 134 million credit card details.

SQL Injection example

This is a sanitization issue as the most common flaw is the lack of sanitization of user input that is used to set up an ad hoc SQL query. If not properly sanitized, the attacker can force their way to inject valid SQL syntax in the original query, thus modifying its prior purpose.

An example of a vulnerable “login” for PHP/MySQL would look something like this:

$db = new mysqli('localhost', 'root', 'passwd', 'base');
$result = $db->query('SELECT * FROM users WHERE user="'.$_GET['user'].'" AND pass= "'.$_GET['password'].'"');

Suppose an attacker submits " OR 1 -- as username and whatever as password. The variables would then contain these values:

$_GET['user'] = " OR 1 --
$_GET['password'] = whatever

The resulting query would become:

SELECT * FROM users WHERE user="" OR 1 -- AND pass="whatever"

Everything after -- (which indicates the start of a comment in SQL) will be discarded and ignored. The query to be executed would then look like this:

SELECT * FROM users WHERE user="" OR 1

The query now states "Grab everything (SELECT *) from the user list (FROM userswhere the username matches nothing (WHERE user="") or 1 (which will be interpreted as True (OR 1))". Since the latter statement will always result in True, the right hand of the statement will successfully eliminate the left hand statement and the condition will always be true. The result of that query would be the same as this one:

SELECT * FROM users

Which would return all data there is about all the users. For example, the injection in the $_GET['user'] parameter is enough to make the MySQL server select the first user and grant the attacker access to that user.

Remediation

Prepared statements will protect against (almost) all SQL injection vulnerabilities. They take the form of a template in which certain constant values are substituted during execution for variables containing user input. This way, you can be sure of the type of the substitutes and it will also help avoid all bad characters that might break an SQL statement, leaving the SQL query properly sanitized as no user input may break it.

Some functions like mysqli_real_escape_string() in PHP can also protect against SQL injections. However, be careful to read the documentation when using those kind of functions. For example, in PHP addslashes() may seem to be a good alternative but useless when it comes to SQL injection protection due to malicious charset tricks.

Resources