SQL Injections Are Worse Than You Think

Data Leaks.

SQL injections are often associated with data leaks.
Indeed, injection attacks are a common way to steal private data from a database and to get access to protected information.

Let's take this query, for instance:
$sql = 'SELECT * FROM users WHERE id = ' . $_GET['id'];

If you know a little about database security, you can see how easy it is to dump all the users table by passing the string "1 OR 1" as the id parameter.
The query becomes like this:

SELECT * FROM users WHERE id = 1 OR 1

Since the "OR 1" condition is always true, the query returns all the rows.

But data leaks are not restricted to just a single table.
Once you have control over the SQL query, you can include other tables in the result.
The easiest way to do that is to use the UNION keyword. UNION adds a new set of rows to the query result.
For example, you can forge the id parameter so that the query becomes like this:

$sql = 'SELECT * FROM users WHERE id = 1 UNION SELECT * FROM items';

Moreover, the attacker can also specify a different schema, and get data from anywhere on the database the user has access to.
For example:

$sql = 'SELECT * FROM users WHERE id = 1 UNION SELECT * FROM schema2.users';

Now, the attacker may not be able to get immediate access to all the database.
For instance, the UNION statement requires all the results to have the same number of columns, as well as knowing the exact names of the tables. Otherwise, the attack won't work.

But do not feel too safe, yet:
There are some special attacks that give the attacker information about the database itself.

Blind SQL Injections.

Sometimes, the attacker is interested in the database structure.
For example, in order to trigger a data leak, sometimes the attacker must know tables and columns names.
In this case, the attacker can perform a blind injection attack.

The idea is that the attacker can leverage a vulnerable SQL query to test some condition.
If a condition is valid then the query will succeed, otherwise it will fail.
Let's take again the previous vulnerable query:

$sql = 'SELECT * FROM users WHERE id = ' . $_GET['id'];

If you pass an invalid value as the id parameter, the query will fail and you'll get a blank page or an error message as a result.

The attacker can use this to probe the database.
Let's look at an example:

SELECT * FROM users WHERE id = 1 AND SUBSTRING(DATABASE(), 1, 1) = 'a'

Here, the WHERE clause only works if the first letter of the schema is "a".
If the attacker sees the result, then it knows that the current schema name begins with an "a". If not, the attacker tries another letter until the right one is found.
The attacker can go on until the full schema name is discovered.

There are many SQL functions that the attacker can use in these attacks to gather all the required information, including table names, column names, and even column types.

Data Manipulation.

You have seen how SQL injections can tell attackers almost everything about a database, including its data content as well as its structure.

But it gets worse than that.
Some SQL queries are not limited to reading, but they can also change the database content: editing or deleting existing data and adding new rows.

Let's take a different query:

$sql = 'UPDATE cart SET quantity = ' . $_POST['quantity'] . ' WHERE id = ' . $_POST['id'];

Here, the attacker can set both the quantity and the id parameters to change any row in the cart table.
On top of that, the attacker can use a subquery to insert a value from another table into the quantity column of a specific item, making it visible via the web interface.
For example, the following query sets the quantity value to the id of the admin user:

UPDATE cart SET quantity = (SELECT id FROM users WHERE username = 'admin') WHERE id = 1;

Queries that use DELETE statements are even more dangerous, especially if the database user has access to different schemas.
Depending on the exact query, the attackers can delete any table from the current schema and, in some cases, even from other schemas as well!

System Takeover.

Some database engines, including MySQL, make it possible to write to system files with special SQL commands.
For example, the following query writes "hello" to the /var/www/hello.html file:

SELECT 'hello' INTO OUTFILE ' /var/www/hello.html';

The MySQL user must have the required permissions to execute these commands.
But if such permissions are in place, injection attacks become very dangerous.

Let's take again our first vulnerable query:

$sql = 'SELECT * FROM users WHERE id = ' . $_GET['id'];

Now, suppose that the attacker forges the following value as the id parameter:

"1 UNION SELECT '<?php ... ?>' into outfile '/var/www/attack.php'"

The query writes the "<?php ... ?>" string into the "attack.php" file on the server.
This way, the attacker can forge a customized PHP script and execute it on the server.

If the server security is poorly configured, the attacker can make the script perform any kind of operation.
In the worst case, the script can open a shell to the attacker's own IP address, effectively giving the attacker full system access.