Prepared Statements in PHP and PDO: Killing SQL Injection by Construction

Every SQL injection in history has the same root cause: program code and user data glued into one string, handed to a parser that cannot tell them apart. "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'" is not code with a bug — it is a bug with code around it. Prepared statements fix the category by construction: the SQL structure goes to the database first, the data goes separately, and no input can ever become syntax.

The Pattern in PDO

Separate structure from data on every query, without exception:

$stmt = $db->prepare("SELECT * FROM posts WHERE slug = :slug LIMIT 1");
$stmt->execute(['slug' => $slug]);

The placeholder :slug is never substituted into the SQL string — the value travels separately and the database binds it as data. Quotes, semicolons, comment markers in the input lose all power. Named placeholders (:slug) beat positional ones (?) for readability the moment a query has more than two parameters.

Three settings matter. Error mode: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION — silent failures hide both bugs and attacks. Fetch mode: PDO::FETCH_ASSOC as default keeps result handling predictable. Emulation: PDO::ATTR_EMULATE_PREPARES => false sends genuinely separate prepare/bind calls to MySQL instead of emulating them client-side — real prepares are the safer construction, and they also make types behave (integers stay integers).

Where Placeholders Cannot Go

Placeholders bind values only — never identifiers or syntax. Table names, column names, sort directions, and (on some drivers) LIMIT values cannot be parameterized. Each needs its own defense:

• Identifiers (ORDER BY column): allowlist against a fixed set of known columns. in_array($sort, ['created_at', 'title'], true) or reject. No escaping scheme substitutes for an allowlist here.
• LIMIT/OFFSET: cast to int explicitly — (int)$_GET['page'] — or bind with PDO::PARAM_INT. Pagination input is attacker input.
• LIKE patterns: placeholders protect the value, but % and _ inside it are still wildcards. Escape them (addcslashes($q, '%_')) when the user should not control pattern matching.

Defense in Depth Around the Query

Prepared statements are necessary, not sufficient. Give the application's database user the minimum privileges it needs (SELECT/INSERT/UPDATE/DELETE on its tables — not DROP, not GRANT, not FILE); keep display escaping separate from query safety (htmlspecialchars on output is a different layer solving a different injection); and log query failures server-side without echoing SQL or parameters to the user — error messages are reconnaissance for attackers.

Adopt the one-line policy that prevents the entire class: no variable interpolation in SQL strings, ever — not in quick admin scripts, not in migrations, not in "internal-only" tools. Grep for query("SELECT ... $ periodically; each hit is a finding. This is the same local-first, no-trust-assumptions posture as keeping data on the device: design the layer so the bad outcome is unrepresentable, then the layer stays safe no matter who calls it.