SQLite is the storage mechanism of choice for the major mobile operating systems in the smartphone market today. It is a standalone database and does not operate under the traditional server-client protocol, which is one reason it is so desirable for mobile devices. Its small, self-contained footprint is also a draw to device engineers. Just as the engineers like that all the data and application functions are contained within one file, mobile device examiners could also agree that this database file makes the recovery of data a less daunting task.

One feature of SQLite that has proven to be a hidden gem for mobile forensic examinations is the fact that ACTIVE SQLite database files often contain DELETED content. When user data is deleted from within an application, many times, the data is simply “marked for deletion” in the SQLite database. When information is deleted from the database, pages containing that data are no longer actively in-use. Unused pages are stored on the freelist and are reused when additional pages are required.¹

As the smartphone tools became more sophisticated, they began capitalizing on the fact that it was relatively easy to recover deleted SQLite database entries if they understood the schema of that particular database. The big commercial vendors began recovering deleted content from the obvious user-generated databases, such as Address Book, Call History, Calendar, Notes, and SMS. Why did they start here? Most mobile platforms were developing devices that came standard with those entities, and the SQLite Schema would, for the most part, stay the same. As the commercial vendors realized that the recovery of deleted content from more than just the standard cell phone files was in demand, they began adding deleted content from third-party applications into the mix.

What is most important to note: All the major vendors are offering the recovery of deleted information from SQLite databases. What the examiner must always be cognizant of is that the vendors cannot support every application from every app store that is available on the market. Research has shown that the most in-demand apps and those that would contain communication and geographical data are often at the forefront of the development list. Just because a tool does not report deleted content related to an application does not mean that deleted content does not exist. Application databases of interest to the examination should always be reviewed for deleted content in a Hex viewer or a tool that can access deleted SQLite records.

Reference:

[1] https://for585.com/kjnw9 (SQLite.org)

It is important to note that not every database will retain deleted records, as you will notice that many of Apple’s native databases no longer retain deleted content. Second, not all database viewers, to include those built into your mobile forensic analytical platforms are created equally. One of the best stand-alone SQLite database tools is Sanderson’s Forensic Browser for SQLite, and I prefer Oxygen’s built-in database browser over the built-ins supplied by other prominent vendors. If you are utilizing Cellebrite Physical Analyzer (Figure 1), be sure to click on the pickaxe at the far right to display deleted entries per table. If you are using Oxygen’s database browser (Figure 2), which can be used as a stand-alone utility, it will display deleted entries per table or from the entire database. There is also a free SQLite database deleted records parser created by Mari DeGrazia, which has been included in your VM and can be downloaded from GitHub.¹

Reference:

[1]https://github.com/mdegrazia/SQLite-Deleted-Records-Parser