Uploading/Downloading files stored in MySQL database

[JD*]

Hey all,

 

This problem just came up in my website and I'm having a hard time figuring out what's wrong. I have a intranet portal running (custom built) and I'm storing all different types of documents in a mysql database. Previously, I was able to read the contents of the file into the DB and then pull it out and the file would open just fine. Now, for some reason, everything except PDF files come across as gibberish.

 

I've gone ahead and tried to upload files that I know are working, I've switched from base64_encode to addslashes, tried to do a mysql COMPRESS and UNCOMPRESS when uploading a new file....nothing. I'm just not sure why all of a sudden this stopped functioning properly (haven't done any upgrades to the server, db or code)

 

Anyone have any ideas I can try?

 

For reference, the code for inserting and downloading follows:

insert:

case "Add File":
$fp = fopen($_FILES['userfile']['tmp_name'], 'r');
$content = fread($fp, filesize($_FILES['userfile']['tmp_name']));
$content = base64_encode($content);
mysql_query("INSERT INTO intranet.documents(id, type, contents, content, name, category, updated) VALUES ('', '".$_FILES['userfile']['type']."', '".$content."', COMPRESS('".$content."'), '".addslashes(str_replace(" ", "_", strtolower($_FILES['userfile']['name'])))."','".$_POST['category']."', '".date("Y-m-d H:i:s")."')") or die(mysql_error());
fclose($fp);			
redirect("?action=".$_GET['action']);
break;

 

download:

$_SESSION['content'] = NULL;
$result = mysql_query("SELECT name, type, contents FROM intranet.documents WHERE ID = '".$_GET['id']."'") or die(mysql_error());
$_SESSION['content'] = base64_decode(mysql_result($result, 0, "contents"));
header("Content-Disposition: attachment; filename=".mysql_result($result, 0, "name")."");
header("Content-type: ".mysql_result($result, 0, "type"));

[simshaun]

Storing files in a database is usually a bad idea.

 

1. Have you verified that all data except the file contents is correct when you query the database?

2. What column type are you using for the file contents?

 

Regardless of whether you use MySQL's compress() function or not, you need to be using a BLOB column type.

 

Also, it does not appear that you are using UNCOMPRESS on the data when you query it from the database.

 

[JD*]

simshaun - thanks for the reply. I know storing files directly in the db is not the recommended way, but, unfortunately, it's how I've been instructed to do it.

 

As for the data, yes, it all checks out. I am currently using a LARGEBLOB type for storage, because I have some very non-tech people who will be uploading files and in most cases they do not know how to optimize (or they don't pay attention to their training, manuals, etc).

 

Sorry for the confusion about the UNCOMPRESS, but I am using it after putting the files into the DB.

 

There are 2 strange parts left to this:

 

1) PDF files come out with no problem. It's only other types (xls, doc, ppt) that are coming across corrupt

2) It works on my testing server without a hitch

 

I just removed the database on my live server and recreated it from my test server and copied the code from my test server that does the uploading and downloading of the files, and still, only PDF files are being recognized. For a word document, the file does come down with the correct headers, it's just garbled.

 

When I upload them, I do a base64_encode first, then the MYSQL Compress, and on the way out I do an UNCOMPRESS, then a base64_decode...is my order for those functions wrong? Or should I be using something other than the base64 (I had used it in the past to ensure binary safe data)?

 

Some of the searching I did online showed people uploading to the db with addslashes, but then on downloading not using stripslashes. I tried both with and without the stripslashes and got the same results each time.

 

[simshaun]

· Try it without base64_encode and decode. With your column-type being a blob, you shouldnt have to worry about encoding.

· Try eliminating COMPRESS/UNCOMPRESS.

· Add this before your mysql_query() call:

mysql_set_charset('utf8');

[PFMaBiSmAd]

magic_quotes_runtime is probably on, thereby escaping the data from fread(). What does the following line of code show for both your development system and your live server -

 

var_dump(get_magic_quotes_runtime());

[JD*]

I gave up and moved everything into the file system. I just got tired of bashing my head against the wall and people were angrily calling me.

 

Thanks for all of the help/suggestions. Now I just have to deal with my boss and not storing the files in the db

[PFMaBiSmAd]

Usually, the additional server overhead when files are stored in a database is enough to convince anyone, even bosses, not to do it. There are just too many disadvantages and most reasons anyone can find to do it can easily be overcome using correct coding.