SQL / SECURITY, ROUTINES, AND DIALECTS
How SQL injection turns input into code
You can trace how a pasted-in value re-parses as SQL grammar, tell which slots are injectable, and predict what a payload does before running it.
What you will learn
- Read a concatenated query as text and find where the value ends the string literal
- Explain why `' OR 1=1 --` changes the parse tree, not just the result
- Spot injectable slots with no quotes: numeric, identifier, ORDER BY, LIMIT
- Predict escalation paths: widened WHERE, UNION reads, stacked statements, blind probes
Understanding How SQL injection turns input into code
A database engine never sees your program's variables. Your client hands it one flat sequence of characters, and the engine tokenises that text: SELECT is a keyword, users is an identifier, and everything between a matched pair of single quotes is one string literal. Nothing in that text records where each character came from, so when a value you pasted in contains a quote, the tokeniser reads it as the end of the literal and parses the characters after it as more SQL. That is the entire mechanism, and it happens at parse time, before a single row is touched.
It follows that injection is not about dangerous words but about which slot the value lands in. Inside a string literal an attacker needs one quote to get out; in a numeric comparison, an ORDER BY position, or a table name there is no literal to leave, and plain text such as 1 OR 1=1 is already valid grammar there. The payload ' OR '1'='1 works because it converts a parse tree meaning "name equals this one string" into an OR whose second branch is a tautology; the engine then behaves exactly as specified, on a query you never intended to write.
Once input can contribute grammar, the ceiling is the SQL language rather than your query. A UNION SELECT with a matching column count returns rows from unrelated tables through the same result set, a subquery inside a boolean condition leaks data one comparison at a time even when the page prints nothing (which is why hiding error messages removes evidence, not the hole), and a semicolon starts a second statement wherever the driver forwards more than one. Keyword and character filters lose this race because the same parse tree can be spelled many ways: different case, comments wedged inside the statement, OR 2>1 instead of OR 1=1. The fix therefore has to keep the value out of the text the parser sees instead of inspecting the value.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, is_admin INTEGER);
INSERT INTO users VALUES (1, 'alice', 0), (2, 'bob', 0), (3, 'root', 1);
-- The application builds: "SELECT id, name FROM users WHERE name = '" + input + "'"
-- input: bob
SELECT id, name FROM users WHERE name = 'bob';
-- input: x' OR 1=1 --
SELECT id, name FROM users WHERE name = 'x' OR 1=1 -- '
; -- the injected -- swallowed the app's own closing quote, so the ; moved down a lineThe engine compiles the finished string it receives, and nothing in that string marks which characters came from user input, so interpolated values are parsed as code.
Worked examples
The text that reaches the parser
Builds the query string with SQL concatenation so you can read what the engine would receive for a harmless value and for a payload.
CREATE TABLE form (label TEXT, raw TEXT);
INSERT INTO form VALUES
('harmless', 'bob'),
('payload', 'x'' OR ''a''=''a');
SELECT label,
'SELECT id FROM users WHERE name = ''' || raw || '''' AS sent_to_parser
FROM form;Example explained
Line 1The doubled quotes in 'x'' OR ''a''=''a' are only SQLite's way of storing one literal quote, so the stored value is the exact characters someone types into a form field.
Line 2The || expression does the same string building the application does; nothing is executed, which lets you read the finished statement.
Line 3In the payload row the value's first quote closes the literal the application opened, and OR 'a'='a lands in the WHERE clause as grammar.
Line 4The app's own trailing quote now closes the attacker's 'a instead of the name, so the statement is still perfectly valid SQL.
A numeric slot needs no quote at all
Shows that when the value is interpolated outside any literal, escaping quotes protects nothing.
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT);
INSERT INTO orders VALUES (1, 'alice'), (2, 'bob'), (3, 'root');
-- The application builds: "SELECT id, customer FROM orders WHERE id = " + input
-- input: 2
SELECT id, customer FROM orders WHERE id = 2;
-- input: 2 OR 1=1
SELECT id, customer FROM orders WHERE id = 2 OR 1=1;Example explained
Line 1There is no literal around the value, so the payload contains no quote and any quote-escaping step leaves it untouched.
Line 2id = 2 OR 1=1 parses as (id = 2) OR (1 = 1) because = binds tighter than OR, and the tautology makes every row match.
Line 3Positions where quoting a value is not even legal, such as ORDER BY <column> or LIMIT <n>, behave the same way.
Input that becomes a second statement
Demonstrates a semicolon in the value ending the intended statement and starting a new one.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'alice'), (2, 'bob'), (3, 'root');
SELECT COUNT(*) FROM users;
-- input: x'; DELETE FROM users; --
SELECT id FROM users WHERE name = 'x'; DELETE FROM users; -- '
SELECT COUNT(*) FROM users;Example explained
Line 1The quote ends the name literal and the semicolon ends the statement, so the value has supplied a statement boundary rather than a condition.
Line 2The trailing -- comments out the application's closing quote, which is what keeps the whole string parseable.
Line 3The first SELECT finds nothing and DELETE prints nothing, so the page can honestly report "no results" while the table has been emptied.
Line 4sqlite3 runs every statement in the script; many drivers send one statement per call, which is why this shape often fails where OR 1=1 still succeeds.
Important notes
Comment syntax is dialect-specific: MySQL requires whitespace after --, and #, /* */ also comment, so payload shapes that fail on one engine work on another.
Run these examples only against a throwaway database; the DELETE in the third example really removes the rows.
Common mistakes
Blocking words like DROP or UNION and the ; character, then assuming the input is clean: ' OR 1=1 -- contains none of them and still bypasses a login check.
Escaping quotes carefully but interpolating numbers, sort columns, or table names unquoted, leaving those slots injectable because no quote is required to break out.
Treating a read-only query or a page that hides errors as safe, when a widened WHERE returns other users' rows and blind techniques recover data from row counts or response delays.
Try it yourself
Change, predict, then run
In a browser SQLite editor, recreate the users table from the main example and write the exact statement produced by the input x' OR is_admin = 1 -- , checking that it returns only the root row. Then, for the numeric form WHERE id = <input>, find a payload containing no quote that returns every row.
Open the SQL workspaceCheck your understanding
An endpoint builds SELECT * FROM orders WHERE id = followed directly by whatever the user typed, because id is numeric. The developer doubles every single quote in the input first. Why is the endpoint still injectable?
- Escaping only protects single-quoted strings, and the parser also accepts double-quoted strings that slip past the filter.
- The engine undoes the doubled quotes while planning the query, so the original quote characters come back.
- The value is not inside a literal at all, so 2 OR 1=1 is already valid grammar in that position without any quote.
- Numeric columns are compared as text internally, so the escaping is discarded before the comparison happens.
Show answer
Quote doubling only means anything while the value sits between quotes; in a numeric slot the typed characters are parsed straight into the WHERE expression, so id = 2 OR 1=1 becomes (id = 2) OR (1 = 1) and every row matches. Option 2 is tempting because people imagine an unescaping step, but no such step exists: a doubled quote is one data character only inside a literal, and here there is no literal.