In my previous blog, the use of EnScript was introduced as a benefit to extend the artifact reach and add custom parsing for the yet supported. This blog will focus on the configuration and use of the Generic SQLite Database Parser, illustrating how custom SQLite queries can be added for one or many SQLite databases, how one or many custom queries can be executed from within the EnScript, and how the exposed data is presented within OpenText™ EnCase™ and as TSV (Tab-separated values).
For review of a single SQLite database that may use a Write Ahead Log (WAL), or for development of relevant SQLite queries, the View SQLite with WAL EnScript plugin will be introduced. Whilst the specific databases mentioned will relate to Apple iOS or macOS, the subject matter explored is directly transferable for any SQLite database.
As a digital forensic examiner, you might have a collection of SQLite queries that can be used in any or all of your DFIR examinations. These might have been created over a period of time and stored in a text file with other SQLite queries grouped by a common theme, such as:
- macOS
- iOS
- Apple Photos
- Web Browser Artifacts
This is great and admit as a forensic examiner myself I have followed a similar practice. The relevant query for a given database can be copied and pasted from the text file into a SQLite viewer and executed. Could this process be made more efficient in a time when work-loads and quantities of data in the forensic examination are increasing?
Using the Generic SQLite Database Parser, EnScript can improve workflow, following some initial configuration. The basic premise of the EnScript is to utilize built-in EnCase SQLite parsing functionality to automate the running of one or more SQLite queries against one or more SQLite databases. The EnScript can be configured with the name of the SQLite database as a parent folder for one or more child objects; custom SQLite queries that can be executed against said database.
Take for example the Photos.sqlite database used by the Photos app on both iOS and macOS. Querying this database can allow the examiner to quickly and easily identify photographs that have been:
- Modified by the user in the Photos app
- Deleted and referenced in the Recently Deleted album
- Contain relevant location data.
As an examiner of the SQLite database and user of the EnScript, it might be advantageous to formulate a naming convention to allow the query to be easily identified. In the following example, the SQLite query name begins with either iOS or macOS to easily identify and differentiate.
for iOS or macOS
Multiple output options are available, bookmarked directly in the EnCase case or exported and saved as a TSV file for later review in third party applications, such as Microsoft Excel.
Given the plethora of Open Source research and information relating to DFIR, an abundance of SQLite queries exist and are available from multiple and respected sources. These can be included for use with the Generic SQLite Database parser, configuring as described above.
It is recommended that the SQLite query being used is validated to ensure it produces expected results. Even if a SQLite query worked for a SQLite database from a given version of an app or mobile OS, it does not mean it will continue to provide required results for later versions.
It is also worthwhile to consider the use of the View SQLite with WAL EnScript plugin. This allows a single SQLite database and Write Ahead Log (WAL) to be temporality exported from the EnCase case and viewed with a third party viewer of choice. The use of this EnScript plugin is beneficial when:
- Developing custom SQLite queries for use with the Generic SQLite Database Parser
- Validation of a custom SQLite queries before configuring them for use with the Generic SQLite Database Parser
- Ad-hoc review of a relevant database, from an app or OS artifact that is not common to #DFIR examinations and might not be beneficial to include in the Generic SQLite Database Parser
- General review of a SQLite database that uses write ahead logging
The use of both the Generic SQLite Database Parser and View SQLite with WAL Plugin as described cannot be recommended enough. Their use has been, and continues to be, invaluable and are used on a frequent basis. Whilst this blog has been written with respect to mobile device examinations, the EnScript programs mentioned are equally valuable to the examination of and research relating to operating systems such as macOS.
The EnScripts mentioned are demonstrated and used during both the DF125 Mobile Device Examinations with EnCase and DF420 Mac Examinations with EnCase. Working with SQLite Databases and SQLite Data Recovery are extensively discussed and demonstrated during the DF320-Advanced Analysis of Windows Artifacts with EnCase.
Training classes can be registered for and purchased individually using the above links or taken as part of the OpenText Learning Subscription, Security Edition Premium. Subscribe to one year or for more information, a quote for a two-year subscription or an upgrade contact us.
