AI-assisted engineering/Reviewing AI code

Fix the filter query builder for a user lookup

Review the AI's codeEasy10 min

Your task

An AI wrote a user lookup that pasted the email query parameter into the SQL text, so ' OR '1'='1 returned every user. The new version accepts several filters from the query string. It must keep everything the caller sends out of the SQL text, and pass values to the database driver as bound parameters.

An assistant wrote build_user_lookup_query(filters). It is in the editor. It has a bug. Find it and fix it.

Input and output:

filters: the caller's filters, in order, each written column=value.
Return a list of strings: first the SQL text, then the bound values in placeholder order ($1, $2 and so on).

Rules the tests check:

Split each filter at its first =. Skip a filter that has no =.
The column is the part before the =, trimmed and lowercased. Only email, name and status are allowed. Skip any other filter.
The value is the part after the =, exactly as given. Never quote, escape, trim or change it.
The SQL text is SELECT id, name FROM users. When any filter is kept, add WHERE and the kept filters as column = $n, joined by AND, where n counts from 1 in the order they were kept. A column kept twice gets two clauses.
With no kept filters, the SQL text has no WHERE.

Examples

build_user_lookup_query(filters: list[str]) -> list[str]

Example 1

Inputbuild_user_lookup_query(["email=alice@example.com"])

Output["SELECT id, name FROM users WHERE email = $1", "alice@example.com"]

Example 2

Inputbuild_user_lookup_query(["email=' OR '1'='1", "status=active"])

Output["SELECT id, name FROM users WHERE email = $1 AND status = $2", "' OR '1'='1", "active"]

Example 3

Inputbuild_user_lookup_query([])

Output["SELECT id, name FROM users"]