Click here to Skip to main content
15,867,308 members
Articles / Mobile Apps / iPhone

Accessing Backed Up iPhone SMS Messages

Rate me:
Please Sign up or sign in to vote.
4.56/5 (8 votes)
27 Oct 2014CDDL10 min read 38.9K   8   12   5
When iPhone SMS messages are backed up to your computer using iTunes, the messages are stored in a SQLite database. This article will show the layout of this database file and how to access all saved message information.

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):

Table Name Purpose
_SqliteDatabaseProperties  A list of nine properties of the database such as the client version and GUID.
attachment List of SMS message attachment (e.g. pictures), including information such as where the images are backed up to, MIME type, file size, etc.
chat Contains information about chats (e.g. room_name column), phone number information, etc., however I am not fully sure the use for this table.
chat_handle_join Contains only two columns, the chat_id column and the handle_id column, which correspond to the ROWID1 column of the chat table and the ROWID column of the handle table.
chat_message_join Contains only two columns, the chat_id column and the message_id column, which correspond to the ROWID column of the chat table and the ROWID column of the message table.
handle Maps phone numbers (id column) to a unique id (ROWID column). Phone numbers will have a different ROWID for SMS and iMessage (e.g. a phone number 212-555-1049 might have a ROWID of 15 for SMS but a ROWID of 25 for iMessage).
message Contains the actually message information include the text, GUID, service (SMS/iMessage), sent or received, date/time2.
message_attachment_join Contains only two columns, the message_id column and the attachment_id column, which correspond to the ROWID column of the message table and the ROWID column of the message table.
sqlite_squence Contains two columns (name and seq) which maps the text chat, handle, message, and attachment to the values 518, 559, 105705, and 1238 respectively. Not sure of its use but I suspect it is for some internal processing

1ROWID column, when present in a table, represents the primary key for the table.
2Date/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.)

Column Name Data Type Purpose1
ROWID Integer Primary key
guid Text Appears to be a GUID assigned to every entry
text Text The SMS message contents.
replace Integer Unknown. In my test file this column is always 0.
service_center Text Unknown. In my test file this column is always null.
handle_id Integer Foreign key to the handle table. This is the ID of the person sending/receiving the message (depending on if the SMS message was incoming or outgoing).
subject Text Subject of the message (if applicable).
country Text Unknown. Based on the name it appears that this column would contain some country information, however in my test file this column is always null.
attributedBody BLOB Contains binary data for attributes. When viewing the binary data various attributes can be seen, but the interesting one is NSString which appears to contain the text as in the text column.
version Integer Unknown version number. Possibilities included message table or SMS libraries/components.
type Integer Unknown. In my test file this column is always set to 0.
service Text Either SMS/iMessage, however other possibilities may be possible.
account Text If my test file, if the service column is SMS then the account column is e:. However, if the service column is iMessage then the account column is the phone owner's phone number formatted as p:+15551234567.
account_guid Text The GUID for the phone owner's phone number/service combination.
error Integer Unknown. Possibly list an error code when an error occurs either sending or receiving SMS messages, however this column is always 0 in my test file.
date Integer The date/time the message was created (for outgoing messages) or the date the message was received (for incoming messages), stored as the number of seconds since midnight of January 1, 2001.
date_read Integer The date/time an incoming message was read, stored as the number of seconds since midnight of January 1, 2001.
date_delivered Integer The date/time an outgoing message was delivered1,stored as the number of seconds since midnight of January 1, 2001.
is_delivered Integer 1 if True, otherwise 0.
is_finished Integer Unknown but I believe this indicates if the message is completed or still a draft; 1 if True, otherwise 0.
is_emote Integer Unknown but I believe this indicates if the message contains emotes; 1 if True, otherwise 0.
is_from_me Integer Indicates if this is an outgoing message; 1 if True, otherwise 0. If my test file, this column always matches the is_sent column.
is_empty Integer Unknown but probably indicates if the message contains any text; 1 if True, otherwise 0.
is_delayed Integer Unknown but most likely indicates if the message was delayed while sending (and possibly receiving); 1 if True, otherwise 0.
is_auto_reply Integer 1 if True, otherwise 0.
is_prepared Integer 1 if True, otherwise 0.
is_read Integer 1 if True, otherwise 0.
is_system_message Integer 1 if True, otherwise 0.
is_sent Integer 1 if True, otherwise 0.
has_dd_results Integer Unknown
is_service_message Integer 1 if True, otherwise 0.
is_forward Integer 1 if True, otherwise 0.
was_downgraded Integer Unknown
is_archive Integer 1 if True, otherwise 0.
cache_has_attachments Integer Unknown
cache_roomnames Text Unknown
was_data_detected Integer 1 if True, otherwise 0.
was_deuplicated Integer 1 if True, otherwise 0.

1I 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:

Column Name Data Type Purpose
ROWID Integer Primary key
id Text Phone number formated as +15551234567.
country Text Appears to indicate the country based on the phone number. In my test file this column is always us.
service Text Indicates the service that the message was sent through. In my test file this column is always SMS or iMessage.
uncanonicalized_id Text Appears to be the phone number unformatted (some entries are +15551234567 and others are 5551234567) or null.

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:

VB.NET
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):

VB.NET
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.

VB.NET
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

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


Written By
CEO Sci-Med Codimg
United States United States
I started programming around 10 years old. C/C++ was the first language I learned and then moved on to Assembly and finally VB/VB.NET which I have stuck with all these years. However, I have kept myself open to new languages and currently learning to work with Rust.

Besides computer programming, I have also learned microchip programming, starting with the Microchip PIC line and now using Raspberry Pi when it is convenient. I also have experience with HTML/ASP.NET/JavaScript/JQuery and built several websites for one of the largest environmental companies in NJ that I use to work for. These included their main website, and e-commerce site for the environmental products they sold, and a website for their training division which managed students and test grades. I wrote several large applications for them as well (e.g. a database front-end, a PDA application for data entry, and a program for analyzing data from an X-ray fluorescence machine and writing a report with recommendations based on the data).

Currently I work for a MSP that serves a good portion of the charter schools in NYC as well as a few small business and NPO's in NY, CT, and MA. Besides providing troubleshooting help, I develop applications which help streamline our work or our clients.

Comments and Discussions

 
QuestionRunning the code Pin
Member 1028263218-Dec-20 17:55
Member 1028263218-Dec-20 17:55 
QuestionDownload Code Pin
adrenalinejunkie20-Apr-15 14:14
adrenalinejunkie20-Apr-15 14:14 
GeneralMy vote of 5 Pin
Volynsky Alex22-Nov-14 13:54
professionalVolynsky Alex22-Nov-14 13:54 
Questionvisit our website Pin
Neha Sharma30-Oct-14 20:34
Neha Sharma30-Oct-14 20:34 
QuestionHow to send bulk sms by excelsheet ? Pin
Neha Sharma30-Oct-14 20:33
Neha Sharma30-Oct-14 20:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.