Activerecord With Automatic Encryption/decryption By The Database?


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()


'customer_id' => $this->customer_id,

'assess' => true


->orderBy('role_id, AES_DECRYPT(lastname_crypt, "' . Yii::$app->params['aes_salt'] . '")')


This leaves me wondering:


[*] 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.


I’m curious about your feedback and ideas.

Reversable crypting makes sense only for situation when somebody can steal the DB and has no access to program code.

(otherwise he can decrypt all the data using salt from your config).

So moving all the encryption to DB (stored logic etc) probably kills the main idea. In this case stealing the DB leads to easy decrypting by using stored logic provided.

If you want to encrypt all the attributes, then yes, it does make sense. Decryption can be done somewhere in afterFind for example.

Thanks for your feedback. Obviously, when I wrote about running the encryption/decryption on the DB, my intention is still to supply the key in my SQL query (as in my last code sample), and not store it in the DB server.

I did not know about the afterFind method, so I’ll check that out…

Mea culpa, missed it, sorry.