huwr

Reading an iPhone’s SMS database with SQLite

A friend of mine asked me for help backing up her iPhone’s SMS database. There are some rather unhelpful posts on the Internet, including people who wanted to charge money for a program that I thought isn’t going to be doing a great deal.

Surely it is just an SQLite database somewhere on the phone, probably stored on the computer after a backup? It is, but it’s a little convoluted. Here is what I discovered.

By default, the phone is backed up by iTunes when you sync. By default it is encrypted, but for our purposes we need to turn that off (but only for now!). We don’t need to sync the phone to back it up, however. We can back it up manually (right click on the phone in iTunes and click backup…)

The backups are stored in this directory (on Mac OS X):

~/Library/Application\ Support/MobileSync/Backup/

In there are a heap of directories that look like “d41d8cd98f00b204e9800998ecf8427e3b886c2f”. Yeah. It’s odd. We can work out which is our latest by looking at the modification dates, etc. And inside there are a heap more odd looking files. The one we’re after is helpfully called “3d0d7e5fb2ce288813306e4d4636395e047a3d28”. I copied that to my desktop:

cp 3d0d7e5fb2ce288813306e4d4636395e047a3d28 ~/Desktop/huws-sms-db.sqlite

Now we can just use sqlite3 like a sane person on it:

sqlite3 Desktop/huws-sms-db.sqlite

The table we’re looking for is Message. Try a select * from message; . That’s all our texts. Notice that it only has one address column - that’s the person we’re sending to or receiving from. You can use the flags column to establish if it’s to or from. Also notice the group_id column. Conversations with one individual are assigned the same group_id. The date column is Unix time.

Chocolate Chip Biscuits

I make these biscuits a lot. They’re good with a cup of tea.

Get these things

• 150g unsalted butter,
• 1/4 cup brown sugar,
• 1/3 cup caster sugar,
• 1 egg yolk,
• 1 bit of vanilla essence,
• 1.5 cups of self-raising flour,
• 1 cup (or just a whole bag) of chocolate chips.

And do this to them:

1. Using electric beater, beat eggs, butter and sugar until light and creamy. Add essence and beat again.

2. Add flour and chips. Use a metal spoon and stir until just combined.

3. Have lots of fun and use your fingers to press all the ingredients together. Get right in there and form a soft dough.

4. Roll one tablespoon of dough at a time into balls. Arrange the balls by rank and number on a tray. Keep a cool head. Don’t let their pleas get to you. Try not to ‘assassinate’ too many or you’ll feel sick and complain but no one will have any sympathy and that ruins the fun.

5. Bake at 180ºC (no idea what that is in pounds) for fifteen minutes, or until crisp and light brown. Allow them to cool and “set”.

The quality of these biscuits improves logarithmically to the quality of the chocolate chips used. Use better chocolate. Also, you can make so-called “Acid Biscuits” by using so-called “Smarties” instead of chocolate chips. They’re fun too.

Credit: some kind of recipe book that I’ve long since lost and years of painstaking biscuit eating.