Difficulty Retrieving Encrypted Attributes From Db Into Model

Yii 1.1 General Discussion
1

Hi,

I have a table where I am storing driver license details. In the database, the field is encrypted using MySQL AES_ENCRYPT. However, I am having a difficulty in getting the un-encrypted values using models.

The approach I have tried follows:


class User extends CActiveRecord

{

public static function model($className=__CLASS__) {...}

public function tableName() {...}

public function rules() {...}

public function relations() {...}

public function attributeLabels() {...}

public function search() {...}


   protected function afterFind()

    {

        list($this->birth_year, $this->birth_month, $this->birth_day) = explode('-', $this->birthday);


        $this->origPassword = $this->password;

        $this->origDriversLicence = $this->user_drivers_license;

        $this->origDriversLicenseState = $this->user_drivers_license_state;

    }

    

    public function beforeSave()

    {

        if (!parent::beforeSave()) return false;


        $this->password = $this->password == $this->origPassword ?

            $this->password : generate_new_hash($this->password);


        $this->user_drivers_license = $this->user_drivers_license == $this->origDriversLicence ?

            $this->user_drivers_license : new CDbExpression("AES_ENCRYPT('$this->user_drivers_license','" . self::getEncryptionKey() . "')");


        $this->user_drivers_license_state = $this->user_drivers_license_state == $this->origDriversLicenseState ?

            $this->user_drivers_license_state : new CDbExpression("AES_ENCRYPT('$this->user_drivers_license_state','" . self::getEncryptionKey() . "')");


        return true;

    }


    public function defaultScope()

    {

        return array(

            'select'=>array(

                '*',

                "AES_DECRYPT('$this->user_drivers_license','" . self::getEncryptionKey() . "')" => 'user_drivers_license',

                "AES_DECRYPT('$this->user_drivers_license_state','" . self::getEncryptionKey() . "')" => 'user_drivers_license_state'

            ),

        );

    }

}

For some reason, the AES_DECRYPT part doesnt seem to get appended to the query and I end up with the encrypted version of the license. Any pointers in this direction would be greatly appreciated.

2

I have also tried this variation with little success:


    public function defaultScope()

    {

        return array(

            'select'=>array(

                "*,AES_DECRYPT('$this->user_drivers_license','" . self::getEncryptionKey() . "') AS user_drivers_license, AES_ENCRYPT('$this->user_drivers_license_state','" . self::getEncryptionKey() . "') AS user_drivers_license_state"

            ),

        );

    }

It just throws an error saying “AES_DECRYPT(’$this->user_drivers_license’,” is not a valid column.

3

Looks like your always encrypting.

You’ll need to decrypt, http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_aes-decrypt

4

Alex, My Bad, that was a typo. have corrected. but doesnt help.

5

Here are several samples that I hope will help you find a solution.

I wrote a simple behavior to handle my encryption methods.




class MySQLEncryption extends CActiveRecordBehavior

{

    public function encrypt($data)

    {

        return new CDbExpression('AES_ENCRYPT("' . $this->getsalt() . $data . '","' . Yii::app()->user->encryptionKey . '")');

    }


    public function decrypt($column)

    {

        return "SUBSTR(CONVERT(AES_DECRYPT($column,'" . Yii::app()->user->encryptionKey . "') USING utf8),9)";

    }


    public function selectAs($column)

    {

        return $this->decrypt($column) . ' AS ' . $column;

    }


    /*

     * Generate a random 8 character string to salt all of the encrypted

     * values.

     */

    protected function getSalt()

    {

        return substr(md5(mt_rand(0,1000000) . mt_rand(0,1000000) . mt_rand(0,1000000)),0,<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />;

    }

}

In my model that uses encryption I attach the behavior and have the following methods that are part of it




public function beforeSave()

{

    // Column Encryption

    $this->encryptColumns();

            

    return(parent::beforeSave());

}







public function encryptColumns()

{

    // Your first encrypted column

    if(!is_null($this->encryptedColumnA))

    {

        $this->encryptedColumnA = $this->encrypt($this->encryptedColumnA);

    }

    else

    {

        $this->encryptedColumnA = null;

    }


    // Your second encrypted column

    if(!is_null($this->encryptedColumnB))

    {

        $this->encryptedColumnB = $this->encrypt($this->encryptedColumnB);

    }

    else

    {

        $this->encryptedColumnB = null;

    }


    //etc

}







public function baseCriteria()

{

    $criteria = new CDbCriteria;


    $select = array();

    $encryptionKey = Yii::app()->user->encryptionKey;


    // encryptedColumnA

    $select[] = $this->selectAs('encryptedColumnA');


    // encryptedColumnB

    $select[] = $this->selectAs('encryptedColumnB');


    $criteria->select = $select;


    return $criteria;

}

I wrote a simple method to fetch a single record.




public function getRecord($yourPrimaryKey)

{

    $criteria = $this->baseCriteria();

    $criteria->compare('yourPrimaryKey', $yourPrimaryKey, false);

    $criteria->limit = 1;

            

    return $this::model()->find($criteria);

}

Lastly here’s a sample search method to search and/or sort by one of your encrypted columns




public function search()

{

    $criteria = $this->baseCriteria();


    $criteria->compare($this->decrypt('encryptedCOlumnA'),$this->encryptedCOlumnA,true);

    $criteria->compare($this->decrypt('encryptedCOlumnB'),$this->encryptedCOlumnB,true);


    $sort = new CSort();

    $sort->attributes = array();


    $sort->attributes['encryptedCOlumnA'] = array('asc'=>$this->decrypt('encryptedCOlumnA'), 'desc'=>$this->decrypt('encryptedCOlumnA') . ' DESC');

    $sort->attributes['encryptedCOlumnB'] = array('asc'=>$this->decrypt('encryptedCOlumnB'), 'desc'=>$this->decrypt('encryptedCOlumnB') . ' DESC');


    return new CActiveDataProvider($this, array(

        'criteria'=>$criteria,

        'sort'=>$sort,

    ));

}