Every value that has reached SQL so far was generated by your own code, three lines before it was used. From this lesson on, values come from filters — dates a user picked, items a user checked — and the rules change completely. The result is a single filter clause that every query in the course shares, and a variable-length IN list built without ever touching the values. Checkpoint: engine-05.

One clause, two shapes

Both filters are optional in different ways. A date range always applies. An item list applies only when something is selected, and “nothing selected” means “all items” rather than “no rows” — a convention worth stating once and holding to:

// Shared filter clause with bound parameters; an empty fruits array means all
// items. Values are bound via prepared statements — never interpolated.
function filterClause(fruits: string[]): string {
    const dateFilter = `date BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)`;
    if (fruits.length === 0) return dateFilter;
    const placeholders = fruits.map(() => '?').join(',');
    return `${dateFilter} AND item IN (${placeholders})`;
}

Look at what is being built and what is not. The function returns SQL text containing ? marks. It never sees a date, never sees an item name, and could not leak one if it tried — fruits.map(() => '?') reads the array’s length and discards its contents.

That is the whole trick to a safe variable-length IN clause. You cannot bind a list to a single parameter, so people reach for item IN ('${items.join("','")}') and ship an injection. Generating one placeholder per element and binding them positionally gives you the same query with none of the exposure.

CAST(? AS DATE) is there because a bound parameter arrives as a string and the column is a DATE. Making the conversion explicit means the comparison happens in date space rather than string space, which is the kind of thing that works by accident with ISO dates and stops working the moment a format changes.

Compile once, execute with values

A prepared statement separates the query from its data. The engine parses and plans the SQL — with the placeholders as holes — and then you execute it with values that were never part of the text and can never be read as SQL:

async function runPrepared(sql: string, params: (string | number)[]) {
    const stmt = await conn.prepare(sql);
    try {
        const res = await stmt.query(...params);
        return res.toArray();
    } finally {
        await stmt.close();
    }
}

The finally is not politeness. A prepared statement holds resources in the engine until it is closed, and this helper is about to be called on every keystroke of a filter for the rest of the course. Leaking one per query is a slow leak that shows up as a mystery an hour later, and putting the close in finally means an error in the query cannot skip it.

Every query function from here on is a call to this helper. The first:

async function countRows(startISO: string, endISO: string, fruits: string[]): Promise<number> {
    const rows = await runPrepared(
        `SELECT COUNT(*) AS count FROM sales WHERE ${filterClause(fruits)}`,
        [startISO, endISO, ...fruits],
    );
    return Number(rows[0].count);
}

The parameter array is where the two halves meet: two dates, then the items spread in the same order filterClause generated their placeholders. Positional binding means that order is load-bearing. If you ever add a filter, it goes into the clause and the array at matching positions, or you will bind an item name to a date.

Number(rows[0].count) is not superstition either — COUNT(*) comes back as a BigInt, and a BigInt will not survive the structured clone into your page as a number. This is the first hint of a problem lesson 6 has to solve properly.

Predict the answer before you run it

Wire two calls into the page, one unfiltered and one narrow:

const all = await db.countRows(DATE_MIN, DATE_MAX, []);
log(`Rows in the full range: ${all.toLocaleString()}.`);

const some = await db.countRows('2025-03-01', '2025-03-31', ['Apples', 'Cherries']);
log(`Apples and Cherries in March: ${some.toLocaleString()}.`);

Now do the arithmetic before you look. Twenty thousand rows, uniformly spread over eight items and a hundred and eighty days. Two items is a quarter of them. March is thirty-one days, about a sixth of the range. A quarter of a sixth of twenty thousand is roughly 860.

The page will say something within a few percent of that. This habit — predict, then run — is the only reliable way to catch a query that returns a plausible number for the wrong reason. A filter that silently ignores its item list still returns a number, and that number looks fine until you have a prediction to compare it against.

Build it

File Action What goes in it
src/types.ts modify Add countRows to DbWorkerApi, with a note that an empty array means all items
src/db-worker.ts modify filterClause, runPrepared, countRows; expose it
src/index.ts modify Log an unfiltered count and a two-item March count

Done when: the page reports 20,000 rows for the full range and a March two-item count near 860, and no value passed from the page appears anywhere in a SQL string.

Answer key: engine-04...engine-05.

Challenge: attempt the injection

Call countRows with an item name of Apples') OR 1=1 --. With prepared statements the count comes back as zero, because there is no item with that name — the string was compared, not executed. Now write a temporary second version of filterClause that interpolates the names directly, run the same input through it, and watch the count jump to the unfiltered total. Delete it afterwards. The gap between those two numbers is the entire argument for this lesson.

Next lesson: pages of rows, a sort you cannot bind, and the Arrow values that arrive in a shape you did not ask for.