Parameterized queries: the right defence against SQL injection

SQL injection is one of the best-known vulnerabilities in web applications, yet it keeps appearing in new code. The cause is almost always the same: building a query by joining fixed text with data supplied by the user. The solution has been known for a long time and is easy to apply, provided you understand why it works and where it stops.

The problem: data that becomes code

Imagine a function that looks up a customer by email:

query = "SELECT * FROM customers WHERE email = '" + email + "'"
cursor.execute(query)

As long as the user types a normal address, everything works. But the database receives a single string and has no way of telling the part written by the programmer from the part that came from the form. If the value entered contains an apostrophe, the apostrophe closes the string and whatever follows is interpreted as SQL. The data has become code.

Even without malicious intent, concatenation causes errors: a surname like O'Brien breaks the query. This is often the first sign that the code is vulnerable.

The solution: parameters

With a parameterized query, the query text and the values travel separately:

cursor.execute(
    "SELECT * FROM customers WHERE email = %s",
    (email,)
)

The %s placeholder (in other libraries ?, :email or $1) tells the database that a value will go in that position. The database first parses the structure of the query, then receives the value and always treats it as data only, whatever characters it contains. An apostrophe stays an apostrophe inside a string; it cannot close anything.

The placeholder must not be confused with the language's string formatting. This form is still vulnerable, even though it looks similar:

# WRONG: this is still concatenation
cursor.execute("SELECT * FROM customers WHERE email = '%s'" % email)
cursor.execute(f"SELECT * FROM customers WHERE email = '{email}'")

The difference lies in passing the values as a separate argument to the execute function, not in how the placeholder looks.

Why manual escaping is not enough

A solution often seen is to «clean» the input by removing or doubling apostrophes. It is fragile for several reasons:

• escaping rules depend on the database, the character encoding and the configuration;
• forgetting a single query out of a hundred is enough to leave the door open;
• numeric values are not in quotes, so removing quotes achieves nothing;
• it makes the code harder to read and to review.

Parameters shift the responsibility to the driver and the database, which know the exact rules.

What cannot be parameterized

Parameters replace only values. They cannot replace table names, column names, keywords such as ASC or DESC, or parts of the query structure. The typical case is a sort order chosen by the user in a table:

# Does not work: a column name is not a value
cursor.execute("SELECT * FROM products ORDER BY %s", (column,))

Here the correct solution is an allow-list:

ALLOWED_COLUMNS = {"name": "name", "price": "price", "date": "created_at"}
DIRECTIONS = {"asc": "ASC", "desc": "DESC"}

col = ALLOWED_COLUMNS.get(requested_column, "name")
dir_ = DIRECTIONS.get(requested_direction, "ASC")
cursor.execute(f"SELECT * FROM products ORDER BY {col} {dir_}")

The text inserted into the query always comes from the dictionary written by the programmer, never directly from the user.

The IN clause

A variable-length list of values, such as WHERE id IN (...), is handled by generating the right number of placeholders and passing the values as parameters. Many libraries and ORMs do this automatically.

ORMs and query builders

ORMs, the libraries that map objects to tables, use parameters internally and protect most operations. Almost all of them, however, offer a way to write raw SQL for complex queries. In those places exactly the same rules apply: if you concatenate, you are vulnerable again. In my work, during a code review, these are the very first places to check.

Defences in the database

Parameterized queries are the main defence, but it is wise to limit the damage in case something slips through:

1. Least-privilege users. The application must not connect as an administrator. A user who can only read and write the tables it needs cannot drop the database or read system tables.
2. Separate users for different functions. A read-only module can use a user with only the SELECT permission.
3. Generic error messages. Detailed database errors belong in the logs, not on the user's screen, because they reveal the structure of the tables.
4. Automated tests. Include values with apostrophes, semicolons and special characters in tests, checking that they are saved and read back unchanged.

In short

• SQL injection comes from concatenating user data into the query text.
• Parameterized queries send structure and values separately: the value always remains data.
• The language's string formatting is not a parameterized query.
• Column names and sort directions are handled with allow-lists.
• Database users with minimal privileges limit the damage of any mistake.