How do prepared statements in PHP PDO prevent SQL injection, and where do they not help?

Injection works because the query is assembled as text, so data can be mistaken for SQL. If $device contains x' OR '1'='1, the string sent to MySQL is WHERE device = 'x' OR '1'='1', which matches every row. The database has no way of knowing that the second half came from a visitor.

A prepared statement sends the two things separately. The SQL with placeholders is parsed first, and the values are supplied afterwards as data. Whatever a value contains, it is only ever compared against the column; it is never parsed as SQL.

With PDO: $stmt = $pdo->prepare('SELECT * FROM readings WHERE device = ? AND ts > ?'); then $stmt->execute([$device, $since]); and $rows = $stmt->fetchAll();. Named placeholders such as :device with an associative array work the same way and read better in long queries.

Create the connection with the character set in the DSN (charset=utf8mb4), exceptions enabled (PDO::ERRMODE_EXCEPTION, the default from PHP 8.0), and PDO::ATTR_EMULATE_PREPARES set to false so that MySQL performs the preparation itself.

Placeholders can stand only for values. They cannot replace a table name, a column name, a keyword such as ASC or DESC, or a whole list. If the user chooses the sort column, do not interpolate their text; map it through a whitelist: $allowed = ['ts', 'value', 'device']; and use the input only if in_array($col, $allowed, true), otherwise fall back to a default.

For IN (...) with a variable number of items, generate one placeholder per item: implode(',', array_fill(0, count($ids), '?')) gives ?,?,? for three IDs, and you pass the array to execute.

Escaping functions such as mysqli_real_escape_string only make a value safe inside a quoted string literal, and only if the connection character set is set correctly. They do nothing for a numeric context: "WHERE id = $id" with $id set to 1 OR 1=1 contains no quote to escape. That is why escaping is considered fragile; one forgotten call or one unquoted number reopens the hole. With placeholders there is no per-value step to forget.

With LIKE, bind the pattern as a value, $stmt->execute(['%' . $term . '%']). It is injection-safe, but % and _ typed by the user still act as wildcards unless you escape them.

Keep the other layers too. Validate types at the door: a device ID that should be an integer can be checked with filter_var($id, FILTER_VALIDATE_INT), which returns false on anything else. Give the web application a database account with only the rights it needs, so that a mistake somewhere cannot drop tables. And treat output separately: a prepared statement protects the database, not the browser, so values printed into HTML still need htmlspecialchars($value, ENT_QUOTES, 'UTF-8').

To audit the old code, search for query strings containing $ or the concatenation dot next to SQL keywords; each hit is a statement to convert.