PHP / MySQL / BLOB / PDF

[65bit]

Hi,

I've been a programmer for many years, but am new to PHP and almost all things related to web developement.

I know there's arguments both ways about storing images / PDF's etc in BLOB's, but it's what I've been tasked to do. I've read many posts and tried many variations of my code, but no matter how simple it looks, I just can't get it to work.

I have a very basic table named "pdf" with a field named "file_name" of type varchar and a field "pdf_file" of type longblob. The script connects to my db fine, inserts a new record, puts the file name in the appropriate field and puts [BLOB] in the pdf_file field. The pdf size is ~ 60 KB and after inserting 15 - 20 records, the db size (pdf table is only one in this test db) is 6K. So, the PDF ins't making it into the table.

Can anyone give me a clue to what I'm missing:

<?
if ($REQUEST_METHOD == "POST") {
$file_name="/1090/WD-D.pdf";
$instr = fopen($file_name,"rb");
$file_content = addslashes(fread($instr,filesize($file_name)));
$file_type = "application/pdf";
SQLQuery("mydb", $cid, "insert into pdf (file_name, pdf_file) VALUES('$file_name', $file_content')");	
}    

?>

And, assuming I get that to work, should I be able to display it (just for testing) by adding the following 3 lines to the above

header("Content-type: application/pdf");
echo $stmt_content;
exit ();

I assume the content type will fire up acrobat reader or open another window with the pdf in it or ???

Your help in getting me on course is appreciated.
David

[NogDog]

A couple things I'd change would be to use "<?php" instead of the not-always-supported "<?", and use $_SERVER['REQUEST_METHOD'] instead of $REQUEST_METHOD (which will only be set if the deprecated register_globals option is enabled.

Lastly, for debugging, you could add the following to the top of the script to have it spit out anything it has problems with:

<?php
ini_set('display_errors', 1); // set to 0 for production version
error_reporting(E_ALL);
// . . . rest of script . . .
//

Also, for something like this I usually leave out the final "?>" so that it does not accidentally output any newlines/spaces that might get appended to the file after the closing tag.

[65bit]

Thanks .... I gave each of what you suggested a try, but it didn't change the results.

I'm sure the POST is firing as the code actually creates a record in MySQL and inserts the first field, it's just the BLOB doesn't get the binary PDF (based on DB size not growing as expected).

I also added the settings to show errors, but none are being generated.

If I display $file_content, I indeed see a bunch of binary, it's just not going into the table.

BTW - the slimmed down code I inserted in my first post has a mis-matched ' on the insert of $stmt_data. That's been fixed and I'm describing the results when that's as it should be.

Any other help would be apprecited.

[NogDog]

What database interface are you using (I don't recognize SQLQuery() as a standard PHP function). You might want to check if it returns false or some othe value if a query fails, and if there is some way to get error info from the database if it fails (such as mysql_error() if it uses the MySQL extension).

[bradgrafelman]

Also note that [man]addslashes/man is not the appropriate function to use when escaping content for SQL queries.

For the mysql extension, you would use [man]mysql_real_escape_string/man. Use the specific DB escaping method appropriate to whichever class/extension you're using.

[65bit]

NogDog - I should have mentioned that SQLQuery is my own function to handle my db connection, etc and that I'm using MySQL as a db.

I've made a little progress in that I'm now storing data in the blob field. I must have been doing something dumb before (was I even refreshing the screen with Windows explorer after I wrote records to see if the db size changed??) as I really haven't changed my code.

My only indication that it's now working is the db is growing by the epected amount. Though it doesn't make any difference from that persepctive, I also changed from addslashes() to mysql_real_escape_string (thanks Brad).

I've created a couple of records, one holding a .jpg and one with a .pdf. Unfortunately, I can't get either to display. I'm not sure if it's because I'm pumping garbage in or if I'm missing something on the output side.

My insert code is:

<?
if ($REQUEST_METHOD == "POST") {
$file_name="/1090/WD-D.pdf";
$instr = fopen($file_name,"rb");
$file_content = mysql_real_escape_string(fread($instr,filesize($file_name)));
fclose($file_name);
SQLQuery("mydb", $cid, "insert into blob (file_name, blob_file) VALUES('$file_name', $file_content')");	
}    

?>

For the display side of the pdf I'm trying:

<?
$blob = SQLQuery($dbname, $cid, "select id, blob_file from blob where id = 1");
header("Content-type: application/pdf");
print(mysql_result($blob, 0, "blob_file"));	
?>

and for the image I have this in a .php page:

<img src = "show_blob.php">

and this for show_blob.php:

<?
$blob = SQLQuery($dbname, $cid, "select id, blob_file where id = 2");
header("Content-type: image/jpeg");
print(mysql_result($blob, 0, "blob_file"));	
?>

When I try to display the .pdf, nothing happens. And when I try to display the image, I get a red "x" where it's supposed to be.

Any other help is appreciated.

Thanks!

[jazz_snob]

I have problems using mysql_real_escape_string() when the blob contains binary data (eg images). The images were corrupted if I used mysql_real_escape_string(). I don't know enough about PDF format to say whether or not that is an issue, however.

You have custom functions. Try writing the code w/ the standard php functions and see if it works. Then refactor to utilize you're own functions. Ignore all the security (escaping args etc) and do a bare bones implementation until you get it working. Then retool it.

[NogDog]

If you think it might be an escaping problem, you could try this:

<?php
if ($REQUEST_METHOD == "POST") {
$file_name="/1090/WD-D.pdf";
SQLQuery("mydb", $cid, 
   "insert into blob (file_name, blob_file) VALUES('$file_name', LOAD_FILE('$file_name'))");	
}
?>

[bradgrafelman]

jazz_snob wrote:

I have problems using mysql_real_escape_string() when the blob contains binary data (eg images). The images were corrupted if I used mysql_real_escape_string(). I don't know enough about PDF format to say whether or not that is an issue, however.

Sounds like a double escaping issue possibly - your server probably has the (very) deprecated magic_quotes directive enabled.