How to query sqlite database with adb tool
Today, I am research how to use adb command line tool, I want to query data from sqlite database, for android device or emulator, it’s not…
Today, I am research how to use adb command line tool, I want to query data from sqlite database, for android device or emulator, it’s not as easy as iOS simulator, on iOS simulator, we can navigate to the directory of the iOS simulator, and open the database files with external database tools.
For android emulator, I usually download the database file from Device File Explorer , but it was too time consume, if I operate the app, then I need to download the file again, so I want to query database directory on the Emulator or Physical Device, we can use adb to connect with the device.
List devices with adb
We can use the following command to list the device on the computer, on my machine, I have two devices, one is a simulator, and the other is the physical machine which I enable debug over wifi, the following output is from my machine.$ adb devices -l
List of devices attached
adb-816d824e-2lp6Pe._adb-tls-connect._tcp. device product:curtana_eea model:Redmi_Note_9S device:curtana transport_id:1
emulator-5554 device product:sdk_gphone64_x86_64 model:sdk_gphone64_x86_64 device:emulator64_x86_64_arm64 transport_id:5
Connect device with adb
We can use adb -s <$ANDROID_SERIAL> , the $ANDROID_SERIAL is the first column of the output of the adb devices command, so for me, I can use the following command to connect to the devices.# Connect to the emulator
adb -s emulator-5554 shell# Connect to physical device
adb -s adb-816d824e-2lp6Pe._adb-tls-connect._tcp. shell
Query sqlite database
To query data from emulator and physical device is different, for on the emulator, it has sqlite3 tool, so we can use it directly, but on the physical device, the tool is missing.
Query on emulator
The application data is under /data/data/<package name> directory, we can’t navigate to the directory directly, but we can change to super user with su command.$ adb -s emulator-5554 shell
emulator64_x86_64_arm64:/ $ cd data/data
emulator64_x86_64_arm64:/data/data $ ls
ls: .: Permission denied
1|emulator64_x86_64_arm64:/data/data $ su
emulator64_x86_64_arm64:/data/data # ls
android com.android.internal.systemui.navbar.gestural_narrow_back com.android.stk com.google.android.dialer
android.auto_generated_rro_product__ com.android.internal.systemui.navbar.gestural_wide_back com.android.storagemanager...emulator64_x86_64_arm64:/data/data # cd com.google.android.apps.photos
emulator64_x86_64_arm64:/data/data/com.google.android.apps.photos # ls
app_null cache code_cache databases files no_backup shared_prefs
emulator64_x86_64_arm64:/data/data/com.google.android.apps.photos # cd databases/
emulator64_x86_64_arm64:/data/data/com.google.android.apps.photos/databases # ls
-1_inbox_threads.notifications.db -1_optimized_threads.notifications.db-journal accounts.notifications.db disk_cache-shm gphotos-1.db-shm local_trash.db-journal media_store_extras-wal
-1_inbox_threads.notifications.db-journal -1_threads.notifications.db accounts.notifications.db-journal disk_cache-wal gphotos-1.db-wal media_store_extras
-1_optimized_threads.notifications.db -1_threads.notifications.db-journal disk_cache gphotos-1.db local_trash.db media_store_extras-shm
emulator64_x86_64_arm64:/data/data/com.google.android.apps.photos/databases # sqlite3 gph
gphotos-1.db gphotos-1.db-shm gphotos-1.db-wal
emulator64_x86_64_arm64:/data/data/com.google.android.apps.photos/databases # sqlite3 gphotos-1.db
SQLite version 3.32.2 2021-07-12 15:00:17
Enter ".help" for usage hints.
sqlite> .tables
action_queue memories_read_state
actors metadata_sync
album_enrichments mobile_ica_scan
Query on physical device
For physical device, we have no permission to visit /data/data/ folder and the on my device, the su and sqlite3 commands are not on the device.$ adb -s adb-816d824e-2lp6Pe._adb-tls-connect._tcp. shell
curtana:/ $ cd /data/data
curtana:/data/data $ ls
ls: .: Permission denied
1|curtana:/data/data $ su
/system/bin/sh: su: inaccessible or not found
127|curtana:/data/data $ sqlite3
/system/bin/sh: sqlite3: inaccessible or not found
In order navigate to the app’s data folder, we can use run-as command, for example, I have an application with package com.swanwish.passwordbook , then I can use the following command:1|curtana:/data/data $ run-as com.swanwish.passwordbook
curtana:/data/user/0/com.swanwish.passwordbook $ ls
cache code_cache databases files shared_prefs
But the sqlite3 command does not exists on the device, how to use sqlite3commands? I try to download the sqlite3 from my emulator, and upload it to my physical machine, but it does not work, it report the following error:$ ./sqlite3
/system/bin/sh: ./sqlite3: not executable: 64-bit ELF fil
This error is caused by different architecture between emulator and physical device, my device cpu info is like below:$ cat /proc/cpuinfo
Processor : AArch64 Processor rev 14 (aarch64)
...
I find a workable sqlite for my device from github sqlite3, after I upload the sqlite3 to /data/local/tmp folder, and can run it with default user like following:curtana:/ $ /data/local/tmp/sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
But when I use run-as command and navigate to my app’s data dir, the command was denied.126|curtana:/ $ run-as com.swanwish.passwordbook
curtana:/data/user/0/com.swanwish.passwordbook $ /data/local/tmp/sqlite3
sh: /data/local/tmp/sqlite3: can't execute: Permission denied
So I want to move the sqlite3 to app’s data dir, and I find a solution that use Device File Explorer to upload the sqlite3 to the application folder, then we can use the command to open the database.$ run-as com.swanwish.passwordbook
curtana:/data/user/0/com.swanwish.passwordbook $ ls
cache code_cache databases files shared_prefs sqlite3
curtana:/data/user/0/com.swanwish.passwordbook $ ls -lah
total 377K
drwx------ 7 u0_a445 u0_a445 3.4K 2022-06-14 21:46 .
drwxrwx--x 471 system system 52K 2022-06-14 10:57 ..
drwxrws--x 2 u0_a445 u0_a445_cache 3.4K 2021-10-13 06:41 cache
drwxrws--x 2 u0_a445 u0_a445_cache 3.4K 2021-11-08 02:04 code_cache
drwxrwx--x 2 u0_a445 u0_a445 3.4K 2021-10-13 06:41 databases
drwxrwx--x 2 u0_a445 u0_a445 3.4K 2021-10-13 06:41 files
drwxrwx--x 2 u0_a445 u0_a445 3.4K 2022-04-12 10:28 shared_prefs
-rwxrwxrwx 1 u0_a445 u0_a445 693K 2022-06-14 21:46 sqlite3
curtana:/data/user/0/com.swanwish.passwordbook $ ./sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
