Zend DbTable case insensitive

Question

I have a login system for my webapp that works well using the Zend auth adapter but the problem is I want the email to be case insensitive when a user logs in. I am using Oracle as the back end DB and normally I would user the LOWER(EMAIL)=LOWER(:email) method. I tried to pass that Oracle function in the setIdentityColumn() but I get the error:

The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity.

protected function _getAuthAdapter()
{
    //$dbAdapter = Zend_Db_Table::getDefaultAdapter();
    $db = Zend_Registry::get('db');
    $authAdapter = new Zend_Auth_Adapter_DbTable($db);
    $authAdapter->setTableName('USER_TABLE')
        ->setIdentityColumn('LOWER(EMAIL)') //Tried to pass LOWER()
        ->setCredentialColumn('ENCODED_PW')
        ->setCredentialColumn('PASSWORD');
    return $authAdapter;
}

The error is coming from the function _authenticateCreateSelect() in the Zend_Auth_Adapter_DbTable class. The problem is this part of the script:

$dbSelect->from($this->_tableName, array('*', $credentialExpression))
             ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . '   = ?', $this->_identity);

The quoteIdentifier() method is like PHP quote() and is turning a query like this:

select * from LOWER(:email)

into this:

select * from "LOWER(:email)"

Anyone see a way around this?

Kind Regards Nathan

Answer 1

Try something like this:

$authAdapter->setTableName('USER_TABLE')
            ->setIdentityColumn(new Zend_Db_Expr('LOWER(USERID)'))
             ->setCredentialColumn('PASSWORD');

The problem is that if you pass 'LOWER(USERID)' as a simple string, Zend will put quotes around it, causing it to create an invalid query. Using Zend_Db_Expr will stop Zend doing this.