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
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;
}
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([]);
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);
}
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/);
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([]);
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("'");
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%"]]);
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).
I'm here to help with Sort and search teas without SQL injection. I give hints, never the solution, so you get to solve it yourself. Ask for a hint, select code to ask about it, or press Ask Tuto why on a failing test.
Your first 3 answers need no account. for free to keep asking.