Thursday, June 10, 2010

Yii User Authentication using MySQL PASSWORD()

This is a pretty simple thing, but it hitched me up for a while, so I thought it worth sharing/recording the solution.

If you use passwords stored with the MySQL PASSWORD() function, the suggested methods of authentication with UserIdentity need to be tweaked.

There are two ways that I have come up with to run the comparison. The first way is more secure, as it doesn't ever send the encrypted password back through the connection, but the second way allows you a more specific 'user found, but password is wrong' error.
Both options involve modifying the UserIdentity::authenticate method.

Option 1:

Change the user model lookup as follows:
$user = User::model()->find(
               array(
                  'select'=>'username,id',  // add ONLY the columns you need
                  'condition'=>'username=:uname AND password = PASSWORD(:up)',
                  'params'=>array(':uname'=>$this->username, ':up'=>$this->password)
               )
             );

Then simply comment out the else clause comparing the passwords.

You've now only shared the minimum of secure data.

Option 2:

Add the following above the user model lookup:
$conn = Yii:app()->db;
$command= $conn->createCommand('SELECT PASSWORD(:pass) as p');
$command->bindParam(":pass", $this->password, PDO_PARAM_STR);
$row = $command->queryRow();
$securePass = $row['p'];
Keep the existing User lookup ( see previous post on Yii authentication )
Then, change the password comparison line to
else if ( $user->password !== $securePass )  // instead of !== $this->password