Update blob in MySQL using PHP

Question

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());
}

If 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?

Pekka · Accepted Answer · 2011-03-03 15:59:46Z

1

BINARY is a reserved word in mySQL.

You need to use backticks or change the column name.

`Binary` = '$datafile'

answered Mar 3, 2011 at 15:59

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

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

Comments

Shakti Singh · Accepted Answer · 2011-03-03 16:01:11Z

0

Binary is the reserved word

Try to enclose it in back quotes

`Binary`='$datafile',

Here is the list of keywords Mysql Keywords

answered Mar 3, 2011 at 16:01

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Comments

NullUserException · Accepted Answer · 2011-03-03 16:05:48Z

0

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.

answered Mar 3, 2011 at 16:05

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

Collectives™ on Stack Overflow

Update blob in MySQL using PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related