Filtering, Sorting, and Search

The Pagination lesson settled how much of a collection to give. Which records to give, and in what order, stayed open. The clerk searches the catalog for “Aho’s books,” “editions after 1990,” “titles containing Network”; wants the result sorted by year or by author.

All of this travels in the query part and all of it turns into SQL. The conversion needs a design decision in two places: which column a client-supplied name corresponds to, and how a client-supplied value enters the query. Keeping these two questions apart is this lesson’s core distinction.

Separating Name from Value

A query parameter can carry two different kinds of information. In the request ?author=Aho, author is a name — it says which column to look at — and Aho is a value. The two cannot enter SQL by the same path.

The value never enters the query text; it travels separately as a bound variable. The engine parses the query text once and slots in the value afterward, so the value’s content cannot change the query’s structure. The Dynamic SQL Risks lesson in the Advanced SQL course spells out why this is mandatory: when values are written into query text through string concatenation, the characters inside a value become part of the query’s structure.

A name, however, cannot be a bound variable — a column name is the query’s structure, not its value. This is why names need a different mechanism: an allowlist. A client-supplied name is looked up in a fixed mapping on the server; if it is not in the mapping, the request is rejected. This way, only column names the server itself wrote enter the query text.

// filter-server.mjs — carries query parameters into SQL through an allowlist and bound variables
import { createServer } from "node:http";
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("library.db");
const respond = (response, status, data) => {
  response.writeHead(status, { "content-type": "application/json; charset=utf-8" });
  response.end(JSON.stringify(data));
};

// Allowlists: a name coming from the client is translated to a column name here.
const FILTERS = {
  author:  "author = ?",
  yearMin: "year >= ?",
  branch:  "branch = ?",
  search:  "title LIKE ?",
};
const SORTS = { year: "year", title: "title", author: "author" };

const server = createServer((request, response) => {
  const url = new URL(request.url, "http://127.0.0.1");
  if (url.pathname !== "/books") return respond(response, 404, { error: "path_not_found" });
  const s = url.searchParams;
  const size = Math.min(Number(s.get("size") ?? 3), 50);

  // Filters: only names in the allowlist turn into a condition.
  const conditions = [], values = [];
  for (const [name, clause] of Object.entries(FILTERS)) {
    const value = s.get(name);
    if (value === null) continue;
    conditions.push(clause);
    values.push(name === "search" ? `%${value}%` : value);   // value never enters the query text
  }

  // Sort: rejected if not in the allowlist, never falls back to a default.
  const sortName = s.get("sort") ?? "year";
  const column = SORTS[sortName];
  if (!column)
    return respond(response, 422, {
      error: "validation", field: "sort", allowed: Object.keys(SORTS),
    });

  // Cursor: loose mode carries only the sort column, strict mode also carries the tiebreaker.
  const cursor = s.get("cursor");
  const strict = s.get("mode") !== "loose";
  if (cursor !== null) {
    if (strict) {
      const [d, k] = cursor.split("|");
      conditions.push(`(${column}, isbn) > (?, ?)`);
      values.push(d, k);
    } else {
      conditions.push(`${column} > ?`);
      values.push(cursor);
    }
  }

  const where = conditions.length ? `WHERE ${conditions.join(" AND ")}` : "";
  const order = strict ? `${column}, isbn` : column;
  const rows = db.prepare(
    `SELECT isbn, title, author, year FROM book ${where} ORDER BY ${order} LIMIT ?`
  ).all(...values, size);

  const last = rows.at(-1);
  respond(response, 200, {
    data: rows.map((r) => `${r.year} ${r.isbn}`),
    pagination: {
      nextCursor: last ? (strict ? `${last[column]}|${last.isbn}` : String(last[column])) : null,
    },
  });
});

server.listen(8481, "127.0.0.1", () => console.log("filter server 127.0.0.1:8481"));

Notice how the query text gets built. Every piece entering the conditions array is a fixed string the server itself wrote; no character coming from the client reaches it. Everything the client sends lives in the values array and is handed to the query as a bound variable.

The catalog is expanded so sort ties become visible:

-- catalog.sql — an expanded catalog containing sort ties
DELETE FROM book;
INSERT INTO book (isbn, title, author, year, branch) VALUES
  ('K-01','Computer Networks','Tanenbaum',1978,'S-01'),
  ('K-02','Data Structures and Algorithms','Aho',1983,'S-01'),
  ('K-03','Compilers','Aho',1983,'S-02'),
  ('K-04','Operating Systems','Tanenbaum',1983,'S-02'),
  ('K-05','Shell Scripting',"O'Reilly",1986,'S-01'),
  ('K-06','Graph Algorithms','Sedgewick',1990,'S-01'),
  ('K-07','Numerical Methods','Press',1990,'S-02'),
  ('K-08','Network Programming','Stevens',1990,'S-01'),
  ('K-09','Formal Languages','Hopcroft',1995,'S-02');

The client that walks the pages is a separate file too; it does not build the cursor itself, it takes it from the response.

// walk.mjs — client that walks every page by following the cursor
// Usage: node walk.mjs <loose|strict>
const MODE = process.argv[2];
let cursor = null, page = 0;
const collected = [];

while (page < 10) {
  const url = new URL("http://127.0.0.1:8481/books");
  url.searchParams.set("mode", MODE);
  url.searchParams.set("sort", "year");
  if (cursor !== null) url.searchParams.set("cursor", cursor);

  const data = await (await fetch(url)).json();
  if (data.data.length === 0) break;
  page++;
  collected.push(...data.data);
  console.log(`${MODE.padEnd(7)} page ${page}: ${data.data.join("  ")}`);
  cursor = data.pagination.nextCursor;
}
console.log(`${MODE.padEnd(7)} total  : ${collected.length} rows\n`);

# The effect of the allowlist, bound variables, and sort ties on pagination.
rm -f library.db && sqlite3 library.db < schema.sql && sqlite3 library.db < catalog.sql
node filter-server.mjs & server=$!
sleep 0.4

get() { curl -sS -G "http://127.0.0.1:8481/books" "$@"; echo; }
echo "--- filter and allowlist ---"
printf 'author=Aho     : '; get --data-urlencode "author=Aho"
printf "author=O'Reilly: "; get --data-urlencode "author=O'Reilly"
printf 'search=Network : '; get --data-urlencode "search=Network"
printf 'sort=branch    : '; get --data-urlencode "sort=branch"

echo "--- pagination on a column with sort ties (catalog has 9 books) ---"
node walk.mjs loose
node walk.mjs strict

kill $server

filter server 127.0.0.1:8481
--- filter and allowlist ---
author=Aho     : {"data":["1983 K-02","1983 K-03"],"pagination":{"nextCursor":"1983|K-03"}}
author=O'Reilly: {"data":["1986 K-05"],"pagination":{"nextCursor":"1986|K-05"}}
search=Network : {"data":["1978 K-01","1990 K-08"],"pagination":{"nextCursor":"1990|K-08"}}
sort=branch    : {"error":"validation","field":"sort","allowed":["year","title","author"]}
--- pagination on a column with sort ties (catalog has 9 books) ---
loose   page 1: 1978 K-01  1983 K-02  1983 K-03
loose   page 2: 1986 K-05  1990 K-06  1990 K-07
loose   page 3: 1995 K-09
loose   total  : 7 rows

strict  page 1: 1978 K-01  1983 K-02  1983 K-03
strict  page 2: 1983 K-04  1986 K-05  1990 K-06
strict  page 3: 1990 K-07  1990 K-08  1995 K-09
strict  total  : 9 rows

Keeping the Value Out of the Query Text

The second line is the most concrete proof of this distinction. The value O'Reilly contains an apostrophe, and that is exactly the character that ends an SQL string literal. If the value had been written into the query text, the query would split at that point and the rest would be read as SQL. Carried as a bound variable, none of that happens: the value is only a value, it finds the record and returns it.

This is not a problem of “escaping” apostrophe-carrying names. An escape character looks like a solution too, but it needs a separate rule for every data type and dialect, and gets forgotten somewhere. With a bound variable there is nothing to forget; the query text stays fixed regardless of the client.

The search=Network filter in the third line carries the same principle one step further. Wildcard characters are added on the server — the client-supplied value Network is turned into %Network% — so the client cannot write a pattern, it can only supply the text to search for.

Rejecting What Is Outside the Allowlist

The fourth line returned a 422 to the sort=branch request and reported the allowed names in the response. Both decisions here are deliberate.

First, the request was rejected; it did not silently fall back to a default sort. Falling back to a default means the client never learns it sent a wrong name; the screen shows the wrong order and no one goes looking for why. The criterion from the Status Code Selection lesson applies here too: an unaccepted field value is reported with 422.

Second, the response lists the allowed names. This is a self-describing part of the contract; it steers the client toward fixing the request without going to the documentation.

The allowlist also keeps sorting’s cost under control. If sorting were allowed by any column, a request to sort by a column with no index would force a full table read. Only columns that have been decided to be supported — and indexed if needed — go on the list.

A Sort Tie Loses Records

The output’s last section loops back to the Pagination lesson. There are nine books in the catalog; loose mode showed seven of them. K-04 and K-08 never appeared on any page.

The reason is measurably plain. In loose mode, the cursor carries only the year. The first page ends at 1983 and the cursor becomes 1983; the second page arrives with the condition year > 1983, and the K-04 record sharing that same year falls outside it. The same thing repeats at 1990, and K-08 is lost. As long as the sort key is not unique, the criterion “after this value” always leaves out part of the ties.

In strict mode, the cursor carries two fields: the sort column and a unique identity. Both the condition and the sort now work over two fields. Because a definite order is established among records sharing the same year, “after this point” is well defined, and all nine of the nine records appear.

Two rules follow from this. Whatever the sort criterion, a unique field is appended to the end of it — this is what deterministic ordering requires. And the cursor carries the entire sort criterion: a single-field cursor can only describe a single-field sort.

Summary

• A query parameter carries two kinds of information: name and value. A name is translated to a column through an allowlist, a value travels as a bound variable; the two do not enter SQL by the same path.
• Only fixed pieces the server wrote enter the query text; no character from the client reaches the query’s structure. An apostrophe-carrying author name is found without incident because of this.
• Search patterns are built on the server; the client supplies the text to search for, not the wildcard.
• A sort request outside the allowlist is rejected with 422 and the allowed names are reported in the response; silently falling back to a default makes the error invisible.
• The allowlist also bounds sorting’s cost: only columns decided to be supported go on the list.
• If the sort key is not unique, cursor-based pagination skips part of the ties; in the measurement, seven of nine records appeared. Appending a unique field to the sort and the cursor removes this loss.

Next Step

Which records come back, and in what order, is settled. What is left is how much of each record comes back. While the catalog list screen shows only a book’s title and author, the server sends every field on every row; the loan history screen, meanwhile, fires off separate requests for information it cannot find in a single response. Both problems come from the same place: the server alone decides the representation’s level of detail. The next lesson builds the mechanism that lets the client choose fields and measures the difference in transferred bytes.