CakePHP 4 use aes_encrypt at SQL level

[gildonei]

I need to use aes_encypt(field, key) and aes_decrypt(field, key) in my project. How can I add it in my table or entity in a way it can works automatic like a virtualField.
Sample
$table->find([‘fields’ => [‘encrypted’]);…

and it get converted to SELECT AES_DECRYPT(‘encrypted’, ‘key’)…

[raul338]

create a finder in a table like this

public function findEncrypted(Query $query, array $options): Query
{
    return $query->select([
        'a' => $query->func()->AES_DECRYPT([
            'field_a' => 'identifier',
            'key' => 'literal',
        ]),
    ]);
}

You can parametrize it to make it more dynamic.

See Custom SQL functions.