Adding binary data to MySQL

Hi,

I'm using PHP and MySQL to store binary data (normal files).
Then I have some other page on which users can download those files.

THe problem is:
The downloaded files are completely rooted up.

Example:

Original document:

Quote:

---

Mult3 - Tijdens deze module werken de projectteams aan de interface van een door
hen zelf ontwikkeld product. De volgende onderdelen komen bij het ontwikkelen van
een user interface in deze module aan de orde
Is bijna af.

Comp3 - Hiervoor moet ik een mouse-driver programmeren in C\Linux. Deze moet
overnieuw gemaakt worden, maar ik heb nu geen Linux.
Kost hooguit een uurtje, als ik Linux heb.

---

Downloaded document:

Quote:

---

ÐÏ à¡± á > þÿ % ' þÿÿÿ $ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ì¥Á 7 ð ¿ é
bjbjU U " 7| 7| é ÿÿ ÿÿ ÿÿ l ¶ ¶ ¶ ¶ ¶ ¶ ¶ Ê ² ² ² ²
¾
Ê ¶ Ö Ö Ö Ö Ö Ö Ö Ö œ ž ž ž ž ž ž $ Ó ó 0  ¶ Ö Ö Ö Ö Ö Â b ¶ ¶ Ö Ö × b b b Ö v ¶ Ö ¶ Ö œ b Ö œ b : b œ ¶ ¶ œ Ö Ê
*•×5yÂ Ê è ² L œ œ í 0 œ # L # œ b Ê Ê ¶ ¶ ¶ ¶ Ù Mult3 - Tijdens deze module werken de projectteams aan de interface van een door hen zelf ontwikkeld
product. De volgende onderdelen komen bij het ontwikkelen van een user interface in deze module aan de ordeIs bijna af.Comp3 - Hiervoor moet ik een mouse-driver
programmeren in C\Linux. Deze moet overnieuw gemaakt worden, maar ik heb nu geen Linux.Kost hooguit een uurtje, als ik Linux heb.

---

I've tried storing and retrieving several files like executables, zip files, documents, pictures. Only the jpg files were retreived correctly, the rest was really *****ed up...


This is the code I use to store and download the data again:

To store the data:

PHP Code:

---

$db=mysql_connect ("localhost", $dbuser, $dbpass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("chieljan_CWN");

if (isset($binary_File) && $binary_File != "none")
{

$data = addslashes(fread(fopen($binary_File, "r"), filesize($binary_File)));
$strDescription = $_POST['description'];

$sql = "INSERT INTO doc_documenten ";
$sql .= "(description, document, category, filename, size ) ";
$sql .= "VALUES ('$strDescription', '$data', '1'";
$sql .= "'$binary_File_name', '$binary_File_size')";

$result = mysql_query($sql, $db)or die("Query failed : " . mysql_error());
} 


---

To retreive the data again (through a link which contains the ID)

PHP Code:

---

$id = $_GET['download'];

if ($id > 0)
{
$db=mysql_connect ("localhost", $dbuser, $dbpass")  or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("chieljan_CWN");



$sql = "SELECT document, filetype, filename, ";
$sql .="filesize FROM doc_documenten WHERE doc_id=$id";

$result = @mysql_query($sql, $db);

$data = @mysql_result($result, 0, "document");
$name = @mysql_result($result, 0, "filename");
$size = @mysql_result($result, 0, "filesize");
$type = @mysql_result($result, 0, "filetype");

header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");
echo $data;
} 


---

Can anyone tell me if I'm doing something wrong in PHP, or it is a mysql problem?

Your problem may be with the addslashes() function. If the magic_quotes_gpc setting is on, PHP automatically applies the addslashes() function to any form, cookie and session data. Applying the addslashes() function again will screw up the file.

You can check whether magic_quotes_gpc is on of off at runc time by using the get_magic_quotes_gpc() function.

Ok, I changed that particular line of code into this:

PHP Code:

---

if (get_magic_quotes_gpc()) 
            {
                $data = fread(fopen($binary_File, "r"), filesize($binary_File));
            }else{
                $data = addslashes(fread(fopen($binary_File, "r"), filesize($binary_File)));
            } 


---

But now when I run the script, I get this error:

Quote:

---

Query failed : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DÒîÏeíÿÇF¡xíé-bÔT' at line 1

---

I really don't understand this error, but I assume it has something to do with the data.. :(

Hey a while ago i was stroin binary files in a database so they could be emailed to people, and i used base64_encode(). I didnt have any problems with MySQL querys. But it proberaly will not be very effective for you as you need to be encoding and decoding them each time. Just though i would mention it to see if it helps.

Disregard what I said about magic_quotes_gpc(), I made a mistake in saying that PHP will add the slashes automatically. It won't, becuase the file data wasn't submitted from a form. Try using quote meta instead, as this is binary data.

PHP Code:

---

$data = quotemeta(fread(fopen($binary_File, "r"), filesize($binary_File))); 


---

The filename is submitted through a form.

PHP Code:

---

<form name="input" action="adddoc.php" method="post" ENCTYPE="multipart/form-data">>
        <input type="hidden" name="f" value="2">
        Beschrijving:<br>
        <input type="text" name="beschrijving" size="60"><br><br>
        Categorie:<br>
        <select name="cars">
        <option value="Test">test</select><br><br>
        Document:<br>
        <input type="file" NAME="binary_File">
        <br><br>
        <input type="submit" value="Opslaan">
</form> 


---

But the latest problem I've encountered is not concerning the binary data itsself, but a problem in my query to add it.

The problem in your query is caused by MySql mistaking one of the binary characters as a SQL special character.

Are you using stripslashes when your retrieve the data? If you are then don't as this is done by MySql.

I'm not using stripslashes to retrieve the data (see code above)

How can I prevent sql from mistaking my data for special commands? :(

I've taken a closer look at the code you have posted. It contains errors - I assume your actual code does not contain these errors becuase if it did it would simply not run.

Firstly with storing files, you need to ensure the data type you are using is large enough to accomodate the file you want to store. If it isn't, MySql will truncate it.

Secondly you don't need to worry about storing the file size a simple call to the strlen() function will suffice:

PHP Code:

---

header('Content-length: ' . strlen($data)); 


---

Thirdly, when dealing with binary files (this is a must with Windows), open the files in rb and wb mode. This isn't an issue on a UNIX system which treats all files the same, but attempting to open a binary file in text mode on a windows system is likely to screw up the data good and proper:

PHP Code:

---

$data = addslashes(fread(fopen($binary_File, "rb"), filesize($binary_File))); 


---

So, what kind of datatype is large enough to hold my files?

Oh, and about the errors..

I don't know what errors you mean..

This is the entire file.

The MySql maximum column sizes are as follows:

BLOB = 65KB
MEDIUMBLOB = 16.8MB
LONGBLOB = 4.3GB

Quote:

---

Originally posted by visualAd
The MySql maximum column sizes are as follows:

BLOB = 65KB
MEDIUMBLOB = 16.8MB
LONGBLOB = 4.3GB

---

I have a longblob in my db, so that's not the problem.

Could it be that the string $data isn't big enough to hold all the data?

How big are the files you are storing?

Quote:

---

Originally posted by visualAd
How big are the files you are storing?

---

Not that big, I think.

Something between 10 kb and 2 mb...


It still crashes on that query though.. :(

Ok - post your script and Ill have a look at it. I'm confused here becuase you seem to be doing everything right and when I code it myself it works fine :confused:

Quote:

---

Originally posted by visualAd
Ok - post your script and Ill have a look at it. I'm confused here becuase you seem to be doing everything right and when I code it myself it works fine :confused:

---

I attached the script above, and didn't change a thing anymore..

I did change some stuff.

This is what it looks like now..

Sorry, I didn't get a chance to look at it today. I will have a look tommorow.

*bump*

I have had a look at your code. It is quite confusing, you appear to be mixing the use of superglobals i.e. $_POST with automatically registered variables like $binary_file, do these variable come from the submit form or another part of the script. I would suggest you turn off register globals and use the $_POST, $_FILES and $_GET arrays instead.

What you will need to do is find the point at which the script is messing the file up. Instead of ouputing the file after retrieving it from the database, write the data to a file on the server and see if that is corrupt. I suspect that what you may need to do is base64 encode the file before outputting it to the browser.

Below are some suggestions to your original code:

Code:

---

<?php
ob_start();
echo "<html>\n<head>";
?>




<?php
$hide=0;
        if ($_POST['f'] =="newdoc") // using the $_POST superglobals??
        {



                $db=mysql_connect ("localhost", "chieljan_root", "root") or die ('I cannot connect to the database because: ' . mysql_error());
            mysql_select_db ("chieljan_CWN");
       
                //replace the above two string's with your database specific values

        /* where does the $binary_file variable come from ?*/
                if (isset($binary_File) && $binary_File != "none")
                {
                       
                       
                        /* you don't need get_magic_quotes_gpc() */
            /*if (get_magic_quotes_gpc())
                        {
                                $data = fread(fopen($binary_File, "rb"), filesize($binary_File));
                        }else{*/
                       
            $data = addslashes(fread(fopen($binary_File, "rb"), filesize($binary_File)));
                                               
                        //echo $data; /* did this ouput the file as expected? */
           
                        /* mixing super globals with register globals again ???

Not checking postedvariable validitiy - this can be a potiential security problem, putting your script at risk from SQL injections */
            $strDescription = $_POST['beschrijving'];
                          $strCat = $_POST['categories'];       
                          $sql = "INSERT INTO doc_documenten ";
                          $sql .= "(beschrijving, document, categorie, filename, filesize, filetype ) ";
                          $sql .= "VALUES ('$strDescription', '$data', '$strCat',";
                          $sql .= "'$binary_File_name', '$binary_File_size', '$binary_File_type')";
   
                        $result = mysql_query($sql, $db)or die("Query failed during data-addition: " . mysql_error());
                   
                        ?>
                       
                        <meta http-equiv="Refresh" content="4;url=http://www.chieljan.com/conventie/index.php">       
                       
                        <?php
                        $msg = "Hartelijk dank. Het document is toegevoegd aan de website. U word nu teruggestuurd naar de Hoofdpagina." . "<br>" .
                        "<br>" . "Indien u niet word doorgestuurd, " . "<a href='index.php'>" . "klik dan hier" . "</a>" . ".";
                       
                }
                mysql_close();
                $hide="1";
        }
                else{
        $msg = "Via deze pagina kunt u een document toevoegen aan de database." ;
}
       
?>

---

A little bit further down in the script, there is a form.
This holds the binary_File and the textfields.

But I have a new problem:

The script crashes at this point:

PHP Code:

---

if (get_magic_quotes_gpc()) 
            {
                $data = fread(fopen($binary_File, "rb"), filesize($binary_File));
            }else{
                $data = addslashes(fread(fopen($binary_File, "rb"), filesize($binary_File)));
            } 


---

The errors raised are:

Quote:

---

Warning: fopen(test.doc): failed to open stream: No such file or directory in /home/chieljan/public_html/conventie/upload.php on line 25

Warning: filesize(): Stat failed for test.doc (errno=2 - No such file or directory) in /home/chieljan/public_html/conventie/upload.php on line 25

Warning: fread(): supplied argument is not a valid stream resource in /home/chieljan/public_html/conventie/upload.php on line 25

---

(Line 25 is the first statement of the If statement)

If I had the time I would re-write it for you. I know I advised earlier in the post to use get_magic_quotes_gpc(), but this was a mistake on my part, you don't need to use it.

You should always use addslashes() on the the file data. The errors in your previous post are generated becuase PHP cannot find the file you are trying to open.

How can it be possible that I select a file that can't be found?

The input file thingie in the form opens a Open File dialog, and the files I select (I've tried several different ones) all really exist...

:confused: