Storing and Retrieving Files from Database

ufbusman

I have a simple webpage which allows users to upload files to my website (in a BLOB in the database) and retrieve files. The problem I am having is that the files that are retrieved are corrupt. It seems to work fine (sometimes) with .PDF files, but .DOC files are always corrupt upon download. In fact, it appears that part of the SELECT statement is showing up at the beginning of the downloaded files.

The beginning of the downloaded files-- if viewed in a text editor-- look like this "SELECT name, type, size, content FROM upload WHERE intid = '60'<br>ﾐﾏ 爍ｱ ・ >  ' ) "

I have attached a pic of the database table called "upload" which stores the files"

Here is the form that allows people to upload files (I deleted the other form fields that didn't pertain to the upload):

<form method="post" action="addassignment.php" enctype="multipart/form-data">
<table>

<tr>
<td>Attach a File (optional):</td>
<td>
<input type="hidden" name="MAX_FILE_SIZE" value="4000000">
<input name="userfile" type="file" id="userfile">
</td>
</tr>

<tr>  

  <td width="175">
  <p>
  </p>
  </td>
  <td>
  <input type="submit" class ="box" value="Send"/>
  <input type="reset" value="Reset"/>
  </td>
</tr>
</table>
  </form>

Here is the snipped of code that checks to see if the person chose to upload a file and writes it to the database:

if(isset($_POST['period']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = mysql_real_escape_string($content);
fclose($fp);

$fileName = str_replace(' ', '', $fileName);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

$query = "INSERT INTO upload (name, type, size, content, teacherid, assignmentId) VALUES('" . mysql_real_escape_string($fileName) . "', '$fileType', '$fileSize', '$content', '" . userIDFromDirectory() . "', '$whichAssignmentId')";

mysql_query($query) or die('<br>Error, file upload failed ' . mysql_error());
echo "<br>File <i>$fileName</i> uploaded!<br>";
}

Here is the snippet of code that allows people to download files from the database:

if(isset($_GET['id']))
{

$id    = $_GET['id'];
$query = "SELECT name, type, size, content FROM upload WHERE intid = '$id'";
echo $query;
echo '<br>';
$result = mysql_query($query) or die('Error, query failed' . mysql_error());
list($name, $type, $size, $content) = mysql_fetch_array($result);

header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=" . $name );
echo $content;

exit;
}

Any suggestions or any other additional information needed??

Thanks so much in advance!

Weedpacket

It might have something to do with possibly doing both addslashes and mysql_real_escape_string. (Incidentally, that whole magic_quotes thing is going away in PHP 6 - dropped as being a bad idea.)

The more modern (PHP5.1+) [man]PDO[/man] drivers provide BLOB-handling facilities.

Another (often more convenient) alternative is to store the binary documents on the filesystem, and only store the path- and filename (and other usefully-searched metadata) in the database.

jazz_snob

You're echoing the query, then doing header, then the "content" download. Stop echoing the query string and see if that stops the query string from appearing in the .doc output.

Make sure you mysql max_allowed_packet_size is big enough. I think its 8MB by default? It has to be bigger than your biggest blob cause you are reading/writing the blob in one shot.

I find mysql_real_escape_string() corrupts binary stuff, like images.

ufbusman

jazz_snob;10908020 wrote:
You're echoing the query, then doing header, then the "content" download. Stop echoing the query string and see if that stops the query string from appearing in the .doc output.

Make sure you mysql max_allowed_packet_size is big enough. I think its 8MB by default? It has to be bigger than your biggest blob cause you are reading/writing the blob in one shot.

I find mysql_real_escape_string() corrupts binary stuff, like images.

Thank you for your help. I took out the echo of the query and you are right-- that line does not show up anymore. Now .PDF documents seem to always work, but I am still having the corrupt problem with .DOC. I tried to comment out the my escape string part of the $content variable (see below) but now every time I try to write a file to the database I get can error:

Error, file upload failed You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ']lzÐô¥4¶“HÒV¼—Å§¬€/’dÉí<ƒeg™ÑV f·Šúó¡§æëÙ¦,›^M±ï@ã… ŽÄ(m(ØAÍ4&PÆ/3Ü‡dø†Ý; Ò' at line 1

if(isset($_POST['period']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
// $content = mysql_real_escape_string($content);
fclose($fp);

$fileName = str_replace(' ', '', $fileName);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

$query = "INSERT INTO upload (name, type, size, content, teacherid, assignmentId) VALUES('" . mysql_real_escape_string($fileName) . "', '$fileType', '$fileSize', '$content', '" . userIDFromDirectory() . "', '$whichAssignmentId')";

mysql_query($query) or die('<br>Error, file upload failed ' . mysql_error());
echo "<br>File <i>$fileName</i> uploaded!<br>";
}

I figured it might have to do with the INSERT query, so I modified it to this (the $content variable is fed in differently)

$query = "INSERT INTO upload (name, type, size, content, teacherid, assignmentId) VALUES('" . mysql_real_escape_string($fileName) . "', '$fileType', '$fileSize', '" . $content . "', '" . userIDFromDirectory() . "', '$whichAssignmentId')";

But I am still having the same problem. I don't mind storing the file on the filesystem and just keeping the link in the database-- but I'm not sure exactly how to do that. I'll try to do some reading and see if I can figure it out.

ufbusman

Weedpacket;10908016 wrote:
It might have something to do with possibly doing both addslashes and mysql_real_escape_string. (Incidentally, that whole magic_quotes thing is going away in PHP 6 - dropped as being a bad idea.)

The more modern (PHP5.1+) [man]PDO[/man] drivers provide BLOB-handling facilities.

Another (often more convenient) alternative is to store the binary documents on the filesystem, and only store the path- and filename (and other usefully-searched metadata) in the database.

Thank you for your quick response. The addslashes part is only affecting the filename variable and I don't think the problem is with the filename but rather the $content. The escape string is acting on the $content but every time I try to remove it, I get errors (see my response to the other poster).

I don't mind doing what you suggested, storing just the path, filename, etc. But I am not sure exactly how to store the file directly to the file system. I will do some reading and see if I can figure it out.

ufbusman

I have rewritten the code. Now (as suggested) the file is just placed in an upload directory and the database stores the information about that file.

This was so much easier, I wish I would of done it this way in the first place! Thanks again for all of your help.

I have a quick question though-- is it possible to download the BLOBs directly from the database through phpmyadmin? I can see that they are there. I can browse and attached a different binary for the BLOB, I just can't figure out how to get them out directly. I know I could write a script to do it via PHP, but I figured that it had to already be implemented in phpmyadmin.

Thanks