The query that runs fine until someone types an apostrophe

Here is a handler that looks like it does exactly what it should:

@app.get("/users")
def get_user():
    uid = request.args["id"]
    cur.execute(f"SELECT * FROM users WHERE id = {uid}")
    return cur.fetchone()

Look up a user by id, return the row. You test it with ?id=42, it returns user 42, the tests go green, and a reviewer nods it through - because there is nothing to argue with. It is a SELECT with a WHERE, which is the whole job.

Then someone requests ?id=0 OR 1=1 and gets the first user in the table. They request ?id=0 UNION SELECT username, password FROM admins and get credentials that were never meant to leave the database. They request ?id=0; DROP TABLE users; -- and the table is gone. Nobody typed anything exotic - they typed into a query-string field that your code pasted, character for character, into a sentence the database reads as code.

This post is about why that handler is a vulnerability, why the value's type is a red herring, the one fix that actually closes it, and a small tool I wrote - sqlfence - that fails the build the moment it sees a SQL query built from user input.

SQL is parsed, and your string is the program

The trap is that a SQL driver does not receive "a query and some data". It receives one string, and it parses the whole thing as a program. When you write:

f"SELECT * FROM users WHERE id = {uid}"

you are not passing uid as data - you are pasting its characters into the source text of a program and asking the database to compile the result. With uid = "42" the program is ... WHERE id = 42. With uid = "0 OR 1=1" the program is ... WHERE id = 0 OR 1=1, whose WHERE is always true. The database cannot tell the difference between the SQL you wrote and the SQL the attacker appended, because by the time it sees the string, it is all just one program.

That is the whole bug. The safe call and the exploitable call are character-for-character identical in your source - the only difference is what the caller decided to put in id. You never wrote OR 1=1 anywhere. The attacker supplied it, and your query language executed it.

Why "just cast it to a string" is not the fix

The instinct is to sanitize the value - strip quotes, escape it, coerce it. This is where SQL injection differs from its NoSQL cousin, and where a lot of "fixed" code is still broken.

Coercing to a string does nothing: the payload 0 OR 1=1 is already a string, and a string is exactly what the injection needs. Manually escaping quotes gets you into an arms race you lose - different databases quote differently, comments (--, /* */), numeric contexts with no quotes at all, and encoding tricks all route around a hand-rolled escaper. Coercing to a number (int(uid)) genuinely does help for that one column, because an integer cannot carry SQL syntax - but it only works where the value truly is numeric, and you have to remember it on every single parameter.

The real fix is to stop building the program from the data at all:

cur.execute("SELECT * FROM users WHERE id = %s", (uid,))

Here the SQL string is a constant - fixed at the moment you write it, containing a placeholder %s. The value uid travels to the database as a separate argument, over a channel that is never parsed as SQL. The database compiles the query first, then binds the value into the already-compiled plan. There is no string for the attacker to escape out of, because the value never becomes part of the query text. This is a parameterized (or prepared) statement, and every mainstream driver has it:

The fix is small and it is the same everywhere: the SQL is a constant, the values are bound. The problem is never knowing it - it is noticing that this particular execute built its string instead of binding.

Why it slips through review

SQL injection is CWE-89, and it has sat at or near the top of the OWASP Top 10 Injection category for two decades. It is the most famous vulnerability in software. So why is it still everywhere?

Because the vulnerable line looks like the line you meant to write. There is no scary function, no eval, no obvious hole - just an f-string, or a +, or a template literal, splicing a value into a query the way you splice values into strings a hundred times a day. The only thing wrong is where the value came from, and that provenance is often a few lines up, or in another function, or behind a variable named uid. A reviewer reads execute(f"SELECT … {uid}"), recognises a query, and moves on.

And you cannot test your way to catching it. Your tests send id=42 and get user 42. Nobody writes the test that sends 0 OR 1=1 as an id - because if they were already thinking about that, they would have parameterized it.

Why this is a linter's job - and why static

The bug is not hiding at runtime; it is right there in the source. A query whose string is built (f-string, %, .format, +, a template literal, fmt.Sprintf, #{}) from something that traces back to request input, reaching an execute/query call with no placeholder-and-bind, is a textual pattern - visible the instant the line is written. That makes it a natural fit for a static gate: read the code, find the SQL sinks, ask how each query string was assembled, and fail the pull request when the answer is "concatenated from the user."

That is exactly what sqlfence does.

What sqlfence checks

sqlfence is a single static binary (Go, stdlib-only) that scans a directory and, for every raw-SQL sink, asks two questions:

1. Is the SQL string built by concatenation or interpolation? - an f-string, a % format operator, .format(), +, a JS template literal ${…}, fmt.Sprintf, PHP "…$var", or Ruby #{…}.

2. Does that built string carry user input? - request.args, req.body, params[…], $_GET, Java getParameter, Go r.URL.Query(), and the like - either directly on the line, or through a variable assigned from user input within a small window (light data flow, reset at every function boundary).

If both hold, it is SF001, a blocker. If a suspiciously-named dynamic-SQL variable (query, sql, stmt) reaches a sink but its origin cannot be confirmed, it is SF002, a warning.

It recognises the sinks across six languages - cursor.execute / SQLAlchemy text() / Django .raw() (Python), db.query / knex.raw (JS/TS), db.Query / Exec (Go), Statement.executeQuery (Java), mysqli_query / $pdo->query (PHP), and connection.execute / .where("…") (Ruby).

What it deliberately does not flag

A linter that cries wolf gets turned off, so sqlfence stays quiet on the code that is already correct:

• Parameterized queries. execute("… %s", (uid,)), db.Query("… $1", id), a PreparedStatement with ?. The value is a separate bound argument and the SQL string has no fusing construct - so there is nothing to flag. This is the recommended fix, recognised as clean.

• ORM query builders. .filter(name=…), .where(id: val), a knex builder chain. These are not raw-SQL sinks; the ORM parameterizes underneath.

• Escaped or numeric values. mysqli_real_escape_string, pg_escape_string, $pdo->quote, ActiveRecord::Base.sanitize_sql, or a numeric cast (int(...), parseInt, strconv.Atoi) in the query expression.

• Constant SQL. execute("SELECT COUNT(*) FROM users") is hardcoded - no user input, no finding.

It fires only when all three line up: an input signal, a string-built SQL, and a query sink. That is the shape of the real bug, and little else.

Try it

go install github.com/jay-tank/sqlfence@latest

sqlfence .            # scan the current tree
sqlfence ./api        # scan a path
sqlfence --json       # machine-readable
sqlfence --strict     # treat SF002 warnings as failures too

Exit code 1 when it finds a blocker, so it drops straight into a pre-commit hook or a CI step:

- run: go run github.com/jay-tank/sqlfence@latest ./

Miss a flow it does not follow, or want it quiet on a line you have vetted? Add a sqlfence:ignore comment on the query line, or a path substring to a .sqlfenceignore file.

sqlfence is open source (MIT) at https://github.com/jay-tank/sqlfence. It is a line- and window-scoped heuristic, not a full taint engine - it can miss SQL that flows through a helper across functions, and it can over-flag a benignly-named variable - but it answers one question well, on every commit, in the languages a real service is actually written in: did this query build its string from the user instead of binding it? That is the question that, asked by hand, is the one everyone forgets.

Part of a small family of zero-config security gates - alongside noscape (NoSQL-operator injection), shellfence (OS command injection), and pathfence (path traversal). Each fences one untrusted-input bug class out of your build.