Injection Defense

The previous lesson targeted the container the value would be read into: body, attribute, script, address. In all four, the container was a document and the reader was a viewer. Placing a value into a query text is a different problem, because there the reader has to separate data from command, and the only thing it has to make that separation with is the text itself.

This lesson counts query points. The Server Security and Going to Production course measured the outcome difference two setups produced; what is measured here is not the outcome but coverage: how many queries keep the value outside the text, how many fold it into the text, and which point is covered on its own when a new query is written.

The Value That Reaches the Query Text

The measure is a single question: once the query text is built, does the value itself appear in that text. This question does not require a database engine; the text is a string, and the value is either in that string or it is not.

IO7 (assumption): the query text is a model; there is no engine running it. A point counts as covered if none of its values appear in the text. How an engine would parse the text is not measured.

// interface.mjs — two ways to build a query. The query text is a model; there is no engine
// running it. The thing measured is a single question: did the value reach the query text?

// Parameterized query interface: accepts only a tagged template; does not accept a string.
export function query(parts, ...values) {
  if (!Array.isArray(parts?.raw)) throw new TypeError("query text cannot be given as a string");
  let text = parts[0];
  const bound = [];
  values.forEach((d, i) => {
    if (d && d.identifier !== undefined) {
      if (d.identifier === null) throw new RangeError("identifier not in allowlist");
      text += d.identifier;                    // not a spot, a name: cannot be passed as bound
    } else { text += "?"; bound.push(d); }
    text += parts[i + 1];
  });
  return { text, bound, tracked: true };
}

// Manual concatenation: everything is folded into the text, no bound values remain.
export function manual(parts, ...values) {
  let text = parts[0];
  values.forEach((d, i) => {
    text += String(d && d.identifier !== undefined ? d.raw : d) + parts[i + 1];
  });
  return { text, bound: [], tracked: false };
}

// Identifier spot: allowlist for field and table names.
const FIELD = { reading_time: "reading_time", period: "period", amount: "amount" };
export const name = (x) => ({ identifier: FIELD[x] ?? null, raw: x });

// Leak checker: how many of the given values appear in the query text.
export const leak = (s, values) =>
  values.filter((d) => s.text.includes(String(d))).length;

The two forms are written almost identically — both take a tagged template, both build the same query. The one place they diverge is where the value goes: one puts a placeholder in the text and writes the value into a separate array, the other folds the value into the text itself.

Query Point Inventory

The query points of the regional measurement network are counted: the subscriber, invoicing, reading, work order, and report services. Each point writes one query shape with one interface.

// coverage.mjs — query point inventory (fictional model) and the coverage of the two forms
import { query, manual, name, leak } from "./interface.mjs";

// Four query shapes; every shape takes the same values, the only difference is which interface writes it.
const SHAPE = {
  search: (b, v) => b`SELECT no FROM subscriber WHERE region = ${v.region} AND tariff = ${v.tariff}`,
  fetch:  (b, v) => b`SELECT * FROM invoice WHERE subscriber = ${v.subscriber} AND period = ${v.period}`,
  sort:   (b, v) => b`SELECT * FROM reading ORDER BY ${name(v.field)} LIMIT ${v.limit}`,
  count:  (b, v) => b`SELECT count(*) FROM work_order WHERE type = ${v.type}`,
};
const V = { region: "north", tariff: "residential", subscriber: "A-100432", period: "2026-03",
  field: "reading_time", limit: 50, type: "disconnection" };
const VALUES_OF = { search: ["north", "residential"], fetch: ["A-100432", "2026-03"], sort: [50], count: ["disconnection"] };

// [service, query point, shape, interface]
const POINT = [
  ["subscriber", "regionSearch", "search", query], ["subscriber", "tariffSearch", "search", query],
  ["subscriber", "historySearch", "search", manual], ["invoice", "periodFetch", "fetch", query],
  ["invoice", "debtFetch", "fetch", query], ["invoice", "archiveFetch", "fetch", manual],
  ["reading", "latestReading", "sort", query], ["reading", "meterHistory", "sort", query],
  ["reading", "aggregateReport", "sort", manual], ["reading", "outlierReading", "search", query],
  ["work-order", "openCount", "count", query], ["work-order", "regionCount", "count", query],
  ["work-order", "historyCount", "count", manual], ["report", "summaryFetch", "fetch", query],
  ["report", "dumpSort", "sort", manual], ["report", "monthlyCount", "count", query],
];

const measure = (list) => {
  let reached = 0, identifierSpots = 0, untracked = 0;
  for (const [, , shape, b] of list) {
    const s = SHAPE[shape](b, V);
    reached += leak(s, VALUES_OF[shape]);
    if (shape === "sort") { identifierSpots++; if (!s.tracked) untracked++; }
  }
  return [reached, identifierSpots, untracked];
};

const P = POINT.filter((n) => n[3] === query), M = POINT.filter((n) => n[3] === manual);
console.log(`${POINT.length} query points, ${Object.keys(SHAPE).length} query shapes\n`);
console.log(`${"form".padEnd(25)}${"points".padStart(8)}${"value spots".padStart(13)}` +
  `${"reached text".padStart(14)}${"identifier spots".padStart(18)}${"untracked".padStart(11)}`);
for (const [label, l] of [["parameterized interface", P], ["manual concatenation", M]]) {
  const spots = l.reduce((t, n) => t + VALUES_OF[n[2]].length, 0);
  const [u, t, d] = measure(l);
  console.log(label.padEnd(25) + String(l.length).padStart(8) + String(spots).padStart(13) +
    String(u).padStart(14) + String(t).padStart(18) + String(d).padStart(11));
}
console.log("\nsample text (parameterized): " + SHAPE.fetch(query, V).text);
console.log("sample text (manual)       : " + SHAPE.fetch(manual, V).text);

16 query points, 4 query shapes

form                       points  value spots  reached text  identifier spots  untracked
parameterized interface        11           17             0                 2          0
manual concatenation            5            7             7                 2          2

sample text (parameterized): SELECT * FROM invoice WHERE subscriber = ? AND period = ?
sample text (manual)       : SELECT * FROM invoice WHERE subscriber = A-100432 AND period = 2026-03

Eleven of the sixteen points go through the parameterized interface, and across those eleven, none of the seventeen value spots reach the text. Five points use manual concatenation, and there all seven of the seven value spots are inside the text. The number of points left exposed is five.

These five have something in common: three are archive and history queries, two are reports. The main flow is protected, the edge flows are not — because the decision was made per point, and the edge flows were written by a different person, or at a different time.

The same measure applies to command points as well: does the value pass to the subprocess as a separate argument, or does it get folded into a single command string. The question is the same question; the measure is the same measure — did the value reach the text.

How the number is obtained is itself part of coverage. The leak checker scans the text here, but the tracked flag the interface returns gives the same information without scanning: every query result carries which form built it. Coverage being countable depends on the defense itself producing the count — a scanner looking from the outside sees the query text, not how that text was built.

The Spot That Cannot Be Closed

The one column in the table that the parameterized interface does not zero out is identifier spots. Two points put a field name into the query, and a field name cannot be passed through a placeholder: a placeholder represents a value, not the structure of the query. The sort field, the table name, and the direction keyword have to be written into the text itself.

The parameterized interface cannot provide coverage here; the only thing it provides is making this spot visible. Because the code is marked with a name() call, identifier spots become countable, and the only defense there is the pattern from the first lesson: the allowlist. The block below puts three decisions side by side.

// identifier.mjs — three decisions at the identifier spot: manual, allowlist, placeholder attempt
import { query, manual, name } from "./interface.mjs";

const INPUT = ["reading_time", "period", "amount", "subscriber_no", "reading_time desc", "internal_note"];
const tail = (f) => { try { return f().text.split("ORDER BY ")[1]; } catch (e) { return e.constructor.name; } };

console.log(`${INPUT.length} sort field inputs, one identifier spot\n`);
console.log(`${"input".padEnd(20)}${"manual".padStart(20)}${"allowlist".padStart(14)}${"placeholder".padStart(13)}`);
let raw = 0, accepted = 0, broken = 0;
for (const g of INPUT) {
  const m = tail(() => manual`SELECT * FROM reading ORDER BY ${name(g)}`);
  const q = tail(() => query`SELECT * FROM reading ORDER BY ${name(g)}`);
  const p = tail(() => query`SELECT * FROM reading ORDER BY ${g}`);
  if (m === g) raw++;
  if (q === g) accepted++;
  if (p !== g) broken++;
  console.log(g.padEnd(20) + m.padStart(20) + q.padStart(14) + p.padStart(13));
}
console.log(`\nmanual: ${raw}/${INPUT.length} inputs entered the text as-is`);
console.log(`allowlist: ${accepted} accepted, ${INPUT.length - accepted} rejected`);
console.log(`placeholder pass-through: ${broken}/${INPUT.length} queries had their structure broken`);

6 sort field inputs, one identifier spot

input                             manual     allowlist  placeholder
reading_time                reading_time  reading_time            ?
period                            period        period            ?
amount                            amount        amount            ?
subscriber_no              subscriber_no    RangeError            ?
reading_time desc      reading_time desc    RangeError            ?
internal_note              internal_note    RangeError            ?

manual: 6/6 inputs entered the text as-is
allowlist: 3 accepted, 3 rejected
placeholder pass-through: 6/6 queries had their structure broken

The last column is the proof of why this spot cannot be closed: when the input is passed as a value, the text becomes ORDER BY ? in all six of the six queries, and the sort field is erased from the query. A placeholder here is not a defense, it is a bug.

The middle column gives the only solution. Three of the six inputs are on the allowlist and pass through as-is; three are rejected — one is a field not in the schema, one is a field with a direction word appended, one is a name not on the list. In manual concatenation, all six enter the query. Coverage at the identifier spot comes from the length of the allowlist, not from the parameterized interface, and a legitimate field that is not on the list gets rejected too — that is the cost of this spot.

When a New Query Is Added

The test of coverage begins again when the inventory grows. Four new query points open, and they are written in two separate access layers: one offers only the parameterized interface, the other also accepts the query text as a string.

IO8 (assumption): in the flexible layer, which form the person writing the new query will choose is not known; what is measured is the worst case the layer allows. In the strict layer, no such case exists, because there is no choice.

// new-query.mjs — when a new query is added, which form covers the point on its own
import { query, manual, name, leak } from "./interface.mjs";

const attempt = (f) => { try { return "passed -> " + f().text; } catch (e) { return e.constructor.name + ": " + e.message; } };
console.log("query given as a string     : " + attempt(() => query("SELECT no FROM subscriber WHERE region = 'north'")));
console.log("identifier outside allowlist: " + attempt(() => query`SELECT * FROM reading ORDER BY ${name("internal_note")}`));
console.log("same identifier, manual     : " + attempt(() => manual`SELECT * FROM reading ORDER BY ${name("internal_note")}`));

// Four new query points; same shapes, only the interface changes.
const NEW = [
  ["missingReading", (b, v) => b`SELECT no FROM reading WHERE source = ${v.source}`, ["field"]],
  ["debtorSearch", (b, v) => b`SELECT subscriber FROM invoice WHERE amount > ${v.amount}`, [1200]],
  ["workOrderFilter", (b, v) => b`SELECT * FROM work_order WHERE type = ${v.type} AND region = ${v.region}`, ["connection", "south"]],
  ["dailyDump", (b, v) => b`SELECT * FROM reading ORDER BY ${name(v.field)} LIMIT ${v.limit}`, [100]],
];
const V = { source: "field", amount: 1200, type: "connection", region: "south", field: "period", limit: 100 };
const spots = NEW.reduce((t, [, , d]) => t + d.length, 0);

console.log(`\n${NEW.length} new query points, ${spots} value spots\n`);
console.log(`${"layer form".padEnd(39)}${"reached text".padStart(14)}${"untracked points".padStart(18)}`);
for (const [label, b] of [["strict layer (parameterized only)", query], ["flexible layer (also accepts strings)", manual]]) {
  const reached = NEW.reduce((t, [, f, d]) => t + leak(f(b, V), d), 0);
  const untracked = NEW.filter(([, f]) => !f(b, V).tracked).length;
  console.log(label.padEnd(39) + String(reached).padStart(14) + String(untracked).padStart(18));
}

query given as a string     : TypeError: query text cannot be given as a string
identifier outside allowlist: RangeError: identifier not in allowlist
same identifier, manual     : passed -> SELECT * FROM reading ORDER BY internal_note

4 new query points, 5 value spots

layer form                               reached text  untracked points
strict layer (parameterized only)                   0                 0
flexible layer (also accepts strings)               5                 4

The first three lines show the interface’s own limits. The query text cannot be given as a string; if it is, the call stops at the first line. A field name that is not on the allowlist does not get through either. That same field name enters the query in manual concatenation and gives no sign of it.

The table below gives the real number. In the strict layer, none of the five value spots across the four new queries reach the text, and the reason is not diligence, it is that there is no other way. In the flexible layer, all five of the five value spots can reach the text; the layer does not prevent this, it only allows it.

The difference here is not a difference in habit. In the strict layer, coverage is fixed without writing any code; in the flexible layer, every new query depends on a person choosing the right form, and across the sixteen-point inventory, that person chose something else five times.

Summary

• The measure is a single question: once the query text is built, does the value appear in that text. This question does not require a database engine; the query text is a string.
• 11 of the sixteen query points go through the parameterized interface, and there 0 of the 17 value spots reach the text; 5 points use manual concatenation, and there 7 of the 7 value spots are inside the text. The number of points left exposed is 5, and all five are edge flows.
• There is one spot the parameterized interface cannot close: the identifier spot. A field name cannot be passed through a placeholder, because a placeholder represents a value, not structure. There are 2 such points in the inventory. When the six sort inputs were passed as values, the structure of 6 queries broke; the allowlist accepted 3 of them and rejected 3; in manual concatenation, all 6 entered the query.
• The interface does not accept the query text as a string, and it rejects an identifier that is outside the allowlist; both stop at the moment of the call. In manual concatenation, the same identifier enters the query and gives no sign of it.
• Across four new queries, the strict layer leaked 0 value spots, the flexible layer leaked 5. In the strict layer, coverage is fixed without writing code; in the flexible layer, every new query depends on a decision.

Next Step

The parameterized interface keeps the value outside the text; at the identifier spot, the allowlist takes over instead. Both treat the text as fixed: the skeleton of the query is what the developer wrote. There is one place where this assumption does not hold. Notification texts, report headers, and invoice description lines are produced from templates, and some of these templates are built at runtime — some take only values, some take user data into their body. The next lesson counts templates and measures a single question: how many templates take user data only as a value, and how many turn that data into part of an expression evaluated on the server.