Update blob in MySQL using PHP

I can't figure out what is wrong with my SQL. Here is the PHP script that generates the query:

function SaveData($data,$id,$file)
{
    $handle = fopen($file['file']['tmp_name'], 'r');
    $datafile = fread($handle, filesize($file['file']['tmp_name']));
    $datafile = mysql_real_escape_string($datafile);
    fclose($handle);        
    $query= "UPDATE data SET Text='" . $data['Text'] . "', Binary='$datafile', Tag='" . $data['Tag'] . "', name='" . $file['file']['name'] . "',type='" . $file['file']['type'] . "', size='" . $file['file']['size'] . "' WHERE Object_ID=".$id;

    mysql_query($query,$this->connection) or die(mysql_error());
}

I开发者_C百科f get the following error:

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 'Binary='%PDF-1.5\r%âãÏÓ\r\n37 0 obj\r<

Can anyone give me some pointers?

---

BINARY is a reserved word in mySQL.

You need to use backticks or change the column name.

`Binary` = '$datafile'

---

Binary is the reserved word

Try to enclose it in back quotes

`Binary`='$datafile', 

Here is the list of keywords Mysql Keywords

---

In addition to the syntax error pointed out by the other answers, you can't just shove a variable with binary data into an SQL query like that. You'll have to convert it to a hex string first.

function SaveData($data,$id,$file)
{
    $handle = fopen($file['file']['tmp_name'], 'rb'); // note the b here
    $datafile = fread($handle, filesize($file['file']['tmp_name']));
    $contents = bin2hex(($datafile)); // no need to escape this
    fclose($handle);        
    $query= "UPDATE data SET Text='" . $data['Text'] . "', `Binary`=x'$contents'; 
    // rest of the code trimmed
}

Also note that your field must be a BLOB to accept binary data.