The Phantom WHERE Clause

Medium

· Database

Incident

SEV-2

BUG-8804

Priority: High · reported by finance (wrong report sent to a client!)

Time to fix: 20 min

Failing checks on the shipped build

  • two conditions combine with AND

    only invoice #1 is paid AND in the EU: expected 1 but got 2

  • placeholders are numbered sequentially

    second condition must bind $2, got: SELECT * FROM invoices WHERE status = $1 OR region = $1: expected a truthy value but got false

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Inspired by

ORM refactors

The internal ORM of Ledgerline builds parameterised SQL. Since last week's refactor, filtered reports return rows that should have been excluded, and reusing a base query leaks conditions between reports.

The mock SQL engine and fixtures are locked — they faithfully implement AND/OR and $n placeholders. Fix queryBuilder.js.

Code map

// Tiny parameterised query builder for the invoices table.
function Query(conditions) {
  this.conditions = conditions;
}

Query.prototype.where = function (field, value) {
  this.conditions.push({ field: field, value: value });
  return new Query(this.conditions);
};

Query.prototype.build = function () {
  if (this.conditions.length === 0) {
    return { sql: "SELECT * FROM invoices", params: [] };
  }
  var parts = [];
  var params = [];
  for (var i = 0; i < this.conditions.length; i++) {
    parts.push(this.conditions[i].field + " = $1");
    params.push(this.conditions[i].value);
  }
  return {
    sql: "SELECT * FROM invoices WHERE " + parts.join(" OR "),
    params: params,
  };
};

exports.query = function () {
  return new Query([]);
};

Sign in to edit the file, run the tests and submit a fix

A free account — no card, no trial clock. You come straight back here.

The Phantom WHERE Clause — JavaScript Bug Hunt

The internal ORM of Ledgerline builds parameterised SQL. Since last week's refactor, filtered reports return rows that should have been excluded, and…

  • Language: JavaScript
  • Layer: Database
  • Difficulty: Medium
  • Modelled on: ORM refactors
  • Visible tests: a single condition filters correctly; two conditions combine with AND; placeholders are numbered sequentially
  • Reward: 50 XP for a complete fix

Incident

  • Severity: SEV-2 (priority: High)
  • Ticket: BUG-8804
  • Reported by: finance (wrong report sent to a client!)
  • Time to fix: 20 minutes, the target for a medium incident

Failing checks on the shipped build

  • two conditions combine with AND — only invoice #1 is paid AND in the EU: expected 1 but got 2
  • placeholders are numbered sequentially — second condition must bind $2, got: SELECT * FROM invoices WHERE status = $1 OR region = $1: expected a truthy value but got false

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Briefing

The internal ORM of Ledgerline builds parameterised SQL. Since last week's refactor, filtered reports return rows that should have been excluded, and reusing a base query leaks conditions between reports.

The mock SQL engine and fixtures are locked — they faithfully implement AND/OR and $n placeholders. Fix queryBuilder.js.

Bug report

BUG-8804 · Priority: High · Reported by: finance (wrong report sent to a client!)

  • .where("status", "paid").where("region", "EU") returns paid OR EU rows — the conditions must combine with AND.
  • Building two different queries from one shared base makes the second query inherit the first one's filters.
  • With two conditions both placeholders render as $1 — the engine then binds the wrong values.

Logs

[sql] SELECT * FROM invoices WHERE status = $1 OR region = $1
[sql] params = ["paid", "EU"]
[report] expected 1 row, got 5

The code as shipped

src/db/queryBuilder.js (editable)

// Tiny parameterised query builder for the invoices table.
function Query(conditions) {
  this.conditions = conditions;
}

Query.prototype.where = function (field, value) {
  this.conditions.push({ field: field, value: value });
  return new Query(this.conditions);
};

Query.prototype.build = function () {
  if (this.conditions.length === 0) {
    return { sql: "SELECT * FROM invoices", params: [] };
  }
  var parts = [];
  var params = [];
  for (var i = 0; i < this.conditions.length; i++) {
    parts.push(this.conditions[i].field + " = $1");
    params.push(this.conditions[i].value);
  }
  return {
    sql: "SELECT * FROM invoices WHERE " + parts.join(" OR "),
    params: params,
  };
};

exports.query = function () {
  return new Query([]);
};

Read-only context: src/db/engine.js, src/db/fixtures.js.

Open the hunt to start the incident clock, edit the files, run the visible tests and submit against the hidden ones, then write the root cause down. More JavaScript bug hunts.