Accessing Backed Up iPhone SMS Messages

Introduction

When backing up the iPhone to your computer iTunes creates multiple files, one of which is all the SMS (text messages) on the phone. Each time a backup is created a new folder is created under C:\users\<username>\AppData\Roaming\Apple Computer\MobileSync\Backup. This folder's name is a string of (what appears to be) random hex digits and will contain multiple files in it. The one that contains the SMS messages will have the file name 3d0d7e5fb2ce288813306e4d4636395e047a3d28 without any extension; this file is the SMS message SQLite database. This article will provide you with the layout of this database and show how to access the messages.

Recommended Software

While first trying to access the database to determine if schema, I tried multiple programs to open the database file. However, for some unknown reason, many programs failed to open the file. The one program that was able to find that could open the database was Database Browser for SQLite and can be downloaded from here.

Also, in order to access the database from .NET code a third-party library is necessary. Again, multiple assemblies where tried without success, but I did find one that worked. This is the dotConnect for SQLite component from Devart. The Standard version is free (although it requires you to enter your email address and first name to download it) and there is a Profession version trial. Both versions (version 5.2) can be downloaded here.

SMS Database

When you open the SMS database file in Database Browser for SQLite you will see a total of nine tables. Below are the nine table names and their purpose (as far as I could determine):

¹ROWID column, when present in a table, represents the primary key for the table.
²Date/time information is stored in a specific format. The number listed is the number of second since midnight of January 1, 2001 (Mac Epoch).

Accessing SMS Data

In order to access SMS messages (ignoring attachments such as pictures) we only need to concentrate on two tables: the message and handle tables. The message table contains a total of 38 columns, provided below for your reference. Where I was unsure of a specific columns function I noted that and give an explanation based on the column name where I have a reasonable idea of what the columns purpose is. If I have no reasonable idea I simply marked the purpose as "Unknown". Luckily none of these columns are important for just grabbing the most commonly sought after SMS message information (e.g. contents, recipients, date/time, etc.)

¹I do not know at this time if this is the date/time that the message was delivered to the carrier's network or to the intended recipient, although I strongly believe it is the time to the network.

The handle table is much simpler. It maps the handle_id column in the message table to a specific phone number/service combination. There are only a total of five columns:

Prior to Coding

Now that we know the format of the SQLite database and some of the tables, we can begin to access and manipulate the data. If you have not already downloaded the dotConnect for SQLite component, please visit the download page and select choose the Standard version as it is free and should be more than adequate for what we are using it for; for this project I am using Standard Version 5.2. Please note that although the component is free, you will be required to enter your email address and first name before you can begin the download.

The code presented here is for a simple console application that will query the database file for all messages sent, and received, between the phone's owner and a specified telephone number. Since the file is a SQLite database the majority of SQL statement should work for querying data. Since you really should not be changing any data in the database, all queries run against it should be SELECT statements. If you are not familiar with SQL queries, please visit http://www.tutorialspoint.com/sqlite/sqlite_select_query.htm for an introduction to SQL SELECT queries. You should also have no trouble finding any additional information you need on SQL statements using Google.

Please note that this console application assumes that the SQLite database file (3d0d7e5fb2ce288813306e4d4636395e047a3d28) is saved in the Debug or Release folder, depending on the mode your code is running in. This is strictly for demonstration and testing purposes. In a production product you would want the ability for the user to search for this file and select which version of the file they want to access, as each time the phone is backed up a new SMS database file is created (with the same name) under a different folder in C:\users\<username>\AppData\Roaming\Apple Computer\MobileSync\Backup. You can use the folder attributes (creation/modified date/time) to find the most recent backup.

Using the Code

Before writing any code, first add a reference to the dotConnect for SQLite component (Devart.Data.dll and Devart.Data.SQLite.dll) and import the required namespaces:

    Imports System.IO   
    Imports System.Text.RegularExpressions  
    Imports Devart.Data.SQLite      
    

Next create a new Message class and Service enumeration. The Message class will help store the information retrieved from the database and perform the conversion of the data/time information. The Service enumeration helps to identify the service that was used to send/receive the message (either SMS or iMessage):

    Public Enum Service
        SMS
        iMessage
        UNKNOWN
    End Enum
    
    Public Class Message
        'All date/time values in the database are saved as the number of seconds since Midnight of January 1, 2001.
        Public ReadOnly Epoc As New DateTime(2001, 1, 1, 0, 0, 0, 0)
        Private _messageDateRaw As Integer
        Private _dateReadRaw As Integer
        Public ReadOnly Property Direction As String
            Get
                If IsFromMe Then
                    Return "Sent"
                Else
                    Return "Received"
                End If
            End Get
        End Property
        Public Property MessageDate As DateTime
        Public Property MessageText As String
        Public Property DateRead As DateTime
        Public Property Service As Service
        Public Property IsFromMe As Boolean
        
        Public Sub New(messageText As String, messageDate As Integer, dateRead As Integer, service As String, isFromMe As Boolean)
            _messageDateRaw = messageDate
            _dateReadRaw = dateRead
            Me.MessageText = messageText
            'Convert the saved date/time value in the database to a DateTime object
            Me.MessageDate = Epoc.AddSeconds(_messageDateRaw)
            Me.DateRead = Epoc.AddSeconds(_dateReadRaw)
            If service.ToLower.Trim = "sms" Then
                Me.Service = ConsoleApplication1.Service.SMS
            ElseIf service.ToLower.Trim = "imessage" Then
                Me.Service = ConsoleApplication1.Service.iMessage
            Else
                Me.Service = ConsoleApplication1.Service.UNKNOWN
            End If
            Me.IsFromMe = isFromMe
        End Sub
    End Class

The main part of this demonstration application is pretty simple; read in a phone number, format the phone number, execute a SELECT query against the SQLite database, and export the formatted results to a text file. Since this is strictly for demonstration purposes, this application will simply find all messages sent between the phone's owner and the entered phone number, however it is heavily commented and should be easy to expand upon since it uses standard SQL. There might be slight differences in queries since this is SQLite, but I have not come across any as of yet.

    Sub Main()
        Dim phoneNumber As String
        Console.Write("Please enter a phone number, including the area code: ")
        'Read phone number enter by user
        phoneNumber = Console.ReadLine()
        'Remove all non-numeric characters
        phoneNumber = Regex.Replace(phoneNumber, "[^0-9]", String.Empty)
        If phoneNumber.Length = 10 Then
            'Phone number contains the area code, without the leading 1
            phoneNumber = "+1" & phoneNumber
        ElseIf phoneNumber.Length = 11 Then
            'Phone number contains the area code with the leading 1
            phoneNumber = "+" & phoneNumber
        Else
            Console.WriteLine("The phone number entered is not valid.")
            Console.ReadKey()
            Exit Sub
        End If
        'The name of the SMS backup file.  This assumes the file is in the same folder at the executing assembly.
        Dim dbConnection As String = "Data Source=3d0d7e5fb2ce288813306e4d4636395e047a3d28.db"
        'Create the SQL SELECT statement to join the message and handle table based on the provided phone number, sorted by ascending date
        Dim sqlSelect As String = "SELECT text, message.service, date, date_read, is_from_me FROM message JOIN handle ON message.handle_id = handle.ROWID WHERE handle.id='" & phoneNumber & "' ORDER BY date ASC"
        Dim dt As New DataTable
        'Create a new SQLite connection
        Using cnn As New SQLiteConnection(dbConnection)
            'Open connection to the database
            cnn.Open()
            'Create the select command
            Dim selectCommand As New SQLiteCommand(sqlSelect, cnn)
            'Execute the SELECT command
            Dim reader As SQLiteDataReader = selectCommand.ExecuteReader
            'Load results into a DataTable
            dt.Load(reader)
            'Close the connection to the database 
            cnn.Close()
        End Using
        'Check if any results were returned
        If dt.Rows.Count > 0 Then
            'At least one result was found.  Create a new StreamWriter to write the results to a file.
            Dim msg As Message
            'Create a new list to hold Message objects
            Dim messages As New List(Of Message)
            'Loop through all the results
            For Each row As DataRow In dt.Rows
                'Create a new Message object
                msg = New Message(row.Item("text").ToString, CInt(row.Item("date")), CInt(row.Item("date_read")), row.Item("service").ToString, CBool(row.Item("is_from_me")))
                'Add new Message object to the list
                messages.Add(msg)
            Next
            'Create a new StreamWriter to export the results to a file
            Using sw As New StreamWriter(New FileStream("export.txt", FileMode.Create, FileAccess.Write, FileShare.None))
                sw.WriteLine("Total Message: " & messages.Count.ToString)
                sw.WriteLine(String.Empty)
                'Loop through all the Messages in the list and write the information to the stream
                For Each m As Message In messages
                    sw.WriteLine("Sent/Received: " & vbTab & m.Direction)
                    sw.WriteLine("Date: " & vbTab & vbTab & m.MessageDate.ToLongDateString)
                    sw.WriteLine("Time: " & vbTab & vbTab & m.MessageDate.ToLongTimeString)
                    sw.WriteLine("Service: " & vbTab & m.Service.ToString)
                    sw.WriteLine("Message Text")
                    sw.WriteLine("------------")
                    sw.WriteLine(m.MessageText)
                    sw.WriteLine(String.Empty)
                    sw.WriteLine("=========================")
                    sw.WriteLine(String.Empty)
                Next
                'Flush any remaining data to the file
                sw.Flush()
            End Using
            'Inform user that the export is finished
            Console.WriteLine("Finished exporting SMS messages!")
            Console.ReadKey()
            Exit Sub
        Else
            'No results were found
            Console.WriteLine("No sent, or received, SMS messages were found for the phone number " & phoneNumber)
            Console.ReadKey()
            Exit Sub
        End If
    End Sub

This application does a very simple check of the phone number; it checks to see if the phone number is 10 or 11 digits. If you would like to perform a thorough check of the phone number against the North American Numbering Plan, see my Tip/Trick Telephone Validation Method

Points of Interest

Please note that the zipped source code does not contain an SQLite database file. I hope it is obvious that this file was not included because of privacy concerns, however I hope to create a dummy test file to included with the source code soon. If someone else already has one and would like to provide it to be downloaded with this project for others to test with, please feel free to message me; you will be given the proper credit for your contribution.

History

• October 24, 2014 - Initial Version