Separate SQL structure from data with sqlite3

A product search builds its SQL by formatting the shopping category and search term straight into the query string, so a crafted term can read other tables or return every row. Repair it to use sqlite3 parameters, while keeping the legitimate search working.

Requires Node.js 22 or newer, which runs the tests, and Python 3.12 or newer, which runs your code (the command python3, or the program named in the PYTHON environment variable). No package installation or server needed. To work in a container instead, open the folder with VS Code Dev Containers or GitHub Codespaces: .devcontainer/devcontainer.json uses the Dev Containers Node.js 24 image, pinned by digest.
1. Unzip all files into one folder.
2. In that folder, run: node --test exercise.test.mjs
3. Edit search.py, then run the same command again.

The starter deliberately fails some tests. A successful repair passes every case, including cases that catch common wrong fixes. The browser runs this same grader with your code in Pyodide (CPython compiled to WebAssembly); locally it runs your code with python3. If you completed the optional first stage in the browser, Download my work adds your input as stage1-input.json and the tests include it. Only run code you wrote or reviewed.

1. Reproduce the injection
Run the starter. The term "\' OR 1=1 --" returns inactive and other-category rows, and a UNION term reads the secrets table, because the term is concatenated into the SQL.

2. Bind the values
Write one SQL string with ? for the category and for the LIKE pattern, and pass (category, pattern) to execute. sqlite3 then sends the values separately from the statement.

3. Build the LIKE pattern safely
Escape %, _ and the escape character in term, wrap it in %...%, and add ESCAPE to the LIKE clause, so a term containing % matches a literal percent instead of everything.

4. Keep the legitimate search
An ordinary term still finds matching active products in the category, ordered by id. Keep active = 1 in the query; returning inactive or other-category rows is not a fix.

SUPPLIED CONTRACT
search_products(connection, category, term) runs against a sqlite3 connection the lab supplies, and returns a list of (id, name, price) tuples for active products in the given category whose name contains term, ordered by id. The products table is (id, name, category, price, active); there is also a secrets table the query must never reach. Build the SQL once with ? placeholders and pass the values as the second argument to execute; let sqlite3 bind them. Match the term as a substring with LIKE, escaping the LIKE wildcards % and _ so a term containing them matches literally; filter to active = 1. Do not format category or term into the SQL text, and do not turn off active filtering.

REFERENCE
reference.py contains an explained solution: With parameters, the term "' OR 1=1 --" is just a string to match, so it finds no product named that and cannot change the WHERE clause; a UNION SELECT term likewise matches nothing and never reaches the secrets table. An ordinary term such as "cable" returns the active cables in the category, ordered by id. A term containing a literal % matches a percent sign rather than every row, because the pattern escapes it with ESCAPE. The category is bound too, so a category of "' OR '1'='1" selects nothing. active = 1 stays in the query, so inactive products never appear.

SCOPE
The lab uses an in-memory sqlite3 database the grader fills, and tests one query. Parameters bind values, not identifiers: a dynamic table or column name still needs an allowlist, and ORDER BY on a user-chosen column needs one too. Real applications also apply least-privilege database accounts and per-tenant authorization; parameterization stops the injection, not the authorization checks.

NEXT TASK
Where does your code build SQL (or any query language) by concatenation or an f-string, and could each value move to a bound parameter?

SOURCE
https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries
