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)

if len(output_lines) > 2 and output_lines[2] == 'opendir failed, Permission denied':
    # device not root
    packages = subprocess.check_output(['adb', 'shell', 'pm list packages -3']).decode('utf-8').splitlines()
    prefix = len('package:')
    for p in packages:
        p = p[prefix:]
        print(subprocess.check_output(['adb', 'shell', 'run-as', p, 'ls', '/data/data/' + p]).decode('utf-8'))
        # TODO Make an android app debuggable and reinstall
        # https://gist.github.com/nstarke/615ca3603fdded8aee47fab6f4917826#gistcomment-2242465

#!/bin/sh
# Make an android app debugabble and reinstall

set -ex

DNAME="CN=Ross Patterson, OU=me, O=rpatterson.net, L=San Francisco, ST=CA, C=US"
PACKAGE_RE='^package:(/data/app/([^-]+)-.+==/(.+.apk))=.+'
MANIFEST_RE='(<application [^>]+)>'

export PATH=$HOME/Android/Sdk/platform-tools:$PATH

app="$1"
storepasswd=$(pwgen 16 1)

package_listing=$(adb shell pm list packages -f -3 | grep "$app")
apk_path=$(echo "$package_listing" | sed -En "s|$PACKAGE_RE|\1|p")
package_name=$(echo "$package_listing" | sed -En "s|$PACKAGE_RE|\2|p")
apk_basename=$(echo "$package_listing" | sed -En "s|$PACKAGE_RE|\3|p")

adb pull "$apk_path" "$package_name.apk"
apktool d -f -o "$package_name" "$package_name.apk"

mv "$package_name/AndroidManifest.xml" "$package_name/AndroidManifest.bak.xml"
sed -E "s|$MANIFEST_RE|\1 android:debuggable=\"true\">|" \
    "$package_name/AndroidManifest.bak.xml" > "$package_name/AndroidManifest.xml"

apktool b -o "$package_name.apk" "$package_name"

keytool -genkey -v \
        -keystore "$package_name.keystore" -storepass "$storepasswd" \
        -alias "${app}_debuggable" -keyalg RSA -keysize 2048 -validity 10000 \
        -dname "$DNAME"
jarsigner -verbose -sigalg SHA1withRSA -digestalg SHA1 \
          -keystore "$package_name.keystore" -storepass "$storepasswd" \
          "$package_name.apk" "${app}_debuggable"

adb shell cmd package uninstall -k "$package_name"
adb install -r "$package_name.apk"

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