Viewing blob data as a hexdump with ASCII in the sqlite3 console

Question

I have a column of data in a SQLite table being stored as a blob. Specifically, it is a serialized POJO (java object).

Either way, I'd like to view it in the SQLite console as a hex dump, sort of like this:

0000000000  |The correction f|
0000000016  |or the aberratio|
0000000032  |n of light is sa|
0000000048  |id,.on high auth|
0000000064  |ority, not to be|
0000000080  | perfect even in|
0000000096  | that most perfe|
0000000112  |ct organ, the.ey|
0000000128  |e..|

I know the statement SELECT HEX(obj) FROM data WHERE rowid = 1 will get the data as just hex, but now I want to pipe it to something that will give me a hexdump view.

PS - I know the data I am trying to view is binary (a serialized POJO) but I would like to see what is inside as an experiment. So, even if the end result is cryptic anyway, please let me know!

Update: I tried some of the suggestions but found that sqlite3 isn't outputing the full hex. I am expecting around 500 bytes but instead getting like 10:

root@ubuntu:~# sqlite3 IceCream.db "select hex(obj) from Customers where rowid=1;" 
ACED00057372002D6564752E6761746563682E7365636C6173732E70726F6A656374322E637573746F6D65722E437573746F6D6572000000000000000102000B4C0007616464726573737400124C6A6176612F6C616E672F537472696E673B4C0012617661696C61626C65467265654974656D737400134C6A6176612F6C616E672F496E74656765723B4C00096269727468446174657400104C6A6176612F7574696C2F446174653B4C000C646973636F756E745261746571007E00024C000966697273744E616D6571007E00014C000A676F6C645374617475737400134C6A6176612F6C616E672F426F6F6C65616E3B4C00086C6173744E616D6571007E00014C000D6D6F6E74686C79506F696E747371007E00024C0013706F696E74734561726E656450657254696D657400134C6A6176612F7574696C2F486173684D61703B4C000B746F74616C506F696E747371007E00024C000876697053696E636571007E0003787200386564752E6761746563682E7365636C6173732E70726F6A656374322E73797374656D732E446174616261736553657269616C4F626A65637400000000000000010200024C000269647400104C6A6176612F6C616E672F4C6F6E673B4C00166C61737454696D654F626A6563744D6F64696669656471007E000778707372000E6A6176612E6C616E672E4C6F6E673B8BE490CC8F23DF0200014A000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B020000787000000000000000017371007E0009000001497757AAFB740006436172736F6E737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C75657871007E000A000000007372000E6A6176612E7574696C2E44617465686A81014B59741903000078707708000001349BB816607871007E000F74000442696C6C737200116A6176612E6C616E672E426F6F6C65616ECD207280D59CFAEE0200015A000576616C7565787000740005313233205471007E000F737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000146000A6C6F6164466163746F7278703F400000770800000010000000007871007E000F70


root@ubuntu:~# sqlite3 IceCream.db "select obj from Customers where rowid=1;" | hexdump -C
00000000  ac ed 0a                                          |...|
00000003

Malinger

Answer 1

The sqlite3 shell cannot display ASCII values in a binary data dump.

You have to pipe its output into a separate tool:

sqlite3 test.db "SELECT MyBlob FROM MyTable WHERE ID = 42;" | hexdump -C
sqlite3 test.db "SELECT MyBlob FROM MyTable WHERE ID = 42;" | xxd -g1

However, sqlite3 converts the blob into a string to display it, so this will not work if the blob contains zero bytes.

You have to output the blob as hex, then convert it back into binary, so that you can then display it in the format you want:

sqlite3 test.db "SELECT quote(MyBlob) FROM MyTable WHERE id = 42;"  \
| cut -d\' -f2                                                      \
| xxd -r -p                                                         \
| xxd -g1

Rioux

Its odd, but the blob isn't fully being piped out of sqlite3. I am expecting something around maybe 500 bytes long but instead getting something like 10? -- See my edit to my original question. – Malinger

It's because you have null bytes in your data. The command-line version of sqlite3 converts everything to strings to display them; the null byte ends the string prematurely. Read the entirety of CL's answer. – Ducharme

Answer 2

You can run the below script in the command.

sqlite3 test.db "select hex(obj) from data where rowid=1" >> hexdump

:) I'm not sure if it's what you need.

Halliard

This is two hex conversions. – Rioux

I didn't need the >> hexdump part – Psf

Answer 3

Not sure if this was the original intent, but I've discovered that if you type .mode quote in the sqlite3 console, it will automatically print blobs in a form like X'f12aa56c', as opposed to dumping a garbled mess of characters.

Waterborne

Answer 4

Here a sample of the query to see and use hex in where statement:

Query blob data as HEX:

> qlite3 your_db_file.db -separator ','  "SELECT _id, timestamp, length(hex(blob_data)), hex(blob_data) FROM your_table;"
11190,1562991418211,8,0020C618
11190,1562991418231,8,5A0E1003

Query blob data as HEX in WHERE:

> qlite3 your_db_file.db -separator ','  "SELECT _id, timestamp, length(hex(blob_data)), hex(blob_data) FROM your_table WHERE hex(blob_data)='0020C618';"
11190,1562991418211,8,0020C618

Fanti