Uploading xlsx file to mysql database?

[chrisindfw]

I am uploading a file to a mysql database table. Now let me say that this works perfectly fine for PDF, XLS, and DOC files. However if I upload an XLSX (office 2007 file), it uploads fine, however when it is downloaded, it cannot be read.

I can open both the original uploaded file and the one I downloaded in wordpad and they look EXACTLY the same.

HELP!

Additionally I am noticing that with Office 2007 files, the mime type is being truncated at 50 in my PHP (verified with a simple echo)

The mime type of "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
is truncated to "application/vnd.openxmlformats-officedocument.spre"

However if I manually set the mime type in the database to the correct value, it still cannot be opened correctly.

Any advice?
Thank you in advance for your time!
(Please be aware that these are not the full scripts. I know theres more to it and I do some validation here and there. I thought I could skip the common fluff)


Insert.php

$name = mysql_real_escape_string($_FILES['c_filename']['name']);
$mime = mysql_real_escape_string($_FILES['c_filename']['type']);
$data = mysql_real_escape_string(file_get_contents($_FILES['c_filename']['tmp_name']);
$size = intval($_FILES['c_filename']['size']);    
 
// I do some validation between then and now... Not going to bore you with that part
 
$query = "INSERT INTO `$filetable` 
     (filename, filetype, filesize,bin_data)
VALUES
    ('{$name}', '{$mime}', {$size}, '{$data}')";
$result = mysql_query($query)
or die ("Invalid query!!!<br><br>".$query);
 

Download.php:

//sql query was executed that selected that record.
 
$name   = mysql_result($result,0,"filename");
$size   = mysql_result($result,0,"filesize");
$type   = mysql_result($result,0,"filetype");
$content= mysql_result($result,0,"bin_data");
 
header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
echo $content;
 

[Atli]

Hey.

Additionally I am noticing that with Office 2007 files, the mime type is being truncated at 50 in my PHP (verified with a simple echo)

The mime type of "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
is truncated to "application/vnd.openxmlformats-officedocument.spre"

Looks like your table is limiting the amount of chars you can put into your "filetype" field. If it is defined like: "filetype VarChar(50)", MySQL would automatically, and silently, discard all but the first 50 chars you try to put into it. You need to up the limit on it if you want to use more.

I can open both the original uploaded file and the one I downloaded in wordpad and they look EXACTLY the same.

How so? I was under the impression that M$ Office files were stored compressed. What does WordPad show you when you open them? (I'd try but I neither have M$ Office, nor a Windows box to try it on xD)

Is there any difference in the sizes of the two files? Does Excel give you any usable error messages when it fails to open the downloaded file?

The two most common errors I've seen in situations like these are:

1. There is an error/warning/notice being printed in the download page, which is corrupting the source of the file you are trying to download. The quick, dirty and often useless fix for this is to just silence the error by turning of error reporting; the proper way to deal with this is to find the error and fix it.
2. There is a charset problem. Best way to check this is to make sure everything from the HTML page that shows the form to the columns in the database are set to the same charset. A simple way to test this is to submit a plain text file, encoded in the charset you are using, with special chars and see if they get messed up in transit.

[chrisindfw]

THanks for the quick response.

For the mime type, its not the database that is truncating the value, its PHP. If I do an echo $mime, I get the truncated value.

Second, I was just comparing the files in a HEX editor. It looks like when my files are being downloaded (or even uploaded) there is one extra blank space at the end. That is the only difference I can see from comparing hex values.

[Atli]

For the mime type, its not the database that is truncating the value, its PHP. If I do an echo $mime, I get the truncated value.

Is that the $mime variable you set on line #2 of the Insert.php code you posted above? If so, then the problem would appear to be with the browser. At that point in the code there has been no modifications made to the value presented by the browser, with the exception of the mysql_real_escape_string function which would not do this.

PHP does not truncate string values out of the blue; there must be something either wrong with the data PHP is receiving (either from the browser or from the database) or some function or logic applied to the data is behaving unexpectedly.

Could you show us all lines that affect the value of the $mime field, from the HTML form where the file is submitted to the header call when the file is downloaded? If PHP is indeed truncating it, that would help us figure out why.

Second, I was just comparing the files in a HEX editor. It looks like when my files are being downloaded (or even uploaded) there is one extra blank space at the end. That is the only difference I can see from comparing hex values.

Seems a very extreme reaction to such a small change. Did you try manually removing the extra space and seeing if the file will open then?

I would try debugging this further by calculating the MD5 checksum for the file at various points in your app. I suggest before uploading, before inserting into MySQL and after fetching it from MySQL. Might tell you at which point in the process the problem is created.