Avoid string-formatting a SQL ORDER BY direction

By Batu · English technical notes

Also published in our primary archive.

Avoid string-formatting a SQL ORDER BY direction

To avoid string-formatting a SQL ORDER BY direction, validate the requested direction against an allowlist and map each accepted spelling to a fixed SQL token. Do not interpolate the request itself, and do not try to bind a direction with a placeholder: placeholders bind values, whereas ASC and DESC are SQL syntax.

This in-memory fixture uses the requested direction sideways. Its naive f-string constructs ORDER BY points sideways; SQLite raises OperationalError. That failure demonstrates the concrete malformed query, but it is not validation. The corrected order_clause() raises ValueError before execute() receives an unsupported direction, then maps asc and desc to the literal, application-owned tokens ASC and DESC.

The output prints the table schema and deterministic seed rows, followed by both outcomes. Assertions establish that the rejected input never produces a clause and that the accepted directions return the expected opposite orderings. A score threshold or other data value should still be passed through ? placeholders, as the Python documentation recommends. This narrow allowlist does not make arbitrary dynamic SQL safe: selectable columns, table names, collations, and expressions need separate constraints designed for the application.

The example requires Python 3 and the standard-library sqlite3 module. It uses only :memory: data, so no external database is involved.

AI-assistance disclosure: Batu Lab Notes used AI assistance to draft and check this synthetic example.

Sources: Python sqlite3 placeholders and Python sqlite3 Cursor.execute.

import sqlite3


def order_clause(requested):
    allowed = {"asc": "ASC", "desc": "DESC"}
    try:
        return allowed[requested.lower()]
    except KeyError:
        raise ValueError("direction must be asc or desc") from None


con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, points INTEGER)")
con.executemany(
    "INSERT INTO scores VALUES (?, ?)",
    [("Ada", 8), ("Bryn", 3), ("Cy", 5)],
)

schema = con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'scores'"
).fetchone()[0]
seed_rows = con.execute("SELECT name, points FROM scores ORDER BY name").fetchall()
requested = "sideways"
naive_sql = f"SELECT name FROM scores ORDER BY points {requested}"

print(f"schema: {schema}")
print(f"seed rows: {seed_rows}")
print(f"naive SQL: {naive_sql}")
try:
    con.execute(naive_sql)
except sqlite3.OperationalError:
    print("naive result: OperationalError")

try:
    order_clause(requested)
except ValueError as error:
    print(f"allowlist result: {error}")

ascending = con.execute(
    f"SELECT name FROM scores ORDER BY points {order_clause('asc')}"
).fetchall()
descending = con.execute(
    f"SELECT name FROM scores ORDER BY points {order_clause('desc')}"
).fetchall()
assert ascending == [("Bryn",), ("Cy",), ("Ada",)]
assert descending == [("Ada",), ("Cy",), ("Bryn",)]
print(f"corrected asc: {ascending}")
print(f"corrected desc: {descending}")
con.close()

schema: CREATE TABLE scores (name TEXT, points INTEGER)
seed rows: [('Ada', 8), ('Bryn', 3), ('Cy', 5)]
naive SQL: SELECT name FROM scores ORDER BY points sideways
naive result: OperationalError
allowlist result: direction must be asc or desc
corrected asc: [('Bryn',), ('Cy',), ('Ada',)]
corrected desc: [('Ada',), ('Cy',), ('Bryn',)]