The Lab

Here is how PortSwigger describes it.

This lab contains a SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned. The database contains a different table called users, with columns called username and password. To solve the lab, find a way to leak the password for the administrator user, then log in to their account.

Looking at the Lab#

This one sits in the same family as the last post, Blind SQL injection with conditional errors, except this time the error message itself is going to do the talking. So I won't repeat the whole discovery process step by step, it's covered there. Short version, I dropped a single quote into the TrackingId cookie.

TrackingId=ogAZZfxtOKUELbuJ'

That threw a 500 error. Then I closed it back up with a second quote.

TrackingId=ogAZZfxtOKUELbuJ''

That came back clean, confirming the cookie value lands straight inside a SQL string and we've got our injection point.

Step 1 - Set Up the Cast#

Now, instead of just using errors as a plain yes/no switch like the last lab, this technique goes a step further. The idea here is that if we can force the database to try converting text into a number, and that text isn't actually numeric, the database throws an error that includes the exact value it choked on. That's the real target, an error message that leaks data on its own.

To get there we first need to build a working CAST into the query. Let's start with a simple one.

' AND CAST((SELECT 1) AS int)--

This throws a new kind of error.

ERROR: argument of AND must be type boolean, not type integer

Here's why. SELECT 1 just returns the number 1, and CAST((SELECT 1) AS int) converts it to an int, which it already is, so the cast itself isn't the problem. The issue is what comes right before it, the AND. In SQL, AND is a logical operator, it's meant to sit between two conditions that each evaluate to true or false, like x=1 AND y=2. What we've written instead is AND <some integer>, and an integer isn't a boolean, so the database has no idea how to treat it as a condition. This isn't related to the cast breaking, it's a completely separate rule about what AND expects on either side of it. So this error is actually useful, it confirms our subquery and cast ran fine, we just need to wrap it in an actual comparison.

Step 2 - Make the Condition a Boolean#

Let's fix that by turning the integer into an actual comparison.

' AND 1=CAST((SELECT 1) AS int)--

The fix is small but important. Instead of just placing the cast value after AND, we compare it against something, 1=CAST(...). Now the whole expression 1=CAST((SELECT 1) AS int) evaluates to either true or false, which is exactly what AND needs. Since SELECT 1 returns 1, and we're casting that to int and comparing it to 1, the condition is true, and the query becomes valid SQL again.

And that's exactly what we see, a clean response. That confirms the query structure works end to end, so now we can swap out the harmless SELECT 1 for something that actually pulls real data out of the database.

Step 3 - Retrieve Usernames#

Let's point the subquery at the users table instead.

' AND 1=CAST((SELECT username FROM users) AS int)--

This time the response comes back complaining that the query got cut off due to a character limit, which also means the comment characters we added at the end, the --, never actually made it into the request. Here's why that matters. The TrackingId cookie has a maximum length the application will accept, and everything we're injecting has to fit inside that same space, original cookie value included. As the payload grows longer, mainly because SELECT username FROM users is a lot more text than SELECT 1, we start running out of room, and whatever doesn't fit just gets sliced off the end. Since the comment marker -- sits right at the tail of our payload, it's the first thing to get chopped, and without it, whatever was left of the original cookie value tags along after our injection and breaks the query.

The fix is simple, free up space by getting rid of dead weight. The original TrackingId value itself isn't doing anything for us anymore, so let's just drop it entirely and inject from a blank cookie.

TrackingId=' AND 1=CAST((SELECT username FROM users) AS int)--

That gives us a bit more breathing room and this time we get a different error.

ERROR: more than one row returned by a subquery used as an expression

This one's straightforward, SELECT username FROM users pulls back every username in the table, but a subquery used inside an expression like this can only hand back a single value. Since there's more than one row in the users table, the database has no idea which username to actually use in the comparison, so it just errors out instead.

Step 4 - Limit the Subquery to One Row#

Let's cap it at a single row.

TrackingId=' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--

Adding LIMIT 1 tells the database to only hand back the first row from the result set, which satisfies the subquery's one-value requirement. Now the cast actually has something concrete to work with, a single username, as text, being forced into an int. And since a username like administrator obviously isn't a number, the conversion fails, and the failure message tells us exactly what it tried and failed to convert.

ERROR: invalid input syntax for type integer: "administrator"

So without even querying for it directly, the error message just handed us the first username in the table, administrator.

Step 5 - Retrieve the Password#

Now that we know administrator is sitting in that first row, we just swap the column being selected.

TrackingId=' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)--

Same exact mechanism, only now instead of username, we're pulling password for that same row. The cast fails the same way, and the error message leaks the password straight into the response.

With the password extracted, all that's left is logging in as administrator using the credentials we just pulled out of the error message.

With this, the lab is solved!