PortSwiggerlabs: SQL injection UNION attack, retrieving multiple values in a single column

Information :

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response so you can use a UNION attack to retrieve data from other tables.

The database contains a different table called users, with columns called username and password.

To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

Solution :

This one is way too easy they already gave us the table name as well as the column names , so all we need to do is find an injectable spot, figure our the number of columns and craft our query that should look something like this :

'UNION SELECT username,password,.... From users-- - : the ... are for other columns if they exist . 

We first inject in the category field which results in an internal server error , if we wanted to confirm it , either add another ‘ or just comment the rest , both will fix the query and remove the error , confirming our SQLi.

Now back to our lab , we first need to identify the colum number , i will use orderby .

We get an error at number 3 which means 2 is the number of columns , now we just retrieve the username and password by dumping both columns from the users table , pretty straight forward :

'UNION SELECT username,password From users-- - 

This returned an error , so we need to step back for a second and see if both columns accept Strings or not.

The second colum does , now let’s check the first one :

This causes an internal server error , which means we got a type mismatch , so first column doesn’t accept strings .

I only used the second one to retrieve usernames and we got these :

Now if we try dumping the passwords :

Perfect we got the passwords as well , Only 3 users and 3 password we should try all of them to see which one is for the administrator :

The third one was the one for the administrator , so once we login as the admin , the lab should be solved .

Few thing to Note :

The tables inside the DB might have multiple columns , but for our request only 2 are necessary in the query made to the server .

This one didn’t need both of them to be strings , maybe it sends an ID or some sort of INT to the server in the first column .

Whenever we only have 1 spot to retrieve data with , we can use concatenation :

' UNION SELECT NULL,username||'~'||password FROM users-- 

This will retrieve both of them at once seperated by a ~ , this is useful when we can only use 1 field to retrieve Strings and we want to extract multiple columns from a table that holds Strings

That was all for this lab , see you in the next one :)