English is currently available.
PHP DISCUSSION
I maintain an old PHP page that stores sensor readings in MySQL and builds queries like "SELECT * FROM readings WHERE device = '$device'" from a URL parameter. I know this is open to SQL injection and that prepared statements are the recommended fix, but I want to understand why they work rather than just copy a snippet.
How does a prepared statement differ from escaping the input, and are there parts of a query it cannot protect?
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.
