Forensic Analysis of SQLite Databases

SQLite databases are widely used across multiple platforms, including mobile devices, web browsers, and desktop applications.

Forensic analysts often encounter SQLite databases during investigations, making it essential to understand their structure and the tools available for analyzing them.

Understanding SQLite Databases

SQLite databases consist of multiple files, each serving a specific purpose. Identifying these files is crucial during forensic investigations:

• Main Database File: Typically has extensions such as .db, .sqlite, .sqlitedb, .storedata, or sometimes no extension at all.

• Write Ahead Log (WAL): A .wal file that may contain uncommitted transactions, providing additional forensic insights.

• Shared Memory File: A .shm file that facilitates transactions but does not store data permanently.

Analyzing SQLite Databases

An SQLite database consists of tables that store data in columns. Some databases have a single table, while others contain hundreds, each with unique schemas and data types. When performing forensic analysis, it’s important to understand how these tables interact and how data is stored.

Tools for SQLite Analysis

Forensic analysts use various tools to examine SQLite databases. These tools fall into two main categories:

1. GUI-Based Viewers: User-friendly tools like DB Browser for SQLite allow visual analysis but may automatically merge WAL file transactions into the main database.

2. Command-Line Utilities: Tools like sqlite3 provide a powerful way to run queries and extract data, making them ideal for scripting and automation.

3. Forensic-Specific Tools: These tools offer advanced recovery features, allowing analysts to examine deleted records and unmerged transactions.

Querying SQLite Databases

Once the database structure is understood, analysts can run SQL queries to extract relevant information. Below are key SQL operations commonly used in forensic investigations:

1. Using the SELECT Statement

The SELECT statement retrieves data from a table. The simplest form is:

SELECT * FROM fsevents;

This retrieves all columns from the access table.

However, for targeted analysis, selecting specific columns is more efficient:

SELECT fullpath, filename, type, flags, source_modified_time FROM fsevents;

When multiple tables share column names, it’s best to specify the table name:

SELECT access.service, access.client FROM access;

2. Converting Timestamps

Many SQLite databases store timestamps in Unix epoch format. Converting them to a readable format is crucial for timeline analysis:

SELECT url, visit_time,datetime((visit_time / 1000000) - 11644473600, 'unixepoch', 'localtime') AS last_modified FROM visits;

The AS keyword renames the column for better readability.

3. Using DISTINCT to Find Unique Values

The DISTINCT keyword helps identify unique values within a column. For instance, to find unique permission types in the access table:

SELECT DISTINCT url FROM urls;

5. Using CASE for Readability

To make data more understandable, analysts can use the CASE expression to replace numerical values with meaningful labels:

SELECT url, visit_count,
CASE hidden
	WHEN 0 THEN "visible"
	WHEN 1 THEN "hide"
END Hidden,
datetime((last_visit_time / 1000000) - 11644473600, 'unixepoch', 'localtime') AS last_modified
FROM urls

6. Sorting Data with ORDER BY

Sorting records chronologically can help establish an event timeline. The ORDER BY clause arranges records based on a specified column:

SELECT url, visit_count,
CASE hidden
    WHEN 0 THEN "visible"
    WHEN 1 THEN "hide"
END AS Hidden,
datetime((last_visit_time / 1000000) - 11644473600, 'unixepoch', 'localtime') AS last_modified
FROM urls
ORDER BY last_modified DESC;

7. Filtering Data with WHERE and LIKE

For large datasets, filtering results is essential. The WHERE clause helps narrow down data based on conditions:

SELECT url, visit_count,
CASE hidden
    WHEN 0 THEN "visible"
    WHEN 1 THEN "hide"
END AS Hidden,
datetime((last_visit_time / 1000000) - 11644473600, 'unixepoch', 'localtime') AS last_modified
FROM urls
WHERE last_modified LIKE '2025-01-16%'

The % wildcard allows partial matches, making it useful for date-based searches.

-----------------------------------------------------------------------------------------------------------

Conclusion

SQLite database forensics plays a crucial role in digital investigations, from mobile forensics to malware analysis. By understanding SQLite file structures, using the right tools, and applying effective query techniques, forensic analysts can extract valuable insights from databases.

-------------------------------------------------Dean-----------------------------------------------