How to Patch a SQL Injection Query in a Flask Search Endpoint

How the injection query works

The vulnerable endpoint builds its SQL by dropping the raw query string straight into the statement with an f-string:

@app.route("/search")
def search():
    q = request.args.get("q", "")
    cur = _conn.cursor()
    # BUG: SQL injection via string concatenation.
    cur.execute(f"SELECT id, name FROM products WHERE name LIKE '%{q}%'")
    rows = cur.fetchall()
    return jsonify([{"id": r[0], "name": r[1]} for r in rows])

A normal request, q=apple, produces the query you expect and matches one row. But the attacker controls q, so they send q=' OR '1'='1 and the SQL the database actually parses becomes:

SELECT id, name FROM products WHERE name LIKE '%' OR '1'='1%'

'1'='1' is always true, so the WHERE filter is defeated and every row in products comes back. On a real users or orders table that is a full data dump. More advanced payloads chain UNION SELECT to read other tables or call functions that exfiltrate data out of band. SQL injection has sat at or near the top of the OWASP Top Ten since 2003 precisely because this pattern is so easy to write by accident.

Patch: bind the value with a parameterized query

Never interpolate user input into the SQL text. Pass it as a separate argument so the psycopg2 driver binds it as a value:

@app.route("/search")
def search():
    q = request.args.get("q", "")
    cur = _conn.cursor()
    cur.execute(
        "SELECT id, name FROM products WHERE name LIKE %s",
        (f"%{q}%",),   # note the trailing comma - this is a tuple
    )
    rows = cur.fetchall()
    return jsonify([{"id": r[0], "name": r[1]} for r in rows])

Two things matter here. First, %s is psycopg2's placeholder, not Python's % string operator - the driver sends the SQL template and the value to PostgreSQL as separate messages, and the engine never parses the value as SQL. Second, the LIKE wildcards now wrap the value (f"%{q}%"), not the query text, so partial-match search still works. The trailing comma is easy to miss: (x) is just x, but (x,) is a one-element tuple, which is what execute() expects for its parameters.

With the patch in place, ' OR '1'='1 becomes the literal search string %' OR '1'='1%, which matches zero products.

Verify the patch

Restart the app and hit it with both a real query and the injection payload:

bash /workspace/run.sh
# Real search still works - returns 1 row
curl 'http://localhost:5001/search?q=apple'
# Injection payload - returns [] now, not all 3 rows
curl --get --data-urlencode "q=' OR '1'='1" http://localhost:5001/search

A clean patch returns exactly one row for apple and an empty list for the payload. In production you would also lock this in with a linter rule (Bandit or Semgrep flags f-string SQL), a least-privilege database role with no DROP grant, and an ORM that makes the parameterized path the default.