Preventing injections: prepared statements and server-side validation

A good tester does not only find problems; they know how to fix them, and they can explain the fix to a developer in a convincing way. The fixes for injection are not complicated. They rely on one idea that, once understood, applies in every language: never let data and instructions travel in the same string.

Defence 1: parameterised queries (prepared statements)

This is the primary, most reliable defence against SQL injection. The principle is separation in time and in channel. First, the application sends the database the query with placeholders, so the structure is fixed. Then it sends the values separately. The database always treats these values as data, whatever they contain, even if they look like SQL.

Compare the vulnerable code from lesson 1 with a safe version in PHP using PDO:

// Vulnerable: concatenation
$q = "SELECT name, email FROM users WHERE id = '" . $_GET['id'] . "'";

// Safe: placeholder + separate value
$stmt = $pdo->prepare("SELECT name, email FROM users WHERE id = :id");
$stmt->execute([':id' => $_GET['id']]);
$rows = $stmt->fetchAll();

The same concept exists in every major language and database driver, usually called prepared statements or parameterised queries. A placeholder can be written ?, :name or $1, depending on the system.

A common misconception: building the query by hand and then calling a function that “escapes” quotes is not the same thing. Escaping is easy to get wrong, depends on the context and on character encodings, and has produced real vulnerabilities. Use placeholders.

Limits of placeholders

Placeholders can only stand for values. You cannot use them for table names, column names or sorting direction. For those, use an allowlist: map the user’s choice to a fixed set of acceptable values in your code.

$allowed = ['price', 'name', 'created_at'];
$sort = in_array($_GET['sort'], $allowed, true) ? $_GET['sort'] : 'name';
$q = "SELECT * FROM products ORDER BY " . $sort;

Here the string that enters the query comes from your list, never from the visitor.

Defence 2: use the framework properly

Object-relational mappers (ORMs) and query builders generate parameterised queries for you in normal use. They greatly reduce the risk, but they are not magic: most offer a “raw query” escape hatch where developers can still concatenate strings. In a code review, those raw spots are the first places to look.

Defence 3: validate input on the server

Validation checks that data matches what the application expects, before using it. Good validation is:

• Server-side. Client-side checks improve user experience but are trivial to bypass with a proxy. They never count as security.
• Allowlist-based. Define what is acceptable (“a positive integer”, “between 3 and 30 letters or digits”, “one of these five values”) rather than trying to list everything that is dangerous.
• Type-aware. Convert identifiers to integers, dates to date objects. A value cast to a number cannot carry text instructions.
• Contextual. A last name may legitimately contain an apostrophe (think of O’Brien). Rejecting apostrophes everywhere breaks real users, which is why validation complements parameterisation but does not replace it.

Think of validation as the security guard at the entrance checking invitations, and parameterised queries as the sealed pipes inside the building: you want both, but the pipes are what truly prevent a spill.

Defence 4: avoid the interpreter

For command injection, the best fix is often to not call a shell at all. Use the language’s own libraries: a DNS library instead of running a lookup command, an image library instead of calling an external converter. If you must run an external program, use the API variant that takes the program and its arguments as a separate list, without passing through a shell, and validate each argument against an allowlist.

// Safer idea: validate against a strict pattern first, then pass arguments separately
if (!filter_var($host, FILTER_VALIDATE_IP)) {
    http_response_code(400);
    exit('Invalid address');
}

For LDAP, use the library’s functions that escape filter values for you. For NoSQL, enforce types and use the driver’s query builders so that an object where a string was expected is rejected.

Defence 5: reduce the damage

Even a good developer might miss one place. Defence in depth limits the consequences.

• Least privilege. The database account used by the application should only have the rights it needs. A catalogue page does not need permission to drop tables or read the users’ credentials table. Separate accounts for separate roles are even better.
• No detailed errors for visitors. Show a generic message to the user and log the detail on the server. This removes valuable clues for attackers.
• Safe storage. Even if data is read, salted password hashes with a slow algorithm, and encrypted sensitive fields, limit the damage.
• Monitoring. Alerts on unusual query errors or unusual volumes help detect attempts.
• Web application firewalls (WAF). They can block many known patterns and are useful as a temporary shield, but they are not a fix: skilled testers regularly find ways around them. The vulnerable code must still be corrected.

Defence 6: test and review continuously

• Static analysis and code review that flag string concatenation in queries.
• Automated security tests in the delivery pipeline, plus regular manual testing like the one you are learning.
• Developer training: the quickest prevention is knowing the pattern.

The OWASP Cheat Sheet Series at owasp.org contains concise, practical prevention guides for each type of injection and is an excellent reference to quote in your recommendations.

Writing the recommendation

A strong recommendation in a report is specific and verifiable:

Recommendation
1. Replace the concatenated query in search.php with a parameterised query.
2. Review other occurrences of query construction in the code base.
3. Restrict the database account used by the web application to the minimum rights.
4. Hide database errors from users and log them server-side.
Verification: retest the same parameter after the fix; the detection test must no longer alter the query.

Notice the “Verification” line. A recommendation is only complete when it says how you will confirm it worked.

Your turn: fix and validate

Rewrite the following vulnerable function so that it is safe, then write the one-line verification test you would perform:

function findUser($pdo, $name) {
    $sql = "SELECT id, name FROM users WHERE name = '" . $name . "'";
    return $pdo->query($sql)->fetchAll();
}

function findUser(PDO $pdo, string $name): array {
    $stmt = $pdo->prepare("SELECT id, name FROM users WHERE name = :name");
    $stmt->execute([':name' => $name]);
    return $stmt->fetchAll();
}

Chapter 3 is complete. In chapter 4 we turn to the other end of the conversation: flaws that target the user’s browser.