Advanced SQLite Analytics with Belkasoft Evidence Center

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:

Write Ahead Logs: Access to Non-Committed Data

From our original article on SQLite forensics you may already know that straightforward analysis of a SQLite file rarely shows the complete picture. Indeed, free and open-source SQLite tools rarely (if ever) deal with freelists. Yet another thing they do not normally deal with is write-ahead logs.

Write-ahead logs, or WAL, work in an opposite way to freelists. While the freelist contains deleted SQLite records, the write-ahead log is used by the SQLite engine to store pages not yet committed into main database.

Belkasoft Evidence Center is an integrated forensic product with advanced out-of-the-box analysis of SQLite databases. Unlike many other tools on the market, Evidence Center automatically parses freelists and write-ahead logs, merging found records together with data from the main database.

General_SQLite

 Evidence Center’s SQLite Viewer is a convenient and powerful built-in tool for thorough examination of SQLite databases

How much information can a write-ahead log contain? Usually, that would be a few hundred records, and it is an awful lot if we are talking, for example, about instant messengers. So what exactly is write-ahead log, and why does SQLite use it?

SQLite Journaling and Write-Ahead Logs

SQLite is a transaction-based database. Historically, SQLite used rollback journals, the atomic commit and rollback mechanism to guard against potential write errors. Rollback journals worked by saving old copies of pages being overwritten with new data into a separate journal file. SQLite removed the journal file if the write operation was concluded successfully. If an error occurred, the engine would roll back the original page from the journal file, returning the database to original state by merging data from rollback journals into the main file.

skype maindb

Skype databases: main.db (8 572 KB) and main.db-journal (1 588 KB)

Rollback journals provided a robust and reliable way of safeguarding information. Unfortunately, their use required a lot of extra read and write operations, causing significant slowdowns on heavy load. Since the release of SQLite 3.7.0 back in 2010, the database engine no longer uses rollback journals. A new commit and rollback method called write-ahead log (“WAL”) was introduced.

Write-ahead logs no longer back up information from the main database. Instead, the new commit scheme uses a temporary database file to write new records to, only merging the temp file with the main database on commit. In essence, write-ahead logs work in the opposite way to rollback journals. Where the rollback journal saved a copy of the original database content into a separate file and then wrote new data directly into the database file, WAL preserves the original content in the main database while writing new data into a separate WAL file. WAL was found to be significantly faster in most scenarios compared to the old journaling mechanism, providing better concurrency and optimizing disk I/O operations.

The two files (the main database and the write-ahead log) are merged when committed. The commit event occurs when a certain size of the write-ahead log is reached, or if a manual commit event is received. Typically, SQLite automatically commits after the WAL reaches the size of 1000 records. Until then, the database reads new records from the WAL file. Does that ring a bell?

Indeed, when analyzing a SQLite database, one can access up to a thousand new records by parsing the WAL file, and read all the old records from the main database file. General-purpose SQLite tools don’t normally offer the choice, either parsing the main database only or (more commonly) automatically merging the content of the WAL file with the main database, thus overwriting old records. Neither approach is good for digital forensics.

Remember how many records a write-ahead log may contain? By default, SQLite commits a checkpoint when the WAL file reaches a threshold size of 1000 pages. A thousand records is an awful lot in the context of chatting or casual Web browsing. A typical chat session never triggers the commit checkpoint, leaving all sent and received messages uncommitted and stored in the WAL file.

Accessing Write-Ahead Logs and Rollback Journals with Belkasoft Evidence Center

Belkasoft Evidence Center natively supports both the old journaling method and the newer write-ahead logs. When opening a SQLite database, Belkasoft Evidence Center will automatically look for rollback journal and write-ahead log files. If either file is discovered, Belkasoft Evidence Center will parse both the main database file as well as the temporary rollback and write-ahead files. As a result, you will be able to see both the old (historic) copy of a page as well as the new (uncommitted) copy of the same page stored in the write ahead log (or vice versa if an older version of SQLite with rollback journals is used). Uncommitted records are then highlighted with a different color:

WAL

SQLite Viewer allows you to see the full picture by reviewing both committed and uncommitted records (the latter are highlighted with blue)

In some cases, cleaning up a database or deleting records does not remove entries from the write-ahead log. This results in the most recent records (up to 1000 in typical conditions) being available for analysis.

SQLite Unallocated Space Analysis

When it comes to storing information in a file, SQLite features a fairly complex structure. As many other databases, SQLite breaks information stored in a file into pages. Inside these pages there are smaller chunks of information called cells. Due to the way SQLite allocates space, new cells are normally placed towards the end of the page. Preceding cells that have not yet been used constitute unallocated areas.

Similar to disk space allocated by the file system, unallocated space in SQLite can be just empty. However, it may contain deleted data or remnants of previously used pages. In other words, unallocated space is constituted from page fragments that contain random pieces of data.

Analyzing unallocated space in SQLite databases is not easy. Since unallocated space does not contain valid data or pointers and is not referenced from the page index, data stored in unallocated areas is difficult to extract and almost impossible to reconstruct into something meaningful. You may find that examining unallocated space can be difficult and time-consuming. Even if you are able to locate a fragment, you will not be able to tell which page used to contain it. Recover the broken relations is also not possible.

It is important to note that absolutely no general-purpose SQLite tools using standard access methods and high-level API’s can access unallocated space or extract data from these areas. You will need a forensic-grade product such as Belkasoft Evidence Center in order to discover unallocated areas inside a SQLite database, view and extract information.

Why should you bother analyzing unallocated space? These free, unallocated areas may contain bits and pieces of data deleted by the user long time ago.

Belkasoft Evidence Center is one of very few products that allow you to examine unallocated space of SQLite databases. The product extracts data located in unallocated space completely automatically. After that, you can use the built-in Hex Viewer for thorough manual examination, or you can simply open the built-in SQLite Viewer and select the “Unallocated space” tab.

Notably, Belkasoft Evidence Center can run carve unallocated SQLite space for supported artifacts, in which case you will have access to the “Carved data from unallocated space” tab (see screenshot below).

Unallocated

Belkasoft Evidence Center can recover information from unallocated SQLite space.

This feature makes work with SQLite databases in Evidence Center even more convenient: instead of spending hours looking through chunks of binary data in Hex Viewer, you can just open the tab with carved data from unallocated space in SQLite Viewer and review it – sorted out by columns, formatted and laid out cleanly.

Belkasoft Evidence Center: Advanced SQLite Analysis at Your Fingertips

Belkasoft Evidence Center implements the lowest-level approach to handling SQLite databases. When it comes to SQLite evidence, Belkasoft Evidence Center is an all-in-one digital forensic tool, and is as close to a one-button solution as at all possible in the complex world of digital forensics. With Belkasoft Evidence Center, you can carve the disk, forensic disk image, or a memory dump for SQLite databases, automatically extract and analyze information from all available sources including freelists, rollback journals and write ahead logs. The built-in SQLite Viewer and Hex Viewer, as well as some of other advanced features, allow you to perform low level examination and, for instance, can help discover evidence that is still available in unallocated areas of the SQLite database.

1. sqlite viewerBelkasoft Evidence Center is perfectly equipped to handle existing, emptied, deleted or corrupted SQLite databases

Belkasoft Evidence Center is able to recognize hundreds of applications that use SQLite, extracting and displaying mobile apps data, browser histories, smses, messages, call logs or chat logs discovered in current, deleted, uncommitted and unreferenced database records.

Interested in SQLite analysis? Get your free evaluation license of Belkasoft Evidence Center at http://belkasoft.com/trial.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: