extract & print database content from android

adb_db_print.py

import os
import sys
import subprocess
import sqlite3
import pandas as pd

arg_folder = sys.argv[1]  # root folder to recursively search db files from
output_lines = subprocess.check_output(['adb', 'shell', ('ls -R %s' % arg_folder)]).decode('utf-8').splitlines()
db_files = []
current_folder = ''
for line in output_lines:
    """
        Output example for 'ls -R /data/data':
            /data/data/org.fdroid.fdroid/files/fdroid/repo/icons:

            /data/data/org.fdroid.fdroid/shared_prefs:
            apks-pending-install.xml
            org.fdroid.fdroid_preferences.xml

            /data/data/ru.meefik.busybox:
            cache
            files
            lib
            shared_prefs

        if line contains '/' it's a directory, we want to extract the full path for '.db' files
    """
    if line.__contains__('/'):
        current_folder = line
    elif line.endswith('.db'):
        db_files.append('%s/%s' % (current_folder[:-1], line))
    """
        if devices is not root, follow the following steps:
        
        adb shell run-as [package] chmod 777 /data/data/[package]/databases/
        adb shell run-as [package] chmod 777 /data/data/[package]/databases/[db_file_name]
        adb shell run-as [package] cp /data/data/[package]/databases/[db_file_name] /sdcard/
        adb pull /sdcard/[db_file_name]
        
        Note: Two permissions are required in Manifest file otherwise data will not copied in pulled DB
            android.permission.WRITE_EXTERNAL_STORAGE 
            android.permission.READ_EXTERNAL_STORAGE
            
       TODO: 
            if unable to read folder files, which means it's not rooted, get user input for specific package
            OR select("adb shell 'pm list packages -f'")
            validate package has permissions & copy to sdcard, pull to local and remove from sdcard.
    """
print("Listing databases..")

while True:
    try:
        for idx, full_path in enumerate(db_files):
            print("{}) {}".format(idx + 1, full_path))
        i = input("Enter database number : ")
        db = db_files[int(i) - 1]  # selected database
        subprocess.check_output(['adb', 'pull', db])  # pulling the .db file from device to local
        db = db.split('/')[-1]  # "/data/data/com.app/folder/private.db".split('/')[-1] = private
        conn = sqlite3.connect(db)
        # getting list of current database tables
        tables = conn.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
        for table in tables:
            table = table[0]  # little fix, it comes as a tuple
            print('%s%s' % (db[:-2], table))
            with pd.option_context('display.width', 1000):  # setting terminal width to view all table columns inline
                print(pd.read_sql_query("SELECT * FROM %s" % table, conn))  # print table content
                # TODO save to csv ? table.to_csv(db[:-2] + table + '.csv', index_label='index')
            print('- ' * 50)  # table print separator
        # cleanup
        if input('remove database file (from local) [y/N] ? ').lower() == 'y':
            print('removing ', db)
            os.remove(db)
        # repeat
        c = input("Press ENTER to continue or CTRL+C to Quit..")
    except KeyboardInterrupt:
        exit(0)

DB="/tmp/a.db"; for tbl in $(adb shell "su -c 'cp $(adb shell "su -c 'find /data/data/com.imo* -name *db'" | fzf) /sdcard/a.db'" && adb pull /sdcard/a.db $DB >/dev/null && sqlite3 $DB "SELECT name FROM sqlite_master WHERE type='table';"); do echo "\n\n***** $tbl" && sqlite3 $DB "SELECT * FROM $tbl;"; done