How to use sql method “AES_DECRYPT” in afterFind in YII2

Question

I need to use sql method of AES_DECRYPT but string is not decrypt.

it's working in sql but not in yii2 afterfind method

it return response like below image.

This is below db table image.

 public function afterFind() {
            parent::afterFind();

            //$this->name = new Expression('AES_DECRYPT("'.$this->name.'", "key test")  as name ');
            $this->name = new Expression('cast(AES_DECRYPT("'.$this->encrypt_name.'", "key test") as char) as name');

            //return true;

        }

`AES_DECRYPT` not going to work in `afterFind()` as it has has to be processed by MySql (you _could_ use PHP but then you would most likely want to also encrypt it in PHP). Can you use the `AES_DECRYPT` Expression when fetching your data by modifying the `select` query or similar? — ldg, Apr 04 '18 at 15:20
deos this help you https://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql/31339955 — Muhammad Omer Aslam, Apr 04 '18 at 17:44
what is the exact error? what exact query was formed? just have a look at the executed query. you can find the executed query in the debug toolbar. — R13e, Apr 05 '18 at 07:16
@R13e Please see db table screenshot. I will need exact query in db table scrrenshot. How i put AES_DECRYPT into afterfind method. — Bhavin Thummar, Apr 05 '18 at 09:12
I think you misunderstood my questions and the concept of `afterFind`. You want to decrypt during the query or afterwards? If you call `afterFind()` in the model, you do it **after** the query was executed. So, using a class like yii\db\Expression on some already populated attribute makes no sense. — R13e, Apr 05 '18 at 12:03

score 3 · Answer 1 · answered Apr 07 '18 at 20:29

afterFind() is called after SQL query was performed, it is too late to modify query and use SQL expressions. You should modify select fileds before you call one() or all().

return MyModel::find()
    ->addSelect(['cast(AES_DECRYPT(encrypt_name, "key test") as char) as name'])
    ->one();

You may override find() method in your model to do this automatically for every query:

public static function find() {
    return parent::find()
        ->addSelect(['cast(AES_DECRYPT(encrypt_name, "key test") as char) as name']);
}

You may reconsider not using AES_DECRYPT at all. When it comes to encryption, for security reasons, it is better to encrypt as soon as possible and decrypt as late as possible. Moving encryption at SQL level introduces several threats:

Your SQL query could be logged (in error or slow-query log) or displayed in exception message. This will reveal your encryption key, making whole encryption quite pointless.
Since you're sending encryption key and unencrypted data between PHP and MySQL server, the attacker can get access to them using MITM attack.
Compromising SQL server will give attacker acces to encrypted data, since you're sending encryption key quite often in SQL query.

You can avoid this by encrypting and decrypting data at PHP level (using Yii security component or some library). You can make this transparent by creating virtual attribute via setter and getter:

public function getName() {
    return $this->decrypt($this->encrypted_name);
}

public function setName($value) {
    $this->encrypted_name = $this->encrypt($value);
}

Then you can access unencrypted data by $model->name.

Thank you sir your answer is also right but other person have already answered me. — Bhavin Thummar, Apr 09 '18 at 05:25

score 1 · Accepted Answer · answered Apr 05 '18 at 13:34

1

I hope this is useful for you :

    public function afterFind() {
        $this->name = (new Query)->select(['AES_DECRYPT("'.$this->encrypt_name.'", "key test") as name'])->scalar();
        return true;
    }

answered Apr 05 '18 at 13:34

Harsh Panchal

308
1
8

Thanks sir for your great help. – Bhavin Thummar Apr 05 '18 at 13:36
This will create extra query for each model (displaying 50 models on one page will give you 50 additional queries). Take a look at [my answer](https://stackoverflow.com/a/49711680/5812455) which should do all the magic in one query. – rob006 Apr 07 '18 at 20:32
Also you should call `parent::afterFind()` instead of `return true`. – rob006 Apr 07 '18 at 20:33

How to use sql method “AES_DECRYPT” in afterFind in YII2

2 Answers2