Extract Columns Name Without Information_Schema

Context

This article focuses on methods to extract column names from a MariaDB database without utilizing the information_schema. Due to security restrictions in MariaDB 5.x, direct access to the information_schema may be blocked, necessitating alternative methods to identify column names. Understanding SQL queries, database schemas, and union-based SQL injection techniques is essential for this task.

Theory

MariaDB 5.x Limitations

MariaDB 5.x enhances security by restricting direct access to the information_schema. This schema is typically used to fetch database metadata, such as column names, but when its access is limited, attackers must find other methods. The restriction is meant to protect sensitive database schema details from being disclosed through unauthorized queries.

Column Guessing Techniques

When access to information_schema is restricted, attackers may use column guessing techniques. This involves making educated guesses about column names based on common naming conventions used in databases (e.g., username, password, email). By iteratively testing these guessed column names via SQL injection, attackers aim to discover valid column names.

Error Fallback Mechanism

An effective method to extract column names without information_schema is through error-induced feedback. By injecting queries designed to produce errors when non-existent column names are queried, attackers can infer the presence of certain column names based on whether the query executes or fails with an error message.

Using UpdateXML for Error-Based Extraction

The UpdateXML function in SQL is known for its role in generating errors for crafted queries. By leveraging UpdateXML, attackers can trigger specific error messages when invalid inputs or conditions lead to errors. These error responses can be orchestrated to reveal the presence or structure of database columns.

Select Into Outfile for Data Extraction

Another approach involves using SELECT INTO OUTFILE, which allows for exporting query results to a file on the server. This can be exploited to write the guessed column names to a server-accessible file, effectively extracting the information without direct schema queries.

Practice

Column Name Extraction via Error Messages

By using error-based techniques, you can extract column names through carefully crafted SQL injections that generate errors when querying incorrect column names. Here's how to do this:

• Trigger Error: Execute the following SQL command to deliberately cause an error that might reveal column names:

  SELECT 1 FROM users WHERE UpdateXML(1, CONCAT(0x3a, (SELECT column_name FROM users)), 1);
  

  This command attempts to use UpdateXML to process a subquery embedding a column guess, producing an error if the column does not exist.

Column Name Extraction via SELECT INTO OUTFILE

Another method of extracting column names is by using the SELECT INTO OUTFILE statement. This involves writing query results directly to a file on the database server.

• Write to File: Run the following command to produce a file containing column names:

  SELECT column_name INTO OUTFILE '/var/lib/mysql-files/columns.txt' FROM users;
  

  Here, the query attempts to write the column names to a specific directory on the database server where the OUTFILE operation is permitted.

Tools

• sqlmap: A tool capable of automating the process of SQL injection and database takeover.
• Burp Suite: A comprehensive tool for web application security testing, useful for manually executing and analyzing SQL injections.