A SQL rule that flagged constants, and how we taught it the difference

A rule that says string interpolation in SQL is an injection is right most of the time. It is also wrong in a predictable way, and the wrong cases are common in real code. This week a SQL rule in our scanner flagged code that only ever ran with fixed values. Here is the code, what the rule saw, and what we changed.

The loop over a fixed list

for (const table of ["messages", "threads"]) {
  await tx.execute(`DELETE FROM ${table} WHERE account_id = ?`, [accountId]);
}

Only accountId comes from outside, and it is bound as a parameter. table is one of two literals. The rule now follows a loop variable back to a literal array in the same file. If the array holds only string literals, the variable is a constant.

The helper that returns a fixed fragment

function clearRole(table: string) {
  return `UPDATE ${table} SET in_inbox = 0 WHERE id = ?`;
}
await tx.exec(`${clearRole("messages")}`);

The helper is called only with literals. A typed parameter does not change that. The rule now checks the call sites: if every call passes a literal, the output is fixed.

The placeholder list

tx.query(`SELECT id FROM t WHERE id IN (${ids.map(() => "?").join(",") || "NULL"})`, ids);

The interpolated part is only question marks, and the values are bound. The rule now recognises placeholder lists, including an empty-list fallback, and row tuples such as (?, ?) for batched inserts.

The cursor clause

const cursorSql = cursor ? `WHERE latest < ? OR (latest = ? AND id > ?)` : "";

A fixed block of SQL is either added or left empty, and the values still go through bind parameters. The rule now treats that conditional as constant.

What did not change

The rule still flags what it exists for: a query built from a request value, a variable taken from req.query, or a table name passed in from outside. Each of those patterns has a vulnerable version that must keep firing and a fixed version that must stay quiet. A future edit that breaks either one fails the test suite.

Why this matters

A scanner that flags constants trains people to ignore it. The next alert gets skipped, and then a real injection gets skipped with it. Reducing noise is part of catching the bug.

If you want to see how a scanner handles your own repository, public repositories can be checked for free at zelsis.com.