View Full Version : Advice on storing files as blobs

07-11-2011, 10:06 AM

Currently i keep files on the file system and store the path of each file in a mySQL db. Next week our team are discussing whether they should be stored in the db as blobs.

So i'm looking for some of your knowledge on this issue/experiences of using blobs.

-The files are under 256kb
-The files shouldn't need to be changed often
-Currently they are stored offline and I use a php script to copy a requested file into the webroot and serve it to the client.

Personally i think its easier (i'm scared of the unknown :D) to leave them on the filesystem. So I know they are there, i can see their fileSize, fileName etc at a glance using a file browser.

So please share any views you have so i can be a bit more clued up, thanks.

Old Pedant
07-11-2011, 07:29 PM
Generally, the only reason to store files as blobs is if you are working on a system so large that you need a distributed database server (that is, a DB server that spans multiple computers and certainly not one that is on the same server as the web server).

You do this for scalability: When the data is in the DB, in blobs, you can use replication to ensure that it is accessible even when one of the DB servers is down (for example). If your system is small enough that you have only one machine for web server, DB server, and file server, then the blobs are an unnecessary level of complexity, in my opinion.

07-12-2011, 10:12 AM
Thanks for your reply.

How would you respond to the argument that it is difficult to keep the file paths stored in the db in sync with the actual files on the server? As that is what my team believe.

My counter argument for that is that the files don't actually change that much, and the paths are stored relatively so i only need to change one variable in my scripts.

Another point they raise is that security would be improved storing them as blobs. Currently the files (valuable to the company) are stored offline, i copy a file into the webroot on a user's request and delete it once it has been downloaded. How would you assess my current security set up? Cause for putting the files as blobs?

Old Pedant
07-12-2011, 09:37 PM
I agree that storing just the relative paths is a good strategy. You could have a single constant that specified the absolute root of the relative paths.

Re security: I think you can do better. There shouldn't be any reason to copy the file to the webroot. You should be able to authenticate the request and then use PHP to go fetch the file and binary write it to the response stream, all without ever copying the file, at all.

I'm *NOT* a PHP person, but I can do that in ASP/ASP.NET/JSP, so I'm sure it can be done in PHP.

Just a quick perusal of the PHP docs led me to this:
which suggests this for downloading/binary writing files:

// We'll be outputting a PDF
header('Content-type: application/pdf');

// It will be called downloaded.pdf
header('Content-Disposition: attachment; filename="downloaded.pdf"');

// The PDF source is in original.pdf

Ahhh...yes, look at this:

Would never have expected that a function named "READ"file would also automatically write it to the output buffer. PHP can be simple, but its function names are often pretty arcane, to me.

Anyway, read all the notes in those docs on readfile. They are enlightening. And they should stop you from copying files to the webroot, which I regard as a security hole.