Insert image into sql server db with pdo

Question

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

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
– stef77, Commented Aug 1, 2015 at 6: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
– Nachan, Commented Aug 1, 2015 at 6:34
Well, then, any difference with PDO::PARAM_LOB? What about my first question? — stef77
– stef77, Commented Aug 1, 2015 at 6:36
Its the same as there file_get_contents(). I forgot to mention that. — Nachan
– Nachan, Commented Aug 1, 2015 at 6:38

stef77 · Accepted Answer · 2015-08-01 08:34:02Z

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.

edited Aug 1, 2015 at 8:34

answered Aug 1, 2015 at 6:49

stef77

1,0005 silver badges19 bronze badges

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

2 Comments

Nachan Over a year ago

Sorry stef77 it is stil the same. The same value is stored in db. No image.

Benyamin Limanto Over a year ago

related to stackoverflow.com/questions/18944368/…

Collectives™ on Stack Overflow

Insert image into sql server db with pdo

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related