Unable to get varbinary data from MSSQL

Question

Background

Development environment:

PHP 7.0.3 with Apache 2.4.16 for Windows 10 x64

SQL Server 2014 Standard

The section has a FileStream for the corresponding columns of the file.

I tried installing the sqlsvr driver, but failed due to lack of support for PHP7

Access SQL Server from ODBC Using the SQL Server Driver

PHP code to insert image data in 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 was successfully uploaded to the SQL server. Opening the DATA SQL Server directory, all downloaded files and it can be opened with Paint.

But extracting the file from the above image save code is corrupt. The file is missing some bytes from the original.

I tried the method

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

as well as the method specified in this link.

Php with MSSQL displays raw data from varbinary field

But both were unlucky, the file also seemed to be corrupted.

Any help is appreciated.

Change 2016-02-25

The SQL statement has been changed as follows, but the output file is still corrupt.

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

Now I'm trying to change another ODBC SQL server driver to check if the file can be successfully output.

Change 2016-08-09

With the updated MSSQL PDU driver for PHP 7. Everything worked again with charm.

Now we no longer need to convert varbinary data to use this new driver.

Link Link

Answer 1

When pasted into tables contain varbinary, data type selection is very important for PHP PDO.

PDO::PARAM_LOB should be used instead of PDO::PARAM_STR for file columns

To make sure that DB displays the correct file through the driver, convert the VARBINARY(MAX) field to a hexadecimal string and pass it to PHP using this CONVERT(VARCHAR(MAX),[FileColumn],2) method CONVERT(VARCHAR(MAX),[FileColumn],2)

Converting a column to VARCHAR(MAX) rather than NVARCHAR(MAX) , because HEX rows do not need Unicode support and leave a simple HEX row.

Hexadecimal strings can then be effectively converted from hexadecimal data to binary through the PHP hex2bin() function.

Decision

 $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 you select a varbinary column from SQL Server Native Client 11.0 and ODBC Driver 11 for SQL Server , without converting the column to varchar or not, PHP produces a corrupted binary result that is undesirable. But the SQL Server driver successfully returns a HEX string to me when I convert a column to VARCHAR(MAX) . I suspect this action is a bug or driver problem.

Answer 2

If you store binary data, do not convert it to NVARCHAR () when you select it - try removing 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] = ?"); 

:

 $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 the column is VARBINARY(MAX) .

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

Answer 3

• Install MSSQL Server

• Install the ODBC driver according to OS version and SQL Server version e.g. 64-bit win 10 and SQL server 12 odbc 11 driver required

  Download from this link

• pdo_sqlsrv_.dll download the files according to the php version and paste them into the php / ext directory and open the php.ini extension part. for example, in my case, install xampp 3.2.2 version, which includes php 7.08, for this I download pdo_sqlsrv_7_ts.dll and install in accordance with the above mentioned in paragraph 3.

  Download from this link:

• Set the database connection parameter in a PHP file, for example

  $ conn = new PDO ("sqlsrv: server = 127.0.0.1,1433; Database = test", "," "); $ Conn-> SetAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);