AI-assisted engineering/Reviewing AI code

Fix the sort handling in a search endpoint

Review the AI's codeEasy12 min

Your task

A Flask search endpoint built its SQL with an f-string, and a pentester dumped the users table through it. Binding the search text as a parameter fixes that part. But the ORDER BY column and direction are SQL identifiers and keywords, which cannot be bound, so they need a different defense.

An assistant wrote build_search_query(term, sort). It is in the editor. It has a bug. Find it and fix it.

Input and output:

term: the search text the user typed.
sort: the sort the user asked for, such as title or id:desc.
Return a list of two strings: the SQL text, then the value bound to the ?.

Rules the tests check:

The SQL text is always SELECT id, title FROM posts WHERE title LIKE ? ORDER BY <column> <direction>.
The bound value is %, then term unchanged, then %.
To read sort: trim whitespace from both ends, lowercase it and split it on :. It must have 1 or 2 parts. The column is the first part, trimmed. The direction is the second part, trimmed, or asc when there is no second part.
Use that column and direction only when the column is one of created_at, title and id, and the direction is asc or desc. Write the direction in capitals: ASC or DESC.
In every other case use created_at ASC. Both fall back together, even when only one of the two was wrong.

Examples

build_search_query(term: str, sort: str) -> list[str]

Example 1

Inputbuild_search_query("django", "title")

Output["SELECT id, title FROM posts WHERE title LIKE ? ORDER BY title ASC", "%django%"]

Example 2

Inputbuild_search_query("a%' UNION SELECT password, email FROM users -- ", "created_at")

Output

["SELECT id, title FROM posts WHERE title LIKE ? ORDER BY created_at ASC", "%a%' UNION SELECT password, email FROM users -- %"]

Example 3

Inputbuild_search_query("ai", "id:desc")

Output["SELECT id, title FROM posts WHERE title LIKE ? ORDER BY id DESC", "%ai%"]