SecurityGENERALDATABASE-SPECIFICFRAMEWORK-SPECIFIC

SQL Injection

Parameterized queries solve injection completely — and do nothing whatsoever for authorization.

What actually happensHow to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

How does user input reach a query safely, and what does making it safe still not fix?

The requirement

A search box filters invoices by customer name, with a sortable column and a page size. All three come from the client.

The obvious build

Build the SQL string with the values in it. It is one line, the resulting query is readable in the logs, and the ORM's abstractions get in the way for a query this simple.

Why it breaks

A name containing a quote breaks the query for an ordinary customer — the same defect that makes it exploitable makes it fragile for honest input.

How it breaks in production
  • A name containing a quote breaks the query for an ordinary customer — the same defect that makes it exploitable makes it fragile for honest input.
  • The value is not data any more. Once it is concatenated into the string, the database parses it as part of the statement, and there is no way for the engine to tell the difference.
  • Escaping by hand works until an encoding, a numeric context, a LIKE pattern or a second escaping layer changes the rules. Correct escaping is engine-specific and version-specific; parameterization is neither.
  • One safe endpoint is worth nothing if a reporting query built later uses concatenation. The property has to hold everywhere, so it has to be structural.
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

  • A parameterized query sends the statement and the values separately. The engine parses the statement first, producing a plan with placeholders, and the values are bound afterwards. There is no parsing step for the value to influence.
  • This is not escaping. Escaping tries to neutralise characters inside a string that will be parsed; parameterization means the string is never parsed at all. That is why it holds regardless of content, encoding or engine.
  • Placeholders can only stand where a value can stand. A table name, a column name, ASC/DESC and LIMIT in some engines are parts of the statement, so they cannot be parameters — which is exactly where injection survives in otherwise careful code.
  • ORMs and query builders parameterize by default, and every one of them has a raw escape hatch. The vulnerability lives in the escape hatch, and it is used most often for the reporting and admin queries that touch the most data (Raw SQL in Application Code).

Statement and values travel separately

The mental model that makes this stick: the database compiles a statement into a plan, then binds values into that plan. When you concatenate, you hand the compiler a string that already contains the user's text, so the user is contributing to the grammar. When you parameterize, the grammar is fixed before the value exists.

That is why parameterization does not care what the value contains. There is no character that means anything special in a bound parameter, because the parameter is never parsed as SQL.

Filtering invoices by customer name
Concatenated
const sql = `SELECT * FROM invoices WHERE customer_name = '${name}'`
const rows = await db.query(sql)
Parameterized
const rows = await db.query(
  'SELECT id, total, issued_at FROM invoices WHERE tenant_id = $1 AND customer_name = $2',
  [session.tenantId, name],
)

In the first, name becomes part of the statement the parser sees. In the second the statement is fixed before name exists, so no value can change its meaning. Note the second query also carries tenant_id from the session — that is the separate control, and it is doing separate work.

The same property in Python
1cur.execute(
2    "SELECT id, total FROM invoices WHERE tenant_id = %s AND customer_name = %s",
3    (session.tenant_id, name),          # values, passed as values
4)
5 
6# Not this, ever - the % happens before the driver sees anything:
7# cur.execute("SELECT * FROM invoices WHERE customer_name = '%s'" % name)

The driver placeholder and Python's own string formatting look almost identical. The difference is whether the substitution happens in your process or in the driver's parameter binding — the second is the entire control.

The part that cannot be a parameter

DATABASE-SPECIFICPostgreSQL takes parameters for LIMIT and OFFSET; MySQL drivers vary and some emulate it client-side. No engine takes a parameter for an identifier, which is the part this section is about.

Sorting is where injection survives in code written by people who know about parameters. ORDER BY $1 does not do what it looks like — an engine that accepts it treats the bound value as a constant expression, not as a column reference. So the column name has to be part of the statement text, and the temptation is to interpolate the client's string after validating it.

Do not validate the string and then use it. Use the string to look up a value your code owns. The difference sounds pedantic and is the whole control: after the lookup, nothing attacker-influenced reaches the statement, whatever the validation missed.

Allow-listing the parts that are grammar, not data
1const SORTABLE = {
2  issued:   'issued_at',
3  total:    'total_cents',
4  customer: 'customer_name',
5} as const
6 
7const DIRECTION = { asc: 'ASC', desc: 'DESC' } as const
8 
9const column = SORTABLE[req.query.sort as keyof typeof SORTABLE]
10const dir = DIRECTION[req.query.dir as keyof typeof DIRECTION]
11if (!column || !dir) return res.status(400).json({ error: 'invalid_sort' })
12 
13// column and dir are now constants from this file, not strings from the client
14const rows = await db.query(
15  `SELECT id, total_cents, issued_at FROM invoices
16     WHERE tenant_id = $1
17     ORDER BY ${column} ${dir}
18     LIMIT $2 OFFSET $3`,
19  [session.tenantId, pageSize, offset],
20)

The interpolated values are literals defined above, so the template is a fixed set of statements chosen by a lookup. pageSize and offset are values and stay parameters — and they need their own bounds, because an unbounded LIMIT is a different problem (Pagination That Survives a Large Table).

Injection and authorization are different problems

This is the point The Trust Boundary introduces and this lesson has to finish, because the confusion is durable. Parameterization answers: can this input change what the statement means? Authorization answers: may this caller see these rows at all? A query can be perfect on the first and silently wrong on the second.

The reason the confusion survives is that both problems are about untrusted input, and a review that finds the parameter placeholders feels complete. It is not: the parameter is safe, and the *value* bound to it may still be a tenant id the caller invented.

Practically: every query touching tenant-scoped data takes its scope from the authenticated principal, and that predicate is not optional or overridable by a request field. Injection safety is a property of how the query is built; authorization is a property of what is in it.

QueryInjection-safe?Authorized?What happens
WHERE id = ' + idNoNoStatement is attacker-influenced; everything the DB user can reach is reachable
WHERE id = $1, id from pathYesNoAny authenticated caller reads any record by changing the id — an IDOR, and the most common serious backend bug there is
WHERE tenant_id = $1 with tenant from the bodyYesNoLooks scoped, is not: the caller supplies the scope (Tenant Isolation)
WHERE tenant_id = $1 AND id = $2, tenant from the sessionYesYesStatement fixed, scope derived from the verified principal — both controls present

How to build it

Most important first.

  • Use parameters for every value, without exception, including values you believe are numeric or came from your own service (The Trust Boundary).
  • For the parts that cannot be parameters — sort column, sort direction, table, dynamic filters — map the client's token through an allow-list to a constant your code owns. Never interpolate the client's string, even after checking it.
  • Make the unsafe path hard to reach: a repository API that only accepts parameters, a lint rule against string concatenation in query calls, and a code-review convention that raw SQL requires a named reason (The Repository Layer).
  • Give the application a database user with only the privileges it needs — no DDL, no access to tables outside its schema. That does not prevent injection; it bounds what a successful one reaches (Defence in Depth).
  • Validate input for shape as well, because a name field that accepts 4 KB of anything is a problem for reasons beyond SQL (Transport Validation).
  • Keep authorization as a separate, explicit concern: every query that reads or writes tenant data carries the principal's scope in its WHERE clause (Object-Level Authorization).

What can go wrong

Failure modes
  • The safe path everywhere except the one dynamic report, the one admin search, or the one migration script written under time pressure.
  • An allow-list implemented as a regular expression on the client's string rather than a lookup to a constant — which passes review and still concatenates attacker-influenced text.
  • String building inside a stored procedure or a database function, where the application looks parameterized and the injection is one layer down.
  • An ORM method that accepts a raw fragment for ORDER BY or a raw where string, used because the typed API did not cover the case.
  • Least privilege configured for the application user and then undone by connecting as the owner in a job, a migration runner or a debug tool.
What can race
  • Reading a row to check ownership and then updating it in a second statement leaves a window. Put the ownership predicate in the UPDATE itself and check the affected-row count (Optimistic Concurrency).
Security
  • A successful injection runs with the privileges of the database user your application connects as. If that user owns the schema, the reachable damage includes reading every table, modifying data and dropping objects.
  • The impact is not limited to reading rows: injection can be used to write, to change state that another part of the system trusts, and to trigger long-running queries that take the database down.
  • Parameterization removes the class entirely for values. It does not remove it for identifiers, and it does not touch access control at all — see the section below, because this is the single most common conflation in this area.
  • Attack technique, detection and exploitation belong to Security Engineering (SQL Injection there). What the implementer owes is the call-site discipline and the privilege boundary.
Misreads
  • "We use an ORM, so we are safe from injection." You are safe on the paths that go through the ORM's query construction. The raw method is part of the ORM, and it is where the exposure is.
  • "The input is a number, so it cannot be injected." It is a string until something makes it a number, and parseInt on unvalidated input in one branch is not a guarantee across all branches.
  • "We escape quotes." Escaping is engine-specific, context-specific and easy to get subtly wrong. Parameterization is a different mechanism, not a better version of the same one.
  • "The query is parameterized, so the endpoint is secure." This is the big one. A parameterized query with a client-supplied tenant id is injection-proof and still returns any tenant's data.

Operating it

How you see it in production
  • Log query shape — the parameterized statement — with parameter values excluded. That gives you a usable slow-query and error picture without putting customer data or credentials in logs (Secrets in Logs).
  • Alert on database errors of the "syntax error" family in production. A steady trickle usually means a concatenation path meeting real data; a spike means someone is probing.
  • Track distinct query shapes per endpoint. A handler that should emit two shapes and emits thousands is building strings.
  • A static-analysis rule for string concatenation reaching a query call is far more reliable than review, and it runs on every commit.
What changes at 10x and 100x
  • Parameterized queries also reuse prepared plans, so the safe path is usually the faster one at high query rates — one of the rare cases where the security control pays for itself in performance (Query Optimization: Finding the Actual Bottleneck in Database Engineering).
  • At 10x endpoints the risk shifts from "did we get this query right" to "can a new engineer write an unsafe query at all". That is a tooling question.
  • Least privilege gets harder as one service grows to touch more tables. Splitting by schema and granting per schema keeps the bound meaningful (Multi-Tenancy).
What this costs
  • Allow-listed sort and filter columns mean a new sortable column is a code change rather than a client change. That is a genuine cost to API flexibility, and it is the correct trade.
  • Restrictive database grants mean migrations need a second, privileged path, and someone has to operate it.
  • A repository layer that forbids raw SQL will eventually block a legitimate query that the typed API cannot express. Plan the escape hatch deliberately, with review, rather than pretending it is never needed.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

  • GENERALThe value/statement separation is true of every SQL engine and every driver worth using. It also generalises to other query languages with a parameter concept.
  • DATABASE-SPECIFICWhat may be a placeholder differs: PostgreSQL accepts parameters for LIMIT/OFFSET, some engines and some drivers do not, and no engine accepts a parameter where an identifier belongs. Check your driver rather than assuming — the identifier case is where careful code still concatenates.
  • FRAMEWORK-SPECIFICEvery ORM parameterizes its typed API and every ORM ships a raw method. The name differs; the fact that the raw method is the exposure does not.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designrequest-contractssortingfiltering
Databasesql-basicsquery-executionsql-parser-internals
Securitysql-injectioninput-validationdatabase-privilegesbroken-access-controlleast-privilege