Using MySQL encode() function

Yii 1.1 General Discussion
1

Hi all,

I’m in the process of rewriting an old app using yii.

In the old app the user password was written into the MySQL database using the MySQL ENCODE function, like

ENCODE( ‘password’, ‘secretkey’ )

as part of the insert statement.

Using yii a new user is stored using the CActiveRecord->save() method which works wonderfully.

But how do I use the MySQL encode() function with CActiveRecord ?

of course I could set up encoding the password differently, but that won’t work with the data already in the database.

Any help is much appreciated!

2

Hi Giordano.

I checked the code of CModel, and in my opinion there are no ways for do it in a "clean way".

My suggestion is to insert the user with AR and then write a custom update for the password




Yii::app()->db->commandBuilder->createSqlCommand("UPDATE user SET PASSWORD ENCODE( 'password', 'secretkey' )")->execute();

And when you check the password, you can write a simiar command for get the password decoded.

Like that is not the best one solution possible, but at least is working and your application can continue to grow. If you find a best one solution, please write here, I am interested too.

3

In the beforeSave() method you can do this:




protected function beforeSave()

{

    if ($this->isNewRecord)

        $this->password = new CDbExpression("ENCODE('{$this->password}', 'key')");

}

http://www.yiiframework.com/doc/api/CDbExpression

4

::)

  • add:

return parent::beforeSave();
5

Thanks for the suggestions.

However, the execution of CDbExpression does not do the trick. Inspecting $this->password after calling

$this->password = new CDbExpression(“ENCODE(’{$this->password}’, ‘key’)”);

returns the exact string passed to CDbExpression, in the above case:

ENCODE(‘9867965’, ‘key’)

where 9867965 is the password to be encoded.

I’ve tried the code in the beforeSave function and also in the controller just before calling the save() method with the same result.

Any ideas?

6

The encode function will work when the record will be saved.

Try to call save() and then check what happens in db.

7

Hmm, sorry to have bothered you with this.

I’ve changed the database field from type varchar to tinyblob which stores the encoded password as expected.

Apparently a binary field is needed.

Yii works as expected, the smile on my face is becoming bigger and bigger :)

Cheers

8

Excellent informations.

But what about decode , How can I read data?

I have field , I used this way to encode it correctly.

but what about reading data again ( decoding ) ?

Thanks,

9

You could change the model dbCriteria before "find" operations ($dbkey here is retrieved from Yii app parameters):




  protected function beforeFind()

  {

    $dbkey = Yii::app()->params['dbkey'];


    $this->model()->dbCriteria->select = array(

      "t.id AS id",

      "DECODE(t.encoded_field_1, '$key') AS encoded_field_1",

      "DECODE(t.encoded_field_2, '$key') AS encoded_field_2",

      "t.field3 AS field3",// UNENCODED FIELD

      //....list all remaining unencoded fields you need here

    );

  }

OR




  protected function beforeFind()

  {

    $dbkey = Yii::app()->params['dbkey'];


    $this->model()->dbCriteria->select = array(

      "*",//all fields are retrieved

      "DECODE(t.encoded_field_1, '$key') AS encoded_field_1",//encoded_field_1 is now overwritten by decode value

      "DECODE(t.encoded_field_2, '$key') AS encoded_field_2",//encoded_field_2 is now overwritten by decode value

    );

  }