I am very new to Yii, but very excited about all the possibilities I see.
I am building an application where several of the fields require encryption (social security number, etc.). In my old, procedural PHP code, I was able to do this when extracting an encrypted field value from MySQL to display in a form:
AES_DECRYPT(insuranceSubscriberSSN,’$key_string’) as insuranceSubscriberSSN
I have looked through the documentation and the forums but have not found a good exmaple of how this works (at least, one that I kind understand).
I would very much appreciate some guidance and examples of the best ways to encrypt and decrypt a couple of fields.
I apologize for the ‘newbness’ of this … I have been writing PHP procedural code for a long time but am new to OO and Yii. Thank you for taking the time to help me with this.
I have placed this at the top of my ‘Clients’ model:
public $encoded;
And then I have placed this down in the model where the other functions live:
public function defaultScope()
{
return array("select" => array("AES_DECRYPT(clientSocialSecurity,'C3yZ)pO|RgP|IaBuCT') as encoded"));
}
And then the form display in my view I have this just to see if I get anything:
But I get nothing visible returned and then also no other data displayed for that client. I also don’t see where that function defaultScope actually gets called.
I am sure I have done something incorrect with the code you provided. Please advise.
If you do a manual query using the same vars, do you get a result then? And if you replace AES_DECRYPT with AES_ENCRYPT, is it returning something? If so, the php code works, but there is something wrong with mysql. I’m not familiar with the AES_ functions so I’m not sure how it behaves.
It might help to configure a CProfileLogRoute (guide: logging) and to set your DB-connection’s “enableProfiling” attribute to true (guide: db). You should then see the queries that are executed during each request at the bottom of your page. Maybe it helps to find out what’s going wrong.
SELECT AES_DECRYPT(clientSocialSecurity,‘C3yZ)pO|RgP|IaBuCT’) as encoded FROM clientst WHERE t.Id=73 LIMIT 1
This query works. But it also is echoing out this right after:
SELECT AES_DECRYPT(clientSocialSecurity,‘C3yZ)pO|RgP|IaBuCT’) as encoded FROM clientst WHERE t.Id IS NULL LIMIT 1
This is making all the records disappear and the pages throw errors.
So, there’s something wrong with how I’m using the code. I guess what I’m missing is how that function gets called (perhaps by default?). Either way, it’s not returning anything, and its also making the other information on the page disappear.
Has anyone had experience with this? I’m really blocked with my application because of this issue (which I had hoped would be simple). Please chime in if you can.
I’ve just read through and i’m not seeing anything obvious, it might help just to post your client model class, the action function that your using to view it and the view it’s displayed in. At least we can then move things around to try, as the chances of you misinterpreting where to put things is more likely than something actually breaking and AR can be a bit temperamental when your doing custom selects and aliasing.
P.S
I’ve not done much with encrypting database fields so this is only a query, but is it really the best way to encrypt sending the raw data over to the DB to do the encryption and decryption? I always assumed it was better PHP side as there’s never any transfer of unsecured data?
Christopher, which version of PHP are you using? If you’re on v5.3+, you could decrypt everything in you model’s afterFind() method via openssl_decrypt().
The following piece of code should work (beware, I’ve not tested this!):
class Clients extends CActiveRecord
{
...
public function afterFind()
{
$this->clientSocialSecurity = openssl_decrypt($this->clientSocialSecurity, 'AES-128-CBC', 'mysupersecreptpasswordthatishallnevereverpasteintoapublicforum');
parent::afterFind();
}
}
The clientSocialSecurity property should then have the SSN unencrypted.
I didn’t know about scopes when I had to solve this exact same problem, so I did what the previous post suggets. I.e., compute the variable in the afterFind() method of the model class. Importantly, you must also remember to AES-encrypt it in the beforeSave() method.
The other difference in my implementation is that I wrote AES-encrypt and decrypt functions which are compatible with MySQL AES_ENCRYPT/AES_DECRYPT, whereas the previous solution relies on a the openssl library for encryption…
Solution:
/**
* 1. Globals.php
* Define functions used everywhere.
*/
class Globals {
const KEY = 'MySuperSecretKey';
/**
* @return string - AES-decrypt $val, using either key passed in, or local key if no key given.
* Compatible with mysql's aes_decrypt.
*/
public static function aesDecrypt($val, $key=null) {
if ($key == null) $key = self::KEY;
$mode = MCRYPT_MODE_ECB;
$enc = MCRYPT_RIJNDAEL_128;
$dec = @mcrypt_decrypt($enc, $key, $val, $mode, @mcrypt_create_iv( @mcrypt_get_iv_size($enc, $mode),
MCRYPT_DEV_URANDOM ) );
return rtrim( $dec, ( ( ord(substr( $dec, strlen( $dec )-1, 1 )) >= 0 and ord(substr( $dec, strlen( $dec )-1, 1 ) ) <= 16 ) ? chr(ord(substr( $dec, strlen( $dec )-1, 1 ))): null) );
}
/**
* @return string - Reversible, AES-encrypted $val, using either key passed in, or local key if no key given.
* Compatible with mysql's aes_encrypt.
* @param $key - string - The key to use for decryption. If none specified, use the local key.
*/
public static function aesEncrypt($val, $key=null) {
if ($key == null) $key = self::KEY;
$mode=MCRYPT_MODE_ECB;
$enc=MCRYPT_RIJNDAEL_128;
$val=str_pad($val, (16*(floor(strlen($val) / 16)+(strlen($val) % 16==0?2:1))), chr(16-(strlen($val) % 16)));
return @mcrypt_encrypt($enc, $key, $val, $mode, mcrypt_create_iv( mcrypt_get_iv_size($enc, $mode), MCRYPT_DEV_URANDOM));
}
} // Globals
/**
* 2. Model class
*/
class MyModel extends CActiveRecord
/**
* After fetching from db, set social_security_number to the AES-decrypted value.
*/
public function afterFind() {
$val = parent::afterFind();
$this->social_security_number = Globals::aesDecrypt($this->social_security_number);
return $val;
}
/**
* Before attempting to save this record, AES-encrypt social_security_number.
*/
public function beforeSave() {
$val = parent::beforeSave();
$this->social_security_number = Globals::aesEncrypt($this->social_security_number);
return $val;
}
}
Emily … I was just about to post some findings … but before I do, I’d like to try your method. It seems like it will work best for what I have existing.
One newb question: Where do you put the globals.php file and how to do make sure it is loaded (include?)?
That globals.php doesn’t really blend in with Yii’s design. You can put the key into the param-stanza of your config/main.php and fetch it later via Yii::app()->params[‘secretKey’]. The methods were best off in a behaviour. Just saying
Hey, I’m always trying to do things the “best practices” way. But I’m confused. I use params for little things like, “adminEmail”. What does it mean to make a class into a parameter? Did you mean, instead, that Globals.php should be made into an application component? If I did that, I could indeed access functions in “Globals.php” via
Hm, I didn’t intend to make the Globals class into a param or a component. I’d rather scrap it entirely and take the key into the application’s config (where it belongs, IMHO) and stash away the encryption and decryption functionality into a behaviour that can easily be attached to multiple models.
Ah, almost forgot: There’s the CSecurityManager class that can take care of en- and decryption as well. In fact, it wraps around PHP’s mcrypt extension, so it’s close to Emily’s solution. Sorry, but I use that class so rarely, I forgot all about it
So, if I run the “encrypt” method of CSecurityManager with a string and a key, will it give me the same result as running MySQL’s AES_ENCRYPT with the same string and key? Lacking that was why I rolled my own in “Globals”.