skip to content

In a legacy PHP app, what does mysqli::real_escape_string() actually protect, and where does it fail even when called on every input?

level: seniorimportance: should knowfreq 38%

answer

  1. only inside a quoted literal
  2. unquoted numbers slip through
  3. identifiers are not literals
  4. set_charset(), never SET NAMES
  5. % and _ left alone

basics

~20 s

mysqli::real_escape_string() only makes a value safe inside a quoted MySQL string literal on a connection whose charset was set with set_charset(). It does nothing for unquoted numbers, identifiers, LIKE wildcards or a charset changed by SET NAMES.

solid answer

~40 s

`real_escape_string()` escapes NUL, `\n`, `\r`, backslash, both quotes and Ctrl-Z, using the connection's character set, so its output is safe **between quotes** in a MySQL string literal. It fails when the value is not in quotes: `WHERE teacher_id = $id` with `1 OR 1=1` needs no quote at all. It cannot make an `ORDER BY` column name safe, and it leaves `%` and `_` active in `LIKE`. It also relies on the character set mysqli knows: the manual says to set it with `set_charset()`, because a `SET NAMES` query changes the server without informing the escaping code. In a legacy app I replace escaping with prepared statements or `execute_query()`, and map identifiers to an allowlist.

code

php · 15 lines
php
<?php
declare(strict_types=1);

$db = new mysqli('db', 'app', $password, 'timetable');
$db->set_charset('utf8mb4');

// Unsafe even with escaping: the value is not inside quotes.
$t = $db->real_escape_string($_GET['t'] ?? '');   // '1 OR 1=1' passes unchanged
$sql = "SELECT subject FROM lessons WHERE teacher_id = $t";

// Safe: the value is bound, the SQL text is fixed.
$result = $db->execute_query(
    'SELECT subject FROM lessons WHERE teacher_id = ?',
    [$_GET['t'] ?? '']
);

go deeper

for a junior

Recall that real_escape_string() is only safe for values placed inside quotes, and that prepared statements are the normal tool.

for a middle

Explain the positions escaping cannot protect — unquoted numbers, identifiers, LIKE wildcards — and why set_charset() matters.

for a senior

Audit a legacy mysqli app: find unquoted and identifier positions, fix the charset setup, and migrate escaped queries to bound parameters.

for a principal

Prioritise a migration away from escaping across a large legacy codebase, deciding which call sites to fix first and how to prevent new ones.

## What the function does `mysqli::real_escape_string(string $string): string` (procedural `mysqli_real_escape_string($link, $string)`) returns a copy of the string with the characters that could end a MySQL string literal escaped. According to the manual those are NUL (ASCII 0), `\n`, `\r`, `\`, `'`, `"` and Ctrl-Z. It needs a connection because the escaping depends on the connection's **character set**. Its whole contract is narrow: **the result is safe to place between quotes in a MySQL string literal**, on that connection, in that character set. Everything outside that contract is where legacy code breaks. ## Where it does not protect | Situation | Why escaping does nothing | |---|---| | Unquoted numbers: `WHERE class_id = $id` | `1 OR 1=1` has no quote to escape; it is valid SQL as it stands | | Identifiers: `ORDER BY $column` | column and table names are not string literals; escaping does not make an arbitrary name safe | | Character set changed with `SET NAMES` | mysqli escapes using the character set it knows about; a `SET NAMES` query changes the server side without telling the client library | | `LIKE` patterns | `%` and `_` are not escaped, so user input still acts as wildcards | | Output to HTML | it is an SQL-literal escape, not an HTML escape | ## The character-set trap in detail Escaping works byte by byte according to the character set. The manual's caution is explicit: the character set must be set at the server level or with `mysqli::set_charset()` for it to affect `real_escape_string()`, and setting it with a query such as `SET NAMES` is not recommended. If the client library thinks the connection is single-byte but the server is decoding a multibyte character set, an escaping backslash can be absorbed into a multibyte character, and the quote after it becomes live again. The rule for any mysqli code is therefore: 1. Call `$db->set_charset('utf8mb4')` right after connecting. 2. Never switch character sets with `SET NAMES` in a query. ## Why "escape everything" still fails in a legacy app A school-timetable app might wrap every input in `real_escape_string()` and still be exploitable: - `"SELECT * FROM lessons WHERE teacher_id = " . $db->real_escape_string($_GET['t'])` — unquoted, so escaping is a no-op. - `"... ORDER BY " . $db->real_escape_string($_GET['sort'])` — an identifier position; escaping cannot turn arbitrary input into a valid, harmless column name. - A developer escapes the value **twice**, or escapes it and later concatenates the unescaped copy by mistake. Escaping depends on discipline at every call site; one missed call is enough. ## What to use instead - **Values:** prepared statements — `prepare()` with `bind_param()`, `execute()` with an array (PHP 8.1), or `execute_query()` (PHP 8.2). The value never becomes part of the SQL text. - **Identifiers and sort directions:** map the input onto a fixed list of allowed names in PHP and use the mapped value. - **Numbers:** validate and cast (`(int)`), or better, bind them. - **Existing escaping calls:** leave them only where the query cannot yet be migrated, and make sure `set_charset()` runs on that connection. ## Auditing a legacy codebase A practical pass over an old mysqli app looks like this: 1. Search for `real_escape_string` and `mysqli_real_escape_string`, and for SQL strings built with `.` or `"$var"` interpolation. 2. For each hit, check where the value lands: inside quotes, unquoted, or in an identifier position. 3. Check how every connection sets its character set; replace any `SET NAMES` query with `set_charset()`. 4. Convert the value positions to bound parameters, starting with code reachable from public forms and URLs. 5. Replace identifier positions with an allowlist lookup. ## When it is still legitimate There are rare cases where no placeholder is possible and the value is a string literal, for example building a SQL script file that another tool will run. Even then the function is correct only for quoted literals on a connection whose character set was set with `set_charset()`. In application code, reaching for `real_escape_string()` is a sign the query should be a prepared statement.

  • Why does real_escape_string() need a connection when addslashes() does not?
    Escaping must respect the connection's character set: in some multibyte sets a backslash byte can be part of a character. `real_escape_string()` uses the character set of that link, as set by `set_charset()`; `addslashes()` knows nothing about it and escapes bytes blindly.
  • Does calling set_charset('utf8mb4') make real_escape_string() safe for unquoted values?
    No. `set_charset()` fixes a different problem: it makes the escaping match the character set the server decodes, closing the multibyte gap. A value outside quotes still needs no quote to change the query, so `1 OR 1=1` passes through unchanged. The value must be quoted, or better, bound as a parameter.

Escaping is like childproofing only the kitchen drawers: it works for things kept in drawers, but anything left on the counter — an unquoted number, a column name — was never inside what the lock protects.

saying these in an interview costs you the question

  • Believing escaping every input makes string-built SQL safe everywhere.
  • Escaping a value placed into an unquoted numeric position.
  • Changing the character set with SET NAMES and relying on escaping.
  • Escaping column names taken from the query string.
  • Treating real_escape_string() output as safe for HTML.