SQL injection for people who write queries: mechanism, fix, test

SQL injection is old, well understood, and still on the OWASP Top 10, which is a strange combination until you notice why: the fix is easy, but it has to be applied to every query, and one that is built by pasting a value into a string undoes it. This article explains the mechanism for the person writing the query, shows the one-line change that closes it, quotes what OWASP and CWE-89 say, and ships a regression test that proves the safe version cannot be widened by hostile input. It describes the defect only as far as a developer needs to recognise and fix it in their own code; it is not a guide to attacking anyone's database.

The mechanism

A database receives a single string of SQL and executes it. When your code builds that string by concatenating user input, the input becomes part of the program. CWE-89 states the problem exactly: the product "constructs all or part of an SQL command using externally-influenced input", but "does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command", so "the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data".

The canonical illustration, from CWE-89, is a lookup that concatenates a name:

SELECT * FROM items WHERE owner = 'wiley' AND itemname = '<input>'

If the input is name' OR 'a'='a, the string the database receives becomes ... itemname = 'name' OR 'a'='a', and, as CWE-89 puts it, the "WHERE clause to always evaluate to true", so the query returns rows the caller was never supposed to see. The database did nothing wrong; it executed the program it was handed. The defect is that the program was partly written by the person supplying the input.

OWASP's Top 10:2021 places injection at position A03 and, from an analysis across contributed data, records 33 mapped CWEs, 274,228 occurrences, and 32,078 CVEs for this category, with a maximum incidence rate of 19.09%. Those numbers are why the fix being easy is not the same as the problem being solved: it has to be applied everywhere, and legacy code accumulates the exceptions.

The fix: separate the code from the data

The OWASP SQL Injection Prevention Cheat Sheet lists the defence first and unambiguously: prepared statements with parameterised queries. "Prepared statements force the developer to define all SQL code first and pass in each parameter to the query later." The SQL text is fixed before any value is attached; the value travels to the database as a typed parameter, not as text spliced into the command. The database never parses the value as SQL, so there is nothing for the input to change.

In Python's DB-API this is the difference between two characters of code:

cur.execute("SELECT balance FROM users WHERE name = '" + name + "'")  # defect: the value becomes SQL text

cur.execute("SELECT balance FROM users WHERE name = ?", (name,))       # fix: the value is bound as a parameter

The placeholder style varies by driver (? for sqlite3, %s for psycopg and mysqlclient, named :name for others), but the principle is identical. Do not build the placeholder list by formatting user input into it either; build it from the number of values you have.

There is one thing parameters cannot do, and OWASP is clear about it: bind variables work for values, not for identifiers such as table or column names. When a query needs a dynamic column or table, the cheat sheet's guidance is that "ideally those values come from the code and not from user parameters", and if user choice really must select a column, validate it against an allow-list of known-good names before it goes near the query. OWASP calls a design that puts user values into table or column names "a symptom of poor design" that warrants a rewrite. Escaping is listed last and described as "fragile"; it cannot "guarantee that this option will prevent all SQL injections in all situations", so it is a fallback, not a plan.

Two supporting controls sit behind the primary fix. Least privilege, from CWE-89's environment-hardening mitigation, means the account the application connects with can read and write only what it needs, so an injection that does slip through cannot drop tables or read other schemas. Allow-list input validation catches malformed input early and documents intent, though OWASP notes that "proper output encoding, escaping, and quoting" and, above all, parameterisation are what actually prevent injection; validation is defence in depth, not the defence.

A test that proves the fix

A fix you cannot test tends to regress. The listing sql-injection-for-people-who-write-queries.py builds a throwaway in-memory SQLite database and runs the same lookup two ways against a set of hostile-looking inputs, then asserts that the parameterised path returns only exact matches and never widens.

python code/sql-injection-for-people-who-write-queries.py

Its output, one line per probe, shows the two paths side by side:

input                 concatenated                            parameterised
'alice'               [('alice', 'alice@example.org')]        [('alice', 'alice@example.org')]  ok
"o'brien"             [('<sql error>', 'near "brien": ...')]   []  ok
"alice' OR '1'='1"    [('alice', ...), ('bob', ...)]           []  ok
"alice'; --"          [('alice', 'alice@example.org')]         []  ok
'%'                   []                                       []  ok

Two rows carry the lesson. The o'brien row is a real surname with an apostrophe: the concatenated query throws a syntax error, which means the same defect that lets an attacker in also breaks on ordinary honest data, so "it works in testing" was never evidence of safety. The alice' OR '1'='1 row is the tautology: concatenation returns every row, while the parameterised query returns nothing, because it looked for a user literally named alice' OR '1'='1 and there is none. The script exits non-zero if the parameterised path ever returns anything but an exact match, so you can drop it into a test suite: point its lookup_safe at your real query function and keep the assertion.

Detection and fix

• Make parameterised queries the only way values enter SQL in your codebase, and treat any string-built query as a defect to fix, not a style preference.
• For dynamic table or column names, use an allow-list of known identifiers from your code; never let a request choose an identifier directly.
• Run the application's database account under least privilege so a missed spot cannot become a full compromise, and reserve schema-changing rights for migrations, not the app.
• Add a regression test in the shape of the shipped script for the query functions that touch user input, so a future concatenation fails the build.
• Use static analysis to flag string-built queries; recent evaluations of automated detectors show they help but miss cases, so they supplement the parameterisation rule rather than replace it.

The limitation to state plainly: parameterised queries stop SQL injection, not every injection. The same class of defect, untrusted input reaching an interpreter that mixes code and data, appears in OS command construction, LDAP filters, and template engines, and each has its own parameterised or context-aware fix. The habit to carry across all of them is the one this article is about: never build the program out of the input.