Recover Deleted SQLite Database

SQLite is an embedded database that exists inside the other applications. These applications use SQLite as a self-contained database engine for storing various forms of data in it. Android, Blackberry & iPhone platforms rely on SQLite for storing their data in the form of tables, triggers, and fields. The SQLite Database is very important for many purposes, which is why recovery of deleted SQLite file is necessary. The page will be discussing about the recovery of deleted database of SQLite.

Why Recovery is Important?

SQLite Database is used by several ranges of desktop and mobile web browsers like Chrome, Firefox and instant messaging applications like Whatsapp, Skype. It plays a very important part in forensic analysis as everything that is happening in each person’s life is recorded in phones and laptops. Various types of files in SQLite database are used in forensic investigations, which are as follows:

Deleted Data/Free List- It contains all the deleted browsing data, chats, messages, browsing history, and other data essential for investigation purposes. The suspect will always try to delete history of data that can act as evidence to prove him as criminal.

Write Ahead Logs- It stores new and modified data. The file will store 1000 pages (default) or committed by checkpoint via database. This file helps in finding if we want web browsing or chatting as whole chat session can never set off an individual checkpoint.

SQLite Carving- It is the technique of recovering physically deleted SQLite files. We need to search the deleted file and then recover its contents with the help of tool.

Unallocated Space in SQLite Database- SQLite database contains some pages called "leaf table b-trees" which contain data. The tree consists of cells placed by SQLite and new cells are added at the end of b-tree page. The new cells may contain no valid data and is taken as unallocated space because it may contain previously deleted data.

Roll Back Journals- It stores automatically the information of actions performed by user. If there is any incomplete action performed by user, it does not get stored in main database file but saves in these journals and helps in forensic investigations.

Recovery of Deleted SQLite Database

Since crimes are growing increasingly every day, investigators need some tools to analyze and extract evidence from the deleted SQLite record without any loss of data. There are many methods to recover deleted data in SQLite such as-

Manual Method

There are many third party tools to recover deleted SQLite database but we also have manual method to recover which is done with the help of python script to parse SQLite database for deleted data.

The leaf table b-trees in some pages of SQLite database consists of cells placed by SQLite and new cells are added at the end of b-tree page. The new cells may contain no valid data and is taken as unallocated. This unallocated space can contain deleted data. The leaf table b-tree page can also contains free blocks. Free blocks are the areas of unallocated space tracked by leaf table b trees. Two areas within a page that can contain deleted data are unallocated and free blocks.

E.g. to parse the deleted SQLite database, we have three forms of SQLParser designed by Mari DeGrazia:

sqlparse.py -Python script

sqlparse.py -f /home/sqliteforensics/smsmms.db -o report.txt

This script can easily recover deleted cell records from the SQLite and output results i.e. recovered deleted records will be then saved in a text file.

sqlparse_CLI.exe

It is a Windows command line tool. It can be used as

sqlparse_CLI.exe -p -f C:\Users\Mari\smsmms.db -r -o report.txt

sqlparse_GUI.exe

It is a Windows GUI interface tool where the SQLite file will be taken and output will be in.tsv format that can be viewed in Excel by importing it.

All these tools will give output with the allocated and freeblock types of data, Offset, length, and data.

Third Party Approach

Though we have many manual methods to recover deleted SQLite database, it is quite difficult to use the manual method for non-technical users or forensic investigators who needs SQLite data the most. For this reason, the third party tools are used such as SQLite Forensic Explorer.

These tools are widely used to explore and extract evidences from SQLite file and recovers deleted data from database. It supports a facility to view deleted and secured deleted data efficiently without losing data integrity. It helps in recording the suspicious activities of the suspect in case he/she had deleted the data that could work as evidence. The output of the recovered data can be in multiple formats like CSV, PDF or HTML files that can be further used in investigation purposes or submission to court of law for legal proceedings. These tools can even recover deleted data from corrupt SQLite database.

Sofia Christen is a Digital Forensics Researcher and writing a blog on latest technology is her passion.