Preventing SQL Injection

Prepared statements stop the classic injection you met earlier, but they only guard the values in a query. Three spots sit just outside that guard: a column name in an ORDER BY, a search term in a LIKE, and a value you stored earlier and trusted later. Each one still catches people who thought they were safe.

Placeholders Guard Values, Not Names

A quick recap of the rule from Prepared Statements: never glue user input into SQL. Send the SQL with a placeholder, then send the value separately, and the value can only ever be data. That single habit closes the door on the injection that breaks most beginner code.

The catch is what a placeholder can stand in for. A placeholder is a value: an email, an ID, a price. It cannot be a table name, a column name, or a keyword like ASC. This is not allowed and will fail:

<?php
// This does NOT work: a placeholder cannot be a column name
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY ?");
$stmt->execute(['email']);

The database plans the query at the prepare step, and it needs the column and table names to make that plan. So when part of the query structure has to change based on input, prepared statements leave you on your own. That is exactly where the next three problems live.

Allowlisting Dynamic Column Names

Say you have a user table that visitors can sort. The sort column arrives in the URL as ?sort=email. Because a placeholder cannot carry a column name, it is tempting to drop the value straight into the SQL:

<?php
// WRONG: the column name comes straight from the visitor
$sort = $_GET['sort'];
$sql  = "SELECT * FROM users ORDER BY $sort";
$pdo->query($sql);

That is the old injection back again, wearing a different hat. A visitor who requests ?sort=email;DROP+TABLE+users writes their own SQL into your query. The fix is not to escape the value. It is to refuse any value you did not write yourself. You keep a fixed list of the columns that are allowed, and if the input is not on the list, you fall back to a safe default:

Checking an identifier against an allowlist is three steps:

1. Write the fixed list of column names you control
2. Test the visitor’s value against it with a strict comparison
3. If it is not on the list, use a safe default instead

<?php
// 1. the only columns a visitor is allowed to sort by
$allowed = ['name', 'email', 'created_at'];

// 2. a hostile value, as if it came from ?sort=
$sort = 'email; DROP TABLE users';

// 3. not on the list, so we refuse it and default to name
if (!in_array($sort, $allowed, true)) {
    $sort = 'name';
}

echo "ORDER BY $sort";

ORDER BY name

The attacker’s string is thrown away because it is not one of the three names you wrote, so the query ends up as ORDER BY name. Notice the true third argument to in_array(): that forces a strict, type-and-value match, which is the safe way to compare (you saw why in Operators). Only after this check do you paste $sort into the SQL, and by then it is guaranteed to be one of a handful of strings you typed by hand. The visitor never gets to choose the text, only which of your approved options wins.

<?php
$allowed = ['name', 'email'];
$sort = 'id';
echo in_array($sort, $allowed, true) ? $sort : 'name';

Escaping Wildcards in a LIKE Search

Now a subtler one. A LIKE query is safe from injection the moment you bind the search term as a value, so this is already correct SQL:

<?php
$term = $_GET['q'];
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute(['%' . $term . '%']);

The problem is not injection here, it is behavior. Inside a LIKE pattern, % means “any run of characters” and _ means “any single character.” If a visitor searches for 100%, that % is treated as a wildcard, so the search quietly matches far more than they asked for. A user who types _ matches almost everything. At best it returns junk results; at worst someone uses it to probe what is in your table one character at a time.

The fix is to escape those wildcard characters so they match literally. You escape the escape character itself first (so you do not double-escape the ones you are about to add), then % and _. Here ! is the escape character, chosen because it is not special inside any SQL string literal, so the query below works unchanged on MySQL, MariaDB, SQLite, and PostgreSQL. This is plain string work, so it runs:

<?php
$term = '100%_off';

// escape !  then  %  then  _  so they match as plain characters
$escaped = str_replace(
    ['!', '%', '_'],
    ['!!', '!%', '!_'],
    $term
);

echo $escaped;

100!%!_off

Both wildcards now carry a leading !, which tells the database to treat them as ordinary text. You then bind the escaped term and tell the query which character does the escaping:

<?php
$stmt = $pdo->prepare(
    "SELECT * FROM products WHERE name LIKE ? ESCAPE '!'"
);
$stmt->execute(['%' . $escaped . '%']);

The outer % signs are yours, the “contains” wildcards you meant to add. The escaped % and _ from the visitor stay literal. Same query, but now it searches for exactly what the user typed.

Second-Order Injection

The last trap is the one that fools careful developers, because the dangerous value is not coming from a form anymore. It is coming from your own database. Picture a sign-up form that stores a display name using a prepared statement, exactly as you should:

<?php
// SAFE insert: the odd name is stored as plain data
$name = $_POST['name'];   // visitor typed:  billy' OR '1'='1
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute([$name]);

That insert is fine. The string billy' OR '1'='1 is saved as a harmless name. The danger comes later, when some other script reads that name back and trusts it because it came from the database:

<?php
// WRONG: the stored name is glued into a new query
$row  = $pdo->query("SELECT name FROM users WHERE id = 1")->fetch();
$name = $row['name'];   // billy' OR '1'='1
$pdo->query("SELECT * FROM logs WHERE user = '$name'");

The moment that stored value is concatenated into the second query, it turns back into SQL and the injection fires. This is second-order injection: the payload is planted safely, then triggered on a later read. The lesson is that “it came from our own database” is not a reason to trust a value. The rule has no exceptions: every value going into SQL, no matter where it came from, goes through a placeholder.

<?php
// RIGHT: bind it, every time, even for your own stored data
$stmt = $pdo->prepare("SELECT * FROM logs WHERE user = ?");
$stmt->execute([$name]);

Your Turn

<?php
$dir = 'DESC; DROP TABLE users';
$allowed = ['ASC', 'DESC'];

<?php
$dir = 'DESC; DROP TABLE users';
$allowed = ['ASC', 'DESC'];
$dir = in_array($dir, $allowed, true) ? $dir : 'ASC';
echo $dir;

Injection is about untrusted input flowing into your database. The same danger runs the other way when untrusted data flows out to the browser: next you will stop cross-site scripting by escaping everything you print with XSS and Output Escaping.