Read SMS directly from Sqlite database in Android

3 minute read

Today I wanted to extract the SMS content out of the android SMS application SQLite database using ADB.

NOTE: The whole procedure was tested on a rooted phone running Cyanogenmod Android. It may not work on unrooted phones

So I achieved that , using the following steps:

  1. Find SMS database file location
  2. Pull SQLite database file with ADB
  3. Identify sms tables
  4. Identify the thread _ID of the SMS you want to read
  5. Find all sms messages for a specific thread id

In detail:

1. Find SMS database file location

After some searching I found that the location of sms database file is in

/data/data/com.android.providers.telephony/databases/mmssms.db

2. Pull SQLite database file with ADB

This step is optional. Open a shell and type:

adb pull /data/data/com.android.providers.telephony/databases/mmssms.db mmssms.db

Now the whole sms database is copied locally in your working directory!

3. Identify sms tables

Open the SQLite using command line or your favourite SQLite editor. The tables in which the android telephony application stores SMS messages are threads and sms. Table Threads stores a record as a header for every SMS thread which is started:

CREATE TABLE threads (
   _id INTEGER PRIMARY KEY AUTOINCREMENT,
   date INTEGER DEFAULT 0,message_count INTEGER DEFAULT 0,
   recipient_ids TEXT,snippet TEXT,
   snippet_cs INTEGER DEFAULT 0,
   read INTEGER DEFAULT 1,
   type INTEGER DEFAULT 0,
   error INTEGER DEFAULT 0,
   has_attachment INTEGER DEFAULT 0
)

Table SMS stores all outgoing and incoming messages for every thread:

CREATE TABLE sms (_id INTEGER PRIMARY KEY,
   thread_id INTEGER,
   address TEXT,
   person INTEGER,
   date INTEGER,
   date_sent INTEGER DEFAULT 0,
   protocol INTEGER,
   read INTEGER DEFAULT 0,
   status INTEGER DEFAULT -1,
   type INTEGER,
   reply_path_present INTEGER,
   subject TEXT,
   body TEXT,
   service_center TEXT,
   locked INTEGER DEFAULT 0,
   error_code INTEGER DEFAULT 0,
   seen INTEGER DEFAULT 0
)

4. Identify the thread _ID of the SMS you want to read

Run a SQL query and read snippet column. This column stores the last sms in your thread and is the visible text of every SMS list item when you open the SMS application.

SELECT _id, snippet
FROM threads;

5. Find all sms messages for a specific thread id

In my case the thread _id had the value 310. Knowing this id we run another query on sms table in order to get SMS messages ordered chronologically by date:

SELECT 	datetime(date/1000, 'unixepoch','localtime')  ,datetime(date_sent/1000, 'unixepoch','localtime') ,person,body
FROM sms
WHERE thread_id = 310
ORDER BY date

The results are going to be similar to the following:

+-----------------------------------------------------------------------------------------------------------------+
| date                | date_sent             | person    |  body						  |
|------------------------------------------------------------------------------------------------------------------
| 2013-10-20 13:48:18 | 2013-10-20 13:48:16   |	  54	  | Hello Christos! How are you?			  |
| 2013-10-20 16:34:03 | 1970-01-01 02:00:00   | 	  | Fine, thanks ! I configure the left MFD of a F-16 jet |
| 2013-10-20 16:40:02 | 2013-10-20 16:40:01   |	  54	  | Awesome! I am throwing a party tomorrow at 21:45!	  |
| 2013-10-20 17:15:15 | 1970-01-01 02:00:00   |		  | Thanks! I will be there!				  |
+-----------------------------------------------------------------------------------------------------------------+

Note that person with id 54 is my friend Kitsos and person = NULL is me!

Good luck tampering and hacking with your Android device !

Comments

You May Also Enjoy