Cannot download and display binary files from database

[waydown]

Hi,
I’ve uploaded Microsoft Word docs to a MySql database and stored them as binary files. But when I download a file and try to display it in a browser (Firefox or IE) illegible characters are displayed. I’ve tried altering collations and character sets but to no avail. The same thing happens if I upload other types of files, e.g. gif, jpg images, etc. When I download I get a few illegible characters. Here is the PHP code for downloading a file:
<?php
$con = mysql_connect("hostserver",“user","pswd");
if(!$con)
{ die('Could not connect: ' . mysql_error());
}
mysql_select_db("database", $con);

$sql = "SELECT name, type, size, content FROM CVs ".
"WHERE id = 3";
$res = mysql_query($sql) or die('Error, query failed');

list($name, $type, $size, $content) = mysql_fetch_array($res);
header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$name");
echo $content;
mysql_close($con);
exit;
?>
I wonder if anyone has any suggestions as to how to rectify the problem. I am completely lost as to what to do next. I would be very grateful for all help.

 

[jpadie]

can you provide the upload code too.

 

[waydown]

Hi,
Many thanks for your reply. Below is the table that was created and the upload code:
CREATE TABLE CVs
( id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY(id),
)
///////////////////////////////////////////////////////////////////////
<?php
if(isset($_POST['cmdSubmit']) && $_FILES['fileUpload']['size'] > 0)
{ $fileType = $_FILES['fileUpload']['type'];
if($fileType == "application/msword") //only accept MS Word docs
{ $fileName = $_FILES['fileUpload']['name'];
$fileSize = $_FILES['fileUpload']['size'];
$tmpName = $_FILES['fileUpload']['tmp_name'];

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

if(!get_magic_quotes_gpc())
{ $fileName = addslashes($fileName);
}
//connection string
$con = mysql_connect("...","...","...");
if(!$con)
{ die('Could not connect: ' . mysql_error());
}
mysql_select_db("....", $con);

$query = "INSERT INTO CVs (name, size, type, content ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed');
mysql_close($con);
echo "<br>File $fileName uploaded<br>";
}
else
{ echo "<br>Must be a MS Word Doc<br>";
}
}
?>

 

[jpadie]

could you try this

<?php
//connection string
$con = mysql_connect("...","...","...") or die('Could not connect: ' . mysql_error());
mysql_select_db("....", $con);

//kill magic quotes
magic_quotes_runtime(false);

/**
 * function used to escape strings for mysql
 * @return string
 * @param object $string
 */
function e($string){
	if (get_magic_quotes_gpc()){
		$string = stripslashes($string);
	}
	$escaped = @mysql_real_escape_string($string);
	if (!$escaped){
		$escaped = mysql_escape_string($string);
	}
	return $escaped;
}


if(isset($_POST['cmdSubmit']) && $_FILES['fileUpload']['size'] > 0){  
	
	if($_FILES['fileUpload']['type'] == "application/msword"){    //only accept MS Word docs
		
		$sql = "INSERT INTO CVs (id, name, size, type, content) values (NULL, '%s', %d, '%s', '%s')";
		$query = sprintf(	$sql, 
							e($_FILES['fileUpload']['name']), 
							intval($_FILES['fileUpload']['size']), 
							e($_FILES['fileUpload']['type']), 
							e(file_get_contents($_FILES['fileUploaded']['tmpName']) ));
		mysql_query($query) or die('Error, query failed <br/>' . mysql_error()); 
		echo "<br>File $fileName uploaded<br>";
	}else {
 		echo "<br>Must be a MS Word Doc<br>";
	}
}
?>

and in your retrieval code change this line

list($name, $type, $size, $content) = mysql_fetch_array($res);

to this

list($name, $type, $size, $content) = mysql_fetch_array($res , MYSQL_NUM);

 

[waydown]

Hi,
Many thanks for your suggestions. I've carried them out. One problem is that although 'name', 'type' and 'size' are being loaded onto the database server the 'content' is not. It is showing as BLOB - 0B. Maybe I need to modify further?

 

[jpadie]

is the size too large for the mediumblob data type?

please tell me that your server has magic_quotes_runtime and magic_quotes_gpc set to false/off?

and you should be doing a bit more error checking on the incoming file upload. at least check that $_FILES['fileUpload']['error'] === 0

otherwise i do not see anything wrong with the code other than a (quite) major error :

this line

 $query = sprintf(    $sql, 
                            e($_FILES['fileUpload']['name']), 
                            intval($_FILES['fileUpload']['size']), 
                            e($_FILES['fileUpload']['type']), 
                            e(file_get_contents($_FILES['fileUploaded']['tmpName']) ));

should be

 $query = sprintf(    $sql, 
                            e($_FILES['fileUpload']['name']), 
                            intval($_FILES['fileUpload']['size']), 
                            e($_FILES['fileUpload']['type']), 
                        e(file_get_contents($_FILES['fileUploaded']['tmp[red]_n[/red]ame']) ));