SQL injection to a parameterised query, drawn

Quick reference

The string concat

• Code and data share one string
• WHERE name = '" + name + "'
• The database cannot tell them apart

The payload

• ' OR '1'='1' -- returns every row
• ' UNION SELECT password FROM users --
• The comment swallows the trailing quote

The prepared statement

• SELECT \* FROM users WHERE name = ?
• The driver sends SQL and values separately
• The value can never become syntax

The allow-list

• ORDER BY name is not a bindable parameter
• Map sort=name to a fixed column list
• Reject anything not on the list

Explained

The string concat

The code builds SELECT \* FROM users WHERE name = '" + name + "'. The value is pasted into the statement before the database parses it. The parser cannot tell the developer's quote from the attacker's quote, because by then there is no difference.

The payload

Send ' OR '1'='1' -- and the WHERE clause becomes true for every row; the comment swallows the trailing quote the developer added. A UNION SELECT can pull a password column into the response. No special tool is needed, only a parameter the app trusts.

The prepared statement

The fix is to send the SQL and the values separately: SELECT \* FROM users WHERE name = ?. The driver parses the statement with a placeholder, then binds the value. The value is data at every stage and can never become syntax, even if it contains quotes, comments or a UNION.

The allow-list

Binding has a boundary: identifiers cannot be parameters. ORDER BY ? does not sort by the column you pass, and a table name cannot be bound. Map the user's sort value to a fixed list of allowed columns and directions, and reject everything else.

In one line

Test the param

$curl -si "$API/q?x=%27"