Mitigating SQL Injection in Legacy PHP Applications

Mitigating SQL Injection in Legacy PHP Applications

Legacy PHP code bases often survive because they are interwoven with a business’s daily operations. They are not the tidy, modular projects that come out of a DevOps pipeline; rather, they are patchwork collections of procedural scripts, coupled with ad‑hoc database calls, and a tradition of “if it works, keep it”. That means the first line of defense—code review—is usually missing, and the traditional practices that once served well now drop a wrench in the gears. In this piece we will map the exact failure points and then describe an uncompromising, technology‑centric approach to bring the code up to industry‑grade security without derailing the operational rhythm of a real organisation.

1. Understanding the Legacy PHP Landscape

First look around. You will see global variables dumping into MySQL queries, string concatenation everywhere, and tables referenced by misspelled names. Almost all query calls are built as raw strings and sent straight to the database driver via the deprecated mysql_query function. Even when mysqli is used, it is common to see mysqli_query invoked without mysqli_real_escape_string or, worse, a simple ad‑hoc addslashes. The result is the classic two‑step attack surface: parameter ingestion and query execution. How to break it? By re‑architecting the entire data pipeline.

2. Attack Surface Mapping

Perform a systematic audit. Typically you will encounter three categories of queries:

• Authentication & Role Checks: Basic username/password validation that concatenates credentials into a request.
• CRUD Operations: Inserts, updates, deletes built by pulling form values and slapping them into SQL.
• Reporting & Analytics: Dynamic SQL strings constructed from user‑supplied filters.

Each of these must be evaluated. Use a static analysis tool like RIPS or the open‑source phpdox suite to flag direct string concatenation into query strings. Generate a heat‑map of functions that directly call mySQLor driver functions; prioritize fixes on those that handle sensitive data or allow row modification.

3. Immediate Defensive Measures

When code change is impossible, environmental hardening is the only remaining safety net. At the database side, enforce the principle: never grant application accounts the ability to DROP, CREATE, or ALTER. The application should have a plain read‑only role for most stored procedures, with write access only through a narrow set of stored procedures that perform bound checks. Move any “admin” privileges into a separate service account, and limit the number of users that can connect using that account within the network.

On the PHP front, immediately update the runtime to PHP 8.1+. Strip out old mysql_ extensions completely; they are no longer supported and will not benefit from future security patches. Replace them with PDO or mysqli driver instances instantiated once per request. Wrap any old query fragments in a function that triggers an exception if the input contains suspicious characters. This is a temporary patch—it does not solve the root problem—but it will kill trivial injection attempts that rely on the old escape logic.

4. The Permanent Fix: Parameterisation

Parameterised queries keep the logic of constructing a query separate from the data it consumes. Begin by refactoring the most visible SQL functions. For example, replace this:

$sql = "SELECT * FROM users WHERE username = '" . $username . "';";
$result = mysqli_query($conn, $sql);

with this:

$stmt = $conn->prepare('SELECT * FROM users WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
$result = $stmt->get_result();

While the code above solves the injection vector for the specific query, scaling it to an entire application requires a design pattern. Adopt a lightweight data abstraction layer that leverages PDO and returns prepared statement objects internally. Abstract database operations into service classes, e.g. UserRepository, OrderRepository, with a single interface for CRUD methods.

Beyond simple SELECT and UPDATE, consider wrapping any operations that accept user controlled input into a validation layer. Normalise inputs: trim, enforce length constraints, and allow only characters you expect. For numeric fields, cast to int before binding. This extra layer is critical when input is expected in specific formats, such as email addresses or identification numbers. Validation catches malformed data early and makes the final parameterised query cheap.

5. Retrofitting Existing Queries in Phases

The legacy stack is not a vacuum. Some queries are