Databases · Lesson 3.1
How your typing ends up in a query
Server A sends Postgres its queries over its connection, the open line it logged in on.
And each of those queries is just text, written in SQL.
Now say Ada opens Simplified's login page, and types her email: ada@simplified.org.
To log her in, server A needs her id, so it asks Postgres for her row.
Her password gets checked too, but leave that aside here.
But server A's query was written long before Ada typed anything.
So how does the email Ada typed get into that query?
Say Simplified's login builds the query from two pieces of text.
The first piece is SELECT id FROM users WHERE email equals, and then a single quote.
The second piece is a closing single quote, then the semicolon that ends the query.
Between them is a gap, for whatever is typed into the email box.
What someone types into a box on a page is called input.
So server A pastes Ada's input, her email, into the gap, between the two quotes.
Now the query reads: WHERE email equals ada@simplified.org, in single quotes.
Text in a condition goes in single quotes, to mark where it starts and ends.
Server A sends that query to Postgres, over its connection.
Postgres checks each row's email, and only Ada's matches.
So one row comes back, with Ada's id: 1.
Now say Simplified also has a search box, for finding lessons.
So how might the words you search for get into its query?
The same way: server A pastes them into a gap in the query's text.
Say you type replicas, to find the lessons on replicas.
This query uses LIKE, so the gap sits between two percent signs, inside the quotes.
The percent signs let a title have anything before or after your words.
So it reads: WHERE title LIKE percent replicas percent, in single quotes.
And server A sends that query to Postgres, just as it did with Ada's.
So with this login, whatever someone types goes straight into the query's text, between the quotes.
Say Simplified's login builds its query from two pieces of text, SELECT id FROM users WHERE email = ' and ';, and pastes what you type between them. Ben types ben@simplified.org into the email box.
What query text does server A send?
Explanation
First, server A starts with the first piece, which ends on an opening quote.
Then, it pastes Ben's email into the gap, and adds the closing quote and the semicolon.
Therefore, server A sends SELECT id FROM users WHERE email = 'ben@simplified.org';.
Say Simplified's login builds its query from two pieces of text with a gap between them, SELECT id FROM users WHERE email = ' and ';, and pastes what you type into the gap.
Why doesn't the query already have your email in it?
Databases · Lesson 3.2
What if someone types a quote?
So with this login, whatever you type goes into its query, between two quotes.
And those quotes are what tell Postgres where the email starts and ends.
So what if someone types a quote of their own into the email box?
Say someone types this into the email box, instead of an email.
It's a single quote, then OR, then 1 equals 1, with each 1 in single quotes.
But the very last quote is left off.
Server A pastes it into the gap, as it does with every email.
Postgres reads the query from left to right.
Quote 1, the server's own, just before what was typed, opens the email.
But quote 2, the typed one, comes straight after it, so it closes the email.
So the email is empty: there's nothing between those two quotes.
The rest of what was typed is now outside the quotes, so Postgres reads it as SQL, not as text.
And quote 3, the server's own last quote, closes that last 1.
So the WHERE now has two conditions: email equals empty text, OR 1 equals 1.
Which rows pass that WHERE, then?
No learner has an empty email, so every row fails the first condition.
But 1 equals 1 is true for every row, because it never looks at the row at all.
And OR keeps a row if it passes either condition.
So every row passes, and Postgres sends back every id: all 2,000,000.
That means a query meant for one learner's row sent back every learner's id, and nobody typed a real email.
Input that becomes part of the query like this is called SQL injection.
SQL injection means input that becomes part of the query, so Postgres runs it as SQL.
So what else could someone type into that email box?
Say they type a quote, then a semicolon, then DELETE FROM users WHERE 1 equals 1.
The typed quote closes the email, and the semicolon ends the SELECT.
So what comes after it is a second query: a DELETE of their own.
Its WHERE, 1 equals 1, is true for every row, so Postgres deletes every learner's row.
Wherever input is pasted into a query's text, a typed quote can turn the rest into SQL.
Say Simplified's login pastes what you type between two quotes: SELECT id FROM users WHERE email = ' + what you typed + ';. Its users table holds 2,000,000 learners, and none has an empty email. Someone types ' OR '1'='1 into the email box.
How many ids does Postgres send back?
Explanation
First, the typed quote closes the email early, so the query reads SELECT id FROM users WHERE email = '' OR '1'='1';.
Then, no row has an empty email, but '1'='1' is true for every row, and OR keeps a row that passes either condition.
Therefore, Postgres sends back all 2,000,000 ids.
Say Simplified's lesson page finds a lesson by its title, pasting what you type between two quotes: SELECT id FROM lessons WHERE title = ' + what you typed + ';. Its lessons table has 100 rows, and no lesson is titled x. Someone types x' OR 'a'='a into the title box.
How many ids does Postgres send back?
Say Simplified's login pastes what you type between two quotes. Someone types eli@simplified.org' OR '1'='1, so server A sends SELECT id FROM users WHERE email = 'eli@simplified.org' OR '1'='1';.
Which quote closes the email?
Say a site's login pastes what you type into its query, between two quotes. Four people use it.
Which of them is doing SQL injection?
Say Simplified's server sends SELECT id FROM lessons WHERE title = '' OR 'a'='a';. No lesson has an empty title.
Why does every row pass the WHERE?
Say Simplified's login pastes what you type between two quotes: SELECT id FROM users WHERE email = ' + what you typed + ';. Someone types '; DELETE FROM attempts WHERE '1'='1.
What does Postgres do?
Say Simplified's login builds its query from SELECT id FROM users WHERE email = ', then what was typed, then ';. Server A sends SELECT id FROM users WHERE email = 'ben@simplified.org' OR '1'='1';.
What was typed into the email box?
Databases · Lesson 3.3
Input that can never become SQL
So when server A pastes what someone types into the query, a typed quote can close the email early.
Then the rest runs as SQL, and the login sends back all 2,000,000 ids.
So how can server A send what Ada types, without it ever becoming SQL?
Server A stops pasting the email into the query's text.
Instead, the query has a placeholder where the email goes: dollar sign 1.
A placeholder is a spot in the query that a value fills in later.
And server A sends the email on its own, alongside the query, not inside it.
A value sent apart from the query like this is called a parameter.
A parameter is a value sent separately, to fill a placeholder in the query.
So what does Postgres do with the query and its parameter?
First, Postgres reads the query, with dollar sign 1 still in it.
So it knows which parts are SQL before it sees any value.
Only then does it put Ada's email in, where dollar sign 1 is.
So her email is only ever a value: the email to compare each row with.
Ada's row matches, so Postgres sends back her id: 1, just as before.
So what happens now if someone types that quote of their own?
Server A sends the same query, and what was typed as its parameter, quotes and all.
Postgres reads the query first, so its only condition is email equals dollar sign 1.
Then it puts the whole typed text in, as one email.
The quotes inside it are just part of that email, not SQL.
No learner has that odd email, so 0 rows come back.
Now say Ada's dashboard counts her answers in lesson 3, from the attempts table.
That query needs two values: her id, 1, and the lesson's id, 3.
So how does one query take two parameters?
It gets two placeholders, numbered in order: dollar sign 1 and dollar sign 2.
Server A sends two parameters with it: 1 first, then 3.
Postgres puts the first into dollar sign 1, and the second into dollar sign 2.
So it counts Ada's answers in lesson 3, and neither value can become SQL.
With parameters, what someone types only ever fills in a value, so it never becomes SQL.
Simplified's login sends Postgres SELECT id FROM users WHERE email = $1;, with what was typed into the email box as its parameter. Its users table holds 2,000,000 learners, each with an email like ada@simplified.org. Someone types ' OR '1'='1.
How many rows come back?
Explanation
First, Postgres reads the query before the parameter, so its one condition is email = $1.
Then, it puts the whole typed text in as one email, and no learner's email is ' OR '1'='1.
Therefore, 0 rows come back.
Simplified's login sends SELECT id FROM users WHERE email = $1;, with what was typed into the email box as its parameter. Its users table holds 2,000,000 learners, and Ada's email is ada@simplified.org. Someone types ada@simplified.org' OR '1'='1.
How many rows does Postgres send back?
Simplified's login sends Postgres SELECT id FROM users WHERE email = $1;, and, separately, cy@simplified.org, to fill $1.
What is cy@simplified.org called here?
Simplified's login sends SELECT id FROM users WHERE email = $1;, with what was typed into the email box as its parameter.
Why can't a quote typed into the box close the email early?
Simplified's server sends SELECT COUNT(*) FROM attempts WHERE user_id = $1 AND lesson_id = $2;, and with it two parameters: 4 first, then 7.
Whose answers, in which lesson, does Postgres count?
Simplified's login sends SELECT id FROM users WHERE email = $1;, with what was typed into the email box as its parameter. Someone types '; DELETE FROM users WHERE '1'='1.
What does Postgres do?
Simplified's login sends SELECT id FROM users WHERE email = $1;, with what was typed into the email box as its parameter.
What can someone change by what they type into that box?