I am facinated by SQLite. But before you say “Mike you really need to get out more and get a life! ” consider this -the two most popular smartphone operating systems of today, iOS and Android, use SQLite databases to store important information such as contacts, SMS, and call records. That’s a lot of users – both victims and perpetrators. Have you ever wondered how the SQLite structures its records? An understanding of the SQLite record architecture is crucial to validating the output of forensic tools and for knowing where to look for evidence – including that elusive brass ring, deleted information.
SQLite database images always begin with a well know 16 byte signature which in ASCII is represented by “SQLite format 3” followed by a null byte. The database image header is 100 bytes in length. A full discussion of the database image header can be found on the official SQLite page. Amongst other things a well formed serialized database image will have a database schema or table layout. Knowing the schema of tables is a key bit of knowledge as it will provide the guide to the record header and subsequent record contents.
We will be using a record from the sms database of a first generation iPhone running iOS version 3.13 as an example for this post. Below is a screen shot taken of the schema of the message table using my favorite Mac SQLite editor Base.
Having noted the schema of the message table within the sms database, lets take a look at a record in a hex viewer.
The record is structured as is show in the below graphic.
In addition the record uses the values in the below table to represent the values of the bytes.
So let’s take our example and work through the record header. The first byte of course is our record length – in this case the record is 110 bytes long. The second byte is our key.
The next byte is the length of our record header including this byte – which is 19 bytes.
The next byte is null which indicates that the value is not included in the record. This would have been the ROWID from the schema. The next byte corresponds to the address row in the schema. The byte 0x27 is odd and greater than 13. According to our table this corresponds to text and the byte length is derived by taking the decimal value subtracting thirteen and then dividing by two.
39 -13 = 26 / 2 = 13
We can see from the below graphic that the address or telephone number is indeed 13 bytes in length.
The next byte, 0x4 corresponds in the schema to the date of the SMS. This is a four byte value and is stored in epoch time. The value here is 1296980309 and translates to Sun, 06 Feb 2011 08:18:29 GMT.
The next byte, 0x81 is, as is indicated in the schema, the text message – but it is unique. SQLite uses a compression method based on Huffman coding to store values greater than 127 bits. In this instance the byte in the record header indicating the text message is \x81 or 129 and therefore greater than 127. Since the method uses 2 bytes up to a decimal value of 16,383, we can assume the next byte \x0D is also for the length of the text message. The method to calculate the length of the text message is as follows – where X = the first byte value and Y = the second byte value –
(X-128) x 128 + Y
Calculated out this comes to the below
(129-128) x 128 + 13
To find the length of the header we now refer to the table and do our calculation as normal
This is indeed the length of the text message. The message is straight hex to ascii.
The next byte in the header is 0x1 and in the schema refers to the flags row. The value in this case is 0x02 which means that it is an incoming text.
The last byte of significance in this record occurs at offset 17 in the record and as our table indicates is a value greater than 13 (0x11 = decimal 17). Since the calculation is N-13/2 the value we have here is 2 bytes and this refers in the schema to the country. In our example this is 0x6A 0x6F or “jo” for Jordan.
I hope that you find this post useful in your forensic endeavors. This post would not have been possible without the generous help and counsel of DC Shafik Punja of Calgary and Sheran Gunasekera of ZenConsult Pte Ltd.
Research regarding Huffman encoding in SQLite records was conducted using Murilo Tito Pereira’s article “Forensic analysis of the Firefox 3 Internet history and recovery of deleted SQLite records” published by Elselvier.
Here’s a cool utility for parsing out deleted SMS from existing IPhone SQLLite SMS Dbs
I’ve been playing around with a Motorola V3c on a case (CDMA RAZR variant)…and have had moderate success with a number of different pieces of software. In general I can get the contacts and call records (not date/time though just index) from the following pieces of software.
- DP Secure View
Curiously, I cannot get Device Seizure to even recognize this phone exists. I of course have to connect over its modem which has been seen on a virtual COM port (16 in my case)…DS just refuses to see the phone.
I may try to hook up to it via the Hyperterminal but it sure would be nice not to have to do this the hard way…
Anyway while looking at the filesystem in BitPim, I managed to find the location of the SMS messages (not parsed). They are located in
I know there is an index which helps mark these up but I havent tried to suss that out yet. What’s cool is that I’ve found some previous messages in there as well…and I’m not talking about a Quick Notes/SMS dump ala Device Seizure either…..
More to come on this one..
I wanted to share a little whitepaper I wrote on the subject of SMS
I hope its of use to practitioners in the community.
While answering a post on a list I belong to, it occured to me that forensic practitioners may not know how a piece of SIM software determines whether an SMS message has been sent or recieved or how it “undeletes” SMS.
The key to this is in the SMS status byte. This byte is the first byte to the message and determines the status of the message as outlined in GSM 03.40 and GSM 03.38 . Here is a graphic cut from those documents (click on the smaller image for full size one)
Breaking this down into a narrative here the status byte determines the following (shown in binary)
- 00000001-Mobile equipment terminated, read
- 00000011-Mobile equipment terminated, not read
- 00000101-Mobile equipment originated, sent
- 00000111- Mobile equipment originated, not sent
This then is how the software determines if the SMS was sent from the phone (originated) or received (terminated). Interestingly, the the status byte is /x00 or unused and there is previous data at that slot-you can recover a deleted SMS. This is similar to how the FAT/MFT works in relation to “deleting” files.
I hope this is of some help to the Community.
Here’s a summary of my understanding of how SMS works….
SMS works on a store-and-forward basis. Instead of being sent directly to the recipient, SMS messages travel through several important nodes before reaching the recipient.
- The SMS message is submitted to your wireless service provider’s SMS Center.
- After the message is processed internally, the SMS Center sends a request to the Home Location Register (HLR) and receives the routing information for the recipient.
- The SMS Center sends the message to the Mobile Switching Center (MSC).
- The MSC collects the recipient’s information from the Visitor Location Register (VLR) and, sometimes, proceeds with an authentication operation.
- The MSC forwards the message to a Mobile Server
- The MSC returns the outcome of the Forward Short operation to the SMS Center.
- The SMS Center reports delivery status of the short message back to the sender.
Remember for serving a search warrant on undelivered SMS, they are stored at the originator’s SMSC. Here is a graphic
In relation to the great debate about interception of communications, here is an US interpretation:
- In order for the SMS to fall under wiretap (Title III in US, RIPA for UK) the SMS would need to be recieved in real time.
- Since the SMS is in fact stored at the provider level it is not in real time
- Therefore, considering the second point, the SMS is a stored electronic communication and subject to the Electronic Comunications Privacy Act (ECPA) and can be obtained via probable cause and a search warrantHope this is helpful to the Community.