Collecting Evidences from Unallocated Clusters in Sqlite Database

Introduction to Unallocated Clusters in Sqlite Database

Unallocated clusters of Sqlite database have a header, but the footer is not available in the file. This causes a lot of trouble while carving Sqlite databases from unallocated clusters. With a widespread usage of Sqlite application for storing data by Google Chrome, IOS, Mac, etc., Sqlite forensics has become a crucial stage for investigators. In order to analyze the Sqlite database and all its elements, it is important to collect Sqlite database evidences from the unallocated clusters. Let us see its importance and the file structure of the Sqlite database.

Sqlite Database Structure

This Sqlite file format and database structure information involves only significant information which is important for Sqlite file forensics.

Size of Sqlite Database and Numbering

• Sqlite database has all pages of equal size and it also comprises of a definite number of pages. Page size of database is always a power of two ranging from 512 (29) and 65536 (216) bytes.
• Page numbers begin from 1 and not from 0. Page size can be determined by 2 byte integer which is located at an offset of 16 bytes from beginning of database file.
• This formula: offset = (page number-1) x page size can be used to reach a particular page.

Sqlite Page Types

Why is First Page of Database Important?

The first page of Sqlite database comprises of the database header. This header information is stored in the first 100 bytes of the page. Another important factor is it contains of the Database Schema i.e. structure of database.

Database header begins with 16 byte sequence:

0x53 0x51 0x4c 0x69 0x74 0x65 0x20 0x66 0x6f 0x72 0x6d 0x61 0x74 0x20 0x33 0x00

This reads the Sqlite format 3 which read as UTF-8 encoded text followed by the null terminator byte. Some of the other forensically important values at offsets available in the database header are:

B-Tree Pages

A large part of Sqlite database file can have many B-Tree structures. A single B-Tree structure is stored with one or multiple pages and each page comprises of single B-Tree node. So when it comes to collect artifacts, it is important to carve B-Tree pages. However, Sqlite database will not store these B-Tree pages in a particular sequence and hence it is difficult for identifying these pages. Investigators can identify these pages as the page header is 8 bytes in length if it is a leaf node page or 12 bytes if internal node page.

Pointer Map Pages

In case the Sqlite database is auto-vacuum capable and the value within 4 bytes of header at offset 52 is non-zero, then the Pointer map pages are said to be existing. If a database is having pointer map pages, the first page is page 2. The first byte of it is one of five values 0x01, 0x02, 0x03, 0x04 or 0x05. It must be noticed that most of the databases which are involved for the examination are not auto-vacuum capable, but iPhone SMS.db and many other Sqlite databases are auto-vacuum capable.

Locking Page

The locking page is database page, which starts at 230 (1,073,741,824) at byte offset and remains unused. Most databases will not be as large for locking it.

How This Help With Carving Sqlite Database?

Database header and the first byte value can help the investigators to determine the data of the page size block is valid or not. So, this first page can be used for calculating page size, read the next page, and for validating the first byte. Conversion of the values attained to the Hex value has to be done for further analysis. Journal files also play an important part in analysis of database from unallocated space. This can help in the investigation of Chrome, iPhone, browsers, Smartphone databases. This concept of carving evidence from Sqlite databases unallocated space can help forensic practitioners in validating and testing the databases completely.