[GLLUG] BLOBs in Databases

[Thomas Hruska]

On 2/2/2015 7:00 PM, Chick Tower wrote:
> On 02/02/2015 02:44 PM, Kami Vaniea wrote:
>> I've never really tried reading the BLOB data directly from the DB.
>> Normally I just use the DB the same way I would use the file system and
>> code the application I'm writing to visualize whatever the binary is.
>
> Thank you, Kami, and everyone else who replied.  So it appears that a
> database would need to be part of a larger application that is able to
> view whatever BLOBs you store in the database.
>
> Some of you made suggestions about better approaches to storing binary
> data in databases, or whether to even do it.  I have no application in
> mind using BLOBs, nor do I want to swap one database for another.  I
> just had a question.  I saw the BLOB storage class, thought "That's
> cool", but then wondered what use it would be.

One thing not pointed out is possible security improvements over 
traditional file system storage.

A database BLOB object offers a fairly clean way to store binary data 
(files mostly) and associate it with a record in a secured, isolated 
environment (the database naturally provides this).  If someone can just 
go to:

http://yourdomain.gov/users/[ROWID]_drivers_license.pdf

They can write a script to easily retrieve a scanned image of every 
driver's license in the system.  If the PDFs are stored in the database 
itself, the user probably has to go through the application to access 
the content.  The application may then include auditing, tracking, and 
intrusion detection features to protect sensitive data.  So BLOBs do 
have some use.

However, as others have pointed out, storing binary data in a database 
is usually (but not always) a bad idea.  A lot of queries are SELECT * 
sort of queries, which means the BLOB columns would be returned whether 
or not the application ends up using the columns in the selected tables. 
  There are server-side modules (e.g. http://wiki.nginx.org/X-accel) 
that are much better to use as far as balancing security and performance 
goes when serving up file data.

-- 
Thomas Hruska
CubicleSoft President

I've got great, time saving software that you will find useful.

http://cubiclesoft.com/