SQL Injection
SQL injection happens when input becomes query syntax; a prepared statement sends the query and the values apart so it cannot.
$stmt = $pdo->prepare('SELECT id FROM users WHERE email = ?'); $stmt->execute([$email]);A query is text. A database reads that text and decides what it means. If you glue user input into the text, the input stops being data and starts being SQL. That is SQL injection.
Take a login that builds its query by concatenation: "SELECT id FROM users WHERE email = '" . $email . "'". Send ' OR '1'='1 as the email and the condition is always true, so the first row in the table comes back. Send '; DROP TABLE users; -- and the quote closes early again, this time with a second statement behind it.
The fix is to send them separately
A prepared statement is a query with placeholders, either ? or :name, handed to the database first. The values follow in a second call. By the time the value arrives the database has already decided what the query means, so a quote or a semicolon inside it is only a character in a string.
Why escaping functions are not the answer
Escaping shifts the job back to you. It depends on quoting the value yourself, on the connection charset matching what the escape function assumed, and on remembering it at every single call site. A prepared statement depends on none of that. Use one for every value, integers included.
Placeholders stand in for values only. They cannot stand in for a table or column name, so a sort column arriving from a URL must be checked against a list of names you wrote yourself.
Example
When to use it
- Looking a user up by the email address typed into a login form.
- Filtering an admin order list by a status and a date range from the query string.
- Letting visitors sort a table by clicking a column header, without letting them name any column they like.
- Running a product search box that has to cope with %, _ and apostrophes in the term.
More examples
The real prepared statement
This is the line you actually write. It cannot run here because there is no database, but every part matters: ATTR_EMULATE_PREPARES set to false makes the MySQL driver send the query and the parameters as two separate messages instead of building the string itself, and since PHP 8.0 PDO throws an exception on an error by default rather than returning false quietly. Note that the value is passed to execute() and never touches the query string.
<?php
declare(strict_types=1);
$pdo = new PDO(
'mysql:host=localhost;dbname=shop;charset=utf8mb4',
'shop_user',
getenv('DB_PASSWORD'),
[
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email LIMIT 1');
$stmt->execute(['email' => $_POST['email'] ?? '']);
$user = $stmt->fetch();
if ($user === false) {
echo 'No such user';
} else {
echo 'User ' . $user['id'];
}An allowlist for a sortable column
Reach for this when a table header lets the visitor choose the sort column. Because a placeholder cannot hold an identifier, the only safe pattern is to compare the input against a fixed list and fall back to a default. The comparison is the security, not the interpolation, so never add a column to that list from the request.
<?php
declare(strict_types=1);
$allowed = ['created_at', 'total', 'email'];
$sort = $_GET['sort'] ?? 'created_at';
if (!in_array($sort, $allowed, true)) {
$sort = 'created_at';
}
$direction = ($_GET['dir'] ?? '') === 'asc' ? 'ASC' : 'DESC';
// $sort and $direction can only be strings this file chose, so the
// interpolation below is safe. Values still go through placeholders.
$sql = "SELECT id, email, total FROM orders
WHERE status = ?
ORDER BY $sort $direction
LIMIT 50";
$stmt = $pdo->prepare($sql);
$stmt->execute(['paid']);A LIKE search where the wildcards are yours
A prepared statement stops injection but it does not stop a LIKE wildcard, because % and _ are part of the pattern language, not of SQL syntax. The surrounding % signs are added by your code; the ones inside the search term are escaped so they match literally. Use this for any search box that feeds a LIKE clause.
<?php
declare(strict_types=1);
function searchProducts(PDO $pdo, string $term): array
{
// Escape the characters that mean something to LIKE, so a user
// searching for "50%" does not match everything.
$escaped = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $term);
$stmt = $pdo->prepare(
'SELECT id, name FROM products WHERE name LIKE :term ORDER BY name LIMIT 20'
);
$stmt->execute(['term' => '%' . $escaped . '%']);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
Discussion