opening iphone AddressBook.sqlitedb file

[cprince]

My friend's phone got stolen, but he has a backup for it. One of the backup files is AddressBook.sqlitedb. I was able to open the file using sqlite3 on linux and issue the command: select * from ABPerson; The result is not what I expected. I can see the list of people names, including myself, but my phone number is not on there. The phone numbers seem to be encoded somehow. Does anybody know how to decode it? Thanks in advance.

 

[cprince]

Never mind. I got it. The names and other properties of the contacts are stored in the ABPerson table, but the actual phone numbers are stored in the ABMultiValue table.

I used these commands from linux with sqlite3 installed:

sqlite3 AddressBook.sqlitedb

sqlite> .mode csv
sqlite> .output contacts.csv
sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
sqlite > .quit

and that's it!

 

[tonman23]

Never mind. I got it. The names and other properties of the contacts are stored in the ABPerson table, but the actual phone numbers are stored in the ABMultiValue table.

I used these commands from linux with sqlite3 installed:

sqlite3 AddressBook.sqlitedb

sqlite> .mode csv
sqlite> .output contacts.csv
sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
sqlite > .quit

and that's it!

You're my Hero! thanks for posting the solution to your own problem. Exactly what I was looking for!

 

[DivideBYZero]

You're my Hero! thanks for posting the solution to your own problem. Exactly what I was looking for!

:hmm: You signed up just to be a douche to someone here? This info is pretty useful for anyone else in this situation, so I suggest you correct your attitude.

 

[AMDZen]

:hmm: You signed up just to be a douche to someone here? This info is pretty useful for anyone else in this situation, so I suggest you correct your attitude.

WTF are you talking about? He wasn't being sarcastic he was thanking him.

Idiot

 

[DivideBYZero]

WTF are you talking about? He wasn't being sarcastic he was thanking him.

Idiot

It's dripping with sarcasm, can you not see?

 

[TheStu]

Also, text messages are stored as plaintext inside the backup file.

 

[AMDZen]

It's dripping with sarcasm, can you not see?

Uhhh NO, no I can't.

The sarcasm you see is simply the way he phrased his statement. What reason would he have to be sarcastic?

thanks for posting the solution to your own problem.

Translates to: Sweet, I googled this problem hoping someone had a solution and came in here and you had answered your own question. Thanks so much. For all you know he came in when the OP had posted just the question and was like "DARN" and then came back later and was lake "HORRAY, the OP solved his own problem thus mine"

 

[amityweb]

Just wanted to re-iterate tonman23 and thank cprince... this is exactly what I was looking for too and it worked like a treat! Downgraded my iPhone 3G's iOS 4 to iOS 3 due to the 3G not being able to handle the iOS4. Took a backup if iOS 4 but did not think that this would be able to be restored... so needed all my contacts from within the backup. Used the "iPhone/iPod Touch Backup Extractor" program to extract the old backup to get the AddressBook.sqlitedb file, then this script extracted the contacts into a CSV. Thanks! 😀

 

[b3k1m1]

Never mind. I got it. The names and other properties of the contacts are stored in the ABPerson table, but the actual phone numbers are stored in the ABMultiValue table.

I used these commands from linux with sqlite3 installed:

sqlite3 AddressBook.sqlitedb

sqlite> .mode csv
sqlite> .output contacts.csv
sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
sqlite > .quit

and that's it!

I got the same problem, I can see with wordpad numbers witch are saved but alot with coded, this is good trick but for me doesnt work? It save contacts.csv with 0 Kbytes

 

[Mysticlord1975]

Thanx cprince, i can see that this thread started long time ago but it solved my problem 🙂. Anyway, for me was only a curiosity, to see if i can save my contact list and phones numbers.
I made an account here to thank you for this thread, but also to answear b3k1m1. I had the same problem, but my mistake was that i didn't download the AdressBook.sqlitedb i downloaded AdressBook.sqlite which is not the good db.
The file is located in /private/var/mobile/Library/Adressbook . If you do the exact procedure described by cprince you will be able to do it just fine. As a sidenote, if you are in linux and in a folder with only root privileges you might not be able to create the output file... as i tried to do 🙂.
Again thanx cprince !

PS:
If you want only to visualize the contacts and the phone numbers you can do the following:
sqlite>.mode line
sqlite>.output stdout ( it is already set by default so change it only if you made some changes to the output method)
sqlite>.separator , (or whatever separator you want to use)
sqlite>.tables (if you want to visualise all the tables found inside the database)
sqlite>select First, Last, value from ABPerson, ABMultiValue where ROWID=record_id;
I made some minor modifications to the select comand, it's not necesary to see also the rowid so like this you can see only the first name, last name and phone number. Also, if you want to make teh output file like this you can cahnge the .output stdout command with .output your_contacts_file.csv

 

[ipoddoctor]

I got the same problem, I can see with wordpad numbers witch are saved but alot with coded, this is good trick but for me doesnt work? It save contacts.csv with 0 Kbytes

I got the same problem so i put the output file after the query
Like this

sqlite3 (my path here)AddressBook.sqlitedb

sqlite> .mode csv
sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
sqlite> .output (my path here)contacts.csv
sqlite> .quit

I should note that when i had already created the csv with my bad attempt i had to change its name (or delete the empty file) to get the right one to be created.

I had never used a database before and this trick worked.
Thank You

ps

I'm on a mac working in Terminal Snow Leopard 10.6.8