Linux Sqlite Files and its Forensics
Last time we pick up Sqlite Mozilla forensics topic, this time we have discuss Linux Sqlite and its Forensic.One of the most striking features of Sqlite is its portability feature. By portability we mean that it can be easily compiled and run on different operating systems such as Windows, Linux, Mac OS X and many more. Additionally, the Sqlite database file formats are compatible across all the operating systems and byte orders. Sqlite offers you the liberty to access a Sqlite database created on an operating system in a different operating system, without requiring any need for modification or conversion. Forensics analysis of the Sqlite database can be difficult at times because the database files do contain a header but they do not possess footer and the file length is not stored in the database file. In this editorial, we will basically focus on the Sqlite database stored in Linux operating system.
Sqlite Database Files In Linux
The first step to create any Sqlite database in Linux operating system is to install Sqlite. For this purpose, download the sqlite-autoconf-*.tar.gz source code from the official website of Sqlite.
The default Sqlite files are designated with same extension in all kinds of operating systems. For instance .sqlite 3 file is the default database file of Sqlite version 3.0 operating in Windows, Linus and Mac operating systems. The other files created by Sqlite by default in Linux are:
- .cls
- .db-journal
- .db3-journal
- .s2db
- .sqlite-shm
- .sqlite-wal
- .sqlite2
- .sqlite3
- .sqlitedb
Page Numbering And Size Of Sqlite Database
The entire Sqlite database is always divided into a fixed number of pages that are always o equal size. Also the page size is a number raised to the power two lying between 512 (29) and 65536 (216) bytes. The size of a particular page can be calculated by the 2 byte integer which is located at offset location of 16 bytes from where the page starts. In order to browse to a particular page, when you have access to the page number; you need to calculate the offset.
Offset= (page number-1)*(Page Size)
Also the page number begins from 1 and not 0.
Structure Of Sqlite Database
- Pages
The first page in the database is very crucial. This is because it contains both the header of the database listed within first 100 bytes of the page and also stores the schema of the database. The beginning of the database header is marked by 16 bytes sequence shown below:
0x53 0x51 0x4c 0x69 0x74 0x65 0x20 0x66 0x6f 0x72 0x6d 0x61 0x74 0x20 0x33 0x00
This sequence when decoded brings forth the information that it is Sqlite 3.0 version database.
- B- Tree
The B-Tree page header states useful information in different offset bytes, like:
- Offset 12 bytes shows the free space on the page
- Offset 32 bytesshows number of cells present on the page
- Offset 64 bytes shows content present at the start of the page
Utility To Analyze Sqlite Database
Forensic analysis of Sqlite database requires careful and perfect examination of each and every aspect of the database. This is not possible manually as there are great chances of missing out crucial information. Therefore, to avoid this, users can take assistance of third party application like Sqlite Forensics. This application is designed to enable the examiners to carefully carve out the information, without leaving out even a tiny bit of information. Also if the Sqlite database file has been deleted somehow, the Sqlite Database Recovery software can be deployed to successfully recover the deleted database and then perform forensics on the database.
