Much has been said about the different tools to extract, view, and recover SQLite databases. Why is SQLite analysis so important for digital forensics? Why is SQLite not straightforward to investigate? Why use Belkasoft Evidence Center for SQLite analysis? Read along to find out!
SQLite: The De-Facto Standard
SQLite is today’s database of choice for nearly every software manufacturer with very few exceptions. Unlike MS SQL Server, SQLite is extremely lightweight and compact, does not require installation, and can be easily distributed with the product if needed. In other words, SQLite is perfect for applications with light database loads – such as Web browsers, instant messengers, or password keepers. Indeed, SQLite is employed by thousands application developers including some well-known names. So who is using SQLite?
Applications Using SQLite
SQLite gained its well-deserved popularity among developers on all major desktop and mobile platforms including Windows, Linux, and Mac OS, as well as Android, iOS, and Windows Mobile. With SQLite being an open format there are no legal, financial or technical limitations that would restrict developers from using the database. As a result, SQLite databases are used system-wide in Android and iOS as containers for call logs and messages, configuration settings, calendars, notes, search history, messages, system logs, Web browsing history and password management. Major Web browsers (Chrome, Firefox) and instant messengers (Skype, WhatsApp) are also using SQLite. Even Belkasoft Evidence Center, a digital forensic tool that can parse others’ SQLite databases, employs a SQLite database internally to keep and manage cases!
To sum it up, SQLite is used in the following applications:
• Android: system-wide for call logs, message history, settings, system logs, apps etc.
• iOS: system-wide for call logs, message history, system logs, apps etc.
• Instant messengers (on all desktop and mobile platforms): Skype, WhatsApp, Viber, eBuddy and hundreds more
• Web browsers (on all desktop and mobile platforms): Firefox, Chrome, Safari
• Other apps: PhotoBox, Picasa Explorer and thousands more
With that many applications using the SQLite format, choosing the correct forensic tool becomes utterly important. One can ask, however, “Why can’t we just use the free DB Browser for SQLite (former SQLite Database Browser)? Oh, and I’ve heard there’s that Firefox plugin! Can we use that to browse SQLite databases?” Yes, you can, but you should keep in mind that in this case you cannot rely on the results you get. And here’s why.
Free SQLite Forensic Tools: You Get What You Pay For
With free SQLite tools you at least get a program that can display the content of a SQLite database. However, this is often not much use for the purpose of digital forensics. Let’s look at this screenshot:
Obviously, the view is empty. You always get what you pay for, in this case, zero for zero…
So what to do if no messages are showing up in the database (message count = 0), but its size is a full 5 MB and a hex viewer shows bits and pieces of conversations? It’s time to use a proper forensic tool. Here’s the very same database opened with Belkasoft Evidence Center’s built-in SQLite Viewer:
As you can see, Belkasoft Evidence Center discovered as many as 53 records in the “Messages” table and highlighted them in red color, signaling that these were deleted by the user. How does that happen?
Native SQLite Processing
The problem with free SQLite tools is the method they use to access databases’ records and tables. In order to keep things simple, these tools just use ready-made components to process SQLite files. These days finding a suitable open-source component in one of the many code repositories is a matter of minutes. However, such components are inherently limited in the way they handle SQLite files. Following established guidelines, these components (as well as the tools using them) communicate with the SQLite engine by using a high-level API. This is a stable and reliable way to handle databases, only it does not work with corrupted SQLite files. Neither can it recover records that have been deleted or not yet committed into the main database.
Belkasoft Evidence Center is not using third-party components for accessing SQLite databases. Instead, we developed our own low-level code for discovering SQLite evidence. This opens the door to a number of things that are not possible if you are using most other tools.
For one, Belkasoft Evidence Center allows analyzing corrupted SQLite databases if, for example, the database files were deleted by the suspect and then recovered with file carving.
Besides, SQLite analysis algorithm used by the product is advanced and efficient, allowing to process gigabytes of SQLite data in reasonable time.
Moreover, Belkasoft Evidence Center fully supports analysis of “freelists” – special area in SQLite databases where unused pages are stored. Belkasoft Evidence Center automatically discovers evidence located in those records that were deleted from SQLite databases.
This enables access, for example, to deleted entries from the call log or Skype histories (as shown in the screenshot above), and even allows recovering deleted iMessages or SMSes from iPhone backups. Let’s talk a bit more about accessing deleted records in SQLite databases.
Recovering Deleted SQLite Records with BEC
When analyzing evidence obtained from various sources, you will likely encounter a database that contains at least a few deleted records. These records may contain information that is vital for an investigation – such as deleted browser history, messenger chat history, sent or received SMS or iMessages, or data from thousands of other apps.
Why is it possible to access deleted messages at all? The reason lies in the way in which SQLite handles deleted records. In order to maintain performance, SQLite does not immediately erase records deleted from the database. Instead, these records are kept temporarily in “freelists”, which can be accessed and parsed using Belkasoft Evidence Center.
While extracting data from freelists, the product automatically figures out which column the deleted items used to belong to, and merges them into this column.
Viewing deleted items in Belkasoft Evidence Center is easy – the product automatically locates and lays out all of the data for you, marking whether it was deleted or not:
"Is Deleted" column helps to understand whether a record was found in existing SQLite database table or recovered from a freelist
Convenient features of SQLite Viewer
We tried to make work with SQLite as convenient as possible, while integrating it into the variety of Evidence Center’s features and capabilities. For instance, while navigating through artifacts found by the product, select an application that uses SQLite for storing data, and Evidence Center will automatically display them in SQLite Viewer:
If you choose Skype profile, the contents of Skype’s main.db are automatically shown to you in SQLite Viewer, including data from freelists.
Item Properties is a convenient window that will show you all of the existing columns of a table, so that you can review the selected record as a whole, even if the amount of columns is too big to fit into the list of records.
If you want to leave just a few of the most important columns, you can change their number by right-clicking the header of the table and selecting visible columns and adjusting their size in convenient "column chooser":
Column Chooser allows to select or deselct the columns, arange them, and adjust their width (automatic or proportional, in % of the maximum size)
Belkasoft Evidence Center allows formatting of any column. For instance, timestamp column can be changed to represent information in the suitable time format:
After that, the contents of the whole column will be adjusted accordingly:
As you can see, timestamp column now correctly displays time and date in convenient format.
Interestingly, column formatting is taken into account and preserved when you create a report from SQLite Viewer:
PDF report, generated based on data from Messages table of Skype’s main.db
In this paper, we have not mentioned some of other important features and capabilities of Belkasoft Evidence Center, such as SQLite unallocated space analysis (don’t confuse with a hard drive unallocated!), analysis of Write-Ahead Logs and journal files, smart carving of SQLite databases, examination of SQLite using Hex Viewer, etc. We will try to cover these topics later.
Advantages of SQLite Analysis with BEC
To sum up, using Belkasoft Evidence Center for SQLite analysis gives you the upper hand in digital investigations. Not only is it fast, but it also gives you access to some of the information that would not be available otherwise.
Deleted records from little-know, but very important areas of SQLite databases, such as freelists and unallocated space, will be found and analyzed by Evidence Center within short time and completely automatically, thus saving your time and effort. You can find even more records in write-ahead logs, a valuable source of data. Native SQLite parsing will allow you to restore most of the information by carving damaged or deleted databases.
Convenient SQLite Viewer tool is built into Evidence Center and available to you out of the box, giving you access to comprehensive low-level expertise of SQLite databases. You can easily present your findings in a court by creating a report directly from SQLite Viewer’s interface:
Belkasoft Evidence Center makes your investigations easier, faster, more comprehensive and more effective. Learn more on our website or download a full trial version today at http://belkasoft.com/trial.
