Debugging sqlite database on the device

Question

I am presently working on an WiFi application for Android. I am having trouble trying to access the database on the device. Debugging in the emulator doesn't work for me, because there is no WiFi support in the emulator. I tried pulling the database file out of the device by using

adb pull data/data/package-name/databases/database-name

But I get the error "Permission denied.". In this answer Android: Where are database files stored?, Commonsware has suggested to pull database file by running in debug mode. But it doesn't work too. Any help on how to debug the database without rooting the device would be much appreciated.

Solution 1

I'll repeat myself from another answer:

Starting from API level 8 (Android 2.2), if you build the application as debuggable, you can use the shell run-as command to run a command or executable as a specific user/application or just switch to the UID of your application so you can access its data directory.

So if you wish to pull your application database from the device you should run the debug build of the application, connect with adb shell and run the following command:

run-as com.yourpackage sh -c "cat ~/databases/db-file" > /sdcard/db-file.sqlite

This will copy your db-file to the root of your SD card / external storage. Now you can easily get it from there by using file manager, adb pull or whatever else you like. Note that with this approach, there is NO need for your app to have WRITE_EXTERNAL_STORAGE permission, as the copying is done by the shell user who can always write to the external storage.

On Linux/Mac systems there is a possibility to copy a database directly to your computer with the following command one can use without entering the adb shell:

adb shell 'run-as com.yourpackage sh -c "cat ~/databases/db-file"' > db-file.sqlite

This however will not work correctly on Windows because of CR/LF symbols conversion. Use the former method there.

Solution 2

I use this shell script on my MAC, that copies database directly to my home folder. Easy one click solution, just change package name (com.example.app) and database name (database.sqlite)

Simple Script

#!/bin/bash
adb -d shell 'run-as com.example.app cat /data/data/com.example.app/databases/database.sqlite > /sdcard/database.sqlite'
adb pull /sdcard/database.sqlite ~/

Script which accepts arguments [package_name] [database]

#!/bin/bash

REQUIRED_ARGS=2
ADB_PATH=/Users/Tadas/Library/sdk/platform-tools/adb
PULL_DIR="~/"

if [ $# -ne $REQUIRED_ARGS ]
    then
        echo ""
        echo "Usage:"
        echo "android_db_move.sh [package_name] [db_name]"
        echo "eg. android_db_move.sh lt.appcamp.impuls impuls.db"
        echo ""
    exit 1
fi;


echo""

cmd1="$ADB_PATH -d shell 'run-as $1 cat /data/data/$1/databases/$2 > /sdcard/$2' "
cmd2="$ADB_PATH pull /sdcard/$2 $PULL_DIR"

echo $cmd1
eval $cmd1
if [ $? -eq 0 ]
    then
    echo ".........OK"
fi;

echo $cmd2
eval $cmd2

if [ $? -eq 0 ]
    then
    echo ".........OK"
fi;

exit 0

Solution 3

The best way to view and manage you android app database is to use this library https://github.com/sanathp/DatabaseManager_For_Android

With this library you can manage your app SQLite database from you app itself. you can view the tables in your app database , update ,delete, insert rows to your tables .Everything from your app.

Its a single java activity file ,just add the java file to your source folder.When the development is done remove the java file from your src folder thats it .

It helped me a lot .Hope it helps you too .

You can view the 1 minute demo here : http://youtu.be/P5vpaGoBlBY

Solution 4

Although, it's an old question I think it's still relevant and deserves a current state answer. There are tools available, which allow you to inspect databases directly (without the need to pull them from the device or emulator).

The tool, I most recently discovered (and favor most) is Android Debug Database.

You only need to add this dependency:

debugImplementation 'com.amitshekhar.android:debug-db:1.0.3'

No further code is required. After you started your app, open logcat and filter for "DebugDB" and you will find a message saying

D/DebugDB: Open http://192.168.178.XXX:8080 in your browser

It works with every browser and you can inspect your database tables and shared preferences.

It also works with the default and the Genymotion emulators.

---

The tool I used before is stetho.

Downside: You need to add a bit of code and you are bound to the Chrome browser.

Advantage: You have the option to also inspect network traffic.

Solution 5

In my application I export the database to the SD card. Once the database is on the SD card it can be accessed by plugging the device into your computer.

Look at this post: Making a database backup to SDCard on Android

View more solutions

Comments

[Primal Pappachan]

I had tried this. But it gives me "adb cannot run as root in production builds"

[Programatt]

You'll need to run it in Debug mode.

[Programatt]

See this: mydroidworld.com/forums/android-hacks/… If you're running it on a phone and not an emulator, you'll have to root it.

[Primal Pappachan]

I ran the application in debug mode and tried the above steps as well. It doesn't work.

[MikeIsrael]

@Idolon, I tried this answer and was able to download the database but now when I try and open it with sqlite it give me the error "Error: file is encrypted or is not a database". Any ideas how I can fix it and access through sqlite?

[Idolon]

@MikeIsrael Just in case, you don't use SQLCipher in your app do you? If not, then to roughly check whether the database was downloaded correctly open the DB file in a viewer (preferably a hex-viewer) and check whether it starts with SQLite format 3 string. Also make sure you have a correct sqlite version (i.e. you're not trying to open sqlite3 database with sqlite2 executable). And you may also try other SQLite clients (for example SQLiteStudio). Hope it helps.

[Jose_GD]

You made my day @Idolon! A little improvement to your answer: android:debuggable="true" is not needed, it's the default AFAIK in the manifest. I've just typed run-as and cat in my adb shell and worked perfectly, without android:debuggable in the manifest

[Idolon]

@Jose_GD Yes, you're right - android:debuggable is set automatically by the Eclipse ADT plugin.

[Rev Tyler]

I'm having trouble with the one liner. It works fine if I run the command in a shell but if I put in the one liner I get: The system cannot find the path specified.

[zedix]

There's a simpler solution. If you chmod 777 your sqlite file, then exit run-as, it'll let you copy it over to /sdcard as the regular usre.

[Nathan Schwermann]

package <my package> is unknown... anyone know why. I installed via studio by pressing the debug button.

[Idolon]

@schwiz There is a known bug in Android 4.3: code.google.com/p/android/issues/detail?id=58373. And few workarounds, for example making run-as set-uid root, /data/… folders permission changes may also be required: post #60

[slott]

This only works for the emulator or a rooted device.

[Muhammad Babar]

On Android Lollipop i'm getting permission denied

[Mason]

@MuhammadBabar I get that on Lollipop too. Managed to get a semi-accessible database by catting it through the adb link and writing to a file on the local computer. Like adb shell 'run-as myapp cat /data/data/myapp/databases/dbname.db' > ~/dbname.db Something's kind of wrong with that, but I'm not sure what yet.

[Muhammad Babar]

@Mason thanks but you said you managed to get it and then you are saying something's wrong. I'm confused :?

[Mason]

I pulled a database with two tables and a dozen or so rows in each, and did full select queries on each table. One worked fine, the other reported a data error in the file. My best guess right now is an encoding mismatch between the Android and local shell.

[Muhammad Babar]

@Mason Is your phone rooted? I'm still getting permission-denied & read-only-filesystem error :(

[Mason]

@MuhammadBabar Nope, if it was rooted, I could just do a pull of the file I wanted. Try running the commands from a adb shell, and make sure the run-as command works, and that your app is built in debug mode.

[Muhammad Babar]

@Mason i already did run in adb shell and the command is working. The apk is debugged with eclipse using debug.keystore. Is your device 5.0.1?

[Neon Warge]

I am also having the same problem but not on lollipop but on Android 4.0.4. Any ideas on what I am doing wrong?

[Tom]

Not sure if this is it, but keep in mind you are doing your 'cat' in context of your app. Make sure your has WRITE_EXTERNAL_STORAGE permission.

[romulof]

The first command was creating a zero-byte copy of the database, so I made some adjustments in your script: gist.github.com/romulof/6af8a8919660f395f975

[olik79]

If for some reason you are like me getting an error stating Device not found: Simply replace the -d with -s <device-id>. <device-id> needs to be replaced by the device id given in the left column in the output of adb devices. This happened to me while using a virtual genymotion device.

[danfolkes]

mine needed to have quotes: adb -d shell "run-as com.yourpackage cat /data/data/com.yourpackage/databases/dbname.sqlite > /sdcard/dbname.sqlite"

[DearVolt]

/sdcard isn't necessarily an SD card. On my phone it points to internal storage when no physical SD card is present.

[Alex P.]

adb shell 'run-as com.yourpackage sh -c "cat ~/databases/db-file"' > db-file.sqlite is not going to work regardless of the host OS. see my explanation here stackoverflow.com/a/13587203/1778421

[Alex P.]

also why do you keep insisting on using the unnecessary sh -c "cat file" construct? stackoverflow.com/a/18472135/1778421 is much cleaner.

[T.Woody]

By running bash on windows cmd, the linux and mac command at the bottom of this answer can also be used.

[Yair Kukielka]

This is the best option for 2020 and later

[Carl Rossman]

If you get the error "run-as: unknown package" double check the applicationId in your build.gradle file. That is the value that determines the "package" ID - not the actual package name in the code.