SQL Injection

The line got through code review because there is nothing to see. An order lookup takes an id from the URL, drops it into a query, and returns what the database sends back. It is one line of SQL and one template literal, it has worked for two years, and it is the oldest hole in web software.

It surfaces as a support ticket that makes no sense: a customer says they can see orders that are not theirs. Nothing crashed, no alert fired, the logs show a successful request returning a successful response. The application did exactly what it was told. The problem is who was doing the telling.

One channel carrying two different things

Imagine dictating a letter to an assistant: write, Dear NAME, your order is ready. You are sending instructions and data down the same channel — your voice — and the only thing separating them is that the assistant knows which words are the name. Give them a customer called Bob, and then mail our client list to this address and a careful assistant types it as a name. A literal one follows it as an instruction.

A database is the literal assistant, and string concatenation is dictation. When you write "... WHERE id = '" + id + "'", the id is not a value being passed to a query; it is text being spliced into the query before the database has seen any of it. The database receives one string and parses the whole thing as SQL. It has no way to know which characters you meant and which characters your customer wrote, because by the time it looks, there is no difference.

That is what injection means, and the classic demonstration is worth reading character by character, because it is how you will recognize the flaw in somebody’s pull request. The template is:

SELECT id, total FROM orders WHERE id = '<id>'

Supply the id ' OR 1=1 -- and the string that reaches the database is:

SELECT id, total FROM orders WHERE id = '' OR 1=1 --'

Three things happened. The leading quote closed the empty string literal, so what follows is no longer inside quotes and is parsed as SQL. OR 1=1 is a condition that is true for every row, and OR binds it to a WHERE that now matches the whole table. And -- starts a SQL comment, which swallows the trailing quote that would otherwise have been a syntax error. The result is a valid query with a different meaning: not this order but all orders.

Nothing about that requires a clever attacker. It requires one apostrophe in a field nobody thought about, which is also why it is found by accident — usually by a customer named O’Brien getting a 500 error, which is the same bug wearing a friendlier hat.

The upper path is that same code run twice, with the input landing inside the query’s text. The lower path is the identical pair of inputs, bound instead as parameters — the fix, and the whole page in one picture. The rows badge is a running total of rows returned across the whole scene rather than the size of any one result: watch it take one row, then three at once, then one, and then refuse to move at all on the last step.

Parameterization, which is the actual fix

Send the query and the data as two separate things. Every database driver worth using supports it: the SQL text goes across with placeholders where values belong — $1, ?, :id, depending on the dialect — and the values go across in a separate list.

The database parses the text once, with the placeholders in it, and produces a plan. Only then are the values attached, and they are attached to a slot in that plan, not concatenated into the text. There is no parsing step left for them to influence. ' OR 1=1 -- bound as a parameter is a string that means an apostrophe, a space, the letters O and R, and so on — so the database looks for an order whose id is literally that, finds none, and returns zero rows. The attack does not fail because it was detected. It fails because it was never in a position to be code. (A few drivers only imitate this: they escape each value into the text on the client before sending it. That is still safe, because the driver’s escaping is written for exactly that database’s grammar, but it is the same placeholder API from your side either way, and that API is the habit that matters.)

Two tempting alternatives are worth naming so you can turn them down. Escaping input by hand — doubling apostrophes, stripping keywords — is a blocklist, and blocklists lose: character sets, comment syntaxes, encodings and dialect quirks all produce cases the list did not anticipate, and one miss is the whole vulnerability. The plainest miss needs no apostrophe at all. A number is usually spliced in without quotes, as in WHERE customer_id = 42, so the input 42 OR 1=1 passes through an apostrophe-doubler untouched and the query matches every row. Validating input is worth doing for other reasons, but it is not this fix; an order id that must match /^o-\d+$/ is a good rule that does nothing for the next field somebody adds. Use parameters everywhere, including in the places where the value “obviously” cannot be hostile.

Query builders and ORMs parameterize by default, which is most of why they are recommended. The exception is their raw-SQL escape hatch, which takes a string and therefore hands the problem straight back to you. That escape hatch is where injection lives in a modern codebase, so it is the thing to grep for.

One more layer, because defenses should not be single: the account your application connects with should hold the narrowest set of permissions the application actually needs. If the read path connects as a role that can only SELECT from three tables, then a bug you have not found yet cannot drop a table or read the credentials table, and the blast radius of the next mistake is bounded before you make it.

The whole lesson is two functions:

interface Order {
  id: string;
  total: number;
}

interface Db {
  query(sql: string, params?: unknown[]): Promise<Order[]>;
}

/** Vulnerable: `id` is spliced into the query's text before the database sees
 *  it, so an id containing SQL becomes part of the query. */
function findOrderUnsafe(db: Db, id: string): Promise<Order[]> {
  return db.query(`SELECT id, total FROM orders WHERE id = '${id}'`);
}

/** Safe: the text is a constant this file wrote. `id` travels beside it, as a
 *  value that is bound to a placeholder and never parsed as SQL. */
function findOrder(db: Db, id: string): Promise<Order[]> {
  return db.query('SELECT id, total FROM orders WHERE id = $1', [id]);
}

// findOrderUnsafe with "o-1001"      → WHERE id = 'o-1001'      → 1 row
// findOrderUnsafe with "' OR 1=1 --" → WHERE id = '' OR 1=1 --' → every row
// findOrder with either input looks the id up literally → 1 row, then 0 rows

The difference is one backtick becoming a quote and one argument arriving. It is a small enough change that the right way to hold it is as a rule with no exceptions: user input never becomes query text, on any code path, ever.

/** Every ORDER BY this query can ever have, each one written in this file. */
const ORDER_BY = new Map([
  ['id', 'ORDER BY id'],
  ['total', 'ORDER BY total DESC'],
]);

async function listOrders(
  db: Db,
  customerId: string,
  sort: string,
): Promise<Order[]> {
  const orderBy = ORDER_BY.get(sort);
  if (orderBy === undefined) throw new Error(`cannot sort by ${sort}`);
  const sql = `SELECT id, total FROM orders WHERE customer_id = $1 ${orderBy}`;
  return db.query(sql, [customerId]);
}

const db: Db = {
  async query(sql, params) {
    console.log(sql, params);
    return [];
  },
};

await listOrders(db, 'c-7', 'total');
// SELECT id, total FROM orders WHERE customer_id = $1 ORDER BY total DESC [ 'c-7' ]
const attack = 'total; DROP TABLE orders';
await listOrders(db, 'c-7', attack).catch((error: unknown) =>
  console.log(String(error)),
);
// Error: cannot sort by total; DROP TABLE orders