Using CMS to manage BLOB Objects in Database

Hi,

I have a secure website accessed by a token authentication system, and while this works great to lock down index.php, it doesn’t lock down any file if the user pastes or types the address of the file in the address bar of their browser. A solution is to insert files directly into a separate Mysql db in a blob table, which is fine - a simple snippet will handle the sql work for this. However, I need a way that it can all be managed within the CMS Manager, whereby a content manager can easily upload a file, view available files, and create links for front end users to access.

Has anyone using MODx come across this problem before? How have they solved it? Or is there an existing snippet or plugin that will save me a whole bunch of time and do it all for me?

Would love to hear asap from anyone who can help.

Have you considered other options for this? Do you need the CMS features because a lot of users will be using the site?

Or could you do something like this:
1. Use an FTP client to upload files to a directory ABOVE the DocumentRoot
2. Create a page(s) in MODx that have restricted permissions (i.e. you must log in)
3. Write a Snippet that slurps these files and displays them on one of the restricted pages.

You could utilize the @DIRECTORY binding to generate links to the files.

Would that work at all?

I did think of this, and while it would be possible, there are a few restrictions:

1. Yes, there will be many general users of this site, even though it’s secure. At last count there was ~1400 users allowed to securely access the site, and this will continue to grow. There is no option for adding a further secure page listing the links to the files; which leads to my next point...
2. Anything in the HTML, either in a href tag or comment, or even a javascript link, that can be used to identify the hard location of the file, regardless of its location on the server, needs to be eliminated. Storing the file outside of the web root folder is, at minimum, a greater security risk. As such, anything that can be used to make the HTML link or actual location dynamic or soft is most preferable.
3. Whatever process used then needs to be communicated to content managers who don’t have any comprehension of web development or programming, and simply use the rich text editor to add/edit content. They will need to add the links to the content with a small number of processes or steps.
4. Lastly, ftp access is highly restricted, and therefore difficult to utilise.

This is why I think the Blob feature is best - at a simple code level it’s simple to implement. The problem is adding it to the CMS Manager.

A module is not that difficult to write.
http://sottwell.com/create-module.html
I wrote a front-end AJAX application to give visitor access to selected rows from the same database:
http://sottwell.com/article-extjs1.html

And I don’t consider myself to be a true programming professional, just a self-taught fairly talented amateur. I’m sure you could do much better.

I don’t follow you with this one:
"Storing the file outside of the web root folder is, at minimum, a greater security risk"

But hey, it’s late.

I still think it’s gonna be easier for you to work with a Snippet that takes an argument that somehow identifies which file you want, then PHP can look above the DocRoot to a special folder, slurp the file, then stream it back to the browser. I’ve used this type of thing to prevent under-18 users from watching a restricted movie trailer in what sounds to me to be very similar situation. No one was allowed to download the file, so the file had to be streamed through code, so there was no file path visible to the outside world.

Now, if your file must be streamed through code to obscure its source, it could be streaming data from a BLOB field in a database or from an actual file on the filesystem. The advantage of the latter option is that you don’t have to code a way to get that data into the database, you simply have to upload the file using an FTP client... but of course coding a module to do this is possible... I just don’t see why it’s required.... but I’m not "in the trenches".

A module is not that difficult to write.
http://sottwell.com/create-module.html
I wrote a front-end AJAX application to give visitor access to selected rows from the same database:
http://sottwell.com/article-extjs1.html

That looks cool. Definitely that would be the way to allow upload and management of the files. I think I’ll take this option as the best option for now, unless someone else has another solution.

you simply have to upload the file using an FTP client

This is a problem Everett, because as I mentioned, FTP connection and even the server environment outside of the web root folder is highly restricted and goes against the idea of having a CMS in the first place. Because of this, the solution has to come from within the CMS and can’t give uploaded files a hard location.

Oh, and thanks for the confidence Sottwell, I’m just a self-taught, probably less talented programmer than you

I agree that the module route would be best for managing files uploaded to the db. But I’d just like to point out that storing large files (>1Mb) as MEDIUMBLOB or LONGBLOB data in MySQL can be problematic. First you have to ensure that MySQL’s max_allowed_packet is large enough to allow the size of file that is being uploaded. Second you will find that to download the file consumes an equivalent amount of system memory. Thirdly queries are very much slower on the table where the data is stored.

The solution is to fragment the data over multiple rows and stream the data back to the user:
http://www.php.net/manual/pl/features.file-upload.php#35583

Just a few things I ’d change from what I wrote back then - set MAX_SQL to 40,000 not 50,000 and ensure the datafield is type text.

I’ve used this solution to successfully store many gb of data without problem on shared hosting, over many years and without experiencing corruption.

The FTP connection and even the server environment outside of the web root folder is highly restricted

Ah... I didn’t realize that. Well, looks like therebechips has some good info on making MySQL blob-ready. One dev team I was on tried using our CMS to serve up images using the database via blob fields, and it was very problematic -- the size limits were expected, but the caching had to be completely rebuilt in a custom way -- MySQL’s caching isn’t optimized for things like that.

Quote from: therebechips at Jun 10, 2009, 04:30 AM

Just a few things I ’d change from what I wrote back then - set MAX_SQL to 40,000 not 50,000 and ensure the datafield is type text.

Thanks therebechips, that code will come in very useful. Just wanted to confirm that you’re using a TEXT field rather than a BLOB? If so, would that be LARGETEXT?

It actually depends on how big your average file size is going to be. TEXT will store ~64kb (and has been fine for me with file sizes averaging around 5-600Kb), while MEDIUMTEXT will go to ~16MB (you don’t need to use all that space in each row of course). Try to aim for around 10 rows per file.