Laravel - 在where子句列中使用mysql函数

Question

I have a database with encrypted field using AES_ENCRYPT

the data is encrypted and should stay this way, so there is no way of changing the method it is encrypted with.

Recently I started working on a new project with Laravel. when I tried to do an update with a where clause on the field encrypted with AES, I received the following query:

SELECT * FROM `users` WHERE `mail` = ? LIMIT 1

And the following binding:

AES_ENCRYPT('[e-mail]', 'somekey')

For the following code:

$email = Input::get('email');
$user = User::where("mail", "AES_ENCRYPT('{$email}', '{$this->aesKey}')")->first();

Which is basically logical - But how do I use force it do the following Query:

SELECT * FROM `users` WHERE `mail` = AES_ENCRYPT(?, 'someKey') limit 1

With this binding:

[e-mail]

Hope there is a way passing function to the where clause on the value

Answer 1

In laravel 5 you can use whereRaw with bindings too, so you get correct escaping and injection prevention.

Just pass bindings array as second parameter. For example:

$user = User::where("mail = AES_ENCRYPT(:email, :aesKey)", ['email' => $email, 'aesKey' => $this->aesKey])->first();