Hi,
I’m building an application with some sensitive information in it, so I am encrypting everything that could identify a user. This is my precaution for should the database contents ever fall into the wrong hands. My web server and database run on two separate machines.
I want to share my way of working with you, and ask if anyone has suggestions for further simplifying things. This might be useful for other people researching the options.
First of all, my users use their e-mail address as login (I can’t expect them to make up unique usernames and remember them). Since encrypting a string might result in different outcomes, it would have been impossible to login a user based on the encrypted e-mail address column.
Therefore I maintain a separate username column where I store a salted hash of the user’s current e-mail address. Upon login, I look up the user by comparing the hash of the e-mail address, and then I compare the password hashes using Yii’s standard functionality.
All encrypted properties are stored as VARBINARY columns in MySQL. I append the suffix _crypt to each column name to indicate it is encrypted. For example, a user’s first name is stored as firstname_crypt
I then use getter/setter functions in my model to make these attributes available to the application. For example:
/**
* @return string the unencrypted lastname, is not saved in the database
*/
public function getLastname()
{
if($this->lastname_crypt === NULL) return '';
else return Security::decrypt($this->lastname_crypt, Yii::$app->params['aes_salt']);
}
/**
* @param string $lastname
* @return boolean if crypt is set
*/
public function setLastname($lastname)
{
return $this->lastname_crypt = Security::encrypt($lastname, Yii::$app->params['aes_salt']);
}
At one point, I had to sort users by last name in an AR query. Luckily AR allows using MySQL functions in its clauses:
return User::find()
->where([
'customer_id' => $this->customer_id,
'assess' => true
])
->orderBy('role_id, AES_DECRYPT(lastname_crypt, "' . Yii::$app->params['aes_salt'] . '")')
->all();
This leaves me wondering:
[list=1]
[*] Could I move the encryption and decryption into the database altogether? This would remove the need for virtual properties and all those getters and setters.
[*] If that’s not possible, could it be simplified by moving the encryption into beforeSave? But then how should I do decryption for reading attribute values? After all, Yii doesn’t allow a getter to override an existing property.
[/list]
I’m curious about your feedback and ideas.