Zend_Auth_Adapter_DbTable with MySQL View

[Joseph Crawford]

Some javascript/style in this post has been disabled (why?)

Hello Everyone,

I had my authentication adapter working just fine using the actual database table of isp_partners_aux.  I needed to join it with isp_partners though so that I could have the joined data in the users identity.

I created a view in Mysql and it works find under the mysql command line client.

I seem to get this error when using it with the Auth Adapter though.

Message: The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity.

I did some debugging by adding echo $dbSelect->__toString(); on line 306 of Auth/Adapter/DbTable.php and got shown the following query with the username and password marked with ** only for security in the email.

SELECT `isp_partners_view`.*, (CASE WHEN `password` = '*****' THEN 1 ELSE 0 END) AS `zend_auth_credential_match` FROM `isp_partners_view` WHERE (`username` = '*****')

This query works fine in the mysql command line client so I am not sure why it is not working well when used with Zend_Auth_Adapter_DbTable

Has anyone else here successfully used the auth adapter with an MySQL view?

Thanks,

Joseph Crawford

[Joseph Crawford]

Some javascript/style in this post has been disabled (why?)

Andrew,

Thank you for pointing out how this could be accomplished.

Joseph Crawford

On Jul 6, 2009, at 5:50 PM, Andrew Pace wrote:

Joseph, 

I have done something similar using multiple tables to manage user details.  I have provided snippets of the code that I use, so I hope this helps.  In the database, I use a table called "users" and "users_profile" to store all data related to a user.  When I authenticate a user, I want information from both tables, "users" and "users_profile".  I do this by first implementing the loginAction pasted below.  As you can see, this authenticates using Auth_Adapter_DbTable.  Once successful, I use the resultRowObject to obtain the "user_id".  I use this user_id to load all data from the "users" table into an object.  Within the "users" table, I then implement a custom function called "postLoad", which then instantiates and loads the "users_profile" data.   With this I then write the data to the identity, using a custom function within the "users" class call "CreateAuthIdentity," which I have pasted below.  As you can see, $this->profile refers to data out of the second table "users_profile," not out of "users".  

I hope this helps.

Andrew Pace

PS: Custom_DatabaseObject is essentially the same as Zend_Db_Table

public function loginAction()

    {

     // if a user's already logged in, send them to their account home page

        $auth = Zend_Auth::getInstance();

        if ($auth->hasIdentity())

            $this->_redirect('/account');

        $request = $this->getRequest();

        // determine the page the user was originally trying to request

        $redirect = $request->getPost('redirect');

        if (strlen($redirect) == 0)

            $redirect = $request->getServer('REQUEST_URI');

        if (strlen($redirect) == 0)

            $redirect = '/account';

        // initialize errors

        $errors = array();

        // process login if request method is post

        if ($request->isPost()) 

        {

         // fetch login details from form and validate them

            $username = $request->getPost('username');

            $password = $request->getPost('password');

            if (strlen($username) == 0)

                $errors['username'] = 'Required field must not be blank.';

            if (strlen($password) == 0)

                $errors['password'] = 'Required field must not be blank.';

            if (count($errors) == 0) 

            {

                // setup the authentication adapter

                $adapter = new Zend_Auth_Adapter_DbTable($this->db,

                                                             'users',

                                                             'username',

                                                             'password',

                                                             'sha1(?)');

                $adapter->setIdentity($username);

                $adapter->setCredential($password);

                // try and authenticate the user

                $result = $auth->authenticate($adapter);

                if ($result->isValid()) 

                {

                    $user = new Custom_DatabaseObject_User($this->db);

                    $user->load($adapter->getResultRowObject()->user_id);

                    // record login attempt

                    $user->loginSuccess();

                    // create identity data and write it to session

                    $identity = $user->createAuthIdentity();

                    $auth->getStorage()->write($identity);

                    // send user to page they originally request

                    $this->_redirect($redirect);

                }

                    // record failed login attempt

                    Custom_DatabaseObject_User::LoginFailure($username,

                                                      $result->getCode());

                    $errors['username'] = 'Your login details were invalid.';

            }

        }

    $this->view->username = $username;

    $this->view->password = $password;

    $this->view->errors = $errors;

    $this->view->redirect = $redirect;

    }

---------------------------------------------------

public function createAuthIdentity()

    {

        $identity = new stdClass;

        $identity->user_id = $this->getId();

        $identity->username = $this->username;

        $identity->user_type = $this->user_type;

        $identity->first_name = $this->profile->first_name;

        $identity->last_name = $this->profile->last_name;

        $identity->email = $this->profile->email;

        return $identity;

    }

On Jul 6, 2009, at 1:59 PM, Joseph Crawford wrote:

Hello Everyone,

I had my authentication adapter working just fine using the actual database table of isp_partners_aux.  I needed to join it with isp_partners though so that I could have the joined data in the users identity.

I created a view in Mysql and it works find under the mysql command line client.

I seem to get this error when using it with the Auth Adapter though.

Message: The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity.

I did some debugging by adding echo $dbSelect->__toString(); on line 306 of Auth/Adapter/DbTable.php and got shown the following query with the username and password marked with ** only for security in the email.

SELECT `isp_partners_view`.*, (CASE WHEN `password` = '*****' THEN 1 ELSE 0 END) AS `zend_auth_credential_match` FROM `isp_partners_view` WHERE (`username` = '*****')

This query works fine in the mysql command line client so I am not sure why it is not working well when used with Zend_Auth_Adapter_DbTable

Has anyone else here successfully used the auth adapter with an MySQL view?

Thanks,

Joseph Crawford