[christomurr]

Hello,

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.

Thanks very much,

Christopher

[wisp]

I would do it like this

In your model, create a new variable at the top like

public $encoded;

Then, create a scope, (I'm using a defaultScope as an example)

 public function defaultScope()
{
       return array("select" => array("AES_DECRYPT(id,'yourkey') as encoded"));
}

Now you can retrieve the data using regular AR methods

$data = Test::model()->findByPk($id);
echo $data->encoded;

[christomurr]

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:

$data = Clients::model()->findByPk($id);
echo $data->encoded;

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.

Regards,
Christopher

wisp, on 03 December 2011 - 01:44 PM, said:

I would do it like this

In your model, create a new variable at the top like

public $encoded;

Then, create a scope, (I'm using a defaultScope as an example)

 public function defaultScope()
{
       return array("select" => array("AES_DECRYPT(id,'yourkey') as encoded"));
}

Now you can retrieve the data using regular AR methods

$data = Test::model()->findByPk($id);
echo $data->encoded;

[wisp]

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.

[christomurr]

Hi Wisp ... Thank you very much again for your time.

When I put this query into mysql directly I get the proper values:

SELECT AES_DECRYPT( clientSocialSecurity, 'C3yZ)pO|RgP|IaBuCT' ) AS encoded

So, I'm stepping on something else ;-)

I'm not sure what else to provide you that might help. Again, I'm very new to OO and Yii so perhaps I'm just misplacing things.

Christopher

[Ben]

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.

[christomurr]

I am only allowed 3 posts on my first day ;-)

This is what the query looks like on echoing out:

SELECT AES_DECRYPT(clientSocialSecurity,'C3yZ)pO|RgP|IaBuCT') as encoded FROM `clients` `t` 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 `clients` `t` 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.

[christomurr]

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.

Thanks,
Christopher

[Joblo]

Quote

And then the form display in my view I have this just to see if I get anything:

$data = Clients::model()->findByPk($id);
echo $data->encoded;

Usually this code should be in the controller action not in the view.

Did you test the value if the $id variable?

Seems to be null when you have logged an sql query like above:

SELECT AES_DECRYPT(clientSocialSecurity,'C3yZ)pO|RgP|IaBuCT') as encoded FROM `clients` `t` WHERE `t`.`Id` IS NULL LIMIT 1

echo "id=$id"; //<--- add this
$data = Clients::model()->findByPk($id);
var_dump($data); //<--- add this
echo $data->encoded;

[paystey]

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?

[Da:Sourcerer]

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.

[Emily Dickinson]

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;
   }
}

[christomurr]

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

Thank you.
Christopher

[Emily Dickinson]

*Any* class that you place in
protected/components
will be available for use by your application.
(The rule is, the filename must match the name of the class it contains. So, as a rule of thumb, don't define more than one class per file.)

So, place the file in "protected/components", and call it Globals.php.
No need for pesky require() statements!

[Da:Sourcerer]

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

[Emily Dickinson]

Da:Sourcerer, on 05 December 2011 - 04:08 PM, said:

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
Yii::app()->globals.
Confused.

[Da:Sourcerer]

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.

[Da:Sourcerer]

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

[Emily Dickinson]

Da:Sourcerer, on 05 December 2011 - 04:23 PM, said:

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.

Okay, I think I follow.
I agree that "key", stuffed into Globals.php, is pure laziness on my part; it should be an application param.
I'll read up on behaviors...haven't used them yet, thanks!
Em

[Emily Dickinson]

Da:Sourcerer, on 05 December 2011 - 04:25 PM, said:

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".