Saving Blob data from SQLite database to file

Question

I am trying to save BLOB data from an SQLite database (Safari cache: Cache.db) to a file, but for some reason sqlite will not read the entire blob. In the end, I would like to do it in ruby, but now something that works directly on the sqlite command line is fine. In addition, I read all the entries that talk about it here in stackoverflow, but most of them only discuss the efficiency of saving images in blocks, and one entry that shows that saving drops to a file is in C #, which does not help me. Here is what I tried:

sqlite> select * from cfurl_cache_response limit 1; 3501 | 0 | 945281827 | 0 | http: //www.gospelz.com/components/com_jomcomment/smilies/guest.gif| 2010-02-24 16:20:07

sqlite> select receiver_data from cfurl_cache_blob_data where entry_ID = 3501;
GIF89a (

The hexdump of the source file (guest.gif) shows that sqlite stops reading blob after the first null value:

$ hexdump -C guest.gif
00000000 47 49 46 38 39 61 28 00 28 00 f7 00 00 f1 f5 fd | GIF89a (. (... |

sqlite> .output test.gif
sqlite> select receiver_data from cfurl_cache_blob_data, where entry_ID = 3501;
$ hexdump -C test.gif
00000000 47 49 46 38 39 61 28 0a | GIF89a (. |

Answer 1

SQLite3 reads the entire blob, but the sqlite shell program only displays up to NUL.

To check this, you can try:

select hex( receiver_data ) from cfurl_cache_blob_data where entry_ID = 3501; 

Answer 2

Generating a file from a blob or raw binary is pretty simple in Ruby. You do this the same as in the file. For example, to create jpeg:

 File.open("image_name.jpg", 'w') {|f| f.write raw_blob_content }