Recovery of Sqlite Databases from Unallocated Space

Sqlite is a popular embedded database that forms the platform for a wide range of applications. Sqlite is being used by these applications 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 desktop applications, web browsers like Chrome, Firefox and instant messaging applications use Sqlite as their storage database. Since people use Sqlite database every day, it is important to keep the record of database intact. This is why in case the Sqlite database is deleted somehow, recovery of deleted Sqlite database is necessary. In this page, we will be discussing about the recovery of Sqlite databases from unallocated space. Types of Files in Sqlite Database Used For Recovery Sqlite database plays very important roles in recovery purpose as in this technological arena people are largely dependent on their smartphones and laptops that use Sqlite as their default platform. The types of files in Sqlite database are as follows: Free List It contains unused pages that comes from deletion of SMS messages, cleared chat logs or emptied browsing histories. Free list is important, as suspect will try to delete data that may act as evidence to prove his /her guilty act. Write Ahead Logs (WAL) It is a way to access records not yet committed into main database. The new or altered data will be stored in a file called WAL until committed to main database by ‘checkpoint’ event that will automatically occur after WAL size reaches a certain size (1000 pages, by default). Talking about chat or browsing history, reaching checkpoint size is difficult which means data in WAL will remain uncommitted to main database. 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 during recovery of Sqlite databases from unallocated space. Sqlite Carving: It is a method to recover physically deleted Sqlite database files. Carving uses information about page size extracted from the database header and value of first byte of each subsequent page to determine data in a particular page is valid in context to Sqlite database format. Unallocated Space in Sqlite Database The Sqlite database is divided into equally sized pages. Some type of pages are ‘leaf table b-trees’ which contain the data. In turn, these leaf table b-trees contain cells. The newly added cells will reside towards the end of the page and space before the first cell starts is unallocated until allocated for a new cell. Unallocated space can be empty or it may contain deleted or remains of used data. The unallocated spaces are page fragments that do not contain valid data or pointers. Page fragments are areas for available space ready to accept new data. As unallocated fragments contain random chunks and fragments, it is impossible to determine which page used to contain these fragments. Recovery of Sqlite Databases from Unallocated Space The recovery of Sqlite databases is not an easy task because although these types of database have a header, there is no footer and length of file is not normally stored within the file either.As we are aware that, Sqlite databases are used by many applications like Firefox, Chrome, many other iOS applications to store data of forensic interest, it is necessary to recover them from unallocated clusters. Table b-tree leaf page has three types of unallocated space- Freeblocks, Freebytes, and ‘unallocated’. The third type ‘Unallocated’ is the space between the end of the cell array and the first cell on the page in Sqlite database. Freeblocks and unallocated can contain recoverable record data while freebytes are too small for further interpretation. Hence, knowing the first freeblock (defined in page header), the length of the cell array (interpreted from the number of cells defined in page header) and offset to the first cell (defined in page header), we can recover the unallocated space in the page. With the Python code for finding table b-tree leaf page unallocated space, we can derive the unallocated data from some Sqlite database. In addition, we have printed the offset of each unallocated block and the contents (in python bytes format) to stdout (standard output). Hence, we now have a way to determine if there is deleted content in the database that can be useful for investigations. E.g., we can analyze the output of android mmssms.db for a phone number to check if there are any deleted records. The recovery of Sqlite databases from unallocated space can be achieved with the help of the python code discussed. Though it helps in recovering the unallocated data in the Sqlite database, it cannot reconstruct the records and it does not reconstruct allocated records with the aim towards reconstructing unallocated records.