Part 4 of our PortSwigger Web Security Academy series. This one covers blind SQL injection with conditional responses, which is the easier of the two blind SQLi flavors we work through in this series.

Wiki notes: SQL Injection/Blind.

The Setup

Some SQL injection vulnerabilities never give you a direct channel back for the query results. The application runs a SQL query with your input, but the response doesn't render the result or leak database errors. The canonical example is a cookie tracking ID: the ID gets used in a SQL query on every request, but the query output is not rendered anywhere on the page.

You can still exploit it. The technique is called blind SQL injection, because you can't see the query result directly, but you can infer it from other things the page does.

The Boolean Oracle

The version we cover in this post relies on the page rendering differently depending on whether the injected query returns a row. Classic PortSwigger example: a page that shows "Welcome back" if the tracking ID matches a valid user in the database, and doesn't otherwise.

Send this request:

TrackingId=xyz' AND '1'='1

The AND '1'='1' is unconditionally true, so if the outer query would have matched anything, it still matches. Welcome-back banner shows up.

Now send this one:

TrackingId=xyz' AND '1'='2

The AND '1'='2' is unconditionally false, so no rows match. No welcome-back banner.

Two requests. Two different pages. That's your boolean oracle. Any yes/no question you can encode as a SQL condition, you can ask the database and get an answer via page behavior.

Extracting a Password One Character at a Time

Once you have the oracle, the game is to encode useful yes/no questions. The useful one is "is the first character of the administrator's password greater than X?"

TrackingId=xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

If the welcome-back banner appears, the first character is greater than m. If not, it's less than or equal to m.

Binary-search the alphabet:

• Is char 1 > m? Yes → search n through z
• Is char 1 > s? Yes → search t through z
• Is char 1 > v? No → search t through v
• Is char 1 > u? No → search t through u
• Is char 1 > t? No → char 1 is t

Repeat for character 2. Then character 3. And so on until the password is in your notebook.

Automating It In Burp Suite

Doing this by hand for a 20-character password is not something a normal person will finish. Burp Intruder does the automation.

The recipe:

1. Right-click the request in Burp, "Send to Intruder"
2. In Intruder → Positions, mark the m (the letter being tested) as the payload position
3. In Intruder → Payloads, set payload type to "Brute forcer" and give it the alphabet you care about (abcdefghijklmnopqrstuvwxyz plus digits and any symbols the password might contain)
4. Run the attack. Sort results by response length or by whether the welcome-back string appears in the body. The one match is your answer.

For 20 characters, you would normally repeat this 20 times. Or, better, use Intruder's "Cluster bomb" attack type with two payload positions (one for character index, one for the letter), which runs all combinations in one go. Cluster bomb is worth learning - it comes up a lot in this kind of extraction work.

When It Doesn't Work

Conditional-response blind SQLi is not always available. Sometimes the page renders identically no matter what the query returns. When that happens, you fall back to either

• Conditional errors - deliberately cause a SQL error when your boolean condition is true, and use HTTP 500 vs 200 as the oracle. This is the next post in the series.
• Time-based - deliberately make the query sleep for a few seconds when your boolean is true, and use response time as the oracle. Useful when even errors are suppressed.

The theme is: as long as anything the server does depends on the truth of your boolean, you have an oracle. The rest is engineering.

References

• Our wiki: SQL Injection/Blind
• PortSwigger blind SQL injection
• Lab 11 - Blind SQLi with conditional responses

Part 3 of our PortSwigger Web Security Academy series. Part 2 sketched the four SQL injection shapes at a bird's-eye view. This post drills into one of them: the UNION attack. It is one of the most useful shapes to understand because it converts a small SQLi foothold into an "I can read anything in the database" primitive.

Wiki reference: SQL Injection/UNION Attack.

The Idea

Say a page runs

SELECT name, description FROM products WHERE category = 'Gifts'

The columns you can see in the response are name and description, because that is what the query is selecting. UNION lets you attach a second SELECT to the query whose results get returned alongside the first. If we inject

' UNION SELECT username, password FROM users--

the query becomes

SELECT name, description FROM products WHERE category = ''
UNION SELECT username, password FROM users--'

and the page renders product names, product descriptions, usernames, and passwords, all in the same result set.

Simple enough in principle. The devil is in getting the UNION to actually be valid SQL.

Two Requirements for a Valid UNION

SQL is picky about UNIONs. The two SELECTs on either side of the UNION have to agree on:

1. The number of columns. If the first query returns 2 columns, your injected SELECT also has to return exactly 2 columns.
2. The data types of the columns. In practice, this means "the column you want to display text from has to line up with a column position that the outer query returned as text-compatible."

Most of the work of pulling off a UNION attack is figuring these two things out from the outside.

Finding the Column Count

Two techniques.

Technique 1: ORDER BY bumping. Send

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
' ORDER BY 4--

and so on. ORDER BY N tells the database to sort by column N. If N exceeds the actual column count, you get a SQL error. So you increment until the error appears - the last value that didn't error is the column count.

Technique 2: UNION SELECT NULL bumping. Send

' UNION SELECT NULL--
' UNION SELECT NULL, NULL--
' UNION SELECT NULL, NULL, NULL--

and so on. NULLs are type-compatible with anything, so the only thing that will make the UNION fail is a column count mismatch. When the query stops erroring, you have found the column count.

ORDER BY is usually faster because you can binary-search it. UNION SELECT NULL is the fallback when the target database is picky about ORDER BY in weird ways.

Finding a Text-Compatible Column

Once you know the column count, you need a column whose position will render as text in the response. If the outer query returns 3 columns and only one of them shows up in the rendered page, you want to know which one.

Send

' UNION SELECT 'a', NULL, NULL--
' UNION SELECT NULL, 'a', NULL--
' UNION SELECT NULL, NULL, 'a'--

Whichever variant makes the letter a show up on the rendered page is your target column position. If the database complains about 'a' being the wrong type for that position, that column is not text-compatible and you have to move on to the next one.

Putting It Together

Now you have a column count and a text-compatible position. Say the count is 3 and position 2 is text-compatible. Then

' UNION SELECT NULL, username, NULL FROM users--

dumps every username, and

' UNION SELECT NULL, password, NULL FROM users--

dumps every password.

You can also concatenate multiple values into a single column, which is useful when only one column position is text-compatible. Depending on the database:

• Oracle: || concatenation - ' UNION SELECT NULL, username || '~' || password, NULL FROM users--
• MySQL: CONCAT() or space-separated string literals
• Microsoft SQL Server: + concatenation

Finding Interesting Tables

At this point you probably want to know which tables exist and what columns they have. Every major database exposes this through metadata tables. On most (Postgres, MySQL, Microsoft SQL Server):

' UNION SELECT table_name, NULL FROM information_schema.tables--
' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = 'users'--

Oracle uses all_tables and all_tab_columns instead of information_schema, because Oracle is Oracle.

The Fix

Same fix as every SQL injection: parameterized queries. The parameters never become part of the query syntax, so no UNION, no --, no OR 1=1 is going to change the shape of the query. It becomes data, not code.

References

• Our wiki: SQL Injection/UNION Attack
• PortSwigger UNION attacks
• Labs 3-6 in the PortSwigger SQL Injection Academy

This is part 2 of our series on working through the PortSwigger Web Security Academy. This one covers SQL injection at a bird's-eye view - the four main attack shapes that show up over and over, with the smallest possible example of each. Later posts will drill into specific attack types.

Full notes on our wiki: SQL Injection.

What SQL Injection Is

SQL injection is a web security bug that lets attackers execute their own SQL against your database, by taking advantage of user inputs that are not sanitized before being pasted into a SQL query.

The reason it is worth caring about is impact vs. effort. SQL injection is one of the highest-impact web vulnerabilities (attacker can potentially read or modify anything in the database), and one of the lowest-effort to actually pull off. That combination is why it has been in the OWASP Top 10 since forever.

The Four Shapes

Our notes group SQL injection into four attack shapes:

1. Retrieving hidden data
2. Subverting application logic
3. UNION attacks
4. Blind SQL injection

The rest of this post walks through the smallest example of each.

Shape 1: Retrieving Hidden Data

Suppose a shopping site has this URL for showing product listings:

https://insecure-website.com/products?category=Gifts

Behind the scenes, that URL runs this SQL query:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

The released = 1 clause hides unreleased products from the public. If the category parameter is not sanitized, we can smuggle in SQL that comments out the rest of the query:

https://insecure-website.com/products?category=Gifts'--

The -- starts a SQL comment. Everything after it is ignored, including the AND released = 1 check. Result: all products, including unreleased ones, get returned.

Same trick with an unconditional truthy clause:

https://insecure-website.com/products?category=Gifts'+OR+1=1--

Shape 2: Subverting Application Logic

Same idea, applied to authentication. If a login form runs

SELECT * FROM users WHERE username = 'user' AND password = 'nopass'

and doesn't sanitize the username field, you can log in as anyone by supplying the username:

administrator'--

Everything after the -- (including the entire password check) becomes a comment. The query returns the administrator row. You are logged in.

You have to guess the username - admin, administrator, root, superuser - so this is not a one-shot attack, but it is not a hard attack either. If you see a login page throwing internal server errors when you put a single quote in the username field, that is your signal.

Shape 3: UNION Attacks

UNION attacks use SQL's UNION operator to piggyback data from other tables into the results of the original query. If a shopping site runs

SELECT name, description FROM products WHERE category = 'Gifts'

and the category parameter is injectable, we can attach a UNION:

' UNION SELECT username, password FROM users--

The final query becomes

SELECT name, description FROM products WHERE category = ''
UNION SELECT username, password FROM users--'

which returns product listings and every username/password pair. Whatever UI was going to display product name and description now also displays usernames and passwords.

The oversimplified version above works out cleanly, but in practice you need to figure out the column count of the outer query first, then make sure the columns you union in are type-compatible. We covered that in the next post.

Shape 4: Blind SQL Injection

Blind SQLi is what you do when the application is vulnerable but the query results don't come back to you in the HTTP response. Cookie tracking IDs are the classic example - the ID gets fed to a SQL query on every request, but the query result is never rendered on the page.

Even without seeing the result directly, you can often infer it from how the page behaves. Ship one of these two requests:

xyz' AND '1'='1
xyz' AND '1'='2

If the page renders differently in the two cases (say, one shows a "Welcome back" banner and the other doesn't), congratulations, you have a boolean oracle. Now you can ask the database yes/no questions and get answers via page behavior. Wrap the boolean around anything you want to know:

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

That request returns "Welcome back" if the first character of the administrator's password is greater than m, and doesn't otherwise. Do that in a binary search per character, and you have the whole password.

Blind SQLi comes in a few flavors - conditional responses (above), conditional errors, and time-based. We cover the first two in the next couple of posts.

What Ties Them Together

Every one of these attacks is a variation on the same theme: a user input is being pasted directly into a SQL query, and the attacker can supply SQL syntax that changes the meaning of the query. The fix is the same in every case: parameterized queries. Not string concatenation. Not escaping. Parameterized queries.

References

• Our wiki: SQL Injection
• PortSwigger SQL Injection cheat sheet
• Web Security Academy - SQL Injection