SQL injection
How an input pasted into a query changes that query: bypassed login, reading other tables with UNION, destruction; the prepared statement, least privilege for the SQL account and the false defences.
- 24 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
A few cards from the deck
SQL injection: what is it?
Show the answer
An attack where an input, pasted as is into a query, is read by the database as SQL code rather than as a plain value.
The flaw comes from building the query by concatenation: the database cannot tell where the programmer's code ends and the input begins.
The site builds the query SELECT * FROM users WHERE login = '<input>' AND pwd = '<password>'. A visitor enters ' OR '1'='1 as the password for the login alice. What does the database do?
- It returns every row, because the final condition is always true
- It rejects the query with a syntax error, because the apostrophe closes the string badly
- It returns only alice's row, whose password is then no longer compared at all
- It returns zero rows, because the string OR is not a password
Show the answer
It returns every row, because the final condition is always true
The query received is ... WHERE login = 'alice' AND pwd = '' OR '1'='1'. AND binds before OR: the condition is (login = alice AND empty pwd) OR (always true), so every row comes back, and the program often logs in the first one, frequently the administrator.
An SQL injection becomes possible when the program builds the query by … text with an unchecked input.
Show the answer
An SQL injection becomes possible when the program builds the query by concatenating text with an unchecked input.
That glue between code and input is the fault: filtering the input does not fix it, separating code and values does.
Prepared statement: what is it?
Show the answer
A query sent to the database with placeholders in place of the values, then executed with the values supplied separately.
The structure is fixed before the input arrives: whatever it contains, it can no longer change the shape of the query.
The server log shows requests to article.php?id=1 AND 1=1, then id=1 AND 1=2, then id=1 UNION SELECT NULL. The page changes with the condition. What is happening, and what must be fixed?
- An SQL injection probe: replace the concatenation of id with a prepared statement
- A normal crawler: simply limit its request rate and let it be
- A denial-of-service attack: add a firewall in front of both the web server and the database
- A cache error: clear the page cache on each request received by the server
Show the answer
An SQL injection probe: replace the concatenation of id with a prepared statement
That the page reacts to AND 1=1 and AND 1=2 proves the input is interpreted as SQL. The attacker is measuring the flaw before reading data. The fix is in the code: a prepared statement, id converted to an integer.