Using CDbExpression for AES_DECRYPT

Yii 1.1 General Discussion
1

I’m trying to modify my table AR model to automate MySQL functions. I have certain fields that are encrypted using MySQL’s AES_ENCRYPT, AES_DECRYPT functions. Whenever an encrypted field is read, I want it to automatically be decrypted, and whenever it is written, I need it to be encrypted.

I’m probably going about this the wrong way, but from what I can tell, I think I need to specify this in the “rules” section of the model something like this?


array ('student_id', 'default', 'value'=>new CDbExpression('AES_DECRYPT()'), 'on'=>'read' ),

array ('student_id', 'default', 'value'=>new CDbExpression('AES_ENCRYPT()'), 'setOnEmpty'=>false, 'on'=>'update,insert' ),

How do I specify the encryption key in the function "AES_DECRYPT(crypt_str,key_str)" ?

What am I missing?

2

I your problem had been to decrypt/encrypt all attributes, I would have proposed to do it in afterFind() and afterValidate($scenario) respectively (possibly in an encryption behavior, see http://www.yiiframework.com/doc/api/CActiveRecordBehavior). Don’t know if there’s a better way to handle selected attributes.

/Tommy

3

Why no overwrite afterFind() and beforeSave() methods of ActiveRecord?

You can decript in afterFind() and encript in beforeSave()

4

That’s a good idea, but my preference is to allow MySQL to do the encrypt/decrypt. This works:


public function beforeFind(){

        $criteria = new CDbCriteria;

        $criteria->select ='*, AES_DECRYPT(`student_id`,"mykey") as student_id';

        $this->dbCriteria->mergeWith($criteria);

        return parent::beforeFind();

    }

However, it does lock my “select” statement to show all columns. Too bad there isn’t a “addSelect” method like there is with conditions.

Any other ideas?

5

Thanks for this - unfortunately, I can’t get it to work when the class with the overriden beforeFind method is a related class - as in, imagine that the above code was part of a “User” class, which was related to a “Student” class.

What I’ve found is that when I get:

$user->password

then the code listed above runs, but when I get:

$student->user->password

it seems to skip the beforeFind entirely.

Is there any way to get that code to run when it’s called as a related table?

6

no, there isn’t any method.

If you take a look at the code of CDbCommandBuilder::createFindCommand (line 74-89) you will see:




		$this->ensureTable($table);

		$select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;

		if($criteria->alias!='')

			$alias=$criteria->alias;

		$alias=$this->_schema->quoteTableName($alias);


		// issue 1432: need to expand * when SQL has JOIN

		if($select==='*' && !empty($criteria->join))

		{

			$prefix=$alias.'.';

			$select=array();

			foreach($table->getColumnNames() as $name)

				$select[]=$prefix.$this->_schema->quoteColumnName($name);

			$select=implode(', ',$select);

		}

That means that you can use or a completely custom select, or the default select (without parameters).

You can try to change this function in your framework, in order to add a new parameter to CDbCriteria (for example, an array with specific select field to change) and then hack this function on order to use it.

If you will cope to make it work, you can create a patch (a file in your application) by extending all needed classes for include in your config main this patch.

The patch only in a second time, try to hack this function first.