View blob data as hexdump with ASCII in sqlite3 console

Question

I have a data column in a SQLite table that is stored as a blob. In particular, this is a serialized POJO (java object).

In any case, I would like to view it in the SQLite console as a hex dump, for example:

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 that the SELECT HEX(obj) FROM data WHERE rowid = 1 will receive the data as soon as hexadecimal, but now I want to pass it to something that will give me a hexdump representation.

PS - I know that the data I'm trying to view is binary (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 does not output full hex. I expect around 500 bytes, but instead get as 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 

Answer 1

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

You must redirect the output to 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 to a string to display it, so this will not work if the blob contains zero bytes.

You have to output the blob as hexadecimal, and then convert it back to binary so that you can then display it in the desired format:

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

Answer 2

You can run below script in command.

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

:) I'm not sure what you need.

Answer 3

Here is an example query to see and use hex in the where statement:

Request the blob data as a 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 

Request 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