1
$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.

Nachan
  • 49
  • 8
  • How is `$data1['hex']` populated in your working example? What's the type of the column `data` in your DB? Might be worth the try to add `PDO::PARAM_LOB` as third argument to `bindparam` if it's a BLOB column. – stef77 Aug 01 '15 at 06:24
  • I am storing the data in varbinary(max) field in the db. I have tried with the third argument also and its not working. Thanks anyway. – Nachan Aug 01 '15 at 06:34
  • Well, then, any difference with `PDO::PARAM_LOB`? What about my first question? – stef77 Aug 01 '15 at 06:36
  • Its the same as there `file_get_contents()`. I forgot to mention that. – Nachan Aug 01 '15 at 06:38

1 Answers1

2

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.

stef77
  • 1,000
  • 5
  • 19