SQL injection example: login bypass and UNION reads on a local API
SQL injection happens when an application pastes request data into the text of a query, so the input can change what the query means. In a small sqlite3-backed HTTP API we bypassed a login with one crafted username and read every user's password hash through a search parameter. Binding values with placeholders stopped both attacks, but a sort parameter still leaked data until we replaced it with an allowlist.
This is a scripted Python and curl experiment with downloadable source and responses. No AI agent or model ran the test.
What SQL injection is and what the fixture covers
SQL injection is a flaw where untrusted input becomes part of the SQL statement instead of staying a value. MITRE lists it as CWE-89, Improper Neutralization of Special Elements used in an SQL Command. The OWASP SQL Injection Prevention Cheat Sheet names parameterized queries as the preferred defense, and it says parts of a query that cannot be parameterized, such as table names, column names and sort direction, should be mapped to a strict allowlist of fixed values the application owns.
The fixture is one Python file with an in-memory sqlite3 database and four endpoints on 127.0.0.1. It has three modes so each defense can be tested alone:
- vulnerable: every query is built by string concatenation.
- bound: values use
?placeholders, but thesortparameter is still concatenated intoORDER BY. - fixed: values use placeholders and
sortmust match an allowlist.
Endpoints are POST /login, GET /search (with q and sort), POST /register and GET /history. The data is synthetic: three users (alice, bob and o'brien), four products and three orders. Demo passwords are public test strings, and the fixture stores unsalted SHA-256 hashes for brevity, which is not a model for real password storage. The fixture is insecure on purpose and always binds loopback. Test only systems you own or have written permission to test; the Acceptable Use Policy describes authorized use, and this article gives no permission to test anything else.
Run the downloadable fixture
Download fixture.py and verify.py into one directory. You need Python 3.9 or later and curl. Python uses only the standard library. Then run:
python3 verify.py --output rerunThe output directory must not exist. The runner starts each mode on a free port, sends 14 real curl requests to it, saves every raw response, and stops the server. Requests bypass proxies, time out after three seconds and never leave loopback.
Our recorded command was python3 verify.py --output recorded-run. It printed {"checks": 42, "passed": true, "failures": []}. That result means all 42 responses matched what we expected, including the unsafe ones. It does not mean the vulnerable mode is safe. The run started at 22:46:49 UTC on 10 November 2026 with Python 3.15.2, SQLite 3.54.1 and curl 8.7.1 on macOS ARM64. Ports, timestamps and response dates differ on a rerun. results.json holds the exact versions and source hashes.
Observed results across three modes
Each row is one test. The vulnerable column is the unsafe baseline, the bound column shows what placeholders alone give you, and the fixed column adds the sort allowlist. Legitimate requests appear as controls so a server that rejects everything cannot pass.
| Test | Safe server should return | vulnerable | bound | fixed |
|---|---|---|---|---|
| Login, right password | 200 as alice | 200 | 200 | 200 |
| Login, wrong password | 401 | 401 | 401 | 401 |
| Login as o'brien, right password | 200 | 500 | 200 | 200 |
Login as alice'--, wrong password | 401 | 200 as alice | 401 | 401 |
Search Widget | 200, two rows | 200, two rows | 200, two rows | 200, two rows |
| Search with UNION payload | 200, no rows | 200, 3 user rows with hashes | 200, no rows | 200, no rows |
Sort by price | 200, ids 4,1,3,2 | 200, ids 4,1,3,2 | 200, ids 4,1,3,2 | 200, ids 4,1,3,2 |
Sort by nosuchcolumn | 400 | 500 | 500 | 400 |
| Sort expression, true condition | 400 | 200, ids 4,1,3,2 | 200, ids 4,1,3,2 | 400 |
| Sort expression, false condition | 400 | 200, ids 2,3,1,4 | 200, ids 2,3,1,4 | 400 |
Register carol, read history | 201, then her one order | 201, one order | 201, one order | 201, one order |
Register x' OR '1'='1, read history | 201, then no orders | 201, then all 3 orders | 201, no orders | 201, no orders |
Login bypass, UNION read and second-order injection
The vulnerable login builds its query like this:
sql = "SELECT username FROM users WHERE username = '" + username + "' AND pw_hash = '" + pw_hash + "'"With the username alice'-- the text becomes ... WHERE username = 'alice'--' AND pw_hash = '...'. The -- starts a SQL comment, so the password test disappears. The server answered {"ok": true, "user": "alice"} to a request with the password wrong. The same fixture returned a 500 for the legitimate user o'brien, because his apostrophe closed the string early. An unexplained 500 on a name with an apostrophe is a common first sign of this flaw.
The search endpoint builds name LIKE '% + q + %'. A UNION payload in q appends a second SELECT from another table with the same column count:
curl --get --data-urlencode "q=zzz' UNION SELECT id, username, pw_hash FROM users --" \
http://127.0.0.1:53155/searchThe recorded response listed alice, bob and o'brien in the name field with their SHA-256 hashes in the price field. The authorization checks around an endpoint do not help here, because the query itself reads data the endpoint was never meant to touch.
Second-order SQL injection stores the payload first and triggers it later. In the fixture, /register inserts the username with placeholders, so registering x' OR '1'='1 succeeds with a 201. Later, /history reads the stored name back and concatenates it into an orders query, on the assumption that data from its own database is safe. In vulnerable mode that request returned the orders of alice, bob and carol. The bug sits in the second query, even though the first one looked clean.
Verify a SQL injection finding before you report it
A single odd error is only a lead. A finding needs a controlled difference. For the login bypass, send three requests: the right password, a wrong password, and the injected username with the same wrong password. A bypass is verified when only the injected request succeeds and the response identifies the user. For the UNION read, check that the returned values come from the other table, here the hashes in price. Record the endpoint, parameter, payload, request, response and the data class exposed. Stop after proving access. Do not dump a real table to show impact.
This experiment shows the flaw in a toy. It gives no severity rating for a production system, and a 500 alone does not prove injection.
Parameterize the queries and retest
The fix for values is to keep the SQL text fixed and pass data separately. This is the login query in bound and fixed mode:
row = db.execute("SELECT username FROM users WHERE username = ? AND pw_hash = ?",
(username, pw_hash)).fetchone()The search value and the history query use the same placeholder style. The retest used the same 14 requests against fresh servers on ports 53170 and 53186. alice'-- returned 401, the UNION payload returned an empty result list, o'brien logged in with a 200, and the stored payload matched no orders. The positive controls still passed: alice's correct login, the Widget search and carol's history all returned the same data as before.
What parameters do not cover: ORDER BY and other identifiers
Placeholders bind values. They cannot stand in for a column name, a table name or a sort direction, so developers often concatenate those. Our bound mode concatenates sort this way, and it still failed. Sorting by nosuchcolumn returned a 500, which shows the input reaches the parser. Two expressions then showed that the order of the results can carry one yes-or-no answer per request:
sort=CASE WHEN (SELECT substr(pw_hash,1,1) FROM users WHERE username='alice')='4'
THEN price ELSE -price ENDWhen the first character of alice's hash matched, ids came back as 4,1,3,2. With a wrong character they came back as 2,3,1,4. We ran one true and one false probe and did not extract a whole hash, but the channel works in both vulnerable and bound modes. In fixed mode the server looks up the requested name in a dictionary:
if sort not in SORT_COLUMNS:
return self.reply(400, {"error": "invalid_sort"})
sort = SORT_COLUMNS[sort]Both probes and the unknown column returned 400 in fixed mode, and sort=price still returned 200. This matches the OWASP guidance to map user choices to fixed application-owned values. Quoting an identifier is a weaker option, because it only controls syntax and does not restrict which columns a caller can name.
Inspect the evidence bundle
- results.json: all 42 requests with commands, expected and observed values, and hashes.
- commands.sh: the exact curl commands. Their ports are no longer listening.
- SHA256SUMS: an integrity check, not independent attestation.
- README.txt, fixture.py and verify.py.
The folder also holds one raw .http file per request, for example the vulnerable login bypass and the fixed-mode rejection of an unknown sort column. All data is synthetic.
What this experiment does not cover
We tested one database engine and one request style. We did not test blind or time-based techniques, out-of-band channels, stacked statements, ORMs, stored procedures, JSON or XML inputs, other SQL dialects, database account privileges or a web application firewall. The OWASP cheat sheet also recommends least-privilege database accounts as a second layer. The fixture does not model that. Passing the fixed mode shows that these 14 requests no longer misbehave. It says nothing about other queries in an unrelated codebase.
For related reading, see how path traversal abuses a different untrusted input, and the OWASP API Top 10 for a test list per API risk. The 2023 API list has no separate injection category.
Excession Labs runs this kind of injection testing in its security assessments, using test accounts and synthetic records to confirm each finding. See how a finding like this looks in a sample report. Download this finding written up as a one-page sample report (PDF).