Krishna Kumar

Posted on Nov 14

💉 SQL Injection: Where Placeholders Can't Go (ORDER BY, IN, LIKE)

#security #python #webdev #appsec

"We use parameterized queries everywhere" is the most common answer to "are you safe from SQL injection?". It's usually almost true. The WHERE clauses are bound, the INSERTs are bound, and then there's a product list with a sort dropdown, and one line that looks like this:

sql = f"SELECT id, name, price FROM products ORDER BY {sort}"

Nobody wrote that out of laziness. They wrote it because the placeholder didn't work there. This post is a fix guide for those spots: the parts of a query a placeholder can't carry, what to do instead, and tests that prove the fix holds. Everything here runs against a local in-memory database. Only test what you own or are authorized to test.

What a placeholder actually binds

A placeholder carries a value. It doesn't carry SQL. The sources agree on this, almost word for word:

The OWASP SQL Injection Prevention Cheat Sheet says parameter markers stand for data values, not table names, column names or keywords such as ASC and DESC.
PortSwigger's SQL injection page says parameterized queries work where untrusted input is data, and can't handle table or column names or the ORDER BY clause.
The psycopg docs say only query values should be bound, and that table or field names shouldn't be merged that way.
The node-postgres docs put it bluntly: PostgreSQL doesn't support parameters for identifiers.

So a query has two kinds of parts. Values (the 42 in id = 42) go through placeholders. Structure (which column, which table, which direction) can't.

Why the f-string shows up

Here's a small table to work with, and the first thing most people try: binding the column name.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER, cost INTEGER);
INSERT INTO products (name, price, cost) VALUES
  ('kettle', 30, 12), ('lamp', 20, 15), ('desk', 90, 14);
""")

def names(sql, params=()):
    return [row[0] for row in con.execute(sql, params)]

# 1. Binding the column name: runs fine, sorts by a constant
print(names("SELECT name FROM products ORDER BY ?", ("price",)))

['kettle', 'lamp', 'desk']

No error, and no sorting either. The database received the string 'price' as the sort key, which is the same for every row, so the rows came back in table order, unsorted. It looks like a driver bug. The "fix" that follows is the f-string:

# 2. So someone "fixes" it with an f-string
def by(sort):
    return names(f"SELECT name FROM products ORDER BY {sort}")

print(by("price"))        # what the dropdown sends
print(by("cost"))         # a column the API never returns
print(by("price * -1"))   # an expression: the input is running as SQL

['lamp', 'kettle', 'desk']
['kettle', 'desk', 'lamp']
['desk', 'kettle', 'lamp']

The first line is the feature. The second sorts by cost, a column this endpoint never returns, so the order of the rows now tells the caller something about data they can't see. The third is the proof that matters: price * -1 was evaluated. The request parameter is no longer data. It's part of the query.

That's the whole bug (CWE-89). From here an attacker isn't limited to arithmetic; the same slot accepts conditional expressions and subqueries, which is how a sort parameter turns into the yes/no signal PortSwigger describes under blind SQL injection. I'm not going further than price * -1 here, because the fix doesn't depend on how bad the payload gets.

Fix 1: map structure to fixed choices

OWASP's wording is the rule: use fixed, application-owned choices for table names, column names and sort order. The user picks a key. Your code owns the SQL that key stands for.

SORT_COLUMNS = {"name": "name", "price": "price", "newest": "id"}
SORT_DIRECTIONS = {"asc": "ASC", "desc": "DESC"}


def list_products(con, sort="name", direction="asc", max_price=1_000_000):
    try:
        column = SORT_COLUMNS[sort]          # the SQL comes from our dict,
        order = SORT_DIRECTIONS[direction]   # never from the request
    except (KeyError, TypeError):
        raise ValueError("unsupported sort") from None
    sql = (
        "SELECT id, name, price FROM products "
        f"WHERE price <= ? ORDER BY {column} {order}"
    )
    return con.execute(sql, (max_price,)).fetchall()

There's still an f-string, and that's fine: everything it interpolates is a constant from a dict in the same file. The value (max_price) still goes through a placeholder. Three details are worth copying:

Reject, don't fall back silently. Turn the ValueError into a 400. A silent default hides the fact that someone is probing.
The map is also your exposure list. cost isn't in it, so nobody can sort by it. Even where an ORM blocks the injection for you, an explicit list of sortable fields is worth having for that reason alone.
Don't validate with a regex and then interpolate the input. A pattern like ^\w+$ would still let cost through. Look the key up and use your string.

The same shape in Node, returning the query config object that node-postgres accepts:

const SORT = { name: "name", price: "price", newest: "id" };
const DIR = { asc: "ASC", desc: "DESC" };

function productQuery({ sort = "name", dir = "asc", maxPrice = 1000000 } = {}) {
  if (typeof sort !== "string" || typeof dir !== "string" ||
      !Object.hasOwn(SORT, sort) || !Object.hasOwn(DIR, dir)) {
    throw new RangeError("unsupported sort");
  }
  return {
    text: `SELECT id, name, price FROM products WHERE price <= $1 ORDER BY ${SORT[sort]} ${DIR[dir]}`,
    values: [maxPrice],
  };
}

// const { rows } = await client.query(productQuery(req.query));

Object.hasOwn matters. With a plain SORT[sort] check, a key like constructor finds an inherited property and passes a truthiness test. I ran this builder in Node 24 and it rejected cost, constructor, __proto__, price * -1 and an array; the client.query call is commented out because I didn't run it against a database.

Fix 2: table and column names

Sometimes the table really is dynamic: one endpoint that counts rows in a few different tables, say. Same rule, plus the driver's identifier quoting on top. In psycopg 3 that's the sql module (written from its docs, not run here):

from psycopg import sql

TABLES = {"orders": "orders", "invoices": "invoices"}

def count_rows(conn, kind, tenant_id):
    table = TABLES[kind]   # KeyError -> 400 in your handler
    query = sql.SQL("SELECT count(*) FROM {} WHERE tenant_id = %s").format(
        sql.Identifier(table)
    )
    return conn.execute(query, (tenant_id,)).fetchone()[0]

Quoting alone isn't the fix. OWASP makes the point directly: identifier quoting protects the SQL syntax, but it doesn't restrict which tables the application will read. sql.Identifier(user_input) produces a perfectly valid query against users. The map decides what's allowed, and the quoting is the second layer. In Node, pg ships escapeIdentifier and its docs point to the pg-format package for dynamic identifiers; the same caveat applies.

Fix 3: IN lists

An IN list is made of values, so it can be bound. The trap is that one placeholder is one value, and people reach for ",".join(ids) when the list length varies. Generate the placeholders instead:

def products_by_ids(con, ids, limit=100):
    ids = list(ids)[:limit]
    if not ids:
        return []
    marks = ", ".join("?" * len(ids))        # only question marks, one per value
    sql = f"SELECT id, name FROM products WHERE id IN ({marks}) ORDER BY id"
    return con.execute(sql, ids).fetchall()

The interpolated piece is a run of ? built from a count. No input text gets near the SQL. On PostgreSQL there's a neater option. The psycopg 3 docs note that IN %s with a tuple doesn't work there and recommend = ANY(%s) with a list, which also accepts an empty list.

Fix 4: LIKE patterns

This one isn't injection, but it lives in the same code. A bound LIKE value can't change the query, yet % and _ inside it are still wildcards. A search box that passes the term straight through lets a user type % and match every row. Escape the wildcards and name the escape character:

def like_escape(term, esc="!"):
    return (term.replace(esc, esc + esc)
                .replace("%", esc + "%")
                .replace("_", esc + "_"))


def search_products(con, term):
    pattern = f"%{like_escape(term)}%"
    sql = "SELECT name FROM products WHERE name LIKE ? ESCAPE '!' ORDER BY name"
    return con.execute(sql, (pattern,)).fetchall()

SQLite and PostgreSQL both document the ESCAPE clause. PostgreSQL's default escape character is the backslash; I name ! explicitly so the code doesn't depend on a default or on how many layers interpret a backslash.

Three more places to look

Stored procedures. Binding the arguments of a call protects the call. OWASP notes that dynamic SQL built inside the procedure can still be injectable.
Data from your own database. PortSwigger calls it second-order injection: a value is stored safely, then concatenated into a later query because it "came from the database". OWASP lists the database itself as a source of untrusted data. Bind it again.
ORM escape hatches. Django's docs say querysets are parameterized, and in the same breath tell you to be careful with raw queries, extra() and RawSQL. Those are the lines to read in a review.

And one layer that limits the damage if you miss something: OWASP's hardening section starts with least privilege. The account your app connects with shouldn't own the schema.

Prove the fix, don't assume it

"We parameterized it" is a claim about intent. The proof is a test that sends structure where a key is expected and gets refused, next to a control that shows the feature still works. A check that only ever rejects proves nothing; it might be rejecting everything.

import sqlite3
import unittest

from catalog import list_products, products_by_ids, search_products


class QueryStructureTests(unittest.TestCase):
    def setUp(self):
        self.con = sqlite3.connect(":memory:")
        self.con.executescript("""
        CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER, cost INTEGER);
        INSERT INTO products (name, price, cost) VALUES
          ('kettle', 30, 12), ('lamp', 20, 15), ('desk', 90, 14), ('50% off bin', 5, 1);
        """)

    def test_control_valid_sort_still_works(self):
        rows = list_products(self.con, sort="price", direction="desc")
        self.assertEqual([r[1] for r in rows], ["desk", "kettle", "lamp", "50% off bin"])

    def test_anything_outside_the_map_is_rejected(self):
        for hostile in ["cost", "price * -1", "name, cost", "price --", "PRICE", "", None]:
            with self.subTest(sort=hostile):
                with self.assertRaises(ValueError):
                    list_products(self.con, sort=hostile)

    def test_direction_is_mapped_too(self):
        for hostile in ["DESC", "desc, cost", "asc --"]:
            with self.subTest(direction=hostile):
                with self.assertRaises(ValueError):
                    list_products(self.con, direction=hostile)

    def test_in_list_values_stay_data(self):
        self.assertEqual(products_by_ids(self.con, [1, 3]), [(1, "kettle"), (3, "desk")])
        self.assertEqual(products_by_ids(self.con, ["1) OR (1=1"]), [])
        self.assertEqual(products_by_ids(self.con, []), [])

    def test_like_wildcards_are_literal(self):
        self.assertEqual(search_products(self.con, "%"), [("50% off bin",)])
        self.assertEqual(search_products(self.con, "_"), [])
        self.assertEqual(search_products(self.con, "lam"), [("lamp",)])


if __name__ == "__main__":
    unittest.main(verbosity=2)

test_anything_outside_the_map_is_rejected ... ok
test_control_valid_sort_still_works ... ok
test_direction_is_mapped_too ... ok
test_in_list_values_stay_data ... ok
test_like_wildcards_are_literal ... ok

Ran 5 tests in 0.002s

OK

That's the output from my local run on Python 3.14, with the class names trimmed. Read test_in_list_values_stay_data closely. The string 1) OR (1=1 goes in and zero rows come out, because it was compared as a value against id and matched nothing. That's what "bound" looks like from the outside. In the LIKE test, searching for % finds only the one product with a literal percent sign in its name.

To find the lines that need this treatment, search your codebase for SQL built with string operations. For Python, Bandit's B608 check looks for exactly that: strings that resemble SQL statements involved in formatting or concatenation. Treat each hit as a line to review, not a verdict. The question for every one is the same: does each interpolated piece come from a fixed map in your code?

Try it yourself

Break the fixed code on purpose: swap SORT_COLUMNS[sort] for sort in a copy and watch test_anything_outside_the_map_is_rejected fail. A test you've never seen fail hasn't proved anything yet.
PortSwigger Web Security Academy, to see the attacker's side on targets built for it: SQL injection vulnerability in WHERE clause allowing retrieval of hidden data, SQL injection vulnerability allowing login bypass and Blind SQL injection with conditional responses.
OWASP Juice Shop, run locally with docker run --rm -p 127.0.0.1:3000:3000 bkimminich/juice-shop. Its Injection category includes the "Login Admin" challenge.
Then go back to your own code and find every sort, filter and "export by table" feature. Those are the places structure comes from a request.

Wrapping up

Placeholders bind values, and they do that job well. They were never able to bind a column, a table or DESC, and the gap gets filled with string formatting by people who believe the app is already covered. Split every dynamic piece of a query into value or structure. Bind the values. Map the structure to choices your code owns, and reject the rest. Then prove it with a control and a hostile input, because a finding isn't real until it's proven, and neither is a fix.

DEV Community