How to insert binary data into sqlite3 database in bash?

bash, binary, sql, sqlite

Solution

Here is one way to do it. The file `test.jpg` is inserted in the table `foo` of the database `foodb` after being `hexdump`ed to the binary literal format of sqlite:

[someone@somewhere tmp]$ sqlite3 foodb "create table foo (bar blob);"
[someone@somewhere tmp]$ echo "insert into foo values (X'`hexdump -ve '1/1 "%.2x"' test.jpg`');" | sqlite3 foodb

EDIT

And here we see that the data is store in "full-fidelity" as the .jpg file can be restored:

[somneone@somewhere tmp]$ sqlite3 foodb "select quote(bar) from foo;" | perl -ne 's/([0-9a-f]{2})/print chr hex $1/gie' > bla.jpg 
[somneone@somewhere tmp]$ ll *.jpg
-rw-rw-r-- 1 someone someone 618441 Apr 28 16:59 bla.jpg
-rw-rw-r-- 1 someone someone 618441 Apr 28 16:37 test.jpg
[someone@somewhere tmp]$ md5sum *.jpg 
3237a2b76050f2780c592455b3414813  bla.jpg
3237a2b76050f2780c592455b3414813  test.jpg

Furthermore, this approach is space efficient as it store the .jpg using sqlite's BLOB type. It doesn't stringify the image using for example base64 encoding.

[someone@somewhere tmp]$ ll foodb 
-rw-r--r-- 1 someone someone 622592 Apr 28 16:37 foodb

Problem

I want to insert binary data(png,jpg,gif,etc) into a sqlite3 database within a bash script. I use the standalone binary `sqlite3`. How can I write the SQL statement? Thanks for your help.

Original source