更新后PHP PDO和大对象（LOB）中断

Question

A few months ago, my Ubuntu package auto-updated PHP from 7.0.8 to 7.0.13, at which point my script for updating photos stored on a SQL database started failing. I got around this by reinstalling 7.0.8. Last month, I was again auto-updated to 7.0.15 and my script failed again.

My script writes a jpg image to a MS-SQL database, using PDO & FreeTDS, plus Large Objects (LOB) to handle the photo. I emphasise that it worked up to PHP version 7.0.8. The following is a test script isolating my problem.

<?php

$dsn = 'dblib:dbname=photos;host=gary';
$id = 693925;

$dbh = new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {    
       $photo = file_get_contents("coco.jpg");
       $query = "UPDATE photo_table SET photo = :photo WHERE id = :id";
       $stmt = $dbh->prepare($query);
       $stmt->bindValue(":photo", $photo, PDO::PARAM_LOB);
       $stmt->bindValue(":id", $id, PDO::PARAM_INT);
       $stmt->execute();
    }
}
catch (PDOException $e) {
    echo $e->getMessage();
}

The result is an "Incorrect syntax" error!?

SQLSTATE[HY000]: General error: 
102 Incorrect syntax near '����'.[102] (severity 15) [(null)]

Using the latest available PHP version 7.0.15, reading from the database works, including reading the photo as a Large Object. There is no problem writing every other field to the database, it only fails on my image.

Despite searching over the last few weeks, I still have to find someone else reporting the same problem.

I am after any advice, either a change to the code, or some configuration settings to allow LOBs to work again.

Answer 1

My solution/workaround was to convert the binary from the image into hexadecimal representation before sending the data to SQL.

$photo = bin2hex(file_get_contents("coco.jpg"));

converting it back again during the SQL statement.

$query = 
"UPDATE photo_table SET photo=CONVERT(varbinary(max), :photo, 2) WHERE id = :id";