SQL Injection: How It Works and How to Stop It

SQL injection is the most famous web security flaw, and it is still common. It happens when user input becomes part of the SQL text, so an attacker can change what the query means.

A vulnerable login query

1
2

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(50), password_hash VARCHAR(60), is_admin TINYINT(1) NOT NULL DEFAULT 0);
INSERT INTO users VALUES (1, 'asha@example.com', 'hash-of-asha', 1), (2, 'ben@example.com', 'hash-of-ben', 0);

The application builds the query by gluing the form values into a string (PHP shown):

1
2
3
4

<?php
// DO NOT DO THIS
$sql = "SELECT id, is_admin FROM users WHERE email = '" . $_POST['email'] . "' AND password_hash = '" . $hash . "'";
$row = $pdo->query($sql)->fetch();

With a normal email, the SQL is fine. Now the attacker enters the “email” ' OR '1'='1' -- (a quote, OR a condition that is always true, then a comment). The text sent to MySQL becomes:

1
2
3

SET @email = "' OR '1'='1' -- ";
SET @sql = CONCAT("SELECT id, is_admin FROM users WHERE email = '", @email, "' AND password_hash = 'whatever'");
SELECT @sql AS the_query_mysql_receives;

1
2
3
4
5
6

+------------------------------------------------------------------------------------------------+
| the_query_mysql_receives                                                                       |
+------------------------------------------------------------------------------------------------+
| SELECT id, is_admin FROM users WHERE email = '' OR '1'='1' -- ' AND password_hash = 'whatever' |
+------------------------------------------------------------------------------------------------+
1 row in set

Everything after -- is a comment, so the password check disappears, and '1'='1' is true for every row. Run that query:

1
2

SELECT id, is_admin FROM users WHERE email = '' OR '1'='1' -- ' AND password_hash = 'whatever'
;

1
2
3
4
5
6
7

+----+----------+
| id | is_admin |
+----+----------+
|  1 |        1 |
|  2 |        0 |
+----+----------+
2 rows in set

Both users come back, so the application logs the attacker in as the first row (Asha, an administrator), without knowing any password.

Reading other tables: UNION

If a page prints query results (a product search, for example), the attacker can append a UNION SELECT to read any table the account can read:

1
2
3
4

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(30), price INT);
INSERT INTO products VALUES (1, 'Notebook', 80), (2, 'Pen', 15);

SELECT id, name FROM products WHERE name LIKE '%x%' UNION SELECT id, email FROM users;

1
2
3
4
5
6
7

+----+------------------+
| id | name             |
+----+------------------+
|  1 | asha@example.com |
|  2 | ben@example.com  |
+----+------------------+
2 rows in set

The attacker typed x%' UNION SELECT id, email FROM users -- into a search box. The page now lists the users’ emails (and with a bit more effort, their password hashes). Attackers use information_schema to discover the other table and column names. They also use blind techniques (true or false answers and timing with SLEEP()) when nothing is printed.

Why it is so bad

An injection hole can let an attacker read the whole database, change or delete data, log in as anyone, and, with privileges such as FILE, even write files to the server. The damage is limited only by what the database account is allowed to do (Application Database Users).

The defence: prepared statements (parameterised queries)

Send the SQL and the values separately. The values are never parsed as SQL:

1
2
3
4
5
6
7
8

<?php
$stmt = $pdo->prepare('SELECT id, is_admin, password_hash FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email']]);
$row = $stmt->fetch();

if ($row && password_verify($_POST['password'], $row['password_hash'])) {
    // logged in
}

The same attack string is now just an unusual email address that matches no row. The same idea in SQL (see Prepared Statements):

1
2
3
4

SET @evil = "' OR '1'='1' -- ";
PREPARE login FROM 'SELECT id FROM users WHERE email = ?';
EXECUTE login USING @evil;
DEALLOCATE PREPARE login;

1
2
3

Statement prepared

Empty set

An empty result: no user has that “email”. This works in every language with the database driver’s parameter binding (PDO Prepared Statements).

What placeholders cannot cover

Placeholders are for values. Table names, column names, ORDER BY columns and ASC/DESC cannot be bound. Use a whitelist:

1
2
3
4
5
6
7

<?php
$allowedColumns = ['name' => 'name', 'price' => 'price', 'newest' => 'created_at'];
$orderBy   = $allowedColumns[$_GET['sort'] ?? ''] ?? 'name';          // unknown input falls back to a safe default
$direction = (($_GET['dir'] ?? '') === 'desc') ? 'DESC' : 'ASC';      // only two possible values
$stmt = $pdo->prepare("SELECT id, name, price FROM products ORDER BY $orderBy $direction LIMIT :n");
$stmt->bindValue(':n', min(100, max(1, (int) ($_GET['n'] ?? 20))), PDO::PARAM_INT);
$stmt->execute();

Only strings that you wrote end up in the SQL text. A LIMIT value is cast to an integer and clamped.

Other defences, and what is NOT a defence

Second-order injection

Data stored safely at first can become dangerous when used later in a query built by string concatenation. A user registers with the name x'; DROP TABLE users; -- . It is saved safely using a prepared statement. A report page later builds "... WHERE author = '" . $name . "'" from the stored value, and the injection fires. Every query must use parameters, including those with values read from your own database.

LIKE and wildcards

A bound value in a LIKE is safe from injection, but % and _ in it still act as wildcards, so a user can search % to match everything. Escape them if that matters:

1
2
3
4

<?php
$term = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $_GET['q']);
$stmt = $pdo->prepare('SELECT id, name FROM products WHERE name LIKE :q');
$stmt->execute(['q' => '%' . $term . '%']);

Test your own application

• Try a single quote ' in every form field and URL parameter. A database error or a changed page means the query is built from text.
• Use tools such as sqlmap on a test copy you own and are authorised to test, never on someone else’s site.
• Review the code: search for query( and mysqli_query with variables, string concatenation and sprintf building SQL, and prepare( with $var inside the SQL string.
• Run static analysis (PHPStan/Psalm taint analysis, Semgrep rules).

Developer checklist

• [ ] Every value from outside (forms, URLs, headers, cookies, files, APIs) goes through a bound parameter.
• [ ] Identifiers and keywords come from a whitelist.
• [ ] Numeric input is cast, and limits (LIMIT) are clamped.
• [ ] The database account has minimal privileges.
• [ ] Errors are logged on the server, and users see a general message (MySQL Errors).
• [ ] The code is reviewed for string-built SQL in every pull request.