MSSQL Snippets

more-on-binary.md

Binary (base2), Hexadecimal (base16), and base64

In PHP, say we have the following:

$sampleImageBase64 = 'R0lGODlhGQAZAPAAAO0cJAAAACH5BAAAAAAALAAAAAAZABkAAAIXhI+py+0Po5y02ouz3rz7D4biSJamWAAAOw==';
$sampleImageBinary = base64_decode($sampleImageBase64);
$sampleImageHexadecimal = bin2hex($sampleImageBinary);

We store the binary file in the MSSQL database as follows wherein blob column is of type VARBINARY(MAX) and we pass in insertImage($sampleImageBinary). Note that both methods insertImage() and insertImageAlternate() work. The former instructs the PDO driver to handle the bound paramater as a SQL large object data type with the driver option of PDO::SQLSRV_ENCODING_BINARY which is part of the PDO_SQLSRV driver. The latter treats the passed in variable as a string that is casted to binary by the SQL query - in other words, SQL converts it whereas the PDO driver handled the binary stream in the former method.

From the documentation:

PDO::SQLSRV_ENCODING_BINARY (integer)

Specifies that data is sent/retrieved as a raw byte stream to/from the server without performing encoding or translation. This constant can be passed to PDOStatement::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam.

You can work with a stream resource using any of the stream functions https://www.php.net/manual/en/ref.stream.php, for example stream_get_contents() https://www.php.net/manual/en/function.stream-get-contents.php.

/**
 * @param $blob
 *
 * @return int|void
 */
public function insertImage($blob)
{
    $mssql = $this->dbh->getInstance('LocalMSSQL');
    $mssql->beginTransaction();

    try {

        $stmt = $mssql->prepare("INSERT INTO images (blob) VALUES (?)");
        $stmt->bindParam(1, $blob, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

        $stmt->execute();
        $mssql->commit();

        return $mssql->lastInsertId();

    } catch(PDOException $e) {

        $mssql->rollBack();
        throw new PDOException($e); // bubble exception

    }
}

/**
 * @param $blob
 *
 * @return int|void
 */
public function insertImageAlternate($blob)
{
    $mssql = $this->dbh->getInstance('LocalMSSQL');
    $mssql->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);
    $mssql->beginTransaction();

    try {

        $stmt = $mssql->prepare("

            INSERT INTO images (

                blob

            ) VALUES (

                (CAST(:blob as VARBINARY(MAX)))

            )

        ");

        $stmt->execute([$blob]);
        $mssql->commit();

        return $mssql->lastInsertId();

    } catch(PDOException $e) {

        $mssql->rollBack();
        throw new PDOException($e); // bubble exception

    }
}

MSSQL stores the image as binary in the respective column, but displays is as a hexadecimal value in Microsoft SQL Server Management Studio as well as returns it in hexadecimal format when retreiving the column in PHP.

/**
 * @param $id
 *
 * @return mixed
 */
public function retrieveImage($id)
{
    $stmt = $this->dbh->getInstance('LocalMSSQL')->prepare("SELECT blob FROM images WHERE id = ?");
    $stmt->bindValue(1, $id, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetch(PDO::FETCH_COLUMN);
}

/**
 * @param $id
 *
 * @return mixed
 */
public function retrieveImageAsStream($id)
{
    $conn = $this->dbh->getInstance('LocalMSSQL');
    $stmt = $conn->prepare("SELECT inputType, blob FROM images WHERE id = ?");

    $stmt->bindValue(1, $id, PDO::PARAM_INT);
    $stmt->execute();

    $resultSet = [
        'inputType' => null,
        'blob' => null,
    ];

    $stmt->bindColumn(1, $resultSet['inputType'], PDO::PARAM_STR);
    $stmt->bindColumn(2, $resultSet['blob'], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

    if ($stmt->fetch()) {
        return $resultSet;
    } else {
        return false;
    }
}

For example, the resultset returned from the above query (which contains the base64 image from above) is: 47494638396119001900F00000ED1C2400000021F90400000000002C0000000019001900000217848FA9CBED0FA39CB4DA8BB3DEBCFB0F86E24896A65800003B

You can render this out the client in PHP in two ways:

Method 1

$image = $ImageModel->retrieveImage();

header('Content-type: image/png');
echo hex2bin($image);

Method 2

$image = $ImageModel->retrieveImage();
// convert hexadecimal to binary then base64 encode it
$base64 = base64_encode(hex2bin($image));
echo '<img src="data:image/jpeg;base64,' . $base64 . '">';

See https://blogs.msdn.microsoft.com/brian_swan/2010/04/20/comparing-the-sqlsrv-and-pdo-apis/ for more details.

mssql-snippets.MD

Casting String to Binary

This situation came up when saving a serialized PHP object into a MSSQL database. Inserting the serialized string results in the database cutting off part of the serialized string. As a note, MSSQL does not support UTF-8 which is what the PHP uses as the encoding for the serialized string. Instead, MSSQL uses the encoding type UCS-2LE. For a good read on the difference and history see http://lucumr.pocoo.org/2014/1/9/ucs-vs-utf8/.

Refresher on UTF-8 and character sets

• UTF-8 is an encoding type whilst Unicode is the character set
• UTF-8 is a way of encoding Unicode (other ways are UTF-16, UTF-32, etc.)

As such, there are several options available. The basic method is to serialize the object to string and then save the string as either a base64 value, json encode it or cast it to binary. Should one wish, the serialized object string can be encoded in formats other than these.

MSSQL Data Type	Process
VARCHAR(MAX)	serialize() -> base64 encode
VARCHAR(MAX)	serialize() -> json encode
VARBINARY(MAX)	serialize() -> CAST AS VARBINARY(MAX) in query

Sample Code

-- convert a string to binary then retrieve it, decode it, and output it as string
DECLARE @b VARBINARY(MAX)
SET @b = CAST('This is a test' AS VARBINARY(MAX)) -- string in binary is 0x5468697320697320612074657374

SELECT CAST(@b as VARCHAR(MAX)) -- This is a test
SELECT @b --  0x5468697320697320612074657374


-- inserting (two methods)
INSERT INTO [SomeDatabase].[dbo].[storage] (dataBlob) SELECT CAST('my string' as VARBINARY(MAX))
INSERT INTO [SomeDatabase].[dbo].[storage] (dataBlob) VALUES (CAST('my string' as VARBINARY(MAX)))

Saving the Serialized Object String in VARBINARY() with PHP PDO

public function saveError(Exception $error)
{
    $sth = $this->dbh->prepare("INSERT INTO errorLogs (exception) VALUES (CAST(? as VARBINARY(MAX)))");
    $sth->execute([
        serialize($error)
    ]);
}

Retrieving the VARBINARY() String with PHP PDO

public function retrieveLogErrors()
{
    $sth = $this->dbh->query('SELECT id, CAST(exception AS VARCHAR(MAX)) FROM errorLogs');
    $sth->execute();
    
    return $sth->fetchAll();
}

Alternative Solution

One alternative solution is to convert the PHP serialized object string from UTF-8 to UCS-2LE and then store it in a MSSQL sql varchar(MAX) column.

The PHP code involved would look something like below (courtesy of http://stackoverflow.com/a/13377935/1727232)

// if you know the encoding you are converting from
mb_convert_encoding($serialized, 'UCS-2LE', 'UTF-8');

// if you know don't know the encoding you are converting from
mb_convert_encoding($serialized, 'UCS-2LE', mb_detect_encoding($serialized, mb_detect_order(), true));

reset-id-counter.sql

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO