Column and Data Type Discovery

Context

This article aims to teach how to discover column names and data types in SQLite databases using SQL injection techniques. The reader should already be familiar with SQL queries, an understanding of database schemas, and basic SQLite enumeration techniques.

Theory

PRAGMA Table Info

PRAGMA table_info is a command specific to SQLite that allows you to retrieve metadata about a table. It is a fundamental tool in exploring the structure of a database, used to enumerate column names, their data types, and any constraints associated with the columns.

Column Metadata

Column metadata includes valuable details such as column names, data types, NOT NULL constraints, default values, and whether a column is part of the primary key. This metadata is critical when seeking to understand the structure and configuration of a database table, especially when exploiting SQL injection vulnerabilities. Extracting this information can reveal a lot about the database's underlying architecture, potentially exposing sensitive configurations and guiding further exploitation efforts.

SELECT * FROM pragma_table_info

To attack and acquire the structure of a table through SQL injection, one approach is to use SELECT * FROM pragma_table_info('table_name'). This query, when injected correctly, can bypass certain input validation checks to execute a PRAGMA query, returning comprehensive details about the columns of the target table.

Practice

Column and Data Type Discovery via SQL Injection

To extract column metadata through SQL injection, follow these steps:

• Execute the following SQL query to directly retrieve column metadata for a specified table:

  SELECT * FROM pragma_table_info('target_table');
  

• Inject a payload designed to enumerate column names and types. You can use a UNION-based SQL injection to achieve this:

  1 UNION SELECT NULL, name, type, NULL, NULL, NULL FROM pragma_table_info('target_table');
  

• To obtain more detailed information about the columns, including constraints and default values, execute:

  SELECT name, type, "notnull", dflt_value, pk FROM pragma_table_info('target_table');
  

The intended outcome of these steps is to gain access to sensitive metadata of the target table, including column names, data types, and constraints. Such information could potentially guide further attacks or exploitation efforts.

Tools

• SQLite3
• sqlmap