How to insert binary data into sqlite3 database in bash?

Question

I want to insert binary data (png, jpg, gif, etc.) into sqlite3 database in bash script.
I am using standalone sqlite3 binary. How to write an SQL statement? Thank you for your help.

Answer 1

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

 [ 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 stored in "full accuracy", since 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 

In addition, this approach is spatially efficient because it stores .jpg using the sqlite BLOB type. It does not build an image using, for example, base64 encoding.

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

Answer 2

As I mentioned in the comment on @sixfeetsix's answer, inserting data is only half the problem. After that, you will need to return it. We can use xxd for this.

 #A nice hubble image to work with. echo 'http://asd.gsfc.nasa.gov/archive/hubble/Hubble_20th.jpg' > imageurl.txt image=imageurl.txt curl $image > image.jpg #Insert the image, using hexdump to read the data into SQLite BLOB literal syntax. echo "create table images (image blob);" | sqlite3 images.db echo "insert into images (image) values(x'$(hexdump -ve '1/1 "%0.2X"' image.jpg)');" | sqlite3 images.db 2>&1 #Select just the one image, then use xxd to convert from hex back to binary. echo "select quote(image) from images limit 1 offset 0;" | sqlite3 images.db | tr -d "X'" | xxd -r -p > newimage.jpg eog newimage.jpg