Inserting binary data into MySQL databases.

[wpb]

Hello, I've had an interesting problem when trying to work with a blob field in my database. At home, where I have Apache, PHP5 and MySQL 5.0.45 set up, I wrote some php to insert images into my db and a script to display the images, and everything worked fine. When I migrated the same code to my hosting account, also running PHP5 and the same MySQL version, the code fails. I get an error saying:

 

The image “http://www.../image.php?id=5” cannot be displayed, because it contains errors.

 

Having done some research, I'm fairly sure the problem comes when I insert the image data into the database, which I do with something like the following:

 

$userfile = fread(fopen($temp_name,'r'), filesize($temp_name));

$userfile = (get_magic_quotes_gpc() ? $userfile : addslashes($userfile));

$query  = "INSERT INTO `images` (`mime_type`, `data`) VALUES ('" . $file_type . "', '" . $userfile . "')";

mysql_query($query);

 

(Obviously, this is a very cut-down version of my script...)

 

I'm sure it's an "addslashes" problem, but I can't figure out what exactly. I tried this also:

 

$unescaped_string = (get_magic_quotes_gpc() ? stripslashes($userfile) : $userfile);

$userfile = mysql_real_escape_string($unescaped_string);

 

But in that case, it failed both on my home setup and on my hosting account.

 

Interestingly, phpmyadmin can successfully insert an image blob into my database for me, and the script to display the blobs works just fine.

 

Can anyone tell me what I'm doing wrong? I'm out of ideas now.

 

Thanks in advance!

 

[tinker]

why do you check magic quotes and then add slashes, wouldn't the magic quotes bit refer to the filename?

also i'm sure there's a tag in mysql which informs it that your inputting binary data...

[wpb]

You're right - I don't need to check for magic quotes when reading in data from a file with fread(), I guess.

 

Now I have this:

 

$userfile = mysql_real_escape_string(fread(fopen($temp_name,'r'), filesize($temp_name)));

$query  = sprintf("INSERT INTO `images` (`mime_type`, `data`) VALUES ('%s', '%s')", $file_type, $userfile);

mysql_query($query);

 

But it still doesn't work. Works on my home setup, but not on my hosting account.

 

Could it be a charset issue? I use this to set the charset to UTF8 when I open the connection:

 

mysql_query('SET CHARACTER SET "utf8"', $db_conn);

 

Could there be some charset differences between my home setup and my hosting setup that would affect this?

 

Thanks,

[wpb]

Just to add... When I look at the query string phpmyadmin produces, it seems to have converted the input file into a massive hex number, that looks something like 0xfe68...blah...blah...forever...

 

Is there a good way to convert a php string into a number like this, so that I can reproduce the exact query phpmyadmin makes? It's not my ideal solution (I'd love to know why what I'm currently doing doesn't work), but it might be another way to tackle it.

 

Thanks,