SQL Injection in APIs
Still present, still catastrophic, and now usually hiding in the parts of a query that cannot be parameterised.
SQL injection is thirty years old and has not gone away. In APIs it survives in specific places: dynamic sorting, dynamic filtering, search, reporting queries, and the one raw query someone wrote because the ORM was awkward.
The only real fix
Parameterised queries. The driver sends the SQL and the values separately, so the database never parses the value as code. This is not escaping — escaping is a string operation that can be defeated by encoding, and parameterisation is a protocol-level separation.
What cannot be parameterised
Values can be bound. Identifiers cannot. Table names, column names, ORDER BY directions and LIMIT in some drivers are part of the SQL text, not parameters. That is exactly where API injection lives, because sorting and filtering are client-controlled by design.
The answer is an allowlist mapping: the client sends sort=name, and you look up a known column. Never interpolate the client's string, however carefully you escape it.
ORMs are not immunity
Every ORM has a raw escape hatch, and every ORM has methods that interpolate. whereRaw, $queryRawUnsafe, literal(), DB::raw() — grep for them, and check every hit.
Second-order injection
Input is stored safely with a parameterised insert, then read back and concatenated into a different query later — often in a report or a background job. The value was validated on the way in and is now trusted on the way out. Parameterise every query, not just the ones handling direct user input.
Defence in depth
A least-privilege database role means injection into a read-only reporting query cannot write. Row-level security means it cannot cross a tenant. Neither replaces parameterisation; both bound the damage when it fails.
Example
// ❌ String building. Every variant of this is exploitable.
db.raw(`SELECT * FROM invoices WHERE user_id = ${userId}`);
db.raw("SELECT * FROM invoices WHERE ref = '" + ref + "'");
db.raw(`SELECT * FROM invoices ORDER BY ${req.query.sort}`);
// ✅ Values are bound
db.raw('SELECT * FROM invoices WHERE user_id = ? AND ref = ?', [userId, ref]);
db('invoices').where({ user_id: userId, ref });
// ✅ Identifiers come from an allowlist — they CANNOT be bound
const SORTABLE = { name: 'name', created: 'created_at', total: 'total_cents' };
const column = SORTABLE[req.query.sort] ?? 'created_at';
const dir = req.query.order === 'asc' ? 'asc' : 'desc';
db('invoices').orderBy(column, dir);When to use it
- A reporting endpoint that accepted a sort column from the query string allowed data extraction through a UNION, despite the rest of the API using an ORM.
- A second-order injection fires in a nightly job that concatenates a stored display name into a summary query.
- A least-privilege read-only role limits an injection in an analytics endpoint to reading, preventing the attacker from writing an admin user.
More examples
Dynamic filtering and sorting, safely
Ignoring unknown filter keys rather than erroring is a deliberate choice here; erroring would let an attacker enumerate which columns are filterable.
// Search and filter endpoints are where API SQL injection actually lives,
// because the client legitimately controls the query shape.
const SORTABLE_COLUMNS = {
createdAt: 'invoices.created_at',
total: 'invoices.total_cents',
customer: 'customers.name',
};
const FILTERABLE = {
status: { column: 'invoices.status', type: 'enum',
values: ['draft', 'open', 'paid', 'void'] },
minTotal: { column: 'invoices.total_cents', type: 'number', op: '>=' },
customerId: { column: 'invoices.customer_id', type: 'uuid', op: '=' },
createdAfter: { column: 'invoices.created_at', type: 'date', op: '>=' },
};
export async function searchInvoices(userId, params) {
let q = db('invoices')
.leftJoin('customers', 'customers.id', 'invoices.customer_id')
.where('invoices.user_id', userId); // authorization, bound
// Filters: the COLUMN comes from our map, the VALUE is bound.
for (const [key, raw] of Object.entries(params.filters ?? {})) {
const spec = FILTERABLE[key];
if (!spec) continue; // unknown filter → ignored
const value = coerce(raw, spec); // throws on a bad type
if (spec.type === 'enum' && !spec.values.includes(value)) {
throw new BadRequestError(`invalid value for ${key}`);
}
q = q.where(spec.column, spec.op ?? '=', value);
}
// Free-text search: still a bound parameter, even inside a LIKE.
if (params.q) {
const term = `%${String(params.q).slice(0, 100).replace(/[%_]/g, '\\$&')}%`;
q = q.where((b) => b.whereILike('invoices.reference', term)
.orWhereILike('customers.name', term));
}
// Sorting: the only safe mechanism is a lookup, not escaping.
const column = SORTABLE_COLUMNS[params.sort] ?? SORTABLE_COLUMNS.createdAt;
const dir = params.order === 'asc' ? 'asc' : 'desc';
return q.orderBy(column, dir)
.limit(Math.min(Number(params.limit) || 20, 100));
}
// Escaping % and _ in the LIKE term is not a security fix — it stops a user
// turning a search into a full table scan with '%%%%%'.Second-order injection, and why it survives review
The grep at the end is the practical takeaway: raw calls that contain no placeholder are the ones worth reading, and there are usually only a handful.
// Step 1 — stored correctly. This code is fine.
app.post('/api/customers', auth, validate(customerSchema), async (req, res) => {
await db('customers').insert({ // parameterised
user_id: req.user.id,
name: req.body.name, // "Robert'); DROP TABLE orders;--"
});
res.sendStatus(201);
});
// Step 2 — read back and CONCATENATED, six months later, in a different file,
// by someone who reasonably assumed the database contained safe data.
async function monthlyReport(userId) {
const customers = await db('customers').where({ user_id: userId });
for (const c of customers) {
// ❌ The value came from OUR database, so it must be safe. It is not.
const rows = await db.raw(
`SELECT SUM(total_cents) FROM invoices
WHERE customer_name = '${c.name}'`);
// ...
}
}
// ✅ Parameterise EVERY query. The origin of the value is irrelevant.
const rows = await db.raw(
'SELECT SUM(total_cents) FROM invoices WHERE customer_name = ?', [c.name]);
// Why it survives review: the injection point and the entry point are in
// different files, written by different people, months apart. The reviewer of
// the report code sees a database read, not user input.
//
// The only rule that scales: there is no such thing as trusted data in a query.
// Bind it, always, regardless of where it came from.
// Find them all:
// grep -rn "raw(\|DB::raw\|queryRawUnsafe\|literal(\|\.query(" src/ \
// | grep -v "?\|:param\|\$1"Bounding the damage when it happens anyway
A spike in SQL syntax errors is one of the highest-signal alerts available — legitimate application traffic produces essentially none.
-- Parameterisation is the fix. These decide how bad the failure is.
-- 1. Least privilege per code path
CREATE ROLE app_ro LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
-- The reporting connection uses app_ro. Injection there cannot write, cannot
-- create a user, cannot drop a table. It is a read breach, not a takeover.
-- 2. No superuser, ever, and no file access
REVOKE ALL ON pg_read_file(text) FROM PUBLIC; -- no reading /etc/passwd
REVOKE ALL ON pg_ls_dir(text) FROM PUBLIC;
-- (Postgres COPY TO PROGRAM requires superuser — another reason not to be one.)
-- 3. Row-level security bounds the blast radius to one tenant
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- A UNION SELECT still only returns the current tenant's rows.
-- 4. Statement timeout: a heavy injected query cannot run for an hour
ALTER ROLE app_ro SET statement_timeout = '10s';
ALTER ROLE app_rw SET statement_timeout = '30s';
-- 5. Detection: log anything that smells like probing
-- postgresql.conf
-- log_min_duration_statement = 1000 # slow queries are often injected ones
-- log_statement = 'ddl' # any DDL from the app role is an alert
# And in the application, alert on the shape of the failure:
# a spike in SQL syntax errors is someone probing. Normal traffic does not
# produce them at all, so the signal is unusually clean.
Discussion