Prepared Statements and Safe Queries

Always pass user data as bound parameters, never by concatenating it into SQL. Parameters can't be used for identifiers (table/column names) or keywords like ASC/DESC — for those, map user input to an allow-list. For IN (...) lists, generate one placeholder per value.

<?php
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email AND active = :active');
$stmt->execute(['email' => $email, 'active' => 1]);
$user = $stmt->fetch();

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

// Dynamic sort with an allow-list
$sortable = ['created' => 'created_at', 'total' => 'total_cents'];
$column = $sortable[$_GET['sort'] ?? 'created'] ?? 'created_at';
$direction = ($_GET['dir'] ?? '') === 'asc' ? 'ASC' : 'DESC';
$rows = $pdo->query("SELECT * FROM orders ORDER BY {$column} {$direction} LIMIT 50")->fetchAll();

Common pitfalls

• Building WHERE name = '$name' strings — classic SQL injection.
• Trying to bind table or column names as parameters.
• Using addslashes() as 'escaping'.

Key points

• Bind every value
• Allow-list identifiers and sort directions
• Generate placeholders for IN lists

Further reading: PHP manual: prepared statements