Table of Contents

SQL injection was described publicly in 1998. It has been in the OWASP Top Ten since the OWASP Top Ten existed. The fix is well understood, cheap, and built into every database driver in common use.

But but but…. it’s still breaching companies today, 20+ years later

This isn’t a hard vulnerability to understand or an expensive one to fix. The question worth asking isn’t “what is SQL injection”. It’s “why does a solved problem keep shipping to prod”.

The actual mechanism

Every explanation of SQL injection involves quote characters and OR 1=1, and they all slightly miss the point. So let’s start one level up.

The root cause is that by default, we write services that execute statements by gluing strings together.

A SQL query is code. When we write:

query = "SELECT * FROM users WHERE email = '" + user_input + "'"

We are generating source code at runtime from untrusted input. The database receives one flat string and has to work out which parts are your instructions and which parts are data. It cannot. that information was destroyed the moment we concatenated.

This is the same class of bug as command injection, XSS, and template injection. Different syntax but same shape: data crossed into the code because there was only one channel to share data and instructions.

Once we see it that way, the fix is obvious and not “escape the quotes”. The fix is to stop having one channel.

In Action

For example. The database receives:

SELECT * FROM users WHERE email = '[email protected]'

Fine. Now the input is ' OR '1'='1:

SELECT * FROM users WHERE email = '' OR '1'='1'

The quote closed the string early and everything after it is now instructions rather than data. '1'='1' is true for every row, so this returns the whole table.

Worse, with '; DROP TABLE users; --:

SELECT * FROM users WHERE email = ''; DROP TABLE users; --'

The -- comments out the trailing quote so the statement parses cleanly. Many drivers reject multiple statements in one call by default, which is the only reason this particular example isn’t more common in the wild.

The attacker doesn’t exploit a bug in the database. The database did exactly what it was told. We wrote the injection; they just supplied the argument.

The fix: Parameterised Queries

What is the fix? Send the query and the data over separate channels.

A prepared statement does exactly that. The database parses the query first, with placeholders where values go. It builds the execution plan while the untrusted data isn’t present. Then the values arrive separately and are bound to placeholders in an already-parsed statement.

Examples

rows, err := db.Query(
    "SELECT * FROM users WHERE email = $1 AND active = $2",
    userEmail, true,
)

Extra safety: Parameter mappings

Parameterisation binds values. It cannot bind identifiers - table names, column names, or the direction of an ORDER BY. We cannot write:

SELECT * FROM users ORDER BY ? ?   -- does not work

And this is where codebases get breached, because at this point we sigh and go back to concatenation for the sort column.

The answer is an allowlist. Never pass the user’s string through - use it to select from values you control:

var sortColumns = map[string]string{
    "name":    "name",
    "created": "created_at",
    "email":   "email",
}

var sortDirections = map[string]string{
    "asc":  "ASC",
    "desc": "DESC",
}

column := sortColumns[req.URL.Query().Get("sort")]
if column == "" {
    column = "created_at"
}

direction := sortDirections[req.URL.Query().Get("dir")]
if direction == "" {
    direction = "ASC"
}

query := fmt.Sprintf("SELECT * FROM users ORDER BY %s %s", column, direction)

That string looks alarming but is completely safe, because column and direction can only ever be values from the dictionaries we wrote. The user’s input picks a key; it never reaches the query. we map, don’t filter - an allowlist that returns the user’s string when it matches is one refactor away from returning it when it doesn’t.

So why does it still happen?

The fix is a one-liner. Here’s where it goes wrong anyway.

ORMs are safe until they aren’t. Every ORM has an escape route for raw SQL, and that’s where injection lives now. session.execute(text(f"SELECT ...")) in SQLAlchemy, @Query or @NativeQuery with concatenation in Spring Data, etc… Teams assume “we use an ORM, we’re fine” and never audit the 3% of queries that dropped to raw SQL precisely because they were the complicated ones. From what I’ve seen, it tends to fail where it’s least expected:

  • Dynamic search and reporting: Filter builders that assemble WHERE clauses from a dozen optional parameters. The complexity just increases as the product evolves so someone concatenates “just this once” for the operator or the column name.
  • Migrations, jobs, admin tooling: Internal scripts don’t get the same review. Then an internal tool gets an internal UI, and “internal” turns out to include anyone who phished one employee.
  • Reports written by non-developers. BI tools, analytics dashboards, some Python script in someone’s home directory that emails a CSV every week. Never reviewed, frequently concatenating random strings.

Multiple layers of security

Parameterisation is the fix. These reduce the damage when something slips through, and something always eventually does:

  • Least privilege principle for the database user: We almost certainly don’t need DROP, or CREATE USER, or read access to every table. A read-only connection for read paths turns a catastrophic injection into a data disclosure - not great but not terrible.
  • Don’t propagate errors: Generic message to the user, full detail to your logs. Error-based injection needs error messages.
  • Log and alert on database errors: A spike in SQL syntax errors from one user is somebody probing. That’s a detection we can have almost for free.
  • Static analysis in CI: Linting, CodeQL, Semgrep, Bandit, etc…

Takeaways

  • SQL injection is malicious code assembled from untrusted strings. Same shape as command injection and XSS.
  • Parameterised queries work always, because the query is parsed before the data exists. Escaping works sometimes.
  • Parameters bind values only. For identifiers and sort order, map through an allowlist over which we have control of.
  • Least privilege on the DB user decides whether an incident is a disclosure or an extinction event.

If you change one thing this week: go and check what your application’s database user is actually permitted to do. Most people are surprised, and it’s a twenty-minute fix that caps the blast radius of a bug you haven’t found yet.

Keep on hacking!