SQL injection attack, listing the database contents on non-Oracle databases

In this post we will walk step by step on how to solve SQL injection attack, listing the database contents on non-Oracle databases on PortSwigger Academy. This lab’s difficulty is Practitioner and it is the fifth lab in the SQL Injection labs on Portswigger. Our objective is to login as administrator.

Link to lab: https://portswigger.net/web-security/sql-injection/examining-the-database/lab-listing-database-contents-non-oracle

To start the lab click the ‘Access the Lab’ button. Burp Suite Community Edition is all we need to solve this lab.

As we begin the lab we encounter a shop page showcasing various categories. It’s worth noting that the categories may differ from the screenshot as PortSwigger Labs can vary the content with each lab session.

When we click on the ‘Corporate Gifts’ page we’re greeted with two distinct sections of text. The first section serves as the header, while the second section constitutes the body of the content.

Using Burp Suite we can send this request to Repeater to analyze. This will allow us to send one request at a time to analyze HTTP Responses.

Knowing we are looking for SQL Injection we should analyze all the places users would have input. The categories are a prime place to start. To test for SQL Injection we can place a single quote into the category parameter and analyze the Response in Burp Suite.

With the single quote in the category parameter we are shown a 500 Internal Server Error. This means the server did not know how to process the input and the single quote broke the application.

Injecting a second quote fixes the query. This is a good sign we have found the injection point for SQL Injection.

Our next step involves identifying the number of columns within the database table. We can achieve this by carefully analyzing the HTTP Responses and using an ‘order by’ clause. This technique entails crafting payloads with ‘order by <number>’ and incrementing the number to determine the exact count of columns in the database table.

When an ‘order by’ statement returns a result less than or equal to the actual number of columns we should observe a 200 HTTP response code. If the ‘order by’ statement exceeds the number of columns the response will typically be a 500 Internal Server Error. This process will help us determine the correct number of columns to proceed with our SQL Injection.

With the server returning a 500 with order by 3 we can determine the correct number of columns in the table is 2.

GET /filter?category=Corporate+gifts'+Order+by+2+--+-

Similar to previous SQL Injection Lab walkthroughs we will now use a union statement to obtain data from the database.

Get /filter?category=Corporate+gifts'+union+select+null,+null--+-

Let’s break what we have so far:

  1. The single quote serves to disrupt the normal SQL statement flow.
  2. The ‘union’ clause enables us to combine SQL statements, granting us the ability to construct our own SQL statement to extract information from the database.
  3. The ‘null, null’ segments serve as placeholders for our union clause and plays a vital role in reflecting data retrieved from the database. We use two ‘null’ values here because they align with the number of columns present in the database table.

To confirm our SQL Injection we can test to gather the version of the database.

Here is the PortSwigger SQL Injection Cheatsheet. We can use this to help us select the function needed to obtain the database version.

Using version() displays the version of PostgreSQL confirming our injection.

Get /filter?category=Corporate+gifts'+union+select+null,+version()--+-

Next we can attempt to get the listing of all the tables. We need to select table_name from information_schema.tables in order to list out the tables.

GET /filter?category=Corporate+gifts'+union+select+null,+table_name+from+information_schema.tables--+-

We can see in the response the users table is named users_xbjodo.

Our next step is to get the column names for the users_xbjodo table. We need to select column_name from information_schema.columns where the table_name is equal to users_xbjodo in order to list out the columns.

GET /filter?category=Corporate+gifts'+union+select+null,+column_name+from+information_schema.columns+where+table_name+%3d+'users_xbjodo'--+-

We can see in the response the username_mefdne columan and password_oamzri column for the table users_xbjodo.

Now that we know the column names we can use them to pull out the passwords and usernames with the columns username_mefdne and password_oamzri. The issue is we can only see one column value at a time. For example this will show us the passwords.

GET /filter?category=Corporate+gifts'+union+select+null,+password_oamzri+from+users_xbjodo--+-

We will concatenate the username_mefdne and password_oamzri columns together with the use of || as shown here. This will display the username and password together with a colon as the separator.

GET /filter?category=Corporate+gifts'+union+select+null,+username_mefdne+||+':'+||password_oamzri+from+users_xbjodo--+-

With the administrator credentials displayed in the response we are able to authenticate into the portal as the administrator.

Having successfully retrieved the administrator credentials from the database and logged in as the administrator we have accomplished our goal and solved the lab!

That completes the lab! Well done! If you found this helpful, please send me a tweet and tell me what you thought! Feedback is always appreciated!