SQL Injection (ORDER BY)

The sneaky vulnerability that bypasses prepared statements.

The idea

We all know that Prepared Statements (Parameterization) stop SQL injection in `WHERE` clauses (e.g. `WHERE username = ?`). However, you cannot parameterize table names, column names, or the ORDER BY direction (ASC/DESC) in most SQL dialects. If you allow users to sort a data table by passing a column name in the URL (`?sort=price`), and you concatenate that directly into the `ORDER BY` clause, you are vulnerable to SQL Injection, even if you use an ORM.

How it works (Strict Whitelisting)

Because `ORDER BY` cannot be parameterized, the only defense is to strictly whitelist the allowed column names in your application code before injecting them into the query.

# VULNERABLE: Direct concatenation of column name
sort_col = request.args.get('sort') # E.g., 'price'
# DANGER: Attackers can inject '?sort=(CASE WHEN ...)'
query = f"SELECT * FROM products ORDER BY {sort_col}"

# SECURE: Strict Whitelisting
sort_col = request.args.get('sort')
ALLOWED_COLUMNS = ['price', 'name', 'date_added']

if sort_col not in ALLOWED_COLUMNS:
    sort_col = 'date_added' # Safe fallback

query = f"SELECT * FROM products ORDER BY {sort_col}"

Watch out for

• Time-Based Blind SQLi: Attackers exploit `ORDER BY` injections using `CASE` statements to ask the database Yes/No questions. `?sort=(CASE WHEN (user='admin' AND password LIKE 'A%') THEN sleep(5) ELSE 1 END)`. If the response takes 5 seconds, they know the password starts with A. They repeat this to steal the entire database character by character.
• ORM Vulnerabilities: Even advanced ORMs can fall victim to this. If you do `User.objects.order_by(request.GET['sort'])` in Django without validating the input, you might expose sensitive relationships or trigger costly subqueries.

Worked example

A data-table in an admin panel shows a list of users. Clicking a column header changes the URL to `/users?sort=email`. An attacker changes the URL to `/users?sort=extractvalue(1,concat(0x7e,(SELECT version())))`. Because the backend concatenates this into the `ORDER BY` clause, MySQL attempts to extract XML using the database version number. It crashes and returns an error: `XPATH syntax error: '~8.0.21'`. The attacker has successfully extracted the database version directly into the error log.

Check yourself

Why can't you just use a Prepared Statement (e.g. `ORDER BY ?`) to fix this vulnerability?