Sqlsrv driver - binary data encoding

[jaroslavrehorka]

• bug report? yes
• feature request? no
• version: 3.2.0

Description

When inserting / updating a binary data to the MSSQL database, the data (output of file_get_contents) cannot be simply inserted as string value, because it rises an encoding issue (Dibi\DriverException An error occurred translating the query string to UTF-16: No mapping for the Unicode character exists in the target multi-byte code page.).

Instead of that, the binary data have to be passed as an parameter to sqlsrv_query (or sqlsrv_prepare) in following format:

array($binaryData, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'));

More informations can be found here https://akrabat.com/some-notes-on-sql-server-blobs-with-sqlsrv/

Steps To Reproduce

Let's say we have following table:

CREATE TABLE dbo.SampleTable (     
	id INTEGER PRIMARY KEY
	imageFile IMAGE NULL
)

Following code should properly create new record, with binary data

$filePath = 'image.jpg';
$data['imageFile'] = file_get_contents($filePath);
$mssqlConnection->insert('SampleTable', $data)->execute()

But the driver returns the mentioned error.

Now I have to use following workaround (I want to use connection from dibi, as well as generated sql query):

$filePath = 'image.jpg';
$binaryData = file_get_contents($filePath);

$data['imageFile'] = '(?)';
$sqlSrvFile = array($binaryData, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'));

$sqlQuery = $mssqlConnection->insert('SampleTable', $data)->__toString();
$sqlsrvConnection = $mssqlConnection->getDriver()->getResource();

$res = sqlsrv_query($sqlsrvConnection, $sqlQuery, array($sqlSrvFile));

[dg]

It requires complex architecture changes, so it may will appear in Dibi 5.

[KarelHrkal]

I just had the same problem when inserting binary data, but the solution can be very easy - just fix the '%bin' type for mssql.
In SqlsrvDriver.php change:

public function escapeBinary($value)
{
    return "'" . str_replace("'", "''", $value) . "'";
}

to:

public function escapeBinary($value)
{
    return '0x' . bin2hex($value);
}

Than way, the binary data will be correctly formated as 0xaf05e34c for example. Then, to insert data, you can specify how your data shlould be encoded:
$data['imageFile%bin'] = file_get_contents($filePath);
But if you don't want to wait for the fixed escapeBinary function, you can also do:
$data['imageFile%ex'] = '0x' . bin2hex(file_get_contents($filePath));

[milo]

This is actually good idea. I verified (on a SQLServer 14 Cluster), that SQLServer types binary, varbinary and image can be interpreted as a hexadecimal value so proposed solution works.

[milo]

https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver15

Btw. image type is deprecated in favour of varbinary.

[dg]

Fixed