Schema and Table Discovery

Context

The objective of this article is to teach how to discover SQLite schema and table information using SQL injection techniques. This content assumes you have foundational knowledge of database schemas, SQL queries, and the structure of SQLite databases.

Theory

SQLite Schema and Table Structure

In SQLite, the schema defines the structure of the database, encompassing all elements such as tables and indexes. Understanding the schema is crucial for anyone looking to exploit or secure an SQLite database.

The sqlite_master table is the primary table that stores all schema information for an SQLite database. This includes details about tables, indexes, and any other object types, such as views.

Similarly, sqlite_temp_master is a temporary storage table used exclusively for temporary tables and indexes. Knowing the structure and storage of the schema is key to extracting and exploiting these details.

Schema and Table Enumeration Techniques

Exploiting SQL injection to extract schema information from sqlite_master is a crucial offensive technique. Attackers can identify table names by executing specific queries against this table.

By using SQL commands like SELECT name FROM sqlite_master WHERE type='table';, one can efficiently retrieve a list of all tables present in the database.

Furthermore, extracting the SQL statements that define each table via SELECT sql FROM sqlite_master WHERE type='table'; provides full details of each table's design, including column names and data types.

Understanding SQLite Metadata

Accessing SQLite metadata gives attackers insights into database structure, which can be used to discover further vulnerabilities or sensitive data.

Exploiting SQL injection vulnerabilities to access metadata in sqlite_master exposes crucial schema details that can lead to further exploitation. By performing focused enumeration, one can gather a comprehensive understanding of the database's internal design and use this information strategically for offensive purposes.

Practice

Schema and Table Enumeration via SQL Injection

To enumerate schema and table information via SQL injection, execute the following commands:

• Retrieve all table names from the database:

  SELECT name FROM sqlite_master WHERE type='table';
  

• Extract SQL statements that define each table:

  SELECT sql FROM sqlite_master WHERE type='table';
  

• Identify temporary tables in the database:

  SELECT name FROM sqlite_temp_master WHERE type='table';
  

Executing these SQL commands on a vulnerable application will allow you to access the database schema and table definitions, giving you valuable insights into potential areas for further exploitation.

Index Discovery via SQL Injection

Index information can further expose vulnerabilities. Use the following commands to discover index details:

• List all indexes in the database:

  SELECT name FROM sqlite_master WHERE type='index';
  

• Retrieve SQL statements defining each index:

  SELECT sql FROM sqlite_master WHERE type='index';
  

These commands facilitate access to index definitions and structures, which can reveal how data is organized and potentially highlight performance or security weaknesses.

Tools

• SQLite3
• sqlmap

These tools assist in exploring and exploiting SQLite database vulnerabilities effectively. Their use allows for both manual and automated SQL injection testing and exploitation.