Injection: SQL, NoSQL, and Command

The query that isn't just a query

Most explanations of SQL injection start with "never trust user input." Fine advice, and it doesn't actually tell you what trusting it would mean. Here's the mechanism underneath it.

A SQL query is a script your database runs exactly as written. Build that script by gluing a string together, and whoever controls part of the string controls part of the script. There's no flag on the data saying "this came from a user, watch it." As far as the database is concerned, it's all just query, start to finish.

1def get_user(username, password):
2    query = (
3        "SELECT * FROM users WHERE username = '"
4        + username + "' AND password = '" + password + "'"
5    )
6    return db.execute(query)

That code passes every test you'd think to write for it. It also happens to work if someone sends admin' -- as the username and leaves the password field blank.

1SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

-- opens a SQL comment. Everything after it, password check included, gets thrown away before the database even looks at it. What actually runs just says: find the user named admin. So it logs you in as admin, not because a password got guessed or cracked, but because the code that was supposed to check one never ran.

admin' -- is not a clever payload. It's the first thing any pentester tries, it's in every intro security course, and it still works against production apps today, because "build the query with an f-string" is a natural thing to write and nothing about it looks wrong until you know what to look for.

Parameterized queries, the actual fix

The fix is not cleverer escaping. It's removing the string-concatenation step entirely. A parameterized query sends the query's shape and its data down two separate channels: the driver compiles the placeholders into a fixed structure first, and only afterward drops your values into those slots, as inert data, never as code.

1def get_user(username, password):
2    query = "SELECT * FROM users WHERE username = %s AND password = %s"
3    return db.execute(query, (username, password))

Submit admin' -- as the username through this version and the database looks for a user literally named admin' --. Nobody by that name exists, so you get nothing back. Same as any other username that isn't in the table.

This is a library feature, not a technique you implement

Every mainstream driver has it: psycopg2, mysqlclient, node-postgres, JDBC's PreparedStatement. If you catch yourself formatting a value straight into a query string, you haven't hit a missing feature. You skipped the one the library actually wanted you to use.

NoSQL and command injection

Injection isn't a SQL problem specifically. It's what happens when untyped input reaches something that interprets structure. MongoDB doesn't parse SQL, but a MongoDB query is a JSON object, and JSON has structure of its own.

NoSQL injection

1// req.body comes straight from the client as parsed JSON
2db.users.findOne({
3  username: req.body.username,
4  password: req.body.password,
5});

No string concatenation anywhere in sight, and it's still injectable. Send a JSON body where password is { "$ne": null } instead of a string, and MongoDB doesn't see a strange password value, it sees a query operator: match any password that is not null. True of every account that has a password. Which is all of them.

1const username = String(req.body.username);
2const password = String(req.body.password);
3
4// { "$ne": null } becomes the literal string "[object Object]",
5// which will never match a real password
6db.users.findOne({ username, password });

Coercing to a string closes this specific hole. It's a floor, not the fix for the underlying problem, which is that nothing validated the request's shape before it reached a query. Add schema validation at the boundary (Zod, Joi, a typed DTO, whatever your stack already has) and reject the malformed request with a 400 before your handler runs at all.

Command injection

Same mechanism, one layer down. Instead of a database interpreting your string, the shell does.

1import os
2
3def ping(host):
4    os.system(f"ping -c 1 {host}")

os.system hands the whole string to a shell, and a shell treats ; as "run the next command too." A host value of 8.8.8.8; rm -rf /data runs the ping, then deletes a directory. The server did exactly what it was told. It just didn't realize the instruction was two instructions.

1import subprocess
2
3def ping(host):
4    subprocess.run(["ping", "-c", "1", host], check=True)

Pass a list of arguments instead of one string and there's no shell involved at all. subprocess.run delivers host to ping as a single literal argument, semicolon included. Worst case, ping chokes on a bad hostname. Nothing gets deleted.

ORMs, allowlisting, and least privilege

ORMs parameterize the queries they build for you, automatically. They do nothing to stop you from building a query yourself, and most of them leave an escape hatch open for exactly that.

1User.objects.raw(
2    f"SELECT * FROM users WHERE username = '{username}'"
3)

.raw(), .extra(), any ORM method that accepts a raw string, it's the same concatenation bug as before, one import statement away from the query builder that would have caught it.

1User.objects.raw(
2    "SELECT * FROM users WHERE username = %s", [username]
3)

Table and column names can't be parameterized

Parameterization covers values: things you can swap out without changing what the query means. A column or table name changes the query's actual shape, so no driver will let you bind one as a parameter. A "sort by any column" endpoint has to solve this a different way, checking the name against a fixed allowlist before it ever touches a query string.

1ALLOWED_SORT_COLUMNS = {"created_at", "username", "email"}
2
3def list_users(sort_by):
4    if sort_by not in ALLOWED_SORT_COLUMNS:
5        raise ValueError("invalid sort column")
6    # safe: sort_by can only be one of three hardcoded strings by this point
7    query = f"SELECT * FROM users ORDER BY {sort_by}"
8    return db.execute(query)

This still builds the query with an f-string, and that's fine here. By the time it runs, sort_by has already been checked against three known values. It can't be attacker-controlled text anymore, whatever the request originally sent.

Least privilege as the last line of defense

Everything above assumes you catch every place this can happen. You won't, not forever, not across every endpoint someone adds two years from now on a Friday afternoon. The last layer isn't in your code at all: the database user your app connects as should only hold the permissions it actually needs.

None of this replaces parameterized queries. It's what's left standing when parameterization gets missed somewhere, which, on some endpoint, eventually, it will.