AI-assisted engineering/Reviewing AI code

Fix the LIKE pattern in a user search

Review the AI's codeEasy15 min

Your task

Your app's user search returns every user whose name contains the text someone typed. The first version pasted that text straight into the SQL, so typing ' OR '1'='1 listed the whole table.

An assistant wrote build_search_query(name) to replace it. It is in the editor. It has a bug. Find it and fix it.

Input and output:

name: the text the user typed. It can be empty and can hold any character.
Return a list of two strings: the SQL text, then the value the database driver binds to the ?.

Rules the tests check:

The SQL text is always exactly SELECT id, email FROM users WHERE name LIKE ? ESCAPE '!'. The name never appears in it.
In a LIKE pattern, % matches any run of characters and _ matches any one character. ESCAPE '!' makes ! the escape character, so !% means a plain %.
The value is %, then the name with a ! put in front of every !, % and _, then %.
Every other character stays as it is, quotes included.

Examples:

build_search_query("ada") returns ["SELECT id, email FROM users WHERE name LIKE ? ESCAPE '!'", "%ada%"].
build_search_query("' OR '1'='1") returns the same SQL and the value "%' OR '1'='1%". A bound value cannot change the statement, so the quotes stay.
build_search_query("50%_off") returns the same SQL and the value "%50!%!_off%". Unescaped, the % and _ would match names the user never typed.

Then, in a sentence or two: how would you catch this kind of bug in review?

Examples

build_search_query(name: str) -> list[str]

Example 1

Inputbuild_search_query("ada")

Output["SELECT id, email FROM users WHERE name LIKE ? ESCAPE '!'", "%ada%"]

Example 2

Inputbuild_search_query("' OR '1'='1")

Output["SELECT id, email FROM users WHERE name LIKE ? ESCAPE '!'", "%' OR '1'='1%"]

Example 3

Inputbuild_search_query("50%_off")

Output["SELECT id, email FROM users WHERE name LIKE ? ESCAPE '!'", "%50!%!_off%"]