[Linux] Fetching VARBINARY value with pdo_sqlsrv does not work

[deeky666]

I am not quite sure if this is a bug or I just cannot figure out how to do this. Fetching a value from a VARBINARY column does not return the same value that was inserted before when using pdo_sqlsrv. Expected result would be the binary representation that was inserted into the database but instead the hexadecimal representation is returned.

Test

$sql = "CREATE TABLE foo (icon VARBINARY(MAX))";
$conn->exec($sql);

$icon = base64_decode(<<<EOF
H4sICJRACVgCA2RvY3RyaW5lLmljbwDtVNtLFHEU/ia1i9fVzVWxvJSrZmoXS6pd0zK7QhdNc03z
lrpppq1pWqJCFERZkUFEDybYBQqJhB6iUOqhh+whgl4qkF6MfGh+s87O7GVmO6OlBfUfdIZvznxn
fpzznW9gAI4unQ50XwirH2AAkEygEuIwU58ODnPBzXGv14sEq4BrwzKKL4sY++SGTz6PodcutN5x
IPvsFCa+K9CXMfS/cOL5OxesN0Wceygho0WAXVLwcUJBdDVDaqOAij4Rrz640XlXQmAxQ16PHU63
iqdvXbg4JOHLpILBUSdM7XZEVDDcfuZEbI2ASaYguUGAroSh97GMngcSeFFFerMdI+/dyGy1o+GW
Ax5FxfAbFwoviajuc+DCIwn+RTwGRmRIThXxdQJyu+z4/NUDYz2DKCsILuERWsoQfoQhqpLhyhMZ
XfcknBmU0NLvQArpTm0SsI5mqKqKuFoGc8cUcjrtqLohom1AgtujQnapmJJU+BbwCLIwhJXyiKlh
MB4TkFgvIK3JjrRmAefJm+77Eiqvi+SvCq/qJahQyWuVuEpcIa7QLh7Kbsourb9b66/pZdAd1voz
fCNfwsp46OnZQPojSX9UFcNy+mYJNDeJPHtJfqeR/nSaPTzmwlXar5dQ1adpd+B//I9/hi0xuCPQ
Nkvb5um37Wtc+auQXZsVxEVYD5hnCilxTaYYjsuxLlsxXUitzd2hs3GWHLM5UOM7Fy8t3xiat4fb
sneNxmNb/POO1pRXc7vnF2nc13Rq0cFWiyXkuHmzxuOtzUYfC7fEmK/3mx4QZd5u4E7XJWz6+dey
Za4tXHUiPyB8Vm781oaT+3fN6Y/eUFDfPkcNWetNxb+tlxEZsPqPdZMOzS4rxwJ8CDC+ABj1+Tu0
d+N0hqezcjblboJ3Bj8ARJilHX4FAAA=
EOF
);

// Insert data using bind parameters
$sql = "INSERT INTO foo VALUES (?)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $icon, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY);
$stmt->execute();

// Query, fetch
$sql = "SELECT icon from foo";
$stmt = $conn->query($sql);

echo $stmt->fetchColumn();

Output

1F8B0808944009580203646F637472696E652E69636F00ED54DB4B147114FE26B58BD7D5CD55B1BC94AB666A174BAA5DD332BB42174D734DF396BA69A6AD695AA2421444599141440F26D8050A89841EA250EAA187EC21825E2A905E8C7C687EB3CECEEC65663BA3A505F51F74866FCE7C677E9CF39D6F60008E2E9D0E745F08AB1F6000904CA012E230539F0E0E73C1CD71AFD78B04AB806BC3328A2F8B18FBE4864F3E8FA1D72EB4DE7120FBEC1426BE2BD09731F4BF70E2F93B17AC37459C7B2821A345805D52F07142417435436AA3808A3E11AF3EB8D17957426031435E8F1D4EB78AA76F5DB83824E1CBA482C151274CED76445430DC7EE6446C8D8049A620B94180AE84A1F7B18C9E07127851457AB31D23EFDDC86CB5A3E196031E45C5F01B170A2F89A8EE73E0C22309FE453C064664484E15F1750272BBECF8FCD503633D83282B082EE1115ACA107E8421AA92E1CA13195DF7249C1994D0D2EF400AE94E6D12B08E66A8AA8AB85A0673C714723AEDA8BA21A26D4082DBA34276A9989254F816F008B2308495F288A961301E1390582F20ADC98EB46601E7C99BEEFB122AAF8BE4AF0AAFEA25A850C96B95B84A5C21AED02E1ECA6ECA2EADBF5BEBAFE965D01DD6FA337C235FC2CA78E8E9D940FA23497F5415C372FA66093437893C7B497EA791FE749A3D3CE6C255DAAF9750D5A76977E07FFC8F7F862D31B823D0364BDBE6E9B7ED6B5CF9AB905D9B15C445580F98670A29714DA6188ECBB12E5B315D48ADCDDDA1B371961CB33950E33B172F2DDF189AB787DBB2778DC6635BFCF38ED6945773BBE71769DCD7746AD1C1568B25E4B879B3C6E3ADCD461F0BB7C498AFF79B1E1065DE6EE04ED7256CFAF9D7B265AE2D5C75223F207C566EFCD68693FB77CDE98FDE5050DF3E470D59EB4DC5BFAD971119B0FA8F75930ECD2E2BC7027C0830BE0018F5F93BB477E37486A7B37236E56E8277063F004498A51D7E050000

I wonder if that is expected behaviour because it does work properly with other PDO drivers and also sqlsrv.

[Hadis-Knj]

@deeky666 Could you send the sqlsrv script that reproduces this issue?

[Hadis-Knj]

You should set the PDO::SQLSRV_ENCODING_BINARY for fetch statement to retrieve the decoded binary data, so if you modify the code as below you'll get the data as inserted

 // Query, fetch
 $sql = "SELECT icon from foo";
 $stmt = $conn->query($sql);
 $stmt->setAttribute(constant('PDO::SQLSRV_ATTR_ENCODING'), PDO::SQLSRV_ENCODING_BINARY);
 echo base64_encode($stmt->fetchColumn());

[deeky666]

@Hadis-Fard here is my script for sqlsrv:

$connectionInfo = array(
    'UID' => 'sa',
    'PWD' => 'D0ctrine',
);

$conn = sqlsrv_connect('mssql, 1433', $connectionInfo);

$query = "DROP TABLE IF EXISTS foo";
sqlsrv_query($conn, $query);

$query = "CREATE TABLE foo (icon VARBINARY(MAX))";
sqlsrv_query($conn, $query);

$icon = base64_decode(<<<EOF
H4sICJRACVgCA2RvY3RyaW5lLmljbwDtVNtLFHEU/ia1i9fVzVWxvJSrZmoXS6pd0zK7QhdNc03z
lrpppq1pWqJCFERZkUFEDybYBQqJhB6iUOqhh+whgl4qkF6MfGh+s87O7GVmO6OlBfUfdIZvznxn
fpzznW9gAI4unQ50XwirH2AAkEygEuIwU58ODnPBzXGv14sEq4BrwzKKL4sY++SGTz6PodcutN5x
IPvsFCa+K9CXMfS/cOL5OxesN0Wceygho0WAXVLwcUJBdDVDaqOAij4Rrz640XlXQmAxQ16PHU63
iqdvXbg4JOHLpILBUSdM7XZEVDDcfuZEbI2ASaYguUGAroSh97GMngcSeFFFerMdI+/dyGy1o+GW
Ax5FxfAbFwoviajuc+DCIwn+RTwGRmRIThXxdQJyu+z4/NUDYz2DKCsILuERWsoQfoQhqpLhyhMZ
XfcknBmU0NLvQArpTm0SsI5mqKqKuFoGc8cUcjrtqLohom1AgtujQnapmJJU+BbwCLIwhJXyiKlh
MB4TkFgvIK3JjrRmAefJm+77Eiqvi+SvCq/qJahQyWuVuEpcIa7QLh7Kbsourb9b66/pZdAd1voz
fCNfwsp46OnZQPojSX9UFcNy+mYJNDeJPHtJfqeR/nSaPTzmwlXar5dQ1adpd+B//I9/hi0xuCPQ
Nkvb5um37Wtc+auQXZsVxEVYD5hnCilxTaYYjsuxLlsxXUitzd2hs3GWHLM5UOM7Fy8t3xiat4fb
sneNxmNb/POO1pRXc7vnF2nc13Rq0cFWiyXkuHmzxuOtzUYfC7fEmK/3mx4QZd5u4E7XJWz6+dey
Za4tXHUiPyB8Vm781oaT+3fN6Y/eUFDfPkcNWetNxb+tlxEZsPqPdZMOzS4rxwJ8CDC+ABj1+Tu0
d+N0hqezcjblboJ3Bj8ARJilHX4FAAA=
EOF
);

$query = "INSERT INTO foo VALUES (?)";
$params = array(
    array(
        &$icon,
        SQLSRV_PARAM_IN,
        SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
        SQLSRV_SQLTYPE_VARBINARY('max')
    )
);
$stmt = sqlsrv_prepare($conn, $query, $params);
sqlsrv_execute($stmt);

$query = "SELECT icon from foo";
$stmt = sqlsrv_query($conn, $query);
$result = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);

var_dump($result[0] === $icon);

I do not understand why I have to specify PDO::SQLSRV_ENCODING_BINARY on the statement when the driver is inspecting the column type on fetch anyways and should be able to infer the correct encoding and type. Using sqlsrv proofs that it should be possible. Also specifying PDO::SQLSRV_ENCODING_BINARY at statement level is not an option because not all of the columns potentially have the same enconding while fetching.

[ulvii]

Hi @deeky666 ,

Could you confirm if this issue has been resolved with the latest release? https://github.com/Microsoft/msphpsql/releases/tag/v4.1.8-preview
Thanks

[morozov]

@ulvii the issue is not reproducible on v4.3.0. Thank you.