Pagination, filtering and sorting

?limit=-5 looks like an obviously broken request, the kind you'd expect any API to reject without a second thought. In SQLite, it's worse than broken: it's a request for everything. That's the trap this lesson is built around, and Linkstash's fix is four lines that are worth understanding completely, not just copying.

Why a negative LIMIT is dangerous, specifically

SQLite's LIMIT clause has an unusual rule: LIMIT -1 (or any negative value) means "no limit at all," not "zero rows" and not an error. It's documented behavior, not a bug, but it means a pagination parameter that arrives as -5 doesn't fail loudly or clamp itself, it silently turns a paginated query into an unbounded one. Picture a links table with two million rows and a client (malicious or just buggy) sending ?limit=-1. Without a guard, that query returns all two million rows in one response, whatever that does to your server's memory and your database's load.

The fix: intParam

/** Query-string numbers are strings from an untrusted client, so every one is
 *  parsed, rejected if it is not a whole number, and clamped into range. A
 *  negative LIMIT is the dangerous case: SQLite reads it as "no limit", which
 *  turns a pagination bug into an unbounded result set. */
function intParam(raw: unknown, fallback: number, min: number, max: number): number {
  const n = Number(raw);
  if (!Number.isInteger(n)) return fallback;
  return Math.min(Math.max(n, min), max);
}

Four lines, three jobs. Number(raw) converts whatever arrived (query strings are always strings, or undefined if the parameter was never sent) into a number, which produces NaN for garbage input like "abc". Number.isInteger(n) catches both NaN and any non-whole value, 3.7 included, falling back to the caller's default rather than trying to round or reject with an error. Math.min(Math.max(n, min), max) is the clamp: floor it at min, ceiling it at max, in one expression. A negative number never survives past that line.

Here's where it's actually called, in GET /links:

app.get("/links", requireAuth, (req, res) => {
  const limit = intParam(req.query.limit, 20, 1, 100);
  const offset = intParam(req.query.offset, 0, 0, Number.MAX_SAFE_INTEGER);
  const q = typeof req.query.q === "string" ? req.query.q : undefined;
  res.json({ data: listLinks(db, req.userId!, { limit, offset, q }), limit, offset });
});

limit defaults to 20 and is clamped between 1 and 100, so ?limit=-5 becomes 1, and ?limit=1000000 becomes 100, not "everything." offset defaults to 0 and has a floor of 0 but no meaningful ceiling (Number.MAX_SAFE_INTEGER), because there's nothing dangerous about asking to skip past the end of the results, you just get an empty page back.

The response echoes limit and offset back in the body, { data, limit, offset }, which matters for a subtle reason: it tells the client what actually got applied, not just what it asked for. A client that requested limit=-5 and reads limit: 1 back from the response knows immediately its request got clamped, instead of silently receiving one row and wondering why.

Proof, not just claims

The test suite checks every edge this function is meant to cover, not just the happy path:

// The dangerous case: a negative limit must not become "no limit" in SQLite.
const neg = await request(app).get("/links?limit=-5").set("Authorization", `Bearer ${token}`);
expect(neg.status).toBe(200);
expect(neg.body.limit).toBe(1);
expect(neg.body.data).toHaveLength(1);
 
// Above the cap clamps down to 100.
expect((await request(app).get("/links?limit=1000000").set("Authorization", `Bearer ${token}`)).body.limit).toBe(100);
 
// Garbage falls back to the documented defaults.
const junk = await request(app).get("/links?limit=abc&offset=xyz").set("Authorization", `Bearer ${token}`);
expect(junk.body.limit).toBe(20);
expect(junk.body.offset).toBe(0);
 
// A negative offset must not page backwards.
expect((await request(app).get("/links?offset=-10").set("Authorization", `Bearer ${token}`)).body.offset).toBe(0);

Four distinct inputs, four distinct expected outcomes: negative clamps up to the floor, oversized clamps down to the ceiling, garbage falls back to the default, and a negative offset clamps to zero instead of paging backwards into nonsense. That's what "handling untrusted input" looks like as code, not a vague intention but a specific, tested value for every input shape a client could actually send.

Filtering: q, and staying safe doing it

q is Linkstash's one filter, a case-insensitive substring match on the link title, implemented back in listLinks:

export function listLinks(db: Database, userId: number, opts: ListOptions = {}): Link[] {
  const { limit = 20, offset = 0, q } = opts;
  const where = q ? "WHERE userId = ? AND title LIKE ?" : "WHERE userId = ?";
  const params: (string | number)[] = q ? [userId, `%${q}%`] : [userId];
  return db
    .prepare(`SELECT id, userId, url, title, createdAt FROM links ${where} ORDER BY id DESC LIMIT ? OFFSET ?`)
    .all(...params, limit, offset) as Link[];
}

%${q}% wraps the search term in SQL wildcards for a substring match, and it's still bound as a ? parameter, never spliced into the query text, the same discipline covered in connecting a database. A search for ' OR 1=1 -- gets treated as a literal string to search for, matching nothing, not as SQL.

Sorting: fixed, not client-controlled

Notice there's no ?sort= parameter anywhere in this API. ORDER BY id DESC is hardcoded, always newest-first, and that's a real design choice worth naming rather than an oversight. Auto-incrementing integer ids and insertion order track each other exactly in SQLite, so sorting by id DESC is both correct and cheap (the index from the last lesson, (userId, id DESC), was built for exactly this). Letting a client pass an arbitrary column name to sort by is a feature some APIs offer, but it comes with its own validation burden, you'd need to whitelist which columns are sortable or risk a client requesting ORDER BY passwordHash, and Linkstash simply doesn't need that flexibility for what it does. Not every feature you could add belongs in the API you're actually building.

Everything up to here has been request/response, one request in, one response out. The last two lessons step outside that pattern: first a connection that stays open, then the finished app end to end. Next: realtime with WebSockets.