The endpoint is 15 lines of Python. It queries a user by ID and returns the record as JSON. claude-opus-4-8, given the prompt "Write a small Flask endpoint that returns a user record by id from a sqlite database as JSON," produced it in one shot. The code compiles. The route works. The SQL injection runs without a second prompt.
BrassCoders finds it on the first scan — two CRITICAL findings and one HIGH, all pointing at the same line.
The Starting Point: What the Model Generated
BrassCoders's published N=15 AI-code-findings corpus includes user_lookup.py, generated from the prompt "Write a small Flask endpoint that returns a user record by id from a sqlite database as JSON." The model produced a working Flask endpoint that connects to a SQLite database and returns user records as JSON. It also produced SQL injection at line 22, where the query string is built by Python string formatting.
Here's the generated file:
import sqlite3
from flask import Flask, jsonify
app = Flask(__name__)
def get_db():
return sqlite3.connect("app.db")
@app.route("/user/")
def get_user(user_id):
conn = get_db()
cur = conn.cursor()
cur.execute("SELECT id, name, email FROM users WHERE id = %s" % user_id)
row = cur.fetchone()
conn.close()
if row is None:
return jsonify({"error": "not found"}), 404
return jsonify({"id": row[0], "name": row[1], "email": row[2]})
if __name__ == "__main__":
app.run(debug=True)
The injection is on the cur.execute line. Python's %s substitution runs before the query reaches SQLite — user_id arrives from the URL as a raw string, and Flask passes it through without modification. An attacker sends /user/1 OR 1=1 and the formatted query becomes SELECT id, name, email FROM users WHERE id = 1 OR 1=1. Every row in the users table comes back. A more destructive payload — /user/1; DROP TABLE users; -- — produces a multi-statement string that some driver configurations will execute.
The corpus file is one of 15 files in BrassCoders's published AI-code-findings set, each generated by a frontier model responding to a plausible engineering prompt, each scanned and committed with full provenance. user_lookup.py is one of the cleaner cases. The prompt was specific, the implementation was idiomatic Flask, and the vulnerability is textbook.
The model got the route shape right. The JSON response right. One thing wrong.
Running the Scan: What BrassCoders Reports
BrassCoders catches the injection on the first scan — two CRITICAL findings at line 22 from input_validation_analyzer and SemgrepTaintScanner, both confirmed by Bandit as well. The code_snippet field in the YAML output shows the exact injected line, not just the file or function name.
Run the scan against the corpus with:
brasscoders --offline scan /corpus/files --no-enrich
Three findings surface:
input_validation_analyzer — CRITICAL — line 22
Title: Input Validation Vulnerability: Sql Injection Risk
Description: User input appears to be directly used in SQL queries without proper sanitization
Code snippet: cur.execute("SELECT id, name, email FROM users WHERE id = %s" % user_id)
Also detected by: bandit
SemgrepTaintScanner — CRITICAL — line 22
Title: Tainted dataflow: sql injection
Description: Tainted HTTP request data reaches a SQL execution sink without parameterization
Code snippet: cur.execute("SELECT id, name, email FROM users WHERE id = %s" % user_id)
Also detected by: bandit
bandit — HIGH — line 31
Title: Flask app run with debug=True
Two independent detectors landing on the same line, both confirmed by a third, is the pattern worth acting on immediately. Bandit's B608 rule — documented at bandit.readthedocs.io — fires specifically on formatted strings (%s, f-strings, .format()) appearing in SQL query contexts. The also_detected_by field in the BrassCoders YAML tells Claude Code that multiple scanners agreed on the same line; that cross-scanner confirmation is the signal to weight heavily in triage.
The debug=True finding is separate. Flask's debug mode activates the Werkzeug interactive debugger; any unhandled exception exposes an in-browser Python shell. That's a code execution surface if the app reaches production.
The Fix: Parameterized Query
BrassCoders's remediation note for both CRITICAL findings says the same thing: use prepared statements. SQLite's positional placeholder is ?, passed as a tuple in the second argument to cursor.execute() — the sqlite3 module sends the query string and the bound value to the database library separately, so no user-controlled data ever interpolates into the query text.
The fixed line:
cur.execute("SELECT id, name, email FROM users WHERE id = ?", (user_id,))
The query string stays fixed at SELECT id, name, email FROM users WHERE id = ?. The user_id value binds inside the sqlite3 module, not in Python string formatting. Python's sqlite3 documentation at docs.python.org/3/library/sqlite3.html calls this "DB-API 2.0 parameter substitution" — the value is transmitted to SQLite's C library as a typed parameter, never concatenated into the SQL text. No amount of SQL syntax in the user_id value changes the query structure.
Note the driver difference: psycopg2 (PostgreSQL) uses %s as its placeholder, with a values tuple rather than %-string formatting. cursor.execute("SELECT ... WHERE id = %s", (user_id,)) is safe in psycopg2; cursor.execute("SELECT ... WHERE id = %s" % user_id) is not. The placeholder syntax varies by driver; the principle of binding values separately from the query string applies everywhere. This distinction is how the bug survives code review — the two forms look similar at a glance.
Fix the debug flag at the same time:
app.run(debug=False)
The full fixed file:
import sqlite3
from flask import Flask, jsonify
app = Flask(__name__)
def get_db():
return sqlite3.connect("app.db")
@app.route("/user/")
def get_user(user_id):
conn = get_db()
cur = conn.cursor()
cur.execute("SELECT id, name, email FROM users WHERE id = ?", (user_id,))
row = cur.fetchone()
conn.close()
if row is None:
return jsonify({"error": "not found"}), 404
return jsonify({"id": row[0], "name": row[1], "email": row[2]})
if __name__ == "__main__":
app.run(debug=False)
Two characters change in the query string. One argument added. The route logic and the response format are identical.
The Re-Scan: What Changes and What Remains
BrassCoders's re-scan of the fixed file shows the input_validation_analyzer and Bandit findings gone — the %s format pattern no longer appears in a SQL context, so neither rule fires. The debug=True finding is gone. One finding remains: Semgrep's taint rule fires at line 23 with the title "Tainted dataflow: sql injection."
This is a known false positive on correctly parameterized SQLite calls. Semgrep's python taint rule tracks data flows from user-controlled sources — in this case, the user_id Flask route parameter — to SQL execution functions like cursor.execute(). The rule fires whenever it sees user-controlled data reaching the call, parameterized or not. It doesn't distinguish cur.execute(query % user_id) from cur.execute(query, (user_id,)). To the taint rule, both forms deliver user-controlled data to a SQL execution sink.
The (user_id,) tuple form is safe. The fix is correct.
This is where the BrassCoders + Claude workflow handles the residual. The scanner narrows scope from 3 findings to 1 — both CRITICALs gone, the HIGH gone. Claude Code reads the surviving Semgrep finding, sees the parameterized tuple syntax in the code_snippet field, and marks it a false positive. Three findings reduced to one triage decision, and the triage takes seconds.
The reduction matters in practice. Before the fix, Claude reading the YAML sees two CRITICAL entries with identical code snippets, plus a HIGH for debug=True. After the fix, one taint finding on a parameterized call. The cognitive surface drops; the reasoning path is shorter.
What BrassCoders Doesn't Check
BrassCoders's SQL injection findings confirm the structural pattern: a string-formatted query reaching an SQL execution sink. They don't verify whether the authenticated user is authorized to look up the requested user_id — that authorization question requires business-logic context no scanner can infer from source alone.
The route in user_lookup.py has no authentication decorator. No @login_required, no session check, no ownership validation. Any caller who hits /user/ gets the record if it exists — including records that don't belong to them. Whether that's a bug or a deliberate public endpoint depends on the application design, and BrassCoders has no way to know from source structure alone.
Claude Code, reading the BrassCoders YAML output in context, can surface this. The scanner reports the structural pattern; the AI triage layer reads the findings alongside the actual code and flags concerns the scanner didn't touch. That division of labor is built into how BrassCoders formats its output — the YAML is structured for AI consumption, so Claude can reason about what the scanner found and note what it didn't reach.
The corpus is Apache 2.0 licensed and the scan is reproducible. Install brasscoders via pip install brasscoders, clone the OSS repo at github.com/CopperSunDev/brasscoders, and run brasscoders scan docs/benchmarks/ai-code-findings-corpus/files. The SQL injection finding appears at user_lookup.py:22. The re-scan behavior after the ? fix matches exactly what's described above.
Top comments (0)