Mr Dreigon

First off some back story for this post. My niece bought a Samsung galaxy S4. She used it for about a month, then decided to go back to her iPhone and give the Samsung to her brother. This meant that as a programmer, I had to find some way to transfer her android messages to her iPhone.

My basic plan is:

backup iPhone via iTunes
backup android SMSs via SMS Backup and Restore
transfer android SMSs into the iPhone backup using sqlite.
restore iPhone from altered backup

So first off I have to take a look at the sms database. It can be found in your iTunes backup folder (for me it was %APPDATA%/Apple Computer/mobile sync/backup/). there’s one more folder with all the backups, the one you are looking for is named “3d0d7e5fb2ce288813306e4d4636395e047a3d28”.

Here are the tables inside:

[Show Tables][Hide Tables]

Table	Description
_SqliteDatabaseProperties	Database properties
attachment	message attachments
chat	message conversations
chat_handle_join	Joins chat and handle table
chat_message_join	Joins chat and message table
handle	Phone addresses
message	The main message table
message_attachment_join	Joins message and attachment table

First lets look at how they all relate:

Just from making up this chart of how the tables relate, I have already seen areas where I made mistakes when I first tried to inject messages into the database. If you don’t understand the lines joining each table, here is a quick refernce sheet: http://www.vivekmchawla.com/2013/04/erd-crows-foot-relationship-symbols-quick-reference.html

So a message is made up of: the body of the message (message), an address (handle), a conversation (chat), and sometimes an attachment (attachment).

Lets look at the tables:

[Show Message Table][Hide Message Table]

Column	Type	Description
ROWID	int	auto incrementing id
guid	text	random identifier, maybe used by iMessage
text	text	message body
replace	int	unknown, however is always set to ‘0’
service_center	text	unknown, though often null/blank
handle_id	int	reference to ROWID in handle, used for address
subject	text	used for mms, otherwise null
country	text	the country code, e.g. “au”
attributedBody	blob	unknown
version	int	either 10 or 1. usually 10 in newer messages<
type	int	unknown, usually 0
service	text	SMS or “iMessage”
account	text	phone number or email, e.g. “p:+614112222” or “e:email@email.com”, used in iMessage
account_guid	text	a guid somehow linked to account
error	int	usually 0
date	int	Mac Absolute Time, (Unix time = date + 978307200)
date_read	int	time message was read
date_delivered	int	time message was delivered
is_delivered	int	if date_delivered is not 0 then this is 1, else 0
is_finished	int	unknown, usually 1
is_emote	int	usually 0, I have no cases of this being 1
is_finished	int	unknown, usually 1
is_from_me	int	1 if you are the sender, else 0
is_empty	int	usually 0
is_delayed	int	unknown, usually 0
is_auto_reply	int	unknown, usually 0
is_prepared	int	unknown, mostly 0, sometimes 1
is_read	int	if date_read is not 0 then 1, else 0
is_system_message	int	unknown, usually 0
is_sent	int	unknown, either 0 or 1, could be related to is_from_me
has_dd_results	int	unknown, usually 0
is_service_message	int	unknown, usually 0
is_forward	int	unknown, usually 0
was_downgraded	int	unknown, usually 0, rarely 1
is_archive	int	unknown, usually 0
cache_has_attachments	int	unknown, usually 0
cache_roomnames	text	unknown, usually null
was_data_detected	int	unknown, usually 0
was_deduplicated	int	unknown, usually 0

[Show Handle Table][Hide Handle Table]

Column	Type	Description
ROWID	int	auto incrementing id
id	text	phone number
country	text	country code e.g. ‘au’
service	text	‘SMS’ or ‘iMessage’
uncanonicalized_id	text	non formatted version of phone number. usually blank.

[Show Chat Table][Hide Chat Table]

Column	Type	Description
ROWID	int	auto incrementing id
guid	text	unique identifier used by iMessage
style	int	unknown, seems to be 45 or 43
state	int	unknown, mostly 3, sometimes 2 or 0
account_id	text	seems to be a guid for the account
properties	blob	unknown contents
chat_identifier	text	recipient phone/chat room name/email
service_name	text	‘SMS’ or ‘iMessage’
room_name	text	‘chat’ + number, e.g. chat246660806475640255
account_login	text	account used, e.g. P:+61411222333 or E:email@email.com
is_archived	int	0
last_addressed_handle	text	seems to be the user’s phone number or email
display_name	text	null

That’s it for now. I’ll continue this blog post another time, though I hope you have found this helpful so far.

Edit. I spent about 2 hours creating custom shortcodes for wordpress to implement those [Show/Hide] tags. I’ll finish this another day.

Tagged: iOS

iOS6 SMS Database investigation