Base64 encoded binary into BLOB, back out and decoded

[jasonroyle]

MySQL 4.0.20-standard

 

I have a problem whereas I want to encode some binary with the php function, base64_encode(), put it into a mysql BLOB field, retrieve the encoded binary and finally decode the binary.

 

If I do not pass it through mysql it works fine, but when I do pass it through mysql it must change the binary data somehow.

 

The 2 fields in my table that i use are

 

file_id - type(int(10)), attributes(unsigned), null(no), extra(auto increment), primary key

binary - type(blob), attributes(binary), null(no)

 

<?php

$filename = "Test";
$file = "test.pdf";
$handle = fopen($file, "r");
$binary = fread($handle, filesize($file));

$update_query = "UPDATE `files` SET `binary` = '" . addslashes($binary) . "' WHERE `file_id` = '9'";

if(mysql_query($update_query, $conn)) {

$retrieve_query = "SELECT `binary` FROM `files` WHERE `file_id` = '9'";
$retrieve_result = mysql_query($retrieve_query, $conn);

if(mysql_num_rows($retrieve_result) == 1) {

$retrieve = mysql_fetch_assoc($retrieve_result);

header("Content-Type: application/pdf");
header("Content-Length: " . strlen($retrieve['binary']));
header("Content-Disposition: inline; filename=\"" . $filename . ".pdf\";");

echo $retrieve['binary'];

}

exit;

?>

 

any explaination and help would be usful, thanks.

 

Jay.

[jasonroyle]

Sorry I posted the code without the base 64 encode, here is the code i ment to post...

 

<?php

$filename = "Test";
$file = "test.pdf";
$handle = fopen($file, "r");
$binary = fread($handle, filesize($file));

$update_query = "UPDATE `files` SET `binary` = '" . base64_encode($binary) . "' WHERE `file_id` = '9'";

if(mysql_query($update_query, $conn)) {

$retrieve_query = "SELECT `binary` FROM `files` WHERE `file_id` = '9'";
$retrieve_result = mysql_query($retrieve_query, $conn);

if(mysql_num_rows($retrieve_result) == 1) {

$retrieve = mysql_fetch_assoc($retrieve_result);

header("Content-Type: application/pdf");
header("Content-Length: " . strlen($retrieve['binary']));
header("Content-Disposition: inline; filename=\"" . $filename . ".pdf\";");

echo base64_decode($retrieve['binary']);

}

exit;

?>

[PFMaBiSmAd]

There are a couple of problems with what you are doing (and if we consider that it adds overhead to store actual files in a database instead of using the file system, there are three problems.)

 

Using base64 encode adds about 30% to the size of the data. So, your database storage requirements are 30% more than what they would be if you just put the file into the database directly (escaping the data so that nulls and other special characters don't "break" the query string, which is why I suspect that you are bothering to use base64 encode in the first place.)

 

And, the file size that you are outputting in the header is the length of the base64 data, not the actual length of the file.

[jasonroyle]

Hmmm...

 

Yeah I knew it put the size of it up by something like 30% and yes I'm doing this so that all the characters are safe.

 

I replaced the content-length with:-

 

header("Content-Length: " . strlen(base64_decode($retrieve['binary'])));

 

and it opens acrobat and says the the file is damaged and could not be repaired

 

Do you know if it is even possible to store the base64 encoded binary in a mysql BLOB because I'm pretty sure its changing it in some manner?

 

Cheers for the help...