ActiveRecord mapping

How can I map active record attribute to expression?

Assume I have this model

<?php


class Customer extends ActiveRecord

{  

    /**

     * @return string the name of the table associated with this ActiveRecord class.

     */

    public static function tableName()

    {

        return 'customer';

    }

}

$customer = new Customer();

$customer->name = 'Qiang';

$customer->sensitive= 'Sensitive Data';

$customer->save();

I want to map sensitive attribute to AES_ENCRYPT(sensitive,‘mykeystring’) and AES_DECRYPT

is it possible?

In you Customer model create the 2 magic method setSensitive() and getSensistive() that does encrypt/decrypt

They will be called automatically by the active record when you assign or read sensitive property

echo $model->sensitive;

will run getSensistive() instead reading directly the sensitive property

Same when you assign a value

$model->sensitive=‘my sensitive data’;

will run setSensitive()

I want to use mysql build in encryption , because I need to search and sort in db

With magic method implementation I lost these features

To have sensitive using mysql encription you have 2 option:

• put a trigger on the column that does the work at select (decripting) and at update/insert (encripting)
• do the work inside beforeSave() and afterFind() (something like this)

public function beforeSave($insert)

{

	if (parent::beforeSave($insert)) {

    	$query = new Query;

    	$query->select("AES_ENCRYPT(".$this->sensitive.", 'your secret salt') AS encrypted")

        	->from('dual');

    	$rows = $query->one();

    	$this->sensitive = $row['encrypted'];

    	return true;

	} else {

    	return false;

	}

}




public function afterFind()

{

	if (parent::afterFind()) {

    	$query = new Query;

    	$query->select("AES_DECRYPT(".$this->sensitive.", 'your secret salt') AS decrypted")

        	->from('dual');

    	$rows = $query->one();

    	$this->sensitive = $row['decrypted'];

    	return true;

	} else {

    	return false;

	}

}

You can optimize a bit for performance the beforeSave() and apply the encryption and save the sensitive only if the content has changed

If you need them to be searchable as far as I know you need first to decrypt them

WHERE AES_DECRYPT(sensitive, 'your secret salt') LIKE '%search text%'

Therefore you need to compose your where accordingly to the above

But I think all this encrypt/decrypt process is going to be quite slow, evaluate carefully if the needs balance the effort and the performance loss.