PHP- inserting binary data in mysql using prepared statements

Question

I have to insert a row using php's mysql improved library to a table in mysql that has its primary key of type VARBINARY. The contents of this field are a computed sha1 hash.

If I run the query the old way it works perfectly:

$mysqli->$query("INSERT INTO table (id, field1) VALUES (0x" . $id . ",'" . $field1 . "')");

But when I try to execute it as a prepared statement, I can't figure out how to do it. If I perform the equivalent action:

if($stmt = $mysqli->prepare("INSERT INTO table (id, field1) VALUES (?, ?)")) {
    $stmt->bind_param('ss', "0x".$id, $field1);
    //execute statement
}

It throws an exception saying that the contents were too large for this field. And If I try to insert it as a BLOB field:

if($stmt = $mysqli->prepare("INSERT INTO table (id, field1) VALUES (?, ?)")) {
    $stmt->bind_param('bs', $id, $field1);
    //execute statement
}

It gives no error, the row is inserted, but the identifier field now is empty (not null, empty).

I know I can mix the query and input the id concatenated in the string and the other fields as bind parameters of the prepared statement, but i'm asking just to know what is the correct way to insert this and perhaps it will help somebody in the future.

Answer 1

PHP's sha1 function returns a string representation of a hex number.

What that means is that if you print it to screen, it'll display a hex number. But in memory, it is an bunch of ASCII characters.

So, take the hex number 1A2F. As ASCII in memory that would be 0x31413246, instead of 0x1A2F

MySQL's normal interface sends all arguments as strings. When using the normal interface, MySQL will convert the ASCII string to a binary value.

The new prepared statement method sends everything as binary. So your nice value of "1A2F" will now be sent as 0x31413246 and inserted into the column. - source: dev.mysql.com - Prepared statements

Instead, convert your Hex string by packing it into a binary string using:

$binId = pack("H*", $id); // this string is not ASCII, don't print it to the screen! That will be ugly.

and then pass $binId to the MySQLi prepared statement instead of $id.

Answer 2

try this instead:

if($stmt = $mysqli->prepare("INSERT INTO table (id, field1) VALUES (unhex(?), ?)") {
    $stmt->bind_param('ss', $id, $field1);
    //execute statement
}