Applications contain key pieces of information that can provide great insights to an investigation. Most of the databases stored on mobile devices (iOS & Android) are SQLite databases. SQLite is a powerful and relatively simple way to store data. When extracting all SQLite databases from a mobile device, you will note that most of the databases are decoded by UFED Physical Analyzer, (which provides support for more than 200 unique apps and 3,000 app versions). However, there are still some databases that are unfamiliar or are not supported. With 2.4 million apps* available on the market today, there isn’t a single mobile forensics tool that can support all these third-party applications.
Cellebrite’s SQLIte wizard
With the release of UFED Physical Analyzer 6.0, we announced a new capability that enables you to access even more data from apps, including unsupported apps. In short, you can access any information stored on mobile devices, reduce time to evidence and close more cases with the new UFED PA.
As an examiner or an investigator, one of your challenges is to get as much information possible out of a mobile device. In many cases, the potential evidence may reside inside a third-party app that’s installed on the device. When this app is not supported by any mobile forensic solution, the alternative is to manually analyze and investigate the content of the app’s database.
With the new and unique SQLite wizard, you can visually map additional data from different databases, build queries and map database fields to supported models, (such as call logs, instant messages and other generic events).
I’ll take you through a step-by-step tutorial on how to recover data from a database using this tool.
If you know that a specific application was used on the device, but it was not automatically parsed during the decoding process, you can look into the database’s content and extract the data.
The database in the project tree (under data files), includes a list of all the databases available, with an indication that specifies if it was decoded by Cellebrite. We suggest that you filter out all the decoded databases, and focus on manually decoding the non-decoded databases that you feel may be important for the investigation.
Alternatively, you also have the option to manually decode a database that was already decoded. And why? There are new developments for applications all the time- for example, WhatsApp recently added video chat, and while Cellebrite is on the task to provide support for this new feature in upcoming releases, you may require this specific record immediately, so manually decoding the database will provide you with instant access to potential evidence.
Let’s assume that you want to extract data from the mmssms.db (database on an Android device), which you suspect may contain critical evidence. First, start the manual decoding process by selecting this database. Within the database viewer pane above, you can see that the selected database has a total number of 362 records, so plenty of information there.
To get started, open the SQLite wizard:
The SQLite wizard allows you to include deleted data. Selecting this option increases the chances of false positive records, and in many cases, the interesting data or potential evidence may be found as deleted.
The list of database tables is available on the left pane. Select the “sms” table with 112 potential records.
Drag the database table to the work area. You have the option to drag several tables and even create relationships between tables (or join in SQLite language). An SQLite query is automatically generated. Alternatively, you can also write your own SQLite query. To see your query results, click on the preview button.
To map the selected data, you need to select one of the existing data models (e.g: call logs, instant messages) or a generic model. For the mmssms.db database, which holds SMS info, you should select the SMS Messages model. Now drag the field types to the correct columns. (See how the screen should look like below before you drag and drop).
Some columns have special formatting options that allow you to convert enum, lookup, XML/plist and timestamp formats to help map the relevant fields and columns, and also make the information readable by selecting the timestamp global format, for example, or customizing your own format.
Now that you completed the mapping process, run the query created in a way that new records are added to the SMS Messages model.
For the the SMS Messages model, there were 207 records as part of the decoding, and after running the manual query there are 319 records available. Therefore, by using the SQLite wizard, I was able to recover a total of 112 new records!
The new records can be treated just like any other decoded record, I can tag, filter, search and include those in my report output. The manual queries can be saved for future use, where you can auto run it as part of the automatic decoding process, and recover huge amount of data that you would otherwise would not be able to access.
In addition to the manual SQLite query tool, we developed another tool to enrich your investigation with valuable data from unsupported database sources, using the Fuzzy model plugin. This innovative solution identifies new data sources, handles and parses unknown databases and endless application databases – some of which are supported by Cellebrite and some are not. Information is being automatically analyzed using a heuristic process and a unique set of rules.
This solution scans and analyzes all the databases and all tables within the databases, and automatically maps the records into a known model ( such as email, IM, call logs etc.).
There are two types of fuzzy models:
- Fuzzy objects – View extracted data from any database which has not being decoded by UFED Physical Analyzer’s parsers. This model holds information regarding a certain artefact such as contact, account etc.
- Fuzzy events – View extracted events such as messages, call logs etc.
For each one of these models, you can see the list of results presented in a table and the database view pane, which displays the contents of database files that were found in the extraction.
Once the decoding process is complete, you can run the Fuzzy plugin directly from the main menu (Tools àRun Fuzzy model plugin).
The results are presented under Analyzed data in the project tree. Any record in these two tables can indicate a potentially relevant piece of evidence. To find more details, it is recommended to analyze the source database.
Records with a timestamp are also available in the timeline view, which allows you to track and view events in a chronological order to quickly understand the chain of events.