$stmt=$con->query("insert into tbl(data) values(0x".$data1['hex'].")");
This is the sql statement and its works just fine. The value
0xFFD8FFE000104A46494600010101006000600000FFDB00430...
gets stored on the database and i have checked, the image gets stored. But i am trying to do this using PDO and the stored value is different and does not show the image. Here is my code
$datastring = file_get_contents("image.JPG");
$data1 = unpack("H*hex", $datastring);
$data = '0x'.$data1['hex'];
$stmt=$conp->prepare("insert into tbl(data) values(:data)");
$stmt->bindparam(':data', $data);
$stmt->execute();
The value in database
0x30786666643866666530303031303461343634393436303...
What is making the difference? Am I doing something wrong with it? I am using SQL Server 2008R2 with Microsoft pdo_odbc driver on php 5.6.
First Google hit for mssql varbinary pdo: https://social.msdn.microsoft.com/forums/sqlserver/en-US/221dcea2-438d-4a3a-b438-b98bff8f3d57/using-pdostatementbindvalue-to-update-varbinary-fields
$sth->bindParam(1, $pswd, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
Therefore, $stmt->bindParam(':data', $data, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY); should work?
Edit: Aaaaah, stupid me: of course this doesn't work. You're not passing binary, your string is just an arbitrary ASCII string which happens to look like something hex encoded. Go to a site like this: http://www.rapidtables.com/convert/number/hex-to-ascii.htm, paste your 0x30786666643866666530303031303461343634393436303, convert to ASCII, and what do you get? 0xffd8ffe000104a4649460, your original data.
What happens is that PDO/MSSQL thinks you're passing binary data which it converts to hex, so 0 ASCII is 30 hex, x is 78, f is 66 and so on, I hope you get the idea.
The difference to your first, working example is subtle: You don't put quotes around the value passed (0x...), hence, it's treated as "true binary" in the form of hex. In your PDO approach, the value passed is, so-to-say, internally "quoted" by PDO, e.g. to prevent SQL injection attacks. If you were to put quotes around your first example, you should get the same results as with PDO.
What's to do? Simply forget about the hex encoding and let the odbc driver / MSSQL handle conversions. Pass $datastring instead of $data and you should be fine.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments