SQLite Database Deleted Records Carving & Forensics

SQLite is a compact database engine highly integrated with embedded devices and locally used applications in various Operating Systems. Recent years have seen extensive usage of portable devices like tablets, smartphones, etc. and this has given rise to adaption of the SQLite application. It also amplifies the chances for surfacing digital evidence using forensic analysis of SQLite database files. Intentional deletion of crucial data from SQLite databases, which are embedded within the applications, can rarely be done by the average person. However, technically expert criminals can perform such tactics to avoid being discovered. In order to perform the deleted data recovery, it is important to understand some factors like structure and architecture of SQLite databases, how the records are stored in the pages of the databases, and how the databases align all the elements.
SQLite Database Structure
SQLite databases belonging to any application are structured with many pages of fixed size and a fixed number of bytes, which can be 1024, 2048, 4096, 8192, 16384, 32768 or 65536. The number of pages and size is declared in the header of the database. Official documents state that header of the page is stored in the first 100 bytes of the first page, and rest of the page structure is maintained. There are mainly 4 types of pages for SQLite databases, and the type can be detected by the first byte of each page, which occurs after the file header. The important row data of the database lies in a “Leaf Table B-Tree” type of page. It takes the flag value as 0xD i.e. 13 decimal.
In order to carve an entire SQLite record, it is important to analyze schema. If the page has overwritten the cell headers, the carving of data from the database including the deleted data can be difficult. However, when we perform the recovery on specific string content of the page, it can be conveniently done. File signature, which is an important element in any file format belonging to the SQLite file, is
File Signature: ‘‘0x53 51 4c 69 74 65 20 66 6f 726d 61 74 20 33 00’’

This file signature can help you to identify the SQLite database, as this file does not have any specific file extension. The header appears in the beginning of the file, which also contains the signature. The lower part will comprise of the schema table, which contains the table information.
After the header portion, the page comprises of b-trees, which are again separated into index b-tree and table b-tree. The data is stored in the table b-tree in cells, which act as an elementary unit of store operation. Cell structure and contents depend on the internal and leaf pages. The internal page is available in the middle section of the tree and the cells store the locators or pointers for locating the lower page. The leaf page is present in the bottom.
The structure of the schema table is comprised of specific schema names. The string, which appears after root page numbers, is SQL query. When investigators want to recover the deleted records, the table information has to be acquired using this SQL query associated with that table. Information in the schema is: table name, field name-type, and number of fields which are usually of four types; TEXT, NUMERIC, INTEGER, & BLOB.
SQLite Deleted Data Recovery from Journal Files
The transaction log file of the SQLite database serves as the best source for acquiring deleted records from the database. However, this process is valid and helpful only when the deleted records are available in the transaction log files. SQLite database system has a different management for the transaction log files and it creates a separate journal file named as “\database file name[/\file extension[-journal’’ when a query is sent to the associated database. Thus, it saves the history of inputs made and outcome results in the form of records before the query is completed on the main database. Forensic analysis of these journal files can bring out a lot of evidence if the records are still available in the journal files. However, modern investigation theories suggest that it is always better to perform the investigation on the records recovered directly from parent databases instead of journal files.
How is Record Deletion Managed in Application Software?
The management of record deletion in application software depends on the application and varies accordingly. The rules or directions for the deletion can be classified as: “Overwritten with zero”, “Remove deleted data”, or “Set as free”. Its recoverability methods and probability also change accordingly.

• The first rule, where record deletion prompts overwriting with zero, is highly used in web-browsers like Safari, Chrome, Firefox, etc. In this case, the recovery of data is not possible even if the deleted data is identified.
• The second rule is in the case of an iPhone, where the small sized data is deleted. When this type of deletion occurs, the whole area is deleted and then there is no way to trace the event of deletion.
• The third rule is where the deleted data is set free and the data is still available in the system. This case includes examples like deleted messages in Skype messenger. When iPhone messages are deleted in bulk, this rule is also followed. In this case, deleted data still remains in the file and SQLite data recovery is possible if it is not overwritten.

Recovery Methodology
While performing recovery of deleted records from the SQLite database, it is important to identify the number of fields in the schema table and its type. Afterwards, the page can be scanned and the data within the unallocated area can be viewed. There can be different cases where the deleted data comprises of an independent record, more than two records, or if the data is partially overwritten.
Considering all the factors and cases, it can be presumed that recovery of SQLite databases depends on various aspects. Once the SQLite database is acquired from the respective locations of Windows, iPhone, Web Browsers, SmartPhones, Androids, etc. a python script can be executed against this attained database. Working on the required workstation with Python installed, users can easily perform this recovery process using a script such as the sqlparse python script developed by Mary DeGrazia.
These scripts can easily recover deleted cell records from the SQLite database and help in investigations. The output results, i.e. recovered deleted records, will then be saved in a text file.
Conclusion

SQLite database engine is used in multiple application softwares for saving data in databases. This includes Firefox, Chrome, Safari, iPhone OS, Android OS, Windows OS, Mac OS, Skype, Whatsapp, TweetDeck, etc. These databases can be of huge importance for forensic investigation as data related to SMS, call logs, cache, history, searches, web cookies, etc. is saved in these databases. In order to perform complete investigation on these SQLite databases, deleted data recovery is extremely important. The above-mentioned script can be helpful for investigators in order to analyze the deleted data.

Fuente: http://articles.forensicfocus.com/2015/10/15/sqlite-database-deleted-records-carving-forensics/