SQLite is an open source, in-process library that implements a self-contained, zero-configuration, and transactional SQL database engine. This is a complete database with multiple tables, triggers, and views that are contained in a single cross-platform file. As SQLite is portable, reliable, and small, it is a popular database format that appears on many mobile platforms.
Apple iOS devices, as with other smartphones and tablets, make heavy use of SQLite databases for data storage. Many of the built-in applications—such as Phone, Messages, Mail, Calendar, and Notes—store data in SQLite databases. Apart from this, third-party applications installed on the device also leverage SQLite databases for data storage.
SQLite databases are created with or without a file extension. They typically have the .sqlitedb or .db file extensions, but some databases are given other extensions as well.
Data in SQLite files is broken up into tables that contain the actual data. To access the data stored in the files, you need a tool that is able to read it. Most commercial mobile forensic tools—such as Belkasoft Evidence Center, Magnet AXIOM, and Cellebrite Universal Forensic Extraction Device (UFED) Physical Analyzer—provide support for the examination of SQLite databases. If you don't own one of these tools, some good free tools are as follows:
- DB Browser for SQLite (DB4S): This can be downloaded from http://sqlitebrowser.org/.
- SQLite command-line client: This can be downloaded from http://www.sqlite.org/.
- SQLiteStudio (https://sqlitestudio.pl): This is a free cross-platform SQLite manager with support for Windows 9x/2k/XP/2003/Vista/7/8/10, macOS, and Linux.
- SQLiteSpy: This is a free graphical user interface (GUI) tool for Windows. You can download it from http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index.
macOS includes the SQLite command-line utility (sqlite3) by default. This command-line utility can be used to access individual files and run SQL queries against a database. In the following sections, we will use both the sqlite3 command-line utility and other SQLite tools and browsers to retrieve data from various SQLite databases. Before retrieving the data, the basic commands that you will need to learn are explained in the following sections.