Read SMS directly from Sqlite database in Android

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 !

Advertisements

About cmanios

programming
This entry was posted in Android, Databases, Linux, SQLite and tagged , , , , , , , , , , , , . Bookmark the permalink.

28 Responses to Read SMS directly from Sqlite database in Android

  1. Tomaz says:

    hello i need to urgent get back some sms… i don know this sql. can u help me i can send u file or something..

  2. Tob says:

    I’ve rooted my Galaxy S3 but I can’t find data/data/com.android.providers.telephony/databases/mmssms.db anywhere. The phone is attached to my computer and I’m looking for the path above in Explorer. I can only find com.android.providers.media.

    • cmanios says:

      Tob I assume that you have attached your phone to a Windows operating system. I am not sure if accessing your files via explorer.exe is done using root user with admin privileges. I suggest that you use ADB or a root file explorer.

      • Tob says:

        Thank you! I found it with root explorer. The problem now is that the database only have a few records visible in the sms table. I know that at least 100 sms has been deleted. Is there anyway to get them back or they just don’t exist anymore if not visible in the sms table?

      • cmanios says:

        I am afraid that you need digital forensics software to view deleted files in your phone storage. Unfortunately I am not aware of such software and techniques.

    • kundan says:

      hello i have made an application that send sms to customer, now the problem is that i am using samsung tablet model number GT-N8000 when i send sms from application it store the sms in inbox of tablet where we can see the sms now i did not want to store or want to see that sms in inbox. Can you just help me out how to hide sms from inbox. and i am using kitkat 4.4.2 version.

  3. Hello! I’ve been trying to write an app on Android that saves incoming SMS (sender, content and time) in an SQLite database and replies based on the content and sender of the message with predefined answers. Unfortunately, I haven’t been able to make it work as it gets stuck on accessing the database. I have written up my question in more detail with a copy of my code on stack overflow and I was wondering if you could help me out. Here’s the link: http://stackoverflow.com/questions/25604039/how-to-update-sqlite-database-when-receiving-sms

    Thanks!

    • cmanios says:

      Demetris, I am afraid that your question is irrelevant to this blog post. When you want to access Contacts programmatically in your application you should use Contacts Provider. Also the error you have in yout StackOverflow post cannot help as there is nothing but a custom log message. You should post the actual Exceptions or stacktrace in order to be comprehensible.

  4. Tharun says:

    I am creating a Kiosk based application on my rooted android tablet.One functionality is to reboot the device remotely by sending an SMS,which is working fine.However,I observe that the phone reboots exactly after 30 min intervals and thereafter for a couple of times.(This reboot functionality when triggered using GCM/phone call works fine and doesnt reboot later,as expected).So i suspect that when I reboot using SMS,the SMS module is firing some notifications after 30 min interval which is causing the erratic reboots.Could it be due to the messages not being marked as read? Anyhow I think I can solve this if I can delete messages from the core android message storage.How can I achieve this ? I’v searched online but no good results.Kindly assist .

    • cmanios says:

      1. I am quite puzzled.. Do you want to reboot your tablet every 30 minutes after the first reboot which was fired by the SMS BroadcastReceiver?
      2. Are you sure that the reboots are caused by your application and not from the tablet itself?
      3. Can you provide more details about your implementation or code extracts ?

      Note that my example describes raw access to SMS Sqlite database via shell (cli) and not from an Android ApplicationContext.

  5. George says:

    Hi, I looked in the location you said but my root data directory contains nothing. I have texts visible in the app so there should be something but data/data doesn’t even exist. Is there any other location they could be found at to your knowledge?

    I’m on a Nexus 4, Cyanogenmod.

    It’s driving me crazy, nobody online seems to give an answer that is true for me! Thanks.

    • cmanios says:

      It seems to me that you have a permission issue. You possibly do not have root access. Try to access / directory with a superuser application or via terminal running

      su

      command before

      ls -l /data/data

      .

  6. kundan says:

    hello i have made an application that send sms to customer, now the problem is that i am using samsung tablet model number GT-N8000 when i send sms from application it store the sms in inbox of tablet where we can see the sms now i did not want to store or want to see that sms in inbox. Can you just help me out how to hide sms from inbox. and i am using kitkat 4.4.2 version.

    • cmanios says:

      There are 2 ways:
      1. Follow my steps in a rooted phone and delete or move messages from sms database.
      2. Use SMS Content Provider like in this example and delete or move messages.

      I do not know if there is a way to “hide” them. You may want to move them to another database.

  7. stalopix says:

    Dear Cristos,
    Many thanks for your detailed description as well the sql scripts!
    I used razorsql https://razorsql.com/ to export xls files of sms from mmssms.db, according to your sql scripts. All as described above.
    Keep going!
    Friendly
    Stavros

  8. Hieu Truong says:

    I’ve to get data from mmssms.db in Android 6 and 5.
    In Android 6, I found it normally by access root to it.
    But in Android 5, I still found that file, but no table, no data in that table.
    Idk what happen. Have ever you seen that?

    • cmanios says:

      Hieu I have never seen such behaviour but I suspect that may the phone vendor uses a different path for mmssms.db in your Android 5 phone. An other reason can be that you may use a different SMS messaging App instead of built in application.

  9. chandan says:

    Good article on getting the sms messages in a readable format. I have a query how do we encrypt or hash the messages as i am developer. i.e how do we mitigate this issue.
    Please provide the valuable suggestions.

  10. rmikke says:

    Hi,
    I have found the database and I can see it with this SQLite editor https://play.google.com/store/apps/details?id=com.ksk.sqliteeditor , but it seems that the messages from current day are missing. Is it possible, that there is some cache and this database is uploaded on a daily basis?

    I.e. i can see messages in the system, but not in SMS table.
    Phone: Galaxy S7710, Android 4.1.2…

    • cmanios says:

      I am quite sure that SMS messages are directly written in mmssms.db SQLite file. I have just verified that the same works in OnePlus X with OxygenOS 2.1.2, which is Android 5.1.1. Are you sure that you have rooted your phone? One thing I suspect is that maybe Samsung has altered the default configuration of AOSP stock messaging application. Thus you can check if other Samsung users with rooted phones have the same issue.

      I hope this might help.
      Greetings from sunny Greece!

  11. Hello, I pulled mmssms.db from a damaged EMMC chip using EMMC reader. I am learning about SQlite database as I read this. I was able to export the db to CSV, and open in Open Office spreadsheet, but it is not easy to read. Is there a program or way to open db to make it easier to read and follow the conversations?

    • cmanios says:

      Hello Joshua! I am not aware of such a program but I am sure If you google , you may find one! However, there are quite many visual database managers , such as SQLite Manager and DBeaver which help you to manipulate SQLite databases easily.

      Cheers!

  12. RehanIjaz says:

    Thanks for ur article , i m using kitkat 4.4.2 I also rooted my android ,
    my question is when to use adb pull command , I used that command but answer is permission denied , and i also used this command in su but reply is device not found ? so any answer for this question.

    • RehanIjaz says:

      ok I got its solution the problem was that sqlite3 was not in devices/system/xbin/
      so i put sqlite3 there and then every thing is fine , now the I want to read the unread sms so i used command
      sqlite> SELECT * FROM sms WHERE read=0;
      and the reply was good but
      +(phone number)|100010101(some binary )|(The message e.g “Hi”)|(some binary)1001

      i want to save that message let say hi into a variable and then want to use that variable in my c# application so any idea how to do that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s