SQLite Studio and SQLite Databases
In Part 1 we covered the tools we’ll need to construct a utility to extract and exportiMessage/SMS data. We went on to explain the structure of the backup, files and name hashing. In this segment we’ll cover the SMS database. Working with the various SQLite databases for the purpose of exporting iMessage/SMS data does not require SQLite Studio. However, this tool is indespensible when it comes to validating and troubleshooting the SQL interactions in the Python code.
So, let’s get started with the SMS database. In Part 1 we explored the various files and databases making up the backup and we know the hashed filename for the SMS.db is , so let’s get SQLite Studio setup to use it…
Select the SMS Database
1. Launch SQLite Studio, and from the top menu click Database | Add Database.
2. In the Database selection dialog click on the folder in the File section to browse for a database.
3. Because the files are named with a hash, and not as database.db, no files will display in the selection window. Because of that we need to change the file filter. In the file selection window change the file type to All files (*). You should now see the list of files.
4. Then navigate to the “3d/” subdirectory. (Remember that the directory is always the first two characters of the filename).
5. You’ll need to scroll down and connect to the SMS.ddb file (3d0d7e5fb2ce288813306e4d4636395e047a3d28). With that file highlighted, click Open and Okay.
Connect the Database
The database has been added to SQLite Studio, and should show up in the database window. (If the database list is not showing click View and place a checkmark next to Database) Now we need to connect to it, so right-click on the database and select ‘Connect to the database’. You’re now ready to work with the data.
Create a Query
To get an idea of what we’ll be working with we can look at the message table data in the SQLite Studio query facility. In the top menu, click on Tools | Open SQL Editor. This will open a new SQL query window. Enter the following into the window:
SELECT ROWID, text, handle_id, service, account, date, is_from_me, cache_has_attachments FROM message
Click on the Run icon (blue triangle) to execute the query and you’ll get your first look at the messages stored in the backup. Below is a snipit of the returned data from my system.
The fields displayed represent the following:
- ROWID is the unique message_id
- text (obscured) – the actual text in the message
- handle_id – the id of the sender in the handle table (foreign key)
- service – iMessage or SMS delivery service
- account – phone or email of the sender
- date – unix timestamp when the message was sent
- is_from_me – 0: No or 1: Yes
- cache_has_attch – 0: No or 1: Yes for attachment
Take some time to further explore the database by expanding the Tables for the database and explore and query some of the other tables. You can right-click on any table in the Database view and select Generate Query for table > SELECT. That will automatically open a new query window with a completed query for every field in the table.
If you’re feeling adventerous, you can connect the Contacts database and look around in it as well. (31bb7ba8914766d4ba40d6dfb6113c8b614be442)
You should take some time to explore the other features inclcuding formatting, saving, loading and more. This tool has some powerful features that makes working with SQLite easy.
In Part 3 we’ll look at the date, datetime and those ugly unix-timestamps used in the databases, and build some queries to extract and format data. With that we’ll construct the first of the queries needed in the project to export iMessage/SMS data from you iPhone backup.