hmacube
(Hmacube)
June 21, 2016, 6:11am
1
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?
bobonov
(Bobonov)
June 21, 2016, 9:10am
2
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()
hmacube
(Hmacube)
June 26, 2016, 5:38am
3
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
bobonov
(Bobonov)
June 27, 2016, 10:15am
4
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.