inserting/reading blob data from php

Question

I am using the following scripts to test inserting and then reading blob data.

insertion script:

include('session.php');

$provider      =$_POST['provider_id'];
$trd_period    =$_POST['trading_period_month'];
$pdf_statement =stream_get_contents(fopen($_FILES['pdf_statement']['tmp_name'], 'rb'));
$pdf_statement_clean=addslashes($pdf_statement);


$insert="update rd_provider_statement
         set pdf_statement='".$pdf_statement_clean."', creation_user_id='SCO'
         where  provider_id='".$provider."' and trading_period_month='".$trd_period."'";

mysql_query($insert);
mysql_query("COMMIT"); 

echo mysql_error();

Download Script:

include('session.php');

//Gather Post Variables
$TP_Month  =$_POST["trading_period_month"];
$provider =$_POST["provider_id"];
$TP_format =substr($TP_Month, 0, 7);

//Download Statement

$sql_qry="select *
          from   rd_provider_statement
          where  provider='".$provider."' and trading_period_month='".$TP_Month."'";
$sql_err_no=sql_select($sql_qry,$sql_res,$sql_row_count,$sql_err,$sql_uerr);

$row = mysql_fetch_assoc($sql_res);
$bytes =stripslashes($row['pdf_statement']);
header("Content-type: application/pdf");
header('Content-disposition: attachment; filename="'.$provider.'statement'.$TP_format.'"');
print $bytes;

However, when the file is downloaded it cannot open on the grounds that it is not a supported format. I use the basis of the script on another page to download blob data from the database however the insertion into the database here is done by a mysql procedure and not PHP. I think it is my insertion script that is causing the problem.

storing files in database is not a good idea: stackoverflow.com/questions/3748/… — Wern Ancheta
– Wern Ancheta, Commented Jul 12, 2012 at 6:30
So if you're on hosting and there's no other way to store/retrieve files on a filesystem how would one like, store and retrieve files? Sometimes they've got those strict PHP/MySQL deals or they cap/filter your file uploads or something. — Marc DiMillo
– Marc DiMillo, Commented Feb 5, 2013 at 11:54

Community · Accepted Answer · 2017-05-23 12:27:53Z

1

try using mysql_real_escape_string() instead of addslashes(). it might fix you problem.

For debugging, you might calculate the md5() of the string before inserting into DB and then after retrieving it. I bet you're going to get different hashes, meaning you're not inserting it correctly and your binary data gets corrupted when inserted into the DB.

Side notes:

don't use inserts like that, use binding - How to bind SQL variables in Php?
check for errors and STOP, dont simply echo them(i hope you're doing this in your production code)

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jul 12, 2012 at 6:30

Quamis

11.2k13 gold badges54 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1488434 Over a year ago

I originally had mysql_real_escape_string() but changed to slashes after I could not get to work. md5 check returned completely different hashes. I have no idea why though. The production code stops on error and rollsback any changes... as this is only for testing I like to keep the code as minimal as possible until it is fulyl working

Quamis Over a year ago

the column in the DB is BLOB (binary) or TEXT(has charset support, and might alter some characters)?

user1488434 Over a year ago

Medium BLOB, perhaps I would use the mysql load_file() instead?

Quamis Over a year ago

Are you sure your data gets into the DB correctly? And that it fits in the BLOB fields? Did you tried looking at it with with phpMyadmin?

Wern Ancheta · Accepted Answer · 2012-07-12 06:50:11Z

Generally you wouldn't want to have any output code before your http header description. See http://php.net/manual/en/function.header.php

Either store the filename and other file information in a session then just access them in another page.

A few things that you need to check:

max_allowed_packet in my.ini should be equal or higher than the file size that you're expecting to store in the database
check to see if the data type that you selected fits the file that you will store. There's tiny blob, blog, medium blob and long blob. You might want to try the largest which is long blob.
I'm not sure about this one but did you already check if file_get_contents works:

mysql_real_escape_string(file_get_contents($file))

user3325657 · Accepted Answer · 2014-08-25 17:00:26Z

Here's my alternative answer.

First the update query:

Prepare the file (assuming that your file is not a binary file):

    $tmpName = $_FILES["pdf_statement"]["tmp_name"];
    $fp = fopen($tmpName, 'r');
    $data = fread($fp, filesize($tmpName));
    $data = addslashes($data);
    fclose($fp);



$insert="update rd_provider_statement
         set pdf_statement='".$data."', creation_user_id='SCO'
         where  provider_id='".$provider."' and trading_period_month='".$trd_period."'";

DOWNLOAD:

enter code here

$sql_qry="select provider_id, pdf_statement
          from   rd_provider_statement
          where  provider='".$provider."' 
         and trading_period_month='".$TP_Month."'";

$sql_err_no=sql_select($sql_qry,$sql_res,$sql_row_count,$sql_err,$sql_uerr);

$row = mysql_fetch_assoc($sql_res);
    $name=$row['provider_id'];
    $file=$row['pdf_statement'];


header("Content-Disposition: attachment; filename=\".$name_statement.$TP_format.\";" );
echo $file;

Hope it helps =)

Collectives™ on Stack Overflow

inserting/reading blob data from php

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related