CODEWITHSUNDEEP
androidsqlitebusybox
HeatherLeigh
HeatherLeigh

Reputation: 71

SQLite select blob with NUL characters

I'm having trouble getting a blob out of a SQLite database. When I attempt a SELECT statement, I only get a few characters up to the first NUL and then it stops.

My goal is to save the blob as a zip file (which is what the blob is) for further processing with another application. I am able to successfully get the hex version of the blob via SELECT hex(data), but that doesn't help me as it is not formatted correctly. I'm using BusyBox on an Android phone and there is a hexcode -r command that can reverse the hex output, but it only works if it's formatted as Canonical hex+ASCII display. Thus, I want to get the blob text as I will be able to simply save that as a zip file.

So, how can I get the whole blob including NUL characters? I've read about binding, but I don't quite understand it. If that is the solution, how would I write it in a shell command for Android with Busybox?

Upvotes: 0

Views: 497

Answers (2)

HeatherLeigh
HeatherLeigh

Reputation: 71

After working at this for about 8 hours straight, I finally got what I needed. Here is the command I used:

echo -ne "$(echo | sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db "SELECT HEX(data) FROM conversations LIMIT 1;" | sed -e 's/../\\x&/g')" > /sdcard/output.zip

The result is the zip file that was stored as a blob in the database. I hope this helps someone in the future!

Upvotes: 0

CL.
CL.

Reputation: 180010

NULs are interpreted as terminators when you try to treat the blob as a string.

Ensure that blobs are always accessed as blobs, i.e., don't try to do any string operations with the value in SQL, use functions like getBlob instead of getString, and use byte arrays instead of strings:

cursor = db.rawQuery("SELECT data FROM ...", ...);
if (cursor.moveToFirst()) {
    byte[] data = cursor.getBlob(0);
    FileOutputStream file = new FileOutputStream(".../data.gz");
    GZIPOutputStream compressor = new GZIPOutputStream(file);
    compressor.write(data);
}

Upvotes: 0

Related Questions