SQL Injection

SQL injection happens when user input becomes part of the SQL command. The attacker’s text is treated as SQL, not as data. It is one of the oldest and most damaging web bugs, and it is entirely preventable.

A vulnerable login check

1
2
3
4
5
6
7
8
9
10
11
12
13

<?php
$pdo = require __DIR__ . "/db.php";

$email = "' OR '1'='1";    // what an attacker types into the email field

// DANGEROUS: the input is joined into the SQL text
$sql = "SELECT name FROM users WHERE email = '$email'";

echo $sql . "\n\n";

$names = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);

echo "Rows returned: " . count($names);

1
2
3

SELECT name FROM users WHERE email = '' OR '1'='1'

Rows returned: 3

Read the SQL that PHP built. The attacker’s text closed the quote and added OR '1'='1', which is always true. The query returns every user. In a login form, that can log the attacker in as the first user in the table.

What attackers can do

• Bypass logins.
• Read data from any table (users, emails, password hashes, orders).
• Change or delete data.
• Sometimes run commands on the server, depending on the database setup.

Real data breaches have started with exactly this kind of mistake.

The fix: prepared statements

1
2
3
4
5
6
7
8
9

<?php
$pdo = require __DIR__ . "/db.php";

$email = "' OR '1'='1";

$statement = $pdo->prepare("SELECT name FROM users WHERE email = :email");
$statement->execute(["email" => $email]);

echo "Rows returned: " . count($statement->fetchAll());

1

Rows returned: 0

The text is sent as data. The database looks for a user whose email is literally ' OR '1'='1, and finds none. See Prepared Statements.

Where injection hides

1
2
3
4
5
6
7

<?php
$allowed = ["name", "price"];
$sort = "price; DROP TABLE products";

$column = in_array($sort, $allowed, true) ? $sort : "name";

echo "ORDER BY " . $column;

1

ORDER BY name

What does not protect you

• addslashes() and manual escaping: easy to get wrong with character sets.
• Checking for bad words such as DROP or --: attackers have many ways around it.
• “Validated by JavaScript”: the attacker does not use your form.
• Numbers: $id = $_GET["id"] is a string. Cast or validate it, and still use a placeholder.
• Hiding error messages: that only hides the symptom. The injection still works (“blind” injection).

Defence in depth

1. Prepared statements everywhere (the main defence).
2. Validate input (type, length, allowed values).
3. A least-privilege database user: only SELECT, INSERT, UPDATE, DELETE on your own database.
4. No detailed database errors on screen (see Production Error Handling).
5. Keep software updated.