Kill SQL Injection With Parameterized Queries and Allowlists

SQL injection has been at or near the top of every web vulnerability list for two decades, and the reason it persists is not that the fix is hard. The primary fix, parameterized queries, is well known and easy. The reason it persists is that "use parameterized queries" gets treated as a complete answer when it is only most of one. There are parts of a SQL statement that cannot be parameterized, there is data that gets injected on the way out rather than on the way in, and there is the simple fact that one missed query in one corner of a large codebase is enough.

To kill SQL injection, parameterize every query so user input is bound as data the database can never execute as code, then add layers for the parts placeholders cannot cover: allowlist table and column names that must be interpolated, validate and escape output, and validate input with a schema before it reaches the database. Defense in depth means no single missed query hands over your data.

So the goal is not a single control. It is layers, arranged so that the failure of any one of them does not hand an attacker your database. Here is how to build that.

Parameterized queries are the foundation, and here is why they work

The core defense is the prepared statement, also called a parameterized query. Instead of building a SQL string by concatenating user input into it, you send the query with placeholders and the values separately. The database compiles the query structure first, then binds the values into the already-compiled plan. Because the values arrive after the query has been parsed, they can never change what the query does. A value of ' OR 1=1 -- is treated as a literal string to compare against, not as SQL to execute.

The contrast is everything. This is the wound:

db.query(`SELECT * FROM users WHERE email = '${email}'`);

This is the fix:

db.execute('SELECT * FROM users WHERE email = ?', [email]);

In the first version, an attacker who controls email controls the query. In the second, the ? is a placeholder the database binds safely, and the input cannot escape its slot no matter what it contains. Every query that touches user data, every one, must look like the second version. That is non-negotiable and it is the layer everything else is built on top of.

Why prepared statements alone are not enough

Here is the part that gets skipped. Parameterization protects values. It does not protect the structure of a query, because the database needs the structure compiled before it can bind anything, and structure is exactly what some user input wants to control.

You cannot parameterize a table name, a column name, or the direction of an ORDER BY. If your application lets users sort a report by a column they choose, or pick which table to query from a dropdown, the placeholder mechanism has nothing to bind, because those are not values, they are parts of the SQL grammar itself. Developers who reach for string concatenation in exactly these spots, because the placeholder will not work there, reopen the hole they closed everywhere else.

The defense for non-parameterizable elements is an allowlist. You do not validate the column name, you check it against an explicit list of permitted column names and reject anything not on the list:

const ALLOWED_SORT = new Set(['created_at', 'name', 'email']);
if (!ALLOWED_SORT.has(sortColumn)) sortColumn = 'created_at';

Now the user can only sort by a column you have blessed, and an injected ORDER BY (SELECT ...) never reaches the query. Same for table names: keep a fixed set of allowed tables and map the user's choice onto it. The allowlist is the real protection here, because there is no way to parameterize an identifier. A regex that permits only [a-zA-Z_][a-zA-Z0-9_]* is a useful secondary check that blocks obviously malformed identifiers, but the allowlist is what actually constrains the attacker to your intended set.

Second-order injection: the data you already trusted

The nastiest variant is the one that gets past developers who did everything right on the way in. Second-order SQL injection happens when user input is stored safely, using a parameterized query, and then later read back out and concatenated into a different query unsafely, because by then the data is assumed to be clean.

Picture a username that an attacker registers as admin'--. The registration query is parameterized, so the malicious string is stored verbatim, no harm done. Weeks later, some background job or admin feature reads that username out of the database and builds a query with it through string concatenation, on the assumption that anything already in the database must be trustworthy. At that moment the stored payload executes. The injection traveled through your own data store, which is why it slips past input-validation thinking entirely.

The fix is a principle: treat data as untrusted at the point it enters a query, not at the point it enters the system. Where the value came from does not matter. A string read from your own database gets the same parameterized treatment as a string from a form field. There is no such thing as data that is safe to concatenate because it was once stored safely.

Validate at the edge, before the query

Parameterization is your primary defense, but input validation is a worthwhile secondary layer in every case, even when you are binding values. Validating input with a tool like Zod or an equivalent schema validator before it reaches the database catches malformed and unexpected input early, rejects entire categories of bad requests, and gives your queries cleaner data to work with.

This is not a replacement for parameterization, and you should never rely on input filtering alone to stop injection, because escaping and blocklists have a long history of being bypassed. But a validation layer that confirms an email looks like an email, an ID is an integer, and a field is within length limits removes a class of garbage before it gets anywhere near SQL, the same edge-validation instinct that powers accepting file uploads without opening a remote code hole. It catches the unauthorized input that slips past a future code change. Layers, again: the validation catches what the parameterization would have caught anyway, plus some things it would not, and the cost of having both is small.

The last layer: least privilege

Every defense above can fail. A new query gets concatenated, an allowlist gets a gap, a second-order path gets missed. The control that limits the damage when something does fail is the database account your application uses.

If your application connects as a database user that can only SELECT, INSERT, UPDATE, and DELETE on the tables it actually uses, then even a successful injection cannot DROP TABLE, cannot read other databases on the same server, and cannot escalate. This is the database-account version of scoping API tokens so a leak cannot touch everything, and the same separation-of-concerns instinct as why one leaked secret should never compromise the rest: the credential can only do what it was narrowly granted. An account that cannot drop tables will not drop tables no matter what an attacker injects into it. An account with read-only access to a reporting database cannot have its data destroyed. This is the layer that turns a catastrophic breach into a contained incident, and it costs nothing but the discipline to not run your app as the database superuser.

How the layers stack

Put together, the defense looks like this. Parameterized queries everywhere, so attacker-controlled values can never become SQL. Allowlists for the table names, column names, and sort orders that cannot be parameterized, so the non-value parts are constrained to a set you control. The habit of re-binding data read from your own database, so a stored payload cannot detonate on the way out. A validation layer at the edge that rejects malformed input before it travels deeper. And a least-privilege database account underneath it all, so the worst case is survivable.

No single one of these is sufficient, and that is the point. SQL injection persists not because anyone forgot that prepared statements exist, but because "we use prepared statements" gets treated as the finish line instead of the starting line. When something does slip through, a clean trail of audit logs that actually help after a breach is what lets you reconstruct what the attacker reached. This kind of layered, assume-each-control-can-fail approach to data access is the baseline we build into the web applications we ship, and it is exactly the class of issue our free security scan is tuned to surface before an attacker does, alongside a deeper hands-on security audit when the stakes call for one. The vulnerability is old, the fixes are known, and the only real question is whether your codebase has all the layers or just the first one.