select row from sql where column is AES

I have a laravel sql query to update a binary column here DB::statement("UPDATE user SET auth = AES_ENCRYPT('748b28e7c414f460607cd8', 'PASS') WHERE id = '3045948e-5521-41aa-bb76-4ad290f39053'"); This update works.

I want to fetch the row this way, but I keep getting an empty array. Am I missing something?

DB::select("SELECT * from user WHERE auth = aes_decrypt('748b28e7c414f460607cd8', 'PASS')");

Basically, I want to encrypt the value that I will insert into the auth column and also fetch a row when I pass the decrypted value into the select statement. Is there a better way to go about this?

CodePudding user response：

If you want to query for the value you previously put in the column, then use the same function you used when you set that value.

SELECT * from user WHERE auth = aes_encrypt('748b28e7c414f460607cd8', 'PASS');

Alternatively, decrypt what you stored in the column and compare it to the same input.

SELECT * from user WHERE aes_decrypt(auth, 'PASS') = '748b28e7c414f460607cd8';

But the first example is preferred, because it can be optimized with a plain index.