The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement

I have the following exception Caught exception: The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity. I have googled and checked my code over and over again but I have not found a solution. The table and column names are all correct.

The section of code that is causing this problem is $result = $auth->authenticate($authAdapter);. Infact the whole controller code is found below:

class AuthenticationController extends Zend_Controller_Action
{
public function init()
{
    $uri = $this->_request->getPathInfo();

    $activenav = $this->view->navigation()->findByUri($uri);
    $activenav->active = true;
}

public function indexAction()
{
    // action body
}

public function loginAction()
{

    if(Zend_Auth::getInstance()->hasIdentity())
    {
        $this->_redirect('index/index');
    }

    $request = $this->getRequest();
    $form = new Application_Form_LoginForm();
    if($request->isPost())
    {
        if($form->isValid($this->_request->getPost()))
        {

            $authAdapter = $this->getAuthAdapter();

            $username = $form->getValue('username');
            $password = $form->getValue('password');

            $authAdapter->setIdentity($username)
                        ->setCredential($password);

            $auth = Zend_Auth::getInstance();

            try
            {
                $result = $auth->authenticate($authAdapter);
            }
            catch (Exception $e) 
            {
                echo 'Caught exception: ',  $e->getMessage(), "\n";
            }

                if ($result->isValid()) 
                {
                    $identity = $authAdapter->getResultRowObject();
                    $authstorage = $auth->getStorage();
                    $authstorage->write($identity);

                   $this->_redirect('index/index');
                }
                else
                {
                    $this->view->errorMessage = "User name or password is wrong";
                }
            }
        }

    $this->view->form = $form;


}

public function logoutAction()
{
    Zend_Auth::getInstance()->clearIdentity();
    $this->_redirect('index/index');
}

private function getAuthAdapter()
{
    $authAdapter = new Zend_Auth_Adapter_DbTable(Zend_Db_Table::getDefaultAdapter());
    $authAdapter->setTableName('users')
                ->setIdentityColumn('username')
                ->setCredentialColumn('password')
                ->setCredentialTreatment('SHA1(CONCAT(?,salt))');

    return $authAdapter;
}
}

I have been stuck on this for a couple of days now and its driving me nuts. BTW how can I echo out the actual sql that is being generated? Thanks all

Solution

It depends on MySQL version as described above. Following MySQL Documentations for version 5.5:

"If an application stores values from a function such as MD5() or SHA1() that returns a string of hex digits, more efficient storage and comparisons can be obtained by converting the hex representation to binary using UNHEX() and storing the result in a BINARY(N) column. Each pair of hex digits requires one byte in binary form, so the value of N depends on the length of the hex string. N is 16 for an MD5() value and 20 for a SHA1() value."

So, instead of downgrading MySQL version, you may do as follows:

change type of 'password' column from varchar(32) to binary(16)
add 'UNHEX()' MySQL function to your MySQL query in ZF code, for example:

$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    'user',
    'login',
    'password',
    'UNHEX(MD5(CONCAT(?, passwordSalt)))'
);

It works well in my case.

Edit -- If your password salt is also stored in a binary column (e.g. if it too was a hex string generated through the SHA1 function) then the last parameter of the Zend_Auth_Adapter_DbTable should be: 'UNHEX(SHA1(CONCAT(?, LOWER(HEX(salt)))))' So, we are converting the salt back to a lowercase hex string before concatenating with the password. HEX() returns your salt in uppercase so you can just omit the LOWER() call if your salt was originally uppercase before you stored it using UNHEX().