Why SQL injection is still in production PHP code
SQL injection has been documented since 1998, appears in every edition of the OWASP Top 10, and has a fix that fits on one line. It keeps turning up in live PHP applications anyway, because avoiding it takes consistency across a whole codebase and one missed query is enough.
This article covers how the vulnerability works, how to check your own application for it, what the correct fix looks like on PHP 8.3 and 8.4, and what to do about the cases prepared statements cannot cover.
How the attack works
The problem appears whenever user input becomes part of the query text rather than a value passed alongside it.
$id = $_GET['id'];
$result = mysqli_query($conn, "SELECT * FROM products WHERE id = $id");The developer expects a number. An attacker sends 1 OR 1=1 and the WHERE clause matches every row. Sending 1 UNION SELECT email, password_hash, 1 FROM users returns data from a table the page was never meant to touch. Nothing has been hacked in any dramatic sense; the query was simply rewritten by whoever supplied the input.
String fields behave the same way once the quote is escaped out of.
SELECT * FROM users WHERE email = '' OR '1'='1'If that query backs a login form and the first row belongs to an administrator, the form has handed over the application.
Where it actually turns up in PHP stacks
Injection is rarely in the code somebody wrote last week. Four places account for most of what gets found in practice.
- Legacy admin pages written before the current codebase, still reachable, still building queries with concatenation.
- Search, sort and filter parameters, because column names and sort direction cannot be bound as parameters and so get interpolated instead.
- Third-party plugins and packages, where the vulnerable code belongs to someone else and arrives through an update you did not review.
- Reporting and export scripts, which are written quickly, run rarely, and get skipped in code review.
The dependency case is worth taking seriously, because it is the one your own coding standards do not touch. Checking what you have installed against published advisories takes one command.
composer audit
composer outdated --directcomposer audit compares your installed versions against the advisory database and reports anything with a known issue. Run it in the deployment pipeline so a vulnerable package stops a release rather than being found later. For WordPress and other CMS plugins, the equivalent is checking installed versions against a plugin vulnerability database before an update is skipped.
When you need to confirm a specific advisory, go to the vendor's own security page or the NVD entry rather than a blog summary. Repeating a CVE number from memory is how a wrong identifier ends up quoted for years.
Prepared statements: the fix that works
A prepared statement sends the query structure to the database first, with placeholders, then sends the values separately. The database has already decided what the statement means before any user data arrives, so no input can change its shape.
$stmt = $pdo->prepare('SELECT id, email, password_hash FROM users WHERE email = ?');
$stmt->execute([$_POST['email']]);
$user = $stmt->fetch();Named placeholders read better once a query has more than two or three parameters.
$stmt = $pdo->prepare(
'SELECT id, total FROM orders WHERE customer_id = :customer AND status = :status'
);
$stmt->execute(['customer' => $customerId, 'status' => 'paid']);Set the connection up properly once and the rest follows. Exception mode has been PDO's default since PHP 8.0, and turning off emulated prepares means the database performs the binding rather than the driver building a string.
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);Emulation is worth understanding rather than just switching off. With it enabled, PDO interpolates the values into the SQL itself before sending it, which is safe when the connection charset is set correctly and less safe when it is not. Disabling it also changes behaviour around LIMIT, which then accepts an integer parameter properly instead of quoting it as a string.
The mysqli equivalent works the same way, and since PHP 8.1 mysqli raises exceptions on error by default rather than returning false.
$stmt = $conn->prepare('SELECT id, email FROM users WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();Every statement type takes parameters, not only SELECT. An UPDATE or DELETE built by concatenation is more dangerous than a SELECT, because the attacker changes data rather than reading it.
mysqli_real_escape_string() still exists and still works, applied correctly to every value on every path. The failure mode of missing one is a full compromise, which is why it belongs in maintenance of old code rather than in anything new. Its predecessor mysql_real_escape_string() was removed in PHP 7.0 and any codebase still calling it has larger problems.
The part prepared statements cannot do
Placeholders bind values. They cannot bind identifiers, which means a table name, a column name, or the direction in an ORDER BY clause has to be interpolated. This is where injection survives in otherwise careful codebases.
// Injectable, however carefully the rest of the query is written
$sql = "SELECT * FROM products ORDER BY {$_GET['sort']} {$_GET['dir']}";The fix is an allowlist. Map the user's input to a value you control, and reject anything that does not match.
$sortable = [
'name' => 'product_name',
'price' => 'unit_price',
'added' => 'created_at',
];
$column = $sortable[$_GET['sort'] ?? 'name'] ?? 'product_name';
$direction = ($_GET['dir'] ?? '') === 'desc' ? 'DESC' : 'ASC';
$sql = "SELECT id, product_name, unit_price FROM products ORDER BY $column $direction";The user's value never reaches the query. Only one of your three known column names does, and an unrecognised input falls back to the default rather than producing an error the attacker can read.
The same pattern applies to dynamic IN clauses, where the number of placeholders has to be generated to match the number of values.
$ids = array_map('intval', $_POST['ids'] ?? []);
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($placeholders)");
$stmt->execute($ids);Blind injection, where nothing visible comes back
An application that shows no query results and no database errors is not therefore safe. Blind injection extracts data by asking questions whose answers change something observable.
Boolean-based extraction submits a condition and watches whether the page renders differently. A true condition returns the normal page; a false one returns something else. Repeating that character by character recovers a whole column eventually.
Time-based extraction works where even the page is identical, using a database function that sleeps when the condition holds. The response takes five seconds instead of fifty milliseconds, which is a perfectly readable answer.
Both are slow by hand and fast when automated, and automation for this is a solved problem. That matters for your own testing as much as for the threat: a vulnerability that would take a person a day to exploit takes a tool a few minutes.
Testing your own application
Start with the code, because it is faster and finds paths that no scanner reaches. Search for query strings that carry request data.
grep -rn --include="*.php" -E '(query|exec)\s*\(.*\$_(GET|POST|REQUEST|COOKIE)' .
grep -rn --include="*.php" -E '"[^"]*(SELECT|INSERT|UPDATE|DELETE)[^"]*\$' .Those two patterns catch most of it. Every match needs reading, and the ones in files nobody has opened for years are exactly the ones worth checking.
From the outside, a single apostrophe submitted into a parameter is the classic first probe. A database syntax error, a 500 response, or any behaviour change means the input is reaching the query unescaped. Comparing a true condition against a false one confirms it, which is the same differential logic blind injection depends on.
For systematic coverage, OWASP ZAP spiders an application and tests each parameter with a range of payloads, and it is free. Static analysis in the deployment pipeline catches the same class of problem earlier, by tracing how request data flows into query functions. Neither replaces reading the code, and both find things a person scanning manually would miss.
Only run active scanning tools against systems you own or have written permission to test. Unauthorised scanning is a criminal offence in the UK under the Computer Misuse Act 1990, and consent needs to be documented before you start rather than assumed.
Limiting the damage when something gets through
Prevention fails occasionally. Database privileges decide how bad that is.
The account your application connects with should hold exactly the rights the application uses. A public-facing catalogue that only reads has no business holding DROP, and an injection that reaches a DROP statement then fails on permissions rather than on your hopes.
CREATE USER 'app_read'@'localhost' IDENTIFIED BY 'a strong unique password';
GRANT SELECT ON app_db.* TO 'app_read'@'localhost';
CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'a different strong password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_write'@'localhost';Separate accounts for separate jobs limit what a compromise of any one part reaches. The migration account that needs schema rights should be used by migrations and by nothing that serves a web request.
File privileges matter for the same reason. MySQL's INTO OUTFILE turns a read-only injection into a web shell if the database process can write into the document root. The database account should not hold the FILE privilege, and the database user should not own the web root.
Error output is the other half of this. A production application that prints a SQL error to the page is telling the attacker the table names, the column names and the database engine. Log the error, alert on it, show the visitor nothing, which is covered in the notes on PHP error logging and production monitoring.
Noticing that it is happening
Injection attempts arrive as bursts of requests with recognisable payloads, so they are visible if anything is looking. Make sure the web server log records query strings, then watch two signals: a rise in application error rate, and repeated requests from one address carrying SQL keywords in parameters.
Pattern matching on access logs with a generic firewall rule is tempting and mostly produces false positives, because ordinary URLs contain apostrophes and the word "or" often enough to bury the real signal. A web application firewall with maintained rules does this job properly, and a spike in database errors is a better home-grown alarm than a hand-written regex.
Rate limiting slows extraction rather than preventing it, and that is still worth having. Blind injection needs thousands of requests to recover a table, so a limit on requests per address turns a ten minute exercise into something visible in your monitoring. The mechanics are in the guide to rate limiting and throttling patterns.
A practical order of work
- Inventory the concatenated queries. Grep the codebase, read every match, and write the list down before changing anything.
- Convert them to prepared statements, starting with anything reachable without logging in.
- Allowlist the identifiers that cannot be bound, particularly sort columns and direction.
- Reduce the database account's privileges to what the application genuinely uses, and split read from write where it is practical.
- Stop errors reaching the page and start alerting on them instead.
- Add dependency scanning to the pipeline so third-party code is covered by the same discipline as your own.
None of this is difficult individually. What makes SQL injection persist is that it takes a complete pass, and one unconverted query in a forgotten export script is enough to undo the rest. The related work on the wider application surface is in the PHP security checklist for business websites and the fuller PHP application security checklist.
If you want someone to work through a PHP codebase and report what is actually exposed, N. Cristea can review the queries, the database privileges and the dependency tree, and come back with a ranked list. Get in touch with the PHP version, the framework or CMS, and roughly how old the oldest code is.