Apps do not type queries by hand. They build them from what a user enters. Here is a login form and the query an app builds from it:

username: ava
password: kraken123

SELECT id, username FROM users WHERE username = 'ava' AND password = 'kraken123';

The app pastes the two inputs between the quotes. One row back means "logged in".

Input that changes the query

An attacker types this as the password:

' OR '1'='1

The app pastes it in, and the query becomes:

SELECT id, username FROM users WHERE username = 'ava' AND password = '' OR '1'='1';

AND runs before OR, and '1'='1' is true. So the condition is true for every row, and the app logs the attacker in as the first user it gets back. This is SQL injectionSQL injectionAn attack where input is pasted into a SQL statement and changes what the statement does.

Other inputs do more damage. -- starts a comment: PostgreSQL ignores the rest of the line. A ; can add a second statement, such as a DELETE.

The fix: parameters

Do not paste input into SQL. Send the query with placeholders, and send the values apart from it. PostgreSQL parses the query first, so a value can never become SQL.

PREPARE login(text, text) AS
SELECT id, username FROM users WHERE username = $1 AND password = $2;

EXECUTE login('ava', 'kraken123');

• $1 is the first value, $2 the second.
• EXECUTE login(…) runs it with values. The attacker's text is now just a password that does not match:

EXECUTE login('ava', ''' OR ''1''=''1');   -- 0 rows

Inside a text value, '' is one quote character. This value is the attacker's input, quotes and all.

• DEALLOCATE login; removes it. A prepared statement lasts until the session ends.

App code does the same with its database library: db.query("… WHERE username = $1", [name]). Every language has this. Use it for every value from outside, every time.