Zend_Auth_Adapter_DbTable with MySQL View

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Zend_Auth_Adapter_DbTable with MySQL View

Joseph Crawford
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
Reply | Threaded
Open this post in threaded view
|

Re: Zend_Auth_Adapter_DbTable with MySQL View

Joseph Crawford
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