News We Recently Launched AD Migrator and AD Reporter.

Steps of SQLite Database Forensics

Potential Forensic Analysis of SQLite Databases

SQLite Database Forensics

The accessibility of some portable and compact devices in our daily life has increased over the years and this has significantly paved the doors for the proliferating adaptation of SQLite databases. With such escalating growth of SQLite usage in iOS devices, there is a much greater likelihood of extracting digital evidence from these databases while conducting forensic investigations.

SQLite is an open source database that has been coded in ANSI- C based programming language. Because of its smaller size and the very ability to execute operations faster than any other available databases, it is largely used where there is a requirement for systematic storage maintenance like in Skype. SQLite databases are known for their abilities in faster processing of large volumes of data. This is the reason for why it is specifically suited for onboard applications built up of embedded tools.

Overview of SQLite Database Forensics

SQLite Database Structure

The SQLite database is parsed into pages that are organized into B-tree structures. Each page of the database is a set of definite number of bytes that has a size expressed in power of 2. This is inclusive of 512 and 65536. Every page belonging to the same database has the same size and they are numbered sequentially starting from the digit 1

Each page of SQLite can be implemented in any one of the following ways that is mentioned below.

Honing SQLite Forensics Processes

With the consistent updating process in the software, SQLite plays a much bigger role in the devices. Consequently, cyber forensics related investigation procedures are increasing in importance for law enforcement agencies and business organizations. The areas where SQLite forensics have earned much more importance are discussed below:

Embedded Applications and Devices

One of the possibilities that can drive forensic investigation procedures is that experts can locate critical amount of data within the database file of SQLite in circumstances where the embedded device like smart phones, mobile handsets, tabs, etc. are projected as a source of evidence. Owing to the fact that compact devices have a scope for limited storage, deletion of data often occurs in such type of embedded devices. This very much stresses on the importance of recovering the deleted records, tables from the SQLite database that may act as an appropriate culpable evidence.

Recovery of Browsing Activity from Internet Artifacts

Reconstruction of activities related to web browsing is one of the most important step while searching for the forensic evidence. Traces of web-activity can be retrieved from the SQLite databases such as in case of Mozilla Firefox .It uses series of tables for maintaining its browsing data related to browsing history, cookies, favorites, etc. SQLite databases like places.sqlite, formhistory.sqlite, downloads.sqlite can be carefully analyzed to extract crucial evidence.

The Remnants of data preserved in tables, indexes, transaction logs, and in other system components. It should be remembered that the deleted data is not securely removed from the database and that users practically do not have any control over the persistence of the data that has been deleted.

Data Extraction from Storage Location of Email Applications

A majority of email clients like Outlook 2015, Incredimail, and Envoy use SQLite database for the storage of data. Envoy uses SQLite for storing e-mails messages, contact details, appointments, etc. All the data are stored in the form of SQLite.db file format. These file formats can be carefully analyzed to dig out critical forensic evidence.

Forensic Implications on SQLite WAL and Rollback Journal File

Any application that has been embedded with SQLite database creates a rollback journal file that maintains a history of the input and output records in addition to the changes that have been made to the database. These journal files can be used to recover the deleted records of the SQLite database.

The database engine of SQLite makes use of another journaling mechanism that is known as Write Ahead Log file. WAL files are in the form of cache memory whereby any data that are modified on the pages of the database is first written to the WAL file and then later on it is enabled on the main database at checkpoint. Therefore, from a forensic viewpoint, a suitable recovery tool can be employed which can help the experts to track down the changes from the WAL file and put it to a new database

Note: While dealing with SQLite forensics, it must be remembered that incase of deletion of data from SQLite database, the data is logically deleted and exactly not removed. However, it may happen that the previous records are overwritten whenever there is an update to the database. What happens is that, sometimes the deleted records which are freed get over-written by new insertion of data in the table. This is one of the greatest challenges faced by forensic investigators while retrieving deleted records from the database.

How to Detect Whether a Specific Database Is In WAL Mode?

This very information can be retrieved from the header of database file. On, examining the file header with a hex editor if an expert find out that the bytes at the file offset 18 and 19 is 0x01 then the database is making use of rollback mechanism and if it is 0x02 then the SQLite database is currently in WAL mode.

Another approach to determine whether the database is in WAL mode is or not is by the use of SQL query PRAGMA journal_mode. This will return the WAL value if the SQLite database is in WAL mode

Presence of files such as -wal and .-shm in the same directory also indicates that the database is in WAL mode

Initial State

Initially it shows that there are no pages stored in the form of cache in WAL.

Changes to Wal

Since the Page 4 is modified, the new page is written to the WAL file and this newer version of the page is used by the database engine instead of the older version of the page.

CheckPoint Wal

Conclusion

It is clear from the above discussion that forensic investigators need the availability of efficient carving techniques and utilities for extraction of culpable evidence. Therefore, whenever experts carry out SQLite database forensic investigation procedures, they can use tools such as the free SQLite Database Viewer which provides an additional functionality to view and examine the .db file format conveniently.