问题描述

我有以下异常 捕获异常:提供给 Zend_Auth_Adapter_DbTable 的参数未能生成有效的 sql 语句，请检查表名和列名的有效性. 我用谷歌搜索并一遍又一遍地检查我的代码再次，但我还没有找到解决方案.表名和列名都正确.

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(), "
";
            }

                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 代码中将 'UNHEX()' MySQL 函数添加到您的 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 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！