Benchmark · edition 1 · finding 08

ORDER BY identifier

A column name cannot be a bound parameter. The only correct defence is an allow-list.

app/finding_b08.py · 11 attack cases · 11 benign inputs

Paste the patch back

Score it.

Copy the module and prompt below to try your fixer. You can prepare a patch now; submitting it for a score will be available when the scoring service launches.

The module

What the fixer gets.

Three steps

01Copy the module and give it to the fixer with the prompt line, exactly as written, and nothing else.

02Paste the patch it returns below, as a unified diff or as the whole patched file.

03Name the tool and its version. That is what makes the result citable.

Taking this to a fixer? so you know to come back with the patch.

Prompt · the only line the fixer is given

Semgrep reports a SQL injection vulnerability at app/finding_b08.py:42. Example payload: (SELECT pw_hash FROM staff LIMIT 1). Fix it.

app/finding_b08.py · unpatched

"""Catalogue listing for the lending library, sorted by a chosen column.

Run it with

    python finding_b08.py

and query it at  /catalog?sort=author  or  /catalog?sort=-title  for descending.
"""

import os
import sqlite3

from flask import Flask, jsonify, request

DB_PATH = os.path.join(os.path.dirname(__file__), "library.db")

app = Flask(__name__)


def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


@app.route("/catalog")
def catalog():
    """List books, featuring and sorting by a chosen column.

    "sort=author" sorts ascending, "sort=-author" descending.
    e.g. /catalog?sort=-title
    """
    spec = request.args.get("sort", "title")
    descending = spec.startswith("-")
    column = spec[1:] if descending else spec
    direction = "DESC" if descending else "ASC"

    conn = get_connection()
    cur = conn.cursor()

    query = f"SELECT id, title, {column} AS featured FROM books ORDER BY {column} {direction}"
    rows = cur.execute(query).fetchall()
    conn.close()

    results = [dict(row) for row in rows]
    return jsonify({"sort": spec, "count": len(results), "results": results})


@app.route("/health")
def health():
    return jsonify({"status": "ok"})


if __name__ == "__main__":
    if not os.path.exists(DB_PATH):
        import seed

        seed.build()
    app.run(host="127.0.0.1", port=5208, debug=False)

Held-out attacks · by id and class · 11 fired after the patch

uniona01, a02, a03, a04, a05, a06, a07, a08

schema-disclosurea09, a10, a11