Defense in Depth: Secure Patterns and the Remaining Edge Cases

By this point in the guide, we've walked through how an attacker breaks into ShopBox, what their payloads look like, and how the forensics almost missed the breach entirely. Now comes the part I actually enjoy: building things that don't fall over. I've been doing defensive code reviews for fifteen years, and I still find injection bugs in supposedly "secure" codebases. Not because the developers are careless—because SQL injection has a nasty habit of hiding in places that look perfectly safe.

Let me show you what I mean, using our ShopBox lab. We'll harden the PHP login on 192.0.2.10, the Python search interface, and the database layer behind both. Along the way, I'll share the mistakes I've made implementing these patterns—because that's where the real learning happens.

Parameterized Queries: The Foundation That Isn't Foolproof

The first thing everyone learns about SQL injection defense is "use prepared statements." And they're right, mostly. But "prepared statement" means different things in different contexts, and the threat model shifts accordingly.

Here's the vulnerable ShopBox login from earlier pages—the classic string concatenation that got us owned:

// Vulnerable: login.php on 192.0.2.10 (ShopBox PHP variant)
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '" . $username . "' AND password = '" . md5($password) . "'";
$result = mysqli_query($conn, $query);

The fix seems obvious. Replace with a prepared statement:

// Hardened: login.php
$stmt = $conn->prepare("SELECT user_id, username, role FROM users WHERE username = ? AND password_hash = ?");
$stmt->bind_param("ss", $_POST['username'], md5($_POST['password']));
$stmt->execute();
$result = $stmt->get_result();

The ? placeholders are sent to the MySQL server on 192.0.2.20 as part of the query template. The actual values travel separately. The server parses the template once, compiles the execution plan, and refuses to reinterpret the values as SQL syntax. That's the mechanism: separation of code and data at the protocol level.

But here's where I screwed up once, and I see this constantly in code reviews. I had a junior dev come to me proud of their "parameterized" query that looked like this:

// STILL VULNERABLE — common mistake
$table = $_GET['table']; // "validated" with a regex, supposedly
$id = $_GET['id'];
$stmt = $conn->prepare("SELECT * FROM $table WHERE id = ?");
$stmt->bind_param("i", $id);

The ? is safe. The $table is not. Prepared statements parameterize values, not identifiers. Table names, column names, ORDER BY clauses, LIMIT offsets—these are structural parts of the query that cannot be bound as parameters in standard SQL. MySQL's prepared statement protocol simply doesn't support placeholder substitution for identifiers.

Why this matters: Most SQL injection tutorials stop at "use prepared statements" without explaining this boundary. I've seen production systems where developers, frustrated that their ORM wouldn't sort dynamically, concatenated ORDER BY columns directly into the query string. The application "used parameterized queries everywhere" and still got hit.

Stored procedures sit at a different point in the threat model. When you call a stored procedure with parameters, you're still using parameterized execution—CALL sp_login(?, ?) binds exactly like a prepared statement. The additional benefit is query plan encapsulation: the application never sees the internal SQL. But stored procedures can be vulnerable if they construct dynamic SQL internally using EXEC() or sp_executesql with concatenation. I've audited SQL Server environments where the stored procedure was "secure" but inside it did EXEC('SELECT * FROM ' + @table). The injection just moved address.

For ShopBox, we standardized on prepared statements for value parameters and strict allow-lists (covered below) for any dynamic identifiers. Stored procedures were reserved for complex multi-statement operations where we wanted to enforce business logic at the database layer.

ORM Safety: When the Abstraction Leaks

ORMs (Object-Relational Mappers, tools that let you interact with databases using object-oriented code rather than raw SQL) promise to eliminate injection by construction. And when used correctly, they mostly do. But "correctly" is doing a lot of work here.

In our ShopBox Python variant on 192.0.2.10 (Django backend, PostgreSQL on 192.0.2.30), the product search started innocent enough:

# Safe: Django ORM query
products = Product.objects.filter(name__icontains=user_input)

Django's ORM builds parameterized queries under the hood. The user_input becomes a bound parameter in WHERE name ILIKE %s. But then requirements changed. Marketing wanted "advanced search" with raw SQL for performance. A developer reached for raw():

# VULNERABLE: Django raw() with string formatting
user_input = request.GET.get('q')
products = Product.objects.raw( f"SELECT * FROM shopbox_product WHERE name ILIKE '%{user_input}%'"
)

Django's raw() returns model instances like a normal QuerySet, but it takes a raw SQL string. The f-string interpolation destroys all safety. The correct pattern uses parameterization within raw():

# Hardened: Django raw() with proper parameter binding
products = Product.objects.raw( "SELECT * FROM shopbox_product WHERE name ILIKE %s", [f"%{request.GET.get('q')}%"]
)

Notice the parameter list [%s]—Django passes this through to PostgreSQL's parameterized execution. The % wildcards are part of the value, not the query structure.

But the edge case that almost burned me: RawSQL and Func() expressions for complex annotations. I needed to rank search results by similarity and tried:

# DANGEROUS: RawSQL with untrusted column reference
from django.db.models.expressions import RawSQL
products = Product.objects.annotate( rank=RawSQL("similarity(%s, %s)", [user_column, user_query])
)

The parameters are safe, but I nearly used RawSQL(f"similarity({user_column}, %s)", [user_query]) because the column name "had to be dynamic." Same identifier problem. I ended up with an explicit allow-list:

ALLOWED_COLUMNS = {'name', 'description', 'sku'}
if user_column not in ALLOWED_COLUMNS: raise ValueError(f"Invalid sort column: {user_column}")
# Then proceed with parameterized RawSQL

Now, Hibernate. Our ShopBox Java variant doesn't run Hibernate, but I've dealt with CVE-2026-0603 in other environments, and it's instructive. Hibernate versions 5.2.8 through 5.6.15 are vulnerable—no official patch exists since 5.6.x is end-of-life. The vulnerability is in InlineIdsOrClauseBuilder during bulk DELETE or UPDATE operations. User-supplied string primary keys containing malicious SQL get inserted directly into WHERE clauses.

⚠️ Authorized, defensive use only. The following describes a vulnerability pattern for defensive understanding. Verify only in isolated lab environments.

The attack flow: an attacker registers with a username like admin' OR '1'='1—but more specifically, a crafted string that becomes an entity identifier. This value is stored in the database. Later, when an administrator performs a bulk operation (delete inactive users, update statuses), Hibernate constructs an IN clause or OR-chained predicate using those stored IDs. The payload executes.

This is second-order injection (covered below), but it illustrates a critical ORM lesson: ORMs protect you only when you stay inside their abstraction. Hibernate's Session.createQuery() with named parameters is safe. Its Session.createSQLQuery() with string concatenation is not. And bulk operations that optimize by building ID lists dynamically—those are where the ORM's internal SQL generation can betray you.

For mitigation, the source mentions InlineIdsOrClauseBulkIdStrategy as a configuration approach in application.yml, though I cannot confirm its effectiveness from available data. In practice, I migrated affected applications to Hibernate 6.x or replaced bulk operations with iterative parameterized deletes.

Input Validation: The Backup, Not the Plan

I want to be blunt here because I see this mistake constantly. Input validation is not your primary SQL injection defense. Parameterized queries are. Validation is your safety net—the thing that catches weirdness when the primary defense has a gap, or when you're forced into a code path that can't be parameterized.

There are two philosophies: deny-lists and allow-lists. Deny-lists say "reject if you see SQL keywords like SELECT, UNION, --". Allow-lists say "accept only known-good patterns."

Deny-lists fail. Every time. I've bypassed filters that rejected UNION by using UNI/**/ON, or SELECT by using nested subqueries that never hit the keyword. The attacker has infinite creativity; your regex has finite patience.

Allow-lists require more thought but actually work. For ShopBox's product search, we defined:

Field	Allow-list Rule	Rejected Example
Product ID	`^[0-9]+$`	`105 OR 1=1`
Sort column	`^(name\\|price\\|created_at)$`	`name; DROP TABLE`
Category filter	`^[a-z_]{1,30}$`	`'; DELETE FROM`
Price range	Integer, 0-1000000	`-1 UNION SELECT`

The sort column is the classic identifier injection surface. We couldn't parameterize ORDER BY ?—MySQL rejects it. So the allow-list ensures only known-safe columns reach the query string.

In plain terms: Don't try to think of every bad thing. Define exactly what good looks like, and reject everything else.

Database Hardening: Assume the Application Fails

This is the part that saved me in a real incident. The application had a zero-day I didn't know about. But the database account couldn't do much, so the blast radius was contained.

For ShopBox, we restructured privileges on both backends. Here's our before/after matrix for the MySQL application account on 192.0.2.20:

Capability	Before (default)	After (hardened)
`SELECT`	All tables	`shopbox.*` only
`INSERT/UPDATE/DELETE`	All tables	`shopbox.*` only
`CREATE/DROP/ALTER`	Granted	Revoked
`FILE` (read/write filesystem)	Granted	Revoked
`SUPER`	Granted	Revoked
`INFORMATION_SCHEMA`	Full access	`TABLES`, `COLUMNS` restricted to `shopbox`
`mysql.user` access	Read	None

The INFORMATION_SCHEMA restriction matters for defense. In earlier pages, we showed how attackers use information_schema.tables to map the database. We created a dedicated view that exposes only ShopBox tables, and revoked direct INFORMATION_SCHEMA access. The application still functions; the attacker loses reconnaissance capability.

For PostgreSQL on 192.0.2.30, we added a read-only replica for reporting queries. The application account connecting to the replica has SELECT only, no INSERT/UPDATE/DELETE, and connects through a separate network segment. If the reporting interface has an injection flaw, the attacker reads product data—cannot modify orders, cannot pivot to the writeable primary.

The Residual Risk Surface: Where Injection Still Hides

Even with all of the above, three edge cases keep me up at night.

ORDER BY injection. The query needs dynamic sorting. The column name can't be parameterized. Your allow-list has a bug—maybe it uses strpos() instead of exact matching, so name, (SELECT password FROM users) passes because name is found. I've seen this in the wild. The fix: strict exact matching against a hardcoded array, never substring checks.

LIMIT injection. MySQL's LIMIT takes two comma-separated integers, or LIMIT offset, count. Some developers do LIMIT $offset, $count with integer validation. But what if the validation is is_numeric() in PHP, which accepts 1,1 PROCEDURE ANALYSE()? (That particular vector is patched in modern MySQL, but the pattern persists.) Cast to integer explicitly: (int)$offset, (int)$count.

Second-order SQL injection. This is the nastiest because your prepared statement is perfect at the point of storage. The payload sits harmlessly in the database. Months later, a different code path retrieves it and uses it in a new query—without parameterization.

Here's how it flows in ShopBox:

[Attacker] → Registration form: username = "admin' UNION SELECT ..." [Prepared statement stores safely in DB] [Time passes] [Admin] → "Delete inactive users" batch job [Application retrieves usernames, builds dynamic SQL] → "DELETE FROM users WHERE username = 'admin' UNION SELECT ...'" [Injection executes]

The first-order interaction was safe. The second-order interaction was not. Testing for this requires stored data scenarios: register, wait, trigger batch operations. Static analysis often misses it because the vulnerable code and the storage code are in different modules.

⚠️ Authorized, defensive use only. Test second-order patterns only in isolated lab environments like our ShopBox setup.

Samuel's Checklist: Before You Call It Done

I use this on every code review. Not all items apply to every application, but the discipline of checking matters.

[ ] All value parameters use prepared statements or ORM equivalent
[ ] All identifiers (tables, columns, ORDER BY) use strict allow-lists
[ ] No dynamic SQL construction in stored procedures
[ ] ORM raw()/createSQLQuery() usages audited for parameter binding
[ ] Application DB account has minimal privileges (no DDL, no FILE, no SUPER)
[ ] INFORMATION_SCHEMA access restricted or filtered
[ ] Read-only replicas used for reporting/analytics interfaces
[ ] Input validation exists as defense-in-depth, not primary defense
[ ] Second-order scenarios tested: stored data later used in queries
[ ] ORDER BY and LIMIT clauses explicitly validated, not just "seem safe"

The last item on that list—"seem safe"—is where experience counts. I've learned to distrust my own confidence. If I can't explain exactly why a code path is safe, with the mechanism, it probably isn't.

In the next pages, we'll compare database-specific behaviors in depth, and later we'll cover testing your defenses without breaking production. For now, get these patterns into your ShopBox lab, break them deliberately, and watch what the logs show. That's how the knowledge sticks.