Where is better to store uploaded files in DB as BLOB or in folder with restrictions?
I'm working with the FileUpload in my project. And this project would be high visited (it's not my ambitions, just because web application does work with a payment system, that's why it will be under high-load). And I wonder, what's better for a storing the user's files? My project is based on ASP.NET.
I suggest two variants:
- save as/load a BLOB object into/from database
- save/load to/from a folder where the files will locate and save info about files in the table for owner recognizing, the table design in BNF:
<user_files> ::= ( <id ::= int, primary_key, auto_increment, indexed><user_id ::= int><file_guid ::= varchar(255)>) | nil
I prefer BLOB , but afraid of a future high-load. Because, fetching data from the database requires more CPU-time and memory allocations, because:
- I need to use a connector, which will open a new socket to connect to DB localhost
- Then must call stored-procedure for a getting BLOB object
- at client-side, I must get the result from some classes from the connector
- I must deserialize it
- and only then just to send the file to a user in uncompressed and not corrupted state, so user can later open it in some editor (files often would be images and ms-office documents)
As I thought all these operations may decrease the server work and will require more time, I think it would be slow for a 2000 users online, which will exchange the documents very quickly
As for the storing files on the filesystem, I see only problem in:
- securing correctly the access of files, because different users must not see others docs and they must be hidden for the other users. I'm afraid, because the folder to which users are uploading files is seen for the system user of Windows for the IIS (IISUser...), because otherwise users won't be able to upload anything, so the folder will be public. I see only solution to make a Windows Service and to use IIS folder for the uploads as temporary. Windows Service will get files from it and place to the secure folder, where users from web would be unable to see it.
But, maybe, I'm going wrong with my ideas, that's why I'm asking you a piece of advice, because I want to make system more perfectly.
securing correctly the access of files
If you run into this situation you are already violation the OWASP security guidelines, since your files are insecure direct object references. This means that users can access files directly, because you opened a complete sub folder on IIS (like www.mysite.com/files/some_file.pdf) and your files probably have a name.
What you should do instead is:
- Register a file in the database with a unique; not its data, just its name and the user who uploaded it (optionally including rights or roles).
- Store the file on disk where the file name is the database identifier.
- Don't allow direct access but write a special HttpHandler that takes in the id of the document (just as you would do when storing the files inside your database).
When taking this approach, you achieve the following:
- Files have a unique number, which prevents them from having naming conflicts on disk.
- The HttpHandler can check the database of the user that downloads that file has the proper rights to do so.
- Because IDs are used, you are not vulnerable to canonical representation attacks, where the attacker does a request like this: www.mysite.com/file.ashx?file=..\web.config.
So from a security perspective, there is no problem in storing files on disk instead of your database.
Storing in a database will scale much better over time. If you use the folder solution, and someday you need or decide to use a cluster, synchronizing the files throughout the server farm will be hellish.
Even though fetching stuff from a database may be more CPU intensive, it does simplify a lot of things (your code will surely be more maintainable and portable), and you can always count on hosting and processing costs diminishing over time.
You can also cache stuff for speed. Either way I hope those files don't change a lot after being uploaded.