PHP Reference ยท Databases (PDO)
SQL injection in PHP
Understand SQL injection in PHP with a safe demo: how concatenated input rewrites a query, why prepared statements stop it, and how to handle IDs, LIKE and ORDER BY.
SQL injection happens when user input becomes part of the SQL text. If a program builds "... WHERE name = '$name'", an attacker who types a quote can end the string and add their own SQL. The result ranges from logging in without a password to reading or deleting the whole database. It has been among the most common web vulnerabilities for decades, and it is easy to prevent.
The cure is simple: keep SQL and data apart. Write the query with placeholders, prepare it, and pass the values separately. The database never mixes them, so a value cannot become code.
// Dangerous $pdo->query("SELECT * FROM users WHERE name = '$name'"); // Safe $stmt = $pdo->prepare('SELECT * FROM users WHERE name = ?'); $stmt->execute([$name]);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.
How the attack works
The unsafe login in the first example produces this SQL for the attack input:
SELECT role FROM account WHERE name = 'admin' --' AND password = 'anything'
The -- turns the rest of the line into a comment, so the password check disappears. With a prepared statement the whole text admin' -- is just a user name that does not exist.
Usage notes
- Prepared statements for every value. Use
?or:nameplaceholders withPDO::prepare()andexecute(), orbind_param()in mysqli. This covers strings, numbers, dates andLIKEpatterns. - Disable emulation on MySQL (
PDO::ATTR_EMULATE_PREPARES => false) for server-side prepares, so values are never merged into the SQL by PHP at all. - Identifiers are not values. Table names, column names and
ASC/DESCcannot be bound. Map the input to a fixed list (a whitelist) and use your own word in the query. - Escaping is not the answer.
addslashes()andmysqli_real_escape_string()are fragile (charsets, numeric contexts) and easy to forget. Do not build queries with them. - Casting numbers is acceptable but weak:
(int) $_GET['id']is safe for that one value, yet a placeholder is just as short and works everywhere. - Second-order injection. Data read back from your own database is not trusted either: if you paste a stored name into new SQL, the stored quote still does damage.
- Least privilege. The database user of a web application should not be able to drop tables or read other schemas. This limits the damage of any mistake.
- Hide error details. Log database errors; do not print them to visitors (see PDO error handling).
- ORMs and query builders use prepared statements for values, but raw-SQL escape hatches (
whereRaw,DB::raw) can reintroduce the problem if you concatenate input into them.