Lukas' Notes

SQL Injection

2 min read

web security databases

Definition

SQL Injection (CWE-89)

A SQL injection occurs when a server builds an SQL query by string-concatenating user input, so attacker-supplied metacharacters such as ' (string delimiter), -- (inline comment), or ; (statement separator) are parsed by the database as code rather than as data.

<?php
$query = "SELECT * FROM users WHERE email = '" . $_POST["email"] .
         "' AND password = '" . $_POST["password"] . "'";
$sth = $db->query($query);
?>

Authentication Bypass

Example

Submitting email=admin@localhost' -- turns the query into

SELECT * FROM users
WHERE email='admin@localhost' -- ' AND password='foobar'

The -- comments out the password check, so the attacker authenticates as admin without a password.

Data Exfiltration via UNION

Column-count probing

The attacker does not know the number of columns of the original SELECT, so they probe with UNION SELECT null, null, … until the query no longer errors. null is type-compatible with every column. Then a second UNION SELECT lifts real columns out of the database:

' UNION SELECT null, mail, password FROM people -- -

Every column in a UNION must have the same count (and, depending on the DBMS, compatible types); LIMIT applies to the whole result set.

Exfiltrating Database Metadata

Enumerate the schema via information_schema

The attacker generally does not know the schema. Relational databases expose their metadata through the information_schema: information_schema.tables and information_schema.columns list every database, table, and column name. A UNION SELECT against these lets the attacker map the schema and then read sensitive tables column by column.

' UNION SELECT table_schema, table_name FROM information_schema.tables -- -
' UNION SELECT null, column_name FROM information_schema.columns
       WHERE table_name = 'users' -- -

Obs

SQLi enables authentication bypass, data exfiltration, data loss and corruption, denial of service, and — through privilege escalation — RCE on the database host.

Backlinks