Cannot fetch varbinary data from MSSQL

Question

Background

Development environment:

PHP 7.0.3 with Apache 2.4.16 on Windows 10 x64

SQL Server 2014 Standard

The sever has FileStream enabled on corresponding file columns.

Tried to install sqlsvr driver but failed due to lack of support to PHP7

Access SQL server from ODBC using driver SQL Server

PHP Code to insert image data into MSSQL

$link = @new \PDO("odbc:Driver={SQL Server};Server=$server;Database=$db", $user, $password);
$stmt = $link->prepare("INSERT INTO [Attachment] (AttID, Seq , ModuleCde, AppID, StaffID , FileName , [File]) VALUES ( NEWID() , ? , ? , ? , ? , ? , ? )");
$stmt->bindValue(1,$_POST["Seq"],PDO::PARAM_INT);
$stmt->bindValue(2,$_POST["ModuleCde"],PDO::PARAM_STR);
$stmt->bindValue(3,$_POST["AppID"],PDO::PARAM_STR);
$stmt->bindValue(4,$_SESSION["StaffID"],PDO::PARAM_STR);
$stmt->bindValue(5,$_FILES["file"]["name"][$_POST["Seq"]],PDO::PARAM_STR);
$stmt->bindValue(6,file_get_contents($_FILES["file"]["tmp_name"][$_POST["Seq"]]),PDO::PARAM_STR);
$stmt->execute();

PHP Code to save image data from MSSQL

$link = @new \PDO("odbc:Driver={SQL Server};Server=$server;Database=$db", $user, $password);
$stmt = $link->prepare("SELECT DATALENGTH([File]) AS [Size] , CONVERT(NVARCHAR(MAX),[File],2) AS [File] FROM [Attachment] WHERE [ModuleCde] = ? AND [AppID] = ? AND [Seq] = ? AND [StaffID] = ?");
$stmt->bindValue(1,$_GET["ModuleCde"],PDO::PARAM_STR);
$stmt->bindValue(2,$_GET["AppID"],PDO::PARAM_STR);
$stmt->bindValue(3,$_GET["Seq"],PDO::PARAM_STR);
$stmt->bindValue(4,$_SESSION["StaffID"],PDO::PARAM_STR);
$stmt->execute();
$stmt->bindColumn(2,$img,PDO::PARAM_LOB, 0);
$stmt->fetch(PDO::FETCH_ASSOC);
file_put_contents( "file" , $img );

The file is successfully uploaded to the SQL sever. Opening the DATA directory of the SQL Server, all of the uploaded files and can be opened up by Paint.

But the file fetch from the above image saving code is corrupted. The file is missing some bytes from the original.

Method tried

fwrite( fopen("file","w+") , strtolower("0x".str_replace("\0","",$img)) );

and also the method stated in this link.

Php with MSSQL display raw data from varbinary field

But both have no luck, the file also seemed to be corrupted.

Any help is appreciated.

Edit 2016-02-25

Modified the SQL statement as the following but the output file is still corrupted.

 $link->prepare("SELECT DATALENGTH([File]) AS [Size] , [File] FROM [Attachment] WHERE [ModuleCde] = ? AND [AppID] = ? AND [Seq] = ? AND [StaffID] = ?");

I am now trying to change different SQL server ODBC driver to see if the file can output successfully.

Edit 2016-08-09

With the updated MSSQL PDO Driver for PHP 7. Everything worked again with a charm.

Now, we don't need to convert the varbinary data anymore using this new driver.

Reference Link

Answer 1

When inserting into tables contain varbinary, choose of data type is very important to PHP PDO.

PDO::PARAM_LOB shall be used instead using PDO::PARAM_STR for file columns

To make sure DB has output the correct file through the driver, convert the VARBINARY(MAX) field into hex string and hand it over to PHP normally using this method CONVERT(VARCHAR(MAX),[FileColumn],2)

Converting the column to VARCHAR(MAX) rather than NVARCHAR(MAX) because HEX strings do not need any support to Unicode and leave a plain HEX string.

Then hex strings can be convert back from hex data to binary through PHP function hex2bin() efficiently.

The Solution

$link = @new \PDO("odbc:Driver={SQL Server};Server=$server;Database=$db", $user, $password);
$stmt = $link->prepare("SELECT DATALENGTH([File]) AS [Size] , CONVERT(VARCHAR(MAX),[File],2) AS [File] FROM [Attachment] WHERE [ModuleCde] = ? AND [AppID] = ? AND [Seq] = ? AND [StaffID] = ?");
$stmt->bindValue(1,$_GET["ModuleCde"],PDO::PARAM_STR);
$stmt->bindValue(2,$_GET["AppID"],PDO::PARAM_STR);
$stmt->bindValue(3,$_GET["Seq"],PDO::PARAM_STR);
$stmt->bindValue(4,$_SESSION["StaffID"],PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
file_put_contents( "file" , hex2bin($result["File"]) );

Additional Information

When selecting varbinary column from SQL Server Native Client 11.0 and ODBC Driver 11 for SQL Server, neither converting the column to varchar or not, PHP outputs a corrupted binary result which is unwanted. But driver SQL Server successfully return a HEX string to me when I am converting the column to VARCHAR(MAX). I suspect this action is a bug or a driver issue.

Answer 2

If you're storing binary data don't convert it to NVARCHAR() when selecting it -- try removing the CONVERT() from this:

$stmt = $link->prepare("SELECT DATALENGTH([File]) AS [Size] , CONVERT(NVARCHAR(MAX),[File],2) AS [File] FROM [Attachment] WHERE [ModuleCde] = ? AND [AppID] = ? AND [Seq] = ? AND [StaffID] = ?");

be this:

$stmt = $link->prepare("SELECT DATALENGTH([File]) AS [Size], [File] FROM [Attachment] WHERE [ModuleCde] = ? AND [AppID] = ? AND [Seq] = ? AND [StaffID] = ?");

You also want to make sure you column is VARBINARY(MAX).

When inserting your data I think you need to use SQLSRV_ENCODING_BINARY (instead of PDO::PARAM_STR)