Inserting binary data into SQL Server with ZF1 & PHP 7

If you want to insert binary data into SQL Server in Zend Framework 1 then you probably used the trick of setting an array as the parameter’s value with the info required by the sqlsrv driver as noted in Some notes on SQL Server blobs with sqlsrv.

Essentially you do this;

$data['filename'] = 'test.gif';
$data["file_contents"] = array(
    $binaryData,
    SQLSRV_PARAM_IN,
    SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), 
    SQLSRV_SQLTYPE_VARBINARY('max')
);
$db->insert($data);

Where $db is an instance of Zend_Db_Adapter_Sqlsrv.

If you use SQL Server with ZF1 and happen to have updated to PHP 7, then you may have found that you get this error:

An invalid PHP type for parameter 2 was specified

(At least, that’s what happened to me!)

Working through the problem, I discovered that this is due to Zend_Db_Statement_Sqlsrv converting the $params array to references with this code:

// make all params passed by reference
$params_ = array();
$temp    = array();
$i       = 1;
foreach ($params as $param) {
    $temp[$i]  = $param;
    $params_[] = &$temp[$i];
    $i++;
}
$params = $params_;

The Sqlsrv driver (v4) for PHP 7 does not like this!

As Zend Framework 1 is EOL, we can’t get a fix into upstream and update the new release, so we have to write our solution.

We want to override Zend_Db_Statement_Sqlsrv::_execute() with our own code. To do this we firstly need to override Zend_Db_Adapter_Sqlsrv. (Also, let’s assume we already have a App directory registered with the autoloader)

Firstly our adapter:

App/Db/Adapter/Sqlsrv.php:

<?php

class App_Db_Adapter_Sqlsrv extends Zend_Db_Adapter_Sqlsrv
{
    protected $_defaultStmtClass = 'App_Db_Statement_Sqlsrv';
}

This class simply changes the default statement class to our new one. Now, we can write our Statement class:

App/Db/Statement/Sqlsrv.php:

<?php

class App_Db_Statement_Sqlsrv extends Zend_Db_Statement_Sqlsrv
{
    /**
     * Executes a prepared statement.
     *
     * @param array $params OPTIONAL Values to bind to parameter placeholders.
     * @return bool
     * @throws Zend_Db_Statement_Exception
     */
    public function _execute(array $params = null)
    {
        $connection = $this->_adapter->getConnection();
        if (!$this->_stmt) {
            return false;
        }

        if ($params !== null) {
            if (!is_array($params)) {
                $params = array($params);
            }

            // make all OUT or INOUT params passed by reference
            $params_ = array();
            $temp    = array();
            $i       = 1;
            foreach ($params as $param) {
                $temp[$i]  = $param;
                if (is_array($param) && in_array($param[1], [SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT])) {
                    $params_[] = &$temp[$i];
                } else {
                    $params_[] = $temp[$i];
                }
                $i++;
            }
            $params = $params_;
        }

        $this->_stmt = sqlsrv_query($connection, $this->_originalSQL, $params);

        if (!$this->_stmt) {
            require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
            throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
        }

        $this->_executed = true;

        return (!$this->_stmt);
    }
}

This class, takes the _execute() method from Zend_Db_Statement_Sqlsrv and makes the necessary changes the section that creates parameter references. Specifically, we only create a reference if the parameter has a direction of SQLSRV_PARAM_OUT or SQLSRV_PARAM_INOUT:

// make all OUT or INOUT params passed by reference
$params_ = array();
$temp    = array();
$i       = 1;
foreach ($params as $param) {
    $temp[$i]  = $param;
    if (is_array($param) && in_array($param[1], [SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT])) {
        $params_[] = &$temp[$i];
    } else {
        $params_[] = $temp[$i];
    }
    $i++;
}
$params = $params_;

Finally, we need to register our new adapter with Zend_Application’s Database resource. This is done in the config file:

application/configs/application.ini:

resources.db.params.adapterNamespace = "App_Db_Adapter"

That’s it.

We can now insert binary data into our SQL Server database from PHP 7 using the latest sqlsrv drivers.