tasks/medium/sql_query_builder.py

"""tasks/medium/sql_query_builder.py — CWE-89: SQL Injection"""

TASK = {
    "id": "sql_query_builder",
    "difficulty": "medium",
    "cwe_targets": ["CWE-89", "CWE-20"],
    "problem_statement": """
Write a function `build_query(conn: sqlite3.Connection, user_input: str) -> list` that:
1. Queries the `users` table for rows where name matches user_input.
2. Uses PARAMETERISED queries — NEVER string-format user input into SQL.
3. Returns a list of matching rows (may be empty list).
4. Handles None and empty string inputs safely (return empty list).
5. Never raises exceptions on invalid input — always return a list.

The function name must be exactly: build_query

The users table schema:
  CREATE TABLE users(id INT, name TEXT, role TEXT, password TEXT)

CORRECT:   cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))
WRONG:     cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
WRONG:     cursor.execute("SELECT * FROM users WHERE name = '%s'" % user_input)

The behavioral harness spies on sqlite3.Cursor.execute at the C-extension level.
If the payload string appears inside the SQL string itself — you fail.
""",
    "starter_code": """
import sqlite3

def build_query(conn: sqlite3.Connection, user_input: str) -> list:
    # TODO: implement using parameterised queries
    # NEVER use f-strings or % formatting to build SQL
    cursor = conn.cursor()
    # cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))
    # return cursor.fetchall()
    pass
""",
    "test_cases": [
        {"input": ["alice", "SAFE"], "expected": None, "fn": "build_query"},  # Returns list
        {"input": ["nobody", "SAFE"], "expected": None, "fn": "build_query"},
        {"input": ["", "SAFE"], "expected": None, "fn": "build_query"},
    ],
    "naive_baseline": {"time_ms": 2, "memory_kb": 100},
    "perf_input": "alice",
}