Search and Sort on Encrypted Fields

I have to encrypt at rest a lastname field and firstname field

I user beforeSave and afterFind decrypt/encrypt

My problem is I cannot search on these fields in their native, at-rest encrypted form

Any suggestions on how to do this with a search Model?

Here is my attempt

public function search($params)

{


    $query = Patient::find();





    $dataProvider = new ActiveDataProvider([


        'query' => $query,


    ]);





    if (!($this->load($params) && $this->validate())) {


        return $dataProvider;


    }





    $query->andFilterWhere([


        'id' => $this->id,


        'birthdate' => $this->birthdate,


        'surgeon' => $this->surgeon,


        'office' => $this->office,


        'referral' => $this->referral,


        'dbowner' => $this->dbowner,


        'user_id' => $this->user_id,


        'created' => $this->created,


        'updated' => $this->updated,


        'edited_by' => $this->edited_by,


    ]);





    $query->andFilterWhere(['like', 'chartid', $this->chartid])


        ->andFilterWhere(['like', 'lastname', base64_decode(Yii::$app->getSecurity()->decryptByKey($this->lastname,Patient::KEY))])


        ->andFilterWhere(['like', 'firstname',base64_decode(Yii::$app->getSecurity()->decryptByKey($this->firstname,Patient::KEY))])


        ->andFilterWhere(['like', 'mi', $this->mi])


        ->andFilterWhere(['like', 'sex', $this->sex])


        ->andFilterWhere(['like', 'ethnicity', $this->ethnicity])


        ->andFilterWhere(['like', 'phone', base64_decode(Yii::$app->getSecurity()->decryptByKey($this->phone,Patient::KEY))]);





    return $dataProvider;


}

}

I have the same issue. This old post is still unanswered and I found it (along with other suggestions) while searching. I hope that someone can help.

Anyways, your code won’t work because you’re decrypting/base64 decoding the user input instead of the table column. Yii2 translates the statement

->andFilterWhere(['like', 'lastname', base64_decode(Yii::$app->getSecurity()->decryptByKey($this->lastname,Patient::KEY))])

into:

WHERE ('lastname' LIKE '%whatever the user entered and you decrypted%)

and what you want is:

WHERE ('decrypted column value' LIKE '%whatever the user entered in cleartext%)

Getting that decrypted column value is the problem. I’ve tried:

1. using a stored procedure. It works by itself but MySQL won’t accept a CALL command in the WHERE clause;

2. MySQL also doesn’t like a dbExpression such as (AES_DECRYPT(FROM_BASE64(‘column’)) on the WHERE clause;

3. looked into MySQL’s (5.7.6+) generated virtual columns with the above expression but couldn’t get it to work;

I read that using FindAll() along with an ArrayDataprovider (instead of ActiveDataProvider) would work but didn’t see any sample code that would point me in the right direction.

Any help is greatly appreciated.

I think there should be no practical solution for it at all.

Only the searching of exact match can be possible with a reasonable performance like the following:

$rawWord = 'some-word';

$encWord = encrypt($rawWord);

$results = [];

foreach($encDatasas $encData) {

    if ($encData->word == $encWord) {

        echo "Found the word (" . $rawWord . ").";

        $resulsts[] = $encData;

    }

}

You need to call "encrypt" method just once.

But when it comes to partial match, then it will cost a lot of CPU time:

$rawWord = 'some-word';

$results = [];

foreach($encDatasas $encData) {

    $word = decrypt($encData->word);

    if (strpos($word, $rawWord) !== false) {

        echo "Found the word (" . $rawWord . ").";

        $resulsts[] = $encData;

    }

}

Note that you have to call "decrypt" method for each and every data.

Both encryption and decryption are very time consuming job. While the former sample only execute encrypt() one time, the latter need to call decrypt() as many times as there are many data. The more there are data to be processed, the more time you have to spend.

Also the sorting requires that all the data be fully decrypted beforehand.

Thank you for the prompt reply, @softark. I realize that for searching/sorting inside a GridView I would need the data already decrypted. Perhaps my best bet would be using a SqlDataProvider where I can decrypt it using SQL. Another approach I’ll look into is the generated virtual column available in MySQL 5.7.6+. I’ll post back after my trials.

Thanks,

Something like the following may work when the count of rows is considerably small.

$objects = MyModel::find()->all();

foreach ($objects as $obj) {

    $obj->field = decrypt($obj->field);

}


$dp = new ArrayDataProvider([

    'allModels' => $objects,

    ...

]);

[EDIT]

Ah, we could improve the performance a little.

Assuming that the table has a non-encrypted column that can be used for filtering:

$objects = MyModel::find()

    ->andFilterWhere(['attr1' => $this->attr1])

    ->all();

...

We have to try to make the result of ‘all()’ as small as possible before we give it to the ArrayDataProvider.

Thank you for the guidance, @softark; your code makes sense. I’ll take a look at the possible complications you mentioned because it seems that it will consume a lot of resources (CPU/RAM) and I don’t know yet the size of the data set.

Regards,

Same problem, how to resolve this?