0

One of clients of company i work in decided he wants to store files inside SQL (most probably mySQL) database. As far as i know it is possible, just by storing binary or contains of file inside. But thats not the question.

The main schema would be that web page is stored at one server and database and uploaded files in another OR web page in one server database in another and uploaded files inside database. Following solution is the one we have doubts about.

The main reason is security. What best security solutions in this situation you can offer? I'm concerned that by breaking sql server they get the files, since sql injected breaks are more common than server, more over that servers are inside the company available only true secure connection, more over if sql server crashes it will be the pain in the ass to restore everything since sql dumps will weight a ton.

I'm asking arguments and other solutions for this situation.

JackLeo
  • 258
  • 2
  • 11

1 Answers1

1

Storing the files in the database means adding custom code to handle the access to the files. While this offer the opportunity to implement complex security models which are not directly supported by the underlying operating system, the more code you add, the greater the possibility of injecting bugs which will compromise the security / integrity of the data.

While I must admit that I'm no great expert on SQL Server (but I do know a bit about mysql) this approach requires that the code executes with the privilege of the super-user within the domain of the data; unlike normal file access there is no privilege seperation. So if the system is compromised, it is completely compromised. Unlike the situation where a OS user account is compromised - the exposure is massively reduced. Compare this with the problems due to viruses on Microsoft platforms compared with Unix platforms - it's not difficult to write viruses for Unix - but until very recently most Microsoft systems did not require privilege seperation.

servers are inside the company available only true secure connection

Not really a big advantage - look at the stats for data compromises - most are internal anyway.

it will be the pain in the ass to restore everything since sql dumps will weight a ton

True, although its a lot easier to do partitioned / incremental backups on a database than on a filesystem - but again this means adding more code = adding more bugs. The big issue is that this is a non-standard way of working. While you can be assured that a junior staff member could turn up at your disaster recovery site with a filesystem backup and be able to restore the data easily, doing the same with an application backup is a different story. And of course you're much more restricted in the choice of tools you use for automated access to your files (backup, AV scanning, de-duplication, archiving....).

Also, delivering the files via HTTP, you've lost all concurrency control over the files.

Another consideration is that, certainly with mysql, once a table outgrows the space on a single disk, adding more capacity is not trivial.

Some of these effects could be mitigated by retaining the files on the filesystem but by providing a web-based front end. If you do it right, then you can still employ privilege seperation (by running the webserver as a proxy between the browser and a per-user/session handling the actual I/O running with the privilege of the authenticated user). But you've still lost the concurrency control and there are other types of vulnerability which could be exploited.

As a IT services company its a great way to get money out of your client - selling them something they don't need, getting them to store their key assets using it, then billing them for supporting it. But it's a very bad idea from your client's point of view.

symcbean
  • 21,009
  • 1
  • 31
  • 52