Using AES_ENCRYPT and AES_DECRYPT

I’m currently using MD5 for password storage, but I’m now looking to convert to the more secure AES. What makes it easy to use MD5 is that the encryption can be done within PHP. So for example when I’m saving a new user record I normally just do:

$model->password=md5($model->password);

$model->save();

However this isn’t possible with AES because it’s a MYSQL function. Similarly in my UserIdentity class I currently do this:

$user=User::model()->findByAttributes(array('email'=>$this->username));


if($user->password!==md5($this->password))

         $this->errorCode=self::ERROR_PASSWORD_INVALID;

So how to do all this with AES?

Maybe this helps you man: http://www.phpaes.com/

Or this: http://www.chilkatsoft.com/p/php_aes.asp

Nah man, what I’m after is how to specify AES_ENCRYPT when creating/selecting a user, for example:

INSERT INTO Users (email, password) VALUES ($email, AES_ENCRYPT('$password', '$secret_key'));


SELECT * FROM Users WHERE email = '$email' AND password = AES_ENCRYPT(password, '$secret_key');

Check out CDbExpression and example usage (at the bottom of the section).

Thought you wanted to do it on PHP (as you showed with md5 function) not in MySQL, this is why I point to those examples.

If you wish to do it with mySQL I do everything through CDbCommands directly -Y!! is 100% right.

Sorry for the confusion Antonio

Anyway I’m now trying to implement this in my CUserIdentity but it doesn’t seem to be working. What is the problem with this:

$password=new CDbExpression('AES_ENCRYPT(:password, :secret_key)', array(':password'=>$this->password, ':secret_key'=>1234567890));

		

$user=User::model()->findByAttributes(array('email'=>$this->username, 'password'=>$password));

It seems to be ok…

Could you try to see the SQL created by activating CWebLogRoute?

If you cannot see anything wrong with the SQL

Try with

User::model()->findBySQL(‘SELECT * FROM {{user}} WHERE {{user}}.email=:email AND {{user}}.password=AES_ENCRYPT(:password,:secret_key)’,array(’:password’=>$this->password,’:secret_key’=>1234567890));

lets see what it happens

The SQL generated is:

[21:43:54.878][trace][system.db.CDbCommand] Querying SQL: SELECT * FROM `user` `t` WHERE `t`.`email`=:yp0 AND `t`.`password`=:yp1 LIMIT 1. 

Bind with parameter :yp0='admin@mysite.com', :yp1=CDbExpression::__set_state(array( 'expression' => 'AES_ENCRYPT(:password, :secret_key)',

'params' =>array ( ':password' => 'password', ':secret_key' => 1234567890, ), '_e' => NULL, '_m' => NULL, ))

I can’t figure out why it won’t work. But this works:

$user=User::model()->findBySQL('SELECT * FROM user WHERE user.email=:email AND user.password=AES_ENCRYPT(:password, :secret_key)', array(':email'=>$this->username, ':password'=>$this->password, ':secret_key'=>1234567890));

THIS WORKS:

$criteria=new CDbCriteria;

$criteria->condition="email=:email";

$criteria->addCondition("password=AES_ENCRYPT(:password, :secret_key)");

$criteria->params=array(':email'=>$this->username, ':password'=>$this->password, ':secret_key'=>1234567890);


$user=User::model()->find($criteria);

So it seems like we only use CDbExpression when creating/updating a record, and manually enter AES_ENCRYPT in our select queries.

Well done GSTAR,

I remember having a discussing on a new feature with CDbCommand and I was saying that sometimes is better to maintain SQL clearance in the code. It is true that is better to handle difficult things automatically for newbies but when it comes to advance things, sometimes is better to see everything clear as water.

At the end of the day, what is important is that you have accomplished your objective, no matter how you do it.

Congrats again GSTAR, happy to help

By the way, have you realized that CDbExpresion value is an array on your CWebLogRoute display?