MYSQL Error Based - GROUP BY

Context

This article explores the exploitation of MySQL error-based SQL injection using the GROUP BY clause. This technique leverages improperly handled errors in SQL queries to extract sensitive information from a database. To fully understand and implement this technique, you should have a foundational knowledge of SQL syntax, MySQL functions, error handling, and previous experience with MySQL error-based SQL injections.

Theory

Error-Based SQL Injection in MySQL

Error-based SQL injection is an offensive technique that exploits error messages to gain insights into the database structure. By crafting deliberate errors in SQL queries, an attacker can force the database to return error messages that may reveal valuable information. This method is particularly useful when other SQL injection techniques do not yield results.

GROUP BY Clause in SQL

The GROUP BY clause is an SQL command used to aggregate data by grouping rows that share certain properties. It is commonly used in conjunction with aggregate functions such as COUNT, SUM, AVG, etc. Using GROUP BY improperly, such as specifying non-existent columns, can generate errors, making it a target for error-based SQL injection attacks.

Error Generation via GROUP BY

Injecting a GROUP BY clause using invalid columns can trigger errors, which are exploited to retrieve information about the database schema. The errors often contain hints about column names and data types, which are critical for further exploitation. This attack sequence involves introducing randomness or deliberate flaws in the GROUP BY clause to provoke the desired errors.

Practice

1. Identify Injectable Parameter: Start by identifying a parameter in a SQL query that is vulnerable to injection. This often involves testing inputs such as page parameters, forms, or URLs for SQL syntax errors or unusual behavior.

   http://example.com/product.php?id=1
   

2. Inject GROUP BY Clause:

   • Use the following SQL injection payload to target a vulnerable query:
   1' GROUP BY CONCAT_WS(':', version(), FLOOR(RAND(0)*2)) HAVING MIN(0) -- -
   

   • Full query becomes:
   SELECT name FROM users 
   WHERE id = '1'
   GROUP BY CONCAT_WS(':', version(), FLOOR(RAND(0)*2)) 
   HAVING MIN(0) -- -
   

   • CONCAT_WS(':', ...): Function: Joins multiple strings using : as a delimiter.

   • FLOOR(RAND(0)*2):

     • RAND(0) is a seeded pseudo-random number generator (same seed = deterministic sequence).

     • RAND(0)*2 returns a float between 0 and 2 → e.g., 0.234, 1.892, etc.

     • FLOOR(...) then reduces it to 0 or 1.

       Each row has a 50/50 chance of being tagged with 0 or 1.

   • GROUP BY CONCAT_WS(...): This randomly groups rows based on:

     • The MySQL version (constant for all rows)

     • A random binary (0 or 1) value

       Because random values are used, it is probable that two rows will have the same group key, like: 8.0.33:1 and 8.0.33:1

   • HAVING MIN(0): The HAVING clause is required syntactically for GROUP BY, and MIN(0) is used as a dummy condition:

     • It’s always false, and ensures that no rows are returned normally.

     • This helps prevent the app from showing anything in the output—only the error is visible.

       So while the real exploit occurs through the error message, the result set itself is empty.

3. Observe Error Message:

   • Execute the injected SQL query and closely observe the error message returned by the database. This could result to an error message like:
   Duplicate entry '8.0.33:1' for key '<group_key>'
   

Tools

• sqlmap