Uploading files into a MySQL database using PHP

[MOBCOM]

Hello Atli, Kovik,

I may actually have a situation where using mysql blobs is preferrable to filesystem for unsearchable binary data.

We have been using filesystem to store photos and various formats of videos, but now are looking into mysql for mail attachments.

The three reasons we consider mysql now are these (control and security):

1. Whereas the web-accessible photos and videos are post-formated versions (converted to new dimensions and renamed), thus any exploit code from uploads is removed and can not be activated by access through direct url.

2. Mail attachments are in all formats and not sanitized like photos and videos, thus creating an increased possibility of 0-day exploit code being uploaded that will be accessible by web url, if filesystem is used.

3. Mail attachments may require additional security of not being accessible through web urls by anyone other than the attachment owner (at least long term when session key has expired). And though we use sha512+filedate +salt for photos/videos urls, filesystem storage would still leave the weakness of files being always accessible in future by everyone once the constant file url is known whereas mysql storage can be done through a php file requiring log in.

"Solution" would have been to store the mail attachments in directories that are not web accessible, e.g. /home/securefiles/topsecret.pdf and include the file through php script, but this creates the problem of how to include the file from remote servers - creating the vulnerability of permanent remote urls again.

Whereas mysql database provides a central authentication and security mechanism that can be accessed from remote servers and is not publicly web-accessible.

If you can think of a filesystem storage alternative that provides the security and authentication mechanisms of mysql storage in a multi-server environment with web-accessible urls, let me know, otherwise this is a situation where mysql storage is preferable to filesystem storage.

[kovik]

1. Whereas the web-accessible photos and videos are post-formated versions (converted to new dimensions and renamed), thus any exploit code from uploads is removed and can not be activated by access through direct url.

... Say that again, possibly without the use of the word "whereas" with the word "thus." Also, "exploits" are when one takes advantage of a flaw. If there is a flaw in your code, then it won't matter which medium you use to store the malicious data. It is still malicious.

2. Mail attachments are in all formats and not sanitized like photos and videos, thus creating an increased possibility of 0-day exploit code being uploaded that will be accessible by web url, if filesystem is used.

Not sanitized? Why do you say that? It is up to the mailing application to handle that. Smart companies like Google and Yahoo sanitize their outgoing mail to ensure they do not get blacklisted, and validate incoming mail for the user's safety.

3. Mail attachments may require additional security of not being accessible through web urls by anyone other than the attachment owner (at least long term when session key has expired). And though we use sha512+filedate +salt for photos/videos urls, filesystem storage would still leave the weakness of files being always accessible in future by everyone once the constant file url is known whereas mysql storage can be done through a php file requiring log in.

You are grossly underestimating the power of a server. MySQL is only an application run by the server; the server itself is by no means weak or insecure. Your server has the power to allow or deny ANY request.

"Solution" would have been to store the mail attachments in directories that are not web accessible, e.g. /home/securefiles/topsecret.pdf and include the file through php script, but this creates the problem of how to include the file from remote servers - creating the vulnerability of permanent remote urls again.

If you can retrieve the file from a database using a script, what is stopping you from retrieving the file from your file system using a script?

Whereas mysql database provides a central authentication and security mechanism that can be accessed from remote servers and is not publicly web-accessible.

You want remote servers to have access to these files?

If you can think of a filesystem storage alternative that provides the security and authentication mechanisms of mysql storage in a multi-server environment with web-accessible urls, let me know, otherwise this is a situation where mysql storage is preferable to filesystem storage.

The multi-server environment is the only time that MySQL would be the "simpler" option. That doesn't mean that MySQL is the only, or the smartest, option.

You state that using MySQL's authentication allows remote servers to connect to the database. You also state that this provides security. However, a server can tell where a request is coming from. MySQL's authentication only cares about whether the requester knows the correct credentials. In order for this data to be accessed, the requester only needs to discover these credentials. If this was handled by the server, the server could accept requests from a white-list and deny all others. Then, it's a matter of whether it is easier to discover your MySQL login details or to compromise your server and alter your white-list.

[MOBCOM]

... Say that again, possibly without the use of the word "whereas" with the word "thus." Also, "exploits" are when one takes advantage of a flaw. If there is a flaw in your code, then it won't matter which medium you use to store the malicious data. It is still malicious.

My statement's wording is correct. It's not the php code that is the issue, the permissions of the uploaded file can be easily set to be non-executable (heard of chmod?). The issue is that historically there have been plenty of attacks which exploit vulnerabilities on the server level.

Not sanitized? Why do you say that?

Because by nature it is possible to "sanitize" photos and videos by e.g. re-converting them to a different format. It is not possible to do this for the hundreds of other file formats that can be uploaded as attachments.

It is up to the mailing application to handle that. Smart companies like Google and Yahoo sanitize their outgoing mail to ensure they do not get blacklisted, and validate incoming mail for the user's safety.

oh come on, Google's idea of validation is not allowing .exe files to be sent. Yahoo and Hotmail use some crappy anti-virus to scan their attachments which is as useful/useless as airport security.

If files are accessed through direct url, then anti-virus isn't even deployed. And anti-virus is completely useless for detecting 0-day exploits which was my whole point, and the point of a 0-day exploit is not to be detected by anti-virus, nothing to do with someone's code (those are - code vulnerabilities , sql injection).

You are grossly underestimating the power of a server. MySQL is only an application run by the server; the server itself is by no means weak or insecure. Your server has the power to allow or deny ANY request.

If you can retrieve the file from a database using a script, what is stopping you from retrieving the file from your file system using a script?

My point was - some things are better to be authenticated on server layer and some on application layer, because when a vulnerability on application layer is discovered (web application/php/mysql), it won't necessarily compromise your server, whereas when server level authentication gets compromised your mysql/php setup goes too. The mysql storage keeps things at application layer (chrooted), protecting server layer from attacks. Also, no high-load production servers use SElinux and there have been cases where SElinux has been the cause of vulnerabilities .

There's an excellent paper by Ken Thompson from 1984 about open code being secure because everyone can read it (NSA being major supporter of SElinux): http://cm.bell-labs.com/who/ken/trust.html

You want remote servers to have access to these files?

The multi-server environment is the only time that MySQL would be the "simpler" option. That doesn't mean that MySQL is the only, or the smartest, option.

You state that using MySQL's authentication allows remote servers to connect to the database. You also state that this provides security. However, a server can tell where a request is coming from. MySQL's authentication only cares about whether the requester knows the correct credentials. In order for this data to be accessed, the requester only needs to discover these credentials. If this was handled by the server, the server could accept requests from a white-list and deny all others. Then, it's a matter of whether it is easier to discover your MySQL login details or to compromise your server and alter your white-list.

I'm talking about a multi-server/multi-DC situation, big sites where everything isn't one one box. Again, read my application layer vs server layer point above. If server level authentication is used that is shared across servers / datacenters and becomes vulnerable then all servers become vulnerable if application layer authentication is used then vulnerability is limited to application layer - now when you consider that chroot is used for php and mysql + data in mysql is non-executable no matter the file permission and dangerous commands in php have been disabled, then the application layer on a production server can be configured to have by multitudes higher security.

At MOB we only offer the highest level of security, and the faux-security that mass-oriented products like Yahoo, Hotmail, Gmail offer you is not acceptable!

With the exception of high security requiring setups, it has long been common knowledge that databases should be used for data only and for everything else filesystem should be the preferred setup, as it's faster, uses significantly less resources, and is easier to back-up.

Then again, times are changing, as the Oracle guru Tom Kyte says: "You have my advice, you want data protected, secure, manageable, restored to a point in time -- you be using a database."

[kovik]

In regards to sanitation, you still haven't explained how the database has an advantage over the file system.

Also, you mentioned direct URL access again. Using the database, you would not want direct file access. The server can accomplish this in a similar matter that the database does: by placing the data in a non-public area and then loading it using a script.

As for whether vulnerability lies at the server level or the application level, how do you plan on this being a reality? I'm assuming that by this, you are referring to something along the lines of a user uploading a PHP script to your server, and then running it. In what way does the placement of this data determine where your vulnerabilities lie? Allowing a vulnerability like this, regardless of the where it resides, should result in the same difficulties.

I also don't see any justification of your bias against your competitors other than that they are your competitors. Google has made the "gmail.com" domain a safe one to receive e-mail from. Google's, Yahoo's, and Microsoft's virus scanning, while only preliminary, helps to block known viruses and keep users aware of the risks when downloading files.

[MOBCOM]

In regards to sanitation, you still haven't explained how the database has an advantage over the file system.

Also, you mentioned direct URL access again. Using the database, you would not want direct file access. The server can accomplish this in a similar matter that the database does: by placing the data in a non-public area and then loading it using a script.

As for whether vulnerability lies at the server level or the application level, how do you plan on this being a reality? I'm assuming that by this, you are referring to something along the lines of a user uploading a PHP script to your server, and then running it. In what way does the placement of this data determine where your vulnerabilities lie? Allowing a vulnerability like this, regardless of the where it resides, should result in the same difficulties.

Re-read my previous post.

I also don't see any justification of your bias against your competitors other than that they are your competitors. Google has made the "gmail.com" domain a safe one to receive e-mail from. Google's, Yahoo's, and Microsoft's virus scanning, while only preliminary, helps to block known viruses and keep users aware of the risks when downloading files.

My justified bias was not against competitors, but against your claim that "Smart companies like Google and Yahoo" are secure. I'll say again - No ESP can make the attachments they forward 100% secure as almost all attachment formats (jpeg,png,gif,p df,js,flash,etc .) can contain (encrypted,obfu scated) malicious code, so anti-virus and disabling .exe attachments is faux-security to make average computer users feel secure.

But I was talking about best storage for server security, not end-user security where db/fs would be equally (un)secure.

And one of the companies you named (the smarter one), also stores attachments in database.


Example: You access your mail over wifi in a public internet cafe, though you access it over https, a man-in-the-middle that eavesdrops on your connection, will still see the urls of your attachments, and be able to directly access them, because they're files, with direct url, whereas if they're accessed through php files that authenticate privileges before even getting the file data from db, you are secure.
Now before you reply, figure out why you can't use php authentication the same way for files stored on filesystem.

Another example: We tested with a direct link of a photo that was received with Hotmail as attachment over 3 months ago and immediately deleted, the photo is still stored on Hotmail servers and accessible through that link.

[kovik]

I re-read your post more than once before responding. You aren't measuring the file system against the database on a level field. I'm starting to feel that maybe you need to re-read MY post.

You state that the database adds extra security because files are not publicly accessible via a URL. But for that to be an advantage, that would mean using the file system makes files publicly accessible. You and I both know that files on the file system do not *have* to be publicly accessible in order to be used.


Allow me to clarify for you.

Example: You access your mail over wifi in a public internet cafe, though you access it over https, a man-in-the-middle that eavesdrops on your connection, will still see the urls of your attachments, and be able to directly access them, because they're files, with direct url, whereas if they're accessed through php files that authenticate privileges before even getting the file data from db, you are secure.

Do you see the flaw in your logic? You are implying that PHP can communicate with the database, but that it cannot communicate with the file system. This is false. Your "extra" security is coming from PHP, not from the database.

[MOBCOM]

Do you see the flaw in your logic? You are implying that PHP can communicate with the database, but that it cannot communicate with the file system. This is false. Your "extra" security is coming from PHP, not from the database.

Now You are simply twisting what I said by using the word "implying". I can just as well say you are implying that you agree with me and are out of arguments.

The extra security, among other factors, comes from MySQL always handling the input as data. When you store data on filesystem it is handled as a file, which can be an executable file, depending on permissions. Data stored in MySQL is never handled as executable file by the server.

Lets say you let your visitor upload a file named "virus.txt" to "/home/secure/virus.txt". Then in future a remote code execution vulnerability is found in PHP/Apache/Kernel/SELinux/FTP/Cups/Anti-virus that lets the attacker execute arbitrary code from files located on the server, like virus.txt, just by knowing the file's path on the server, whereas when the upload would be stored in mysql as data, this type of very common attack becomes by multitudes more difficult, pretty close to impossible actually. Do you understand now?

[Atli]

The extra security the database provides comes from the fact that a database exists on a "deeper" layer than the file system.

Meaning; a file on the file system exists in the same "scope" as the server itself, and by extension, the PHP scripts. And as MOBCOM says, a server-level vulnerability, or even a vulnerability in a PHP scrip, would provide an attacker direct access to the files on the file-system.

A file inside a database, on the other hand, would not be *as* vulnerable. Even a OS-level code-injection attack would require database access in order to manipulate the files stored there, and even then it would not be able to execute them directly. A server-level file-execution attack, which could easily execute a file on the file-system, would have no chance to execute files stored in a database.

As to which method makes files safer from unwanted viewers, that depends. A vulnerability in the PHP application would make them equally unsafe. But, again, a vulnerability in the server or the OS would give an attacker direct access to the file-system, but it would still require database access to fetch the file from the database. (Which I guess it could get form the file-system, granted.)

So in that case the database would be marginally safer.

[Atli]

...You also state that this provides security. However, a server can tell where a request is coming from. MySQL's authentication only cares about whether the requester knows the correct credentials. In order for this data to be accessed, the requester only needs to discover these credentials.

No true. MySQL users can be - and should be - limited to a specific domain/IP-range.

[kovik]

Lets say you let your visitor upload a file named "virus.txt" to "/home/secure/virus.txt". Then in future a remote code execution vulnerability is found in PHP/Apache/Kernel/SELinux/FTP/Cups/Anti-virus that lets the attacker execute arbitrary code from files located on the server, like virus.txt, just by knowing the file's path on the server, whereas when the upload would be stored in mysql as data, this type of very common attack becomes by multitudes more difficult, pretty close to impossible actually. Do you understand now?

Yes, I do. I didn't consider the vulnerability issues beyond public access. So you're right. That would be an advantage of the database versus the file system. In order to exploit a flaw, one would need access to both the server and the database rather than just the server. And, to run a file from the database, one would need to be able to access the database, retrieve the file data, and then run it using a script that has to exist on the server level instead of just running a file on the server level.

Looks like Atli has something to add to his article, now. :P

[kovik]

No true. MySQL users can be - and should be - limited to a specific domain/IP-range.

Wouldn't that be handled at the server-level, though?

[Atli]

Wouldn't that be handled at the server-level, though?

I would assume MySQL gets the location of the connection from the OS, just as a HTTP server would. However, this makes MySQL at least equally secure in that regard as the HTTP server.

Simply knowing the MySQL login credentials is not enough. You need to be connecting from a valid location, which in the case of most web-based applications should be either localhost or an in-house network, neither of which is exactly easy to fake. (Remote addresses can be faked easily enough, sure, but it would take something extra to fake a network/localhost connection.)

[kovik]

My curiosity caused me to take a look into this, and the connections are handled by sockets. I know what sockets are used for, but no idea how (regardless of how much I read). I feel like I'm getting off-topic though. lol

[itsmenarwal]

Hello Fiends...

I follow all steps mentioned in this tutorials...All things are going fine..
But i have one problem When i download the file(it is downloaded no problem) but when i open it then file is not opening..Like suppose i have downloaded an image then after download if i try to open it erro is photogallry can not open this,may be file format is not supported..I am using window vista..So plz tell me where i am falling wrong

[itsmenarwal]

i posted one problem regarding opening downladed file error(not opening image)..i am not finding it..where it is