SQLite is a popular database format that is used in programs of both mobiles devices and personal computers. Most of the popular browsers such as Google Chrome, Mozilla Firefox and Yandex Browser, and a great number of messengers, for example WhatsApp, Viber, WeChat etc. keep their data in this format. This fact makes such databases a valuable source of digital evidence.
In the context of forensic analysis, the main features of SQLite databases are free lists, write-ahead log and unallocated space. These areas may contain records that were not put into the database or were deleted from the database. We are going to take a closer look at them in this article.
Fundamentals of SQLite
SQLite is a relation database and the requests to it are done via Structured Query Language . In spite of the fact that the format does not support all of the SQL features, it is widely used, especially in the mobile devices.
Each database is kept in a separate file. Number of tables and stored data are limited only with the free space on the digital storage medium.
As D. Makeev, N. Timofeev and others  note, SQLite format database consists of several pages of a fixed size, which is according to the official data , is a square of one of the numbers in the range from 512 to 65536 including.
First 100 bytes of SQLite format database file are its header. Let’s take a closer look at the most valuable parts of the file in the context of a forensic analysis.
The header starts with a character string «SQLite format 3\000», which occupies first 16 bytes and is a signature of this type of files.
After the signature, there are two bytes that are used by the database and in which the size of a page is specified. If the value equals 1 then the size of a page is 65536.
The information about unused pages is stored at the free list. Information about the first page of the list can be found after 32 bytes from the beginning of the file. If the value equals 0 then the free list is empty. After the next 4 bytes, there is a record, which contains data about the number of pages in the list. We will take a closer look at it further in the article.
Information about strings encryption that is in the database is located in the 4-bytes record that can be found after 56 bytes from the beginning of the file. If the value equals 1 – UTF-8 encoding is used, 2 – UTF-16le encoding is used, 3 – UTF-16le encoding is used.
The access to the header of the file can be got by the analysis of the file via hex-viewer, which is usually a part of comprehensive approach of the forensic analysis. A good example of it is Evidence Center developed by Belkasoft.
Database content can be viewed either via software with an open source code, such as SQLite Database Browser, or via viewers that are part of the comprehensive approaches. It is worth noting that when SQLite Database Browser is used, an expert is not be able to get an access to the unused pages of the databases, information from the write ahead log and to the unallocated space. The mentioned above parts of a file will be described in details further in this article.
As it was mentioned before, SQLite format database can contain one or several unused pages, information about their location is specified in the header of the file. Such pages can appear when, for example, one of the databases was deleted. The information about this pages and about their numbers is contained in the free list. This list is a valuable source of information for the forensic analysis as they can contain important information such as deleted SMS or other messages, browser history etc.
An expert can use the script written by M. DeGrazia  in order to extract data from unused pages. The script is also available as an executable file (sqlparse_GUI.exe). This script has a graphical interface and two modes: Formatted Output and Raw Output.
The first mode is recommended when an expert needs to extract strings from free blocks of database, for example, if one needs to find out which web pages were visited. Also, this mode can be used in order to make preliminary analysis, which allows to check relevancy of the detailed analysis.
The second mode allows to extract the complete content of the free blocks and unallocated space. The file can be analyzed via hex-viewer that, on the basis of the database table structure will allow the analyst to restore not only the content of the messages or web-pages addresses but also time stamps.
Even though this method is different from the rest with its availability, it requires from an expert severe experience and great time expenditure.
The most applicable tool of SQLite database format analysis, and also unused pages analysis is BelkasoftEvidence Center, in our opinion. SQLite viewer that is a part of this program allows an expert to get an access to the records that are on the pages of the free list, in the automatic mode, including time stamps etc.
From version 3.7.0, SQLite database format employs a new journaling mechanism – write ahead log or WAL. This journal is stored in the same catalog with the file of database, and has the same name but it has a string “-wal” at the end. This mechanism allows to make records of the changes first in the journal and later to the database. From time to time the information recorded to the WAL-file is transferred to the main file, this operation is called “checkpoint”.
WAL-file consists of the header and the frames after it. The information in each frame is recorded only for one page of the database. It means that all the changes that have to be done in the databases, are recorded to the frames of WAL-file, and they are stored in this file until the “checkpoint” is done. The checkpoint event occurs automatically after the WAL size reaches a certain size, usually it is 1000 pages. It means that WAL file can contain a great number of valuable information for forensics analysis. An expert can extract this information via, for example, Belkasoft Evidence Center. SQLite-viewer of this tool extracts this information automatically when it analyses the main file of the database.
Unallocated space of the SQLite format databases are fragments of pages which are ready to accept new data and which can contain parts of information that were stored there before. Unlike free lists, unallocated space is not referenced from anywhere in the database and it contains random fragments of data. This part of the main file is also valuable for an expert as it can contain information that was deleted by a user.
When the analysis is done via SQLite-viewer, which is a part of Belkasoft Evidence Center, an expert can get an access to the unallocated space by choosing an option from the pop-up menu. More over it, the viewer conducts the carving of records in automatic mode that reduces time expenditures.
As the experience shows, SQLite format database is a valuable source of the digital evidences. In this article, the main principles of its analysis was shown, and also such features as free lists, write-ahead log and unallocated space were described.
- Goncharov A.N. Rabota s SQLite [Dealing with SQlite]. URL: http://www.softtime.ru/info/articlephp.php?id_article=70
- Makeev D., Timofeev N., Afonin O., Gubanov Yu. Forensic Analysis of SQLite Databases: Free Lists, Write Ahead Log, Unallocated Space and Carving. 2015. URL: http://belkasoft.com/sqlite-analysis
- The SQLite Database File Format. URL: http://sqlite.org/fileformat2.html
- DeGrazia M. Python Parser to Recover Deleted SQLite Database Data. 2013. URL: http://az4n6.blogspot.be/2013/11/python-parser-to-recover-deleted-sqlite.html