MariaDB Group By Rand Trick

Context

The purpose of this article is to teach the exploitation of MariaDB's GROUP BY RAND() trick for error-based SQL injection. This technique is particularly useful for extracting sensitive database schema information by leveraging the non-deterministic nature of the RAND() function. To follow this guide, you should have a strong understanding of SQL query structures, the use of the GROUP BY clause, the RAND() function, and the basics of MariaDB error-based injection.

Theory

Non-Deterministic Behavior in SQL

Non-deterministic functions in SQL return different results each time they are executed. This characteristic is evident with the RAND() function, where each invocation results in a distinct output. This unpredictability can be manipulated within SQL injection techniques to induce errors and extract valuable information.

GROUP BY Clause and Its Exploitation

The GROUP BY clause is utilized to organize identical data into groups within a SQL query, typically following aggregate functions like COUNT() or SUM(). When combined with the RAND() function, the non-deterministic sorting can lead to unintended behavior, paving the way for potential exploitation.

Duplicate Entry and Row Collision

Using GROUP BY RAND() can result in duplicate entries, which may trigger SQL errors. These errors can be intentionally caused to reveal underlying database structures. By forcing row collisions, attackers can exploit these errors to gain insights into the schema and other sensitive details.

Randomized Error Exploitation

The injection of GROUP BY RAND() into SQL queries to induce errors represents a targeted approach to information extraction. The error messages generated by these random errors can disclose crucial information about the database's schema and its organization. This protocol weakness is a key component that can be exploited to access sensitive data.

Practice

MariaDB GROUP BY RAND() Injection

Manual execution of GROUP BY RAND() injection involves the following steps:

• Execute a basic injection query to test for vulnerabilities:

  SELECT column FROM table GROUP BY RAND();
  

  This command attempts to induce non-deterministic ordering, which can generate errors if exploited properly.

• Refine the query by limiting the results, targeting specific error messages:

  SELECT column FROM table GROUP BY RAND() LIMIT 1;
  

  By restricting results, the database might produce insightful error messages due to unexpected processing requests.

• Force a duplicate entry error to disclose database information:

  SELECT column FROM table GROUP BY RAND() HAVING COUNT(*) > 1;
  

  This command aims to deliberately cause row collisions, resulting in error messages that could reveal database structure details.

The outcome of these injections could be access to database schema details through error messages.

Automated Error-Based Injection with GROUP BY RAND()

By using automated tools like Sqlmap, the process can be streamlined:

• Run Sqlmap with the appropriate settings for MariaDB error-based injection:

  sqlmap -u 'http://target.com/vuln.php?id=1' --random-agent --level=5 --risk=3 --technique=E --dbms=mariadb
  

  Sqlmap facilitates automated exploration and exploitation by leveraging GROUP BY RAND() as a technique for inducing and exploiting errors.

The automated execution of these steps can lead to the extraction of sensitive database schema information through error-based injection techniques.

Tools

• sqlmap