SQL injection UNION attack, retrieving data from other tables | Dec 4, 2022

Introduction

Welcome to my another writeup! In this Portswigger Labs lab, you’ll learn: SQL injection UNION attack, retrieving data from other tables! 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. To construct such an attack, you need to combine some of the techniques you learned in previous labs.

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 that the product category filter is vulnerable to SQL injection, and the query is being reflected to the application’s response.

Now, let’s enumerate the number of columns in this table via UNION clause!

' UNION SELECT NULL,NULL,NULL-- -

When we try to select 3 columns, it returns a 500 Internal Server Error HTTP status, which means this database table has no 3 columns.

How about 2 columns?

' UNION SELECT NULL,NULL-- -

No error this time!

Next, we need to enumerate which column accepts string datatype:

' UNION SELECT 'SQL Injection 1','SQL Injection 2'-- -

We can see that all columns accept string datatype!

• First column: Header
• Second column: article content

Then, we need to find out which DBMS(Database Management System) is using:

' UNION SELECT NULL,version()-- -

• DBMS information: PostgreSQL version 12.12

Note: Although the lab background gave us the table name and column names, I wanna practice SQL injection without anything information in advance.

After that, we try to enumerate the current table data!

According to PayloadAllTheThings about PostgreSQL injection, we can list all the database names:

' UNION SELECT NULL,datname FROM pg_database-- -

• Databases: template1, academy_labs, postgres, template0

Looks like academy_labs is our current database? Let’s check it is true:

' UNION SELECT NULL,current_database()-- -

Yep, let’s list all the table names in this database!

' UNION SELECT NULL,table_name FROM information_schema.tables-- -

The users table looks interesting! Let’s enumerate it’s column names:

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

• Table users column names: password, username

Sounds very interesting! Let’s list all the data in this table!

' UNION SELECT username,password FROM users-- -

Nice! We found all the users’ name and password in plaintext!!

Now, our objective is log in as administrator user.

Armed with above information, we know that it’s username and password is administrator:n84eoukut9lau0n2n4s2!

Let’s login as administrator then!

We’re successfully logged in as administrator!!

Conclusion

What we’ve learned:

1. SQL injection UNION attack, retrieving data from other tables