How do I prevent SQL injection in PHP with PDO prepared statements?

SQL injection happens when user input becomes part of the SQL text. The fix is to keep SQL and data separate with prepared statements.

Basic fix

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status = :status');
$stmt->execute(['email' => $email, 'status' => 'active']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

The database receives the query structure and the values separately, so a value like ' OR 1=1 -- is just a string.

Connection options that make this safe

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

Turning emulation off makes MySQL do real server-side preparation.

IN() lists

$ids = [3, 7, 9];
$marks = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM posts WHERE id IN ($marks)");
$stmt->execute($ids);

Only the placeholders (generated by you) are interpolated, never the values. Check the array is not empty first.

Things you cannot bind: identifiers and keywords

Table names, column names and ASC/DESC cannot be parameters. Use an allow list:

$allowed = ['created_at', 'title'];
$col = in_array($sort, $allowed, true) ? $sort : 'created_at';
$dir = $desc ? 'DESC' : 'ASC';
$stmt = $pdo->prepare("SELECT * FROM posts ORDER BY $col $dir LIMIT :n");
$stmt->bindValue(':n', (int) $limit, PDO::PARAM_INT);

With emulation off, LIMIT values must be bound as integers (PDO::PARAM_INT).

Pitfalls

• addslashes and mysqli_real_escape_string are not a substitute, charset issues can bypass them.
• LIKE needs the wildcard in the value: execute(['%'.$q.'%']). Escape % and _ if you want literal matches.
• Stored data is untrusted too: second-order injection happens when you build SQL from values read from the database.
• Use a least-privilege DB user (no DROP, no FILE).