Sep 06 2007

Zend_Auth bug with MS SQL

Tag: PHP, Zend Framework — Grantus Maximus @ 3:11 pm

Currently Zend_Auth won’t work if you’re using a Micrsoft SQL Server database for storing your account credentials.

This is because of a bug in the \Zend\Auth\Adapter\DbTable.php specifically in the authenticate() function. The SQL Statement it generates is not MS SQL friendly:

SELECT "users".*, "credential" = 'mypass' AS zend_auth_credential_match
FROM "users"
WHERE ("identity" = 'me')

Consequently causing the following error:

Incorrect syntax near the keyword 'AS'.

The good news is the code below can be used as a replacement in this function until the Zend Framework team get a chance to fix it themselves. It has been tested in MS SQL 2005 but I imagine it should work well in another DB (but test this yourself and comment back!).

// build credential expression
if (empty($this->_credentialTreatment) || (strpos($this->_credentialTreatment, "?") === false)) {
    $this->_credentialTreatment = '?';
}
 
$credentialExpression = new Zend_Db_Expr(
    $this->_zendDb->quoteInto('(CASE WHEN '
        . $this->_zendDb->quoteIdentifier($this->_credentialColumn)
        . '=' . $this->_credentialTreatment, $this->_credential)
        . ' THEN 1 ELSE 0 END) '
        . ' AS ' . $this->_zendDb->quoteIdentifier('zend_auth_credential_match'));
 
// get select
$dbSelect = $this->_zendDb->select();
$dbSelect->from($this->_tableName, array('*', $credentialExpression))
         ->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $this->_identity);

The code above generates the following MS SQL friendly SQL statement:

SELECT "users".*, CASE WHEN "credential" = 'mypass' THEN 1 ELSE 0 END AS zend_auth_credential_match
FROM "users"
WHERE ("identity" = 'me')

There is an issue open with the team if you’re interested in reading it (and please vote for it to be resolved).