Skip to the code

Sort and search teas without SQL injection

The tea list at /teas searches by ?q= and sorts by ?sort=, and reads the teas with sql from @/lib/db, a tagged template like the ones many Postgres clients give you. Each ${value} in it is sent to the database as a parameter, apart from the SQL text, which shows $1, $2 in its place, so a value can never change what the query does. raw(text) is the exception: it writes the text into the SQL itself, which you need for what can't be a parameter, like the column in ORDER BY.

The starter builds the whole query as one string from the URL, so /teas?q=' OR ''=' breaks it and /teas?sort=name; DROP TABLE teas runs whatever the visitor wrote. Rewrite app/teas/page.tsx.

Select slug, name, price_cents FROM teas. When there's a search, add WHERE name ILIKE <pattern>, with the pattern sent as a parameter: %, the text of q trimmed, and %. An empty search, or a q that isn't a string, means no WHERE. Then ORDER BY one of four orders, and only these: name (the default) is name ASC, price is price_cents ASC, rating is avg_rating DESC, and newest is created_at DESC. Any other sort, a repeated one, or none sorts by name. Only these four pieces of your own SQL may go through raw().

Show an <h1> with Teas, then a list with an item for each tea like Green tea, $4.00, or, when nothing matches, a paragraph with No teas match your search. instead of the list. queries in @/lib/db records every query the database received, its SQL text and its values.

/teas?q=tea&sort=price
→ sql`SELECT slug, name, price_cents FROM teas WHERE name ILIKE ${"%tea%"} ORDER BY ${raw("price_cents ASC")}`
→ text: SELECT slug, name, price_cents FROM teas WHERE name ILIKE $1 ORDER BY price_cents ASC
  values: ["%tea%"]
→ Mint tea, $3.50 · Green tea, $4.00 · Oolong tea, $5.50

The app's other files

lib/db.ts, read-only. Import it as @/lib/db.

export type TeaRow = { slug: string; name: string; price_cents: number; avg_rating: number; created_at: string };
 
const teas: TeaRow[] = [
  { slug: "green", name: "Green tea", price_cents: 400, avg_rating: 4.6, created_at: "2024-03-02" },
  { slug: "mint", name: "Mint tea", price_cents: 350, avg_rating: 4.2, created_at: "2024-06-18" },
  { slug: "oolong", name: "Oolong tea", price_cents: 550, avg_rating: 4.8, created_at: "2023-11-20" },
  { slug: "lapsang", name: "Lapsang souchong", price_cents: 600, avg_rating: 3.9, created_at: "2025-01-09" },
  { slug: "rooibos", name: "Rooibos", price_cents: 300, avg_rating: 4.4, created_at: "2024-09-27" },
];
const COLUMNS = ["slug", "name", "price_cents", "avg_rating", "created_at"];
 
/** Every query the database received: the SQL text, and the values sent apart from it. */
export const queries: { text: string; values: unknown[] }[] = [];
 
const RAW = Symbol("raw SQL");
export type Raw = { [RAW]: string };
 
/** SQL written into the query as it is, not as a value. Only for SQL your own code wrote. */
export function raw(text: string): Raw {
  return { [RAW]: text };
}
 
/**
 * Runs a query written as a tagged template: sql`SELECT ... WHERE name ILIKE ${pattern}`.
 * Each ${value} becomes a placeholder, $1, $2, …, and its value is sent to the database apart
 * from the SQL, so it can never change what the query does. A raw() goes into the SQL itself.
 */
export async function sql(strings: TemplateStringsArray, ...parts: unknown[]): Promise<TeaRow[]> {
  let text = strings[0];
  const values: unknown[] = [];
  parts.forEach((part, index) => {
    if (typeof part === "object" && part !== null && RAW in part) text += String((part as Raw)[RAW]);
    else {
      values.push(part);
      text += "$" + values.length;
    }
    text += strings[index + 1];
  });
  queries.push({ text, values });
  return run(text, values);
}
 
// What the stand-in understands: SELECT <columns> FROM teas [WHERE name ILIKE <pattern>]
// [ORDER BY <column> [ASC|DESC], ...] [LIMIT <n>], as Postgres would run it.
function run(text: string, values: unknown[]): TeaRow[] {
  const query = text.replace(/\s+/g, " ").trim().replace(/;$/, "");
  const shape = /^SELECT (.+?) FROM teas(?: WHERE name ILIKE (\$\d+|'(?:[^']|'')*'))?(?: ORDER BY (.+?))?(?: LIMIT (\d+))?$/i.exec(query);
  if (!shape) throw new Error("syntax error in: " + text);
  const [, selected, pattern, orderBy, limit] = shape;
  for (const column of selected.split(",").map((item) => item.trim())) {
    if (column !== "*" && !COLUMNS.includes(column)) throw new Error('column "' + column + '" does not exist');
  }
 
  let rows = teas.map((row) => ({ ...row }));
  if (pattern) {
    const like = pattern.startsWith("$") ? values[Number(pattern.slice(1)) - 1] : pattern.slice(1, -1).replace(/''/g, "'");
    if (typeof like !== "string") throw new Error("ILIKE needs text");
    const regex = new RegExp("^" + like.replace(/[.*+?^${}()|[\]\\]/g, "\\$&").replace(/%/g, ".*").replace(/_/g, ".") + "$", "is");
    rows = rows.filter((row) => regex.test(row.name));
  }
  if (orderBy) {
    const keys = orderBy.split(",").map((item) => {
      const key = /^(\S+)(?: (ASC|DESC))?$/i.exec(item.trim());
      if (!key) throw new Error("syntax error in: " + text);
      // A placeholder here is a constant, the same for every row: it doesn't sort anything.
      if (/^\$\d+$/.test(key[1])) return null;
      if (!COLUMNS.includes(key[1])) throw new Error('column "' + key[1] + '" does not exist');
      return { column: key[1] as keyof TeaRow, direction: (key[2] ?? "ASC").toUpperCase() === "DESC" ? -1 : 1 };
    });
    rows.sort((a, b) => {
      for (const key of keys) {
        if (!key || a[key.column] === b[key.column]) continue;
        return (a[key.column] < b[key.column] ? -1 : 1) * key.direction;
      }
      return 0;
    });
  }
  return limit ? rows.slice(0, Number(limit)) : rows;
}

What the tests check

  1. lists every tea by name by default, in one query
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    await open("");
    expect(screen.getByRole("heading", { level: 1, name: "Teas" })).toBeInTheDocument();
    expect(screen.getAllByRole("listitem").map((item) => item.textContent)).toEqual([
      "Green tea, $4.00",
      "Lapsang souchong, $6.00",
      "Mint tea, $3.50",
      "Oolong tea, $5.50",
      "Rooibos, $3.00",
    ]);
    expect(queries.length).toBe(1);
    expect(queries[0].values).toEqual([]);
    
  2. sorts by price, rating, and newest
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    const expected = {
      price: ["Rooibos", "Mint tea", "Green tea", "Oolong tea", "Lapsang souchong"],
      rating: ["Oolong tea", "Green tea", "Rooibos", "Mint tea", "Lapsang souchong"],
      newest: ["Lapsang souchong", "Rooibos", "Mint tea", "Green tea", "Oolong tea"],
      name: byName,
    };
    for (const [sort, teas] of Object.entries(expected)) {
      cleanup();
      await open("sort=" + sort);
      expect(names()).toEqual(teas);
    }
    
  3. any other sort uses name, and never reaches the SQL
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    for (const sort of ["price_cents", "PRICE", "name; DROP TABLE teas", "avg_rating DESC, (SELECT 1)", "constructor", "__proto__", "toString", ""]) {
      cleanup();
      await open("sort=" + encodeURIComponent(sort));
      expect(names()).toEqual(byName);
    }
    cleanup();
    await renderServer(<Page searchParams={params({ sort: ["price", "rating"] })} />);
    expect(names()).toEqual(byName);
    for (const query of queries) expect(query.text).not.toMatch(/DROP|SELECT 1|PRICE|constructor|__proto__|toString|function/);
    
  4. searches by name, with the text sent as a parameter
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    await open("q=" + encodeURIComponent("  OOLONG "));
    expect(names()).toEqual(["Oolong tea"]);
    expect(queries.length).toBe(1);
    expect(queries[0].values).toEqual(["%OOLONG%"]);
    expect(queries[0].text).not.toMatch(/oolong/i);
    cleanup();
    await open("q=" + encodeURIComponent("   "));
    expect(names()).toEqual(byName);
    expect(queries[1].values).toEqual([]);
    
  5. quotes and SQL in a search are only text to look for
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    for (const q of ["' OR ''='", "x%' OR 1=1 --", "O'Brien"]) {
      cleanup();
      await open("q=" + encodeURIComponent(q));
      expect(screen.getByText("No teas match your search.")).toBeInTheDocument();
      expect(screen.queryAllByRole("listitem")).toEqual([]);
    }
    expect(queries.map((query) => query.values)).toEqual([["%' OR ''='%"], ["%x%' OR 1=1 --%"], ["%O'Brien%"]]);
    for (const query of queries) expect(query.text).not.toContain("'");
    
  6. searches and sorts together
    const open = (search) => renderServer(<Page searchParams={params(Object.fromEntries(new URLSearchParams(search)))} />);
    const names = () => screen.queryAllByRole("listitem").map((item) => item.textContent.split(",")[0]);
    const byName = ["Green tea", "Lapsang souchong", "Mint tea", "Oolong tea", "Rooibos"];
    await open("q=tea&sort=price");
    expect(names()).toEqual(["Mint tea", "Green tea", "Oolong tea"]);
    cleanup();
    await open("q=tea&sort=rating");
    expect(names()).toEqual(["Oolong tea", "Green tea", "Mint tea"]);
    expect(queries.map((query) => query.values)).toEqual([["%tea%"], ["%tea%"]]);
    

Learn more

app/teas/page.tsxSaved in this browser

Run tests runs every test on your code, here in your browser. The problem is solved when they all pass.

Ctrl + Enter runs the tests (⌘ + Enter on a Mac).