SQL Injection, and the Boring Fix That Actually Works

SQL injection is one of the oldest web vulnerabilities around, and it still turns up constantly. The reason is simple. The vulnerable version of the code is the most obvious way to write it.

Picture a search box that looks up a user by email. The natural first attempt in PHP looks like this:

// Don't do this
$email = $_GET['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
$result = $pdo->query($sql);

It works. Type in an email, get a user back. The problem is that you've taken text from a stranger and pasted it directly into a command your database will run. The database has no way to tell which parts you wrote and which parts the visitor wrote. It just sees one string of SQL.

So if someone types this into the box:

' OR '1'='1

the query becomes:

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

The quote they typed closed your string early, and the rest got read as SQL. '1'='1' is always true, so now the query returns every user in the table. That's the gentle version. Depending on the database and the permissions involved, an attacker might be able to read other tables, bypass a login check, or change data. All because of a quote mark.

People often try to fix this by escaping quotes or stripping out "dangerous" characters. That's a losing game. There are edge cases with character sets, numbers that don't need quotes, and spots in a query where escaping doesn't help at all. You end up chasing the problem instead of removing it.

The real fix is prepared statements (also called parameterized queries). Instead of building one string, you send the database two separate things: the SQL with placeholders, and the values to put in them.

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

Or with named placeholders, which read better when there are several:

$stmt = $pdo->prepare(
    'SELECT * FROM orders WHERE user_id = :uid AND status = :status'
);
$stmt->execute([
    'uid'    => $userId,
    'status' => $status,
]);

Here's why that's fundamentally different. The database receives and plans the query first, with the placeholders as empty slots. The values arrive separately and are only ever treated as data. If someone types ' OR '1'='1, the database just goes looking for a user whose email is literally that string. It doesn't find one. Nothing gets reinterpreted, because there's no step where user input becomes part of the SQL text.

That's the whole idea. Keep code and data apart and the attack has nowhere to go.

A few details that trip people up.

Placeholders only work for values. You can't use one for a table name, a column name, or a keyword like ASC and DESC. If you let users sort by a column, check their choice against a list you control:

$allowed = ['created_at', 'name', 'price'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'created_at';
$dir  = (($_GET['dir'] ?? '') === 'asc') ? 'ASC' : 'DESC';

$stmt = $pdo->prepare("SELECT * FROM products ORDER BY $sort $dir LIMIT 50");
$stmt->execute();

The user never gets to put their own text into the query. They just pick from options you wrote.

IN lists need one placeholder per item. Build the right number of question marks and pass the array:

$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM items WHERE id IN ($placeholders)");
$stmt->execute(array_values($ids));

(Make sure $ids isn't empty first, since IN () is invalid SQL.)

Set up PDO sensibly. Put the character set in the connection string, turn on exceptions, and with MySQL or MariaDB it's good practice to turn off emulated prepares so the database does the real work:

$pdo = new PDO(
    'mysql:host=localhost;dbname=app;charset=utf8mb4',
    $dbUser,
    $dbPass,
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES   => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

And pull those credentials from environment variables or a config file outside your web root, not hardcoded in the script.

Don't stop at prepared statements. Give your app's database user only the permissions it needs. An app that only reads and writes rows doesn't need the right to drop tables. Don't show raw database errors to visitors, since they hand out free hints about your schema. Log them instead.

ORMs and query builders mostly handle parameterization for you, which is great, but almost all of them have a "raw query" escape hatch. The same rules apply the moment you use it.

Honestly, the best habit is the simplest one. Any time you see a variable sitting inside a SQL string, stop and ask whether it should be a placeholder. Nearly always, it should.