Zend_Auth_Adapter_DbTable提供的参数无法生成有效的sql语句 [英] The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement

问题描述

我有以下例外情况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.我已经用Google搜索并一遍又一遍地检查我的代码，但是我没有找到解决方法.表和列的名称都是正确的.

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.

导致此问题的代码段为$result = $auth->authenticate($authAdapter);.实际上，可以在下面找到整个控制器代码:

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;
}
}

我已经坚持了几天，这让我发疯. 顺便说一句，我该如何回显正在生成的实际SQL? 谢谢大家

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

推荐答案

它取决于如上所述的MySQL版本.遵循5.5版的MySQL文档:

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

"如果应用程序存储了诸如MD5()或SHA1()之类的函数的返回十六进制数字字符串的值，则可以通过使用UNHEX()将十六进制表示形式转换为二进制并存储来获得更有效的存储和比较.结果在BINARY(N)列中，每对十六进制数字都需要一个二进制形式的字节，因此N的值取决于十六进制字符串的长度，MD5()值的N为16，SHA1的N为20 ()值."

"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."

因此，您可以执行以下操作，而不是降级MySQL版本:

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

• 将密码"列的类型从varchar(32)更改为binary(16)
• 使用ZF代码向您的MySQL查询添加"UNHEX()" MySQL函数，例如:

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

在我的情况下效果很好.

It works well in my case.

编辑- 如果您的密码盐也存储在二进制列中(例如，如果它也是通过SHA1函数生成的十六进制字符串)，则Zend_Auth_Adapter_DbTable的最后一个参数应为: 'UNHEX(SHA1(CONCAT(?, LOWER(HEX(salt)))))'' 因此，在将密码与密码连接之前，我们将盐转换回小写的十六进制字符串. HEX()以大写形式返回盐，因此，如果在使用UNHEX()存储盐之前，盐最初是大写，则可以省略LOWER()调用.

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

这篇关于Zend_Auth_Adapter_DbTable提供的参数无法生成有效的sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！