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

Question

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

Answer 1

Ok so first off I need to make all aware that this answer has been patented and licensed under one of those linceses that mean that you can't even read or come up with a similar answer.(you know I kidding right?). Ok ok to the point ....

After 3 days I stumbled upon a solution. A weird one of cause but it fixed my problem. So things were not working and no one had answered my question so I got hold of this new zend book that I bought to just try and distract myself from the problem. A further distraction was to boot into Linux instead of windows (I dual boot you know).

In Linux i just decided to create a virtual host for the problematic project and just try and run it. To my supprise it run without problems. I was able to login. Then i took a look at phpmyadmin and saw that I the mysql version is 5.1 where as the one on my widows setup is 5.5. So I thought why not downgrade the mysql in windows from 5.5 to 5.1.

So i did et viola my problem was gone. I don't know what the folks at mysql did to it but it seems ver 5.5 may have issues with SHA1. Not sure if it applies to other hash functions though. May be someone will comfirm this suspicion?