toofishes.net

iPhone SMS database hacking

I haven’t mentioned the fact that I have an iPhone on this blog so I thought I’d do that now. It is quite the device to play around with once you jailbreak it and basically turn it into the smallest computer you’ve owned.

I have been pretty modest with the things I’ve done to my iPhone since jailbreaking. Winterboard is pretty slick, and I did turn enable the user wallpaper feature which makes it quite obvious you aren’t using a stock iPhone.

iPhone screenshot

Once you have a jailbroken phone, the two most interesting apps for a developer/programmer have to be Terminal and OpenSSH. Poking around the iPhone you find quite a few things you can hack at. The one I’ve played with the most is the SQLite SMS database, located at /private/var/mobile/Library/SMS/sms.db.

We use Nagios at work to monitor our servers, and when things blow up, text messages get sent to our phones. Unfortunately, the email to SMS gateway makes every message appear to show up from a new number (something like ‘1010100059’). When 50 messages roll in, it is a pain to have to delete these one-by-one. So what does a programmer do? Writes a script that executes some SQL directly and cleans these messages up.

Prior to the iPhone and the G1 going mainstream, people would have laughed if you said you have Python installed on your phone. It’s no laughing matter anymore, and allows you to do just about everything you can think of if you are willing to invest the time.

The one tricky part of writing a cleanup script is the fact that the SMS database needs a trigger defined on the message table if you want to do any modifications. I found another blog post that got me off to a great start with my script, and I modified it from there to produce the following:

#! /usr/bin/env python
from sqlite3 import dbapi2 as sqlite

SMS_DB = '/var/mobile/Library/SMS/sms.db'

def message_read(flags):
    """reimplementation of an sqlite user defined function called by a trigger
    on the messages table.

    the trigger checks the message flags to see if a message has been read to
    see if the counter of unread messages in another needs to be updated when
    a message is deleted.
    """
    # second bit is the "was read" flag
    return (int(flags) & 0x02) >> 1

db = sqlite.connect(SMS_DB)

# register the user-defined function used by delete trigger
db.create_function('read', 1, message_read)

c = db.cursor()
c.execute("DELETE FROM message WHERE text LIKE '%FRM:nagios%';")
db.commit()

# vim:set ts=4 sw=4 ai et tw=80:

Tada! Scp this script to the mobile user’s home directory, mark it executable, and whenever the Nagios messages are overwhelming my SMS inbox, I can just run it from the terminal. I’ve also modified this script slightly to restore a bunch of text messages from a copy of the database when my friend hit “Clear Conversation” on accident.

On a side note, the ease of using sqlite when playing around with this stuff made me appreciate it a lot more. The fact that my x86_64 Linux box and my ARM iPhone can read and write the same database file is pretty cool.

Edit: I have an updated version of this script in a later post that has a bit more functionality including configurable stale message cleanup of all of your conversations. You may want to check that out.

Tags

See Also