Detect Columns Number

Context

In this guide, we will explore techniques to determine the number of columns in a MariaDB table using union-based SQL injection. This approach is crucial for capably leveraging SQL injection vulnerabilities to retrieve sensitive data. Prior knowledge of SQL syntax, HTTP requests handling, understanding database schemas, and familiarity with union-based SQL injection in MariaDB are assumed.

Theory

Union Injection in SQL

Union injection is a powerful technique that leverages the SQL UNION operator to combine the results of multiple SELECT statements into a single result set. Attackers use this method to append a crafted UNION SELECT statement to an existing query, thereby extracting data from the database that the application might not directly expose.

Column Count Detection

To exploit union-based SQL injection effectively, it is essential to determine the number of columns in the original query's result set. This knowledge allows for crafting an injection query that matches the original query structure, enabling the successful execution of union-based injections.

Null Method Usage

The NULL method involves appending a UNION SELECT query with a series of NULL values to ascertain the number of columns. By incrementally adjusting the number of NULL placeholders until the query executes without error, the correct column count is identified.

Order By Clause

The ORDER BY clause is employed to sort query results based on specified columns. Attackers use it to detect column counts by appending ORDER BY clauses with incremental column indices. An error in response indicates a column number beyond the actual column count of the result set.

SQL Comment Syntax

SQL comments are employed to truncate the rest of the SQL query that trails the injection point, ensuring that the crafted injection is executed successfully. The syntax --+ is typically used to comment out unwanted parts after the injection.

Practice

Detecting Column Count via Union Injection

To infer the number of columns, follow this sequence of requests and responses observing the errors or absence thereof:

1. Test the existence of columns using the ORDER BY clause.

   • Begin by testing the existence of the first column:

     http://example.com/page?id=1 ORDER BY 1--+
     

   • Incrementally test up to the third column or until an error occurs:

     http://example.com/page?id=1 ORDER BY 2--+
     

     http://example.com/page?id=1 ORDER BY 3--+
     

     Observe when a response error appears to stop.

2. Use the UNION SELECT method with NULLs to verify the column count.

   • Start with two NULLs and increase gradually until no error is encountered:

     http://example.com/page?id=1 UNION SELECT NULL,NULL--+
     

   • Increment as needed:

     http://example.com/page?id=1 UNION SELECT NULL,NULL,NULL--+
     

   Once the errors cease, you have determined the correct number of columns.

Upon successfully executing these methods, the number of columns in the targeted SQL query’s result set becomes evident, allowing further exploitation using carefully structured union-based injections.

Tools

• Burp Suite
• SQLMap

These tools can facilitate analysis and automate requests, aiding efficient probe and discovery processes in SQL injection testing scenarios.