The supplied parameters to Zend_Auth_Adapter_DbTab

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

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().

Answer 2

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?

Answer 3

I resolved this as below:

1) vim /etc/php.ini

error_reporting = E_ALL & ~E_NOTICE
; which actually disable the notice errrors in log files too

2) Then i installed php other packages such as below where it works, and then added those missing packages to the main server, and it works.

# yum list installed php*
Loaded plugins: auto-update-debuginfo, langpacks, presto, refresh-packagekit
Installed Packages
php.i686                                                                5.3.10-1.fc15                         @updates
php-Smarty.noarch                                                       2.6.26-2.fc15                         @fedora 
php-ZendFramework.noarch                                                1.11.10-1.fc15                        @updates
php-ZendFramework-Cache-Backend-Apc.noarch                              1.11.10-1.fc15                        @updates
php-ZendFramework-Cache-Backend-Memcached.noarch                        1.11.10-1.fc15                        @updates
php-ZendFramework-Services.noarch                                       1.11.10-1.fc15                        @updates
php-ZendFramework-demos.noarch                                          1.11.10-1.fc15                        @updates
php-ZendFramework-extras.noarch                                         1.11.10-1.fc15                        @updates
php-bcmath.i686                                                         5.3.10-1.fc15                         @updates
php-cli.i686                                                            5.3.10-1.fc15                         @updates
php-common.i686                                                         5.3.10-1.fc15                         @updates
php-devel.i686                                                          5.3.10-1.fc15                         @updates
php-gd.i686                                                             5.3.10-1.fc15                         @updates
php-mbstring.i686                                                       5.3.10-1.fc15                         @updates
php-mcrypt.i686                                                         5.3.10-1.fc15                         @updates
php-mysql.i686                                                          5.3.10-1.fc15                         @updates
php-pdo.i686                                                            5.3.10-1.fc15                         @updates
php-pear.noarch                                                         1:1.9.4-1.fc15                        @updates
php-pear-Cache-Lite.noarch                                              1.7.11-1.fc15                         @updates
php-pear-XML-Beautifier.noarch                                          1.2.2-2.fc15                          @fedora 
php-pear-XML-Parser.noarch                                              1.3.4-2.fc15                          @fedora 
php-pear-XML-RPC2.noarch                                                1.0.6-1.fc15                          @fedora 
php-pear-XML-RSS.noarch                                                 1.0.2-1.fc15                          @updates
php-pear-XML-Serializer.noarch                                          0.20.2-2.fc15                         @fedora 
php-pecl-apc.i686                                                       3.1.9-1.fc15                          @updates
php-pecl-apc-devel.i686                                                 3.1.9-1.fc15                          @updates
php-pecl-memcache.i686                                                  3.0.5-3.fc15                          @fedora 
php-php-gettext.noarch                                                  1.0.11-3.fc15                         @updates
php-process.i686                                                        5.3.10-1.fc15                         @updates
php-qt.i686                                                             4.6.5-1.fc15                          @updates
php-qt-devel.i686                                                       4.6.5-1.fc15                          @updates
php-snmp.i686                                                           5.3.10-1.fc15                         @updates
php-soap.i686                                                           5.3.10-1.fc15                         @updates
php-xml.i686                                                            5.3.10-1.fc15                         @updates
phpMyAdmin.noarch                                                       3.4.9-1.fc15                          @updates

Answer 4

I had the same error and found it to be misleading. In my case, it turned out that I was connect to the wrong database for reasons not worth explaining. What you want to do is get the previous exception that caused Zend_Auth_Adapter_DbTable to throw the exception you mentioned. Below is how I accomplished this:

    $adapter = $this->_getAuthAdapter();
    $adapter->setIdentity($values['username']);
    $adapter->setCredential($values['password']);

    $auth = \Zend_Auth::getInstance();
    try {
        $result = $auth->authenticate($adapter);

    } catch (\Zend_Auth_Adapter_Exception $ex) {
        die($ex->getPrevious()->getMessage());
    }

So in the end, the answer isn't exactly:

    SET NAMES 'utf8'

It could really be any number of issues. Best bet is to let MySQL tell you by getting the previous exception. Maybe the answer will be related to character encoding. In my case it wasn't.

Answer 5

    $dbAdapter = Zend_Db_Table::getDefaultAdapter();
    $authAdapter = new Zend_Auth_Adapter_DbTable($dbAdapter);

    $version = $dbAdapter->getServerVersion();

    if (!is_null($version))
    {
        if (version_compare($version, '5.5', '>=')){
            $credentialTreatment = 'CAST(SHA1(CONCAT(?, salt)) AS CHAR) AND active = 1';
        }else{
            $credentialTreatment = 'SHA1(CONCAT(?, salt)';
        }
    }


    $authAdapter->setTableName('users')
    ->setIdentityColumn('username')
    ->setCredentialColumn('passwd')
    ->setCredentialTreatment($credentialTreatment);

    return $authAdapter;

Please check this

As of MySQL 5.5.3, the return value is a nonbinary string in the connection character set. Before 5.5.3, the return value is a binary string; see the notes at the beginning of this section about using the value as a nonbinary string.

It's works for me.

Answer 6

As Kervin has already answered, this error appears because of collation mismatch between php and mysql.

You can either stop using utf8 as suggested in the above solution or you can change the database table to use utf8.(It worked for me only after I deleted the data inside the table and reinserted them.)