Ad

Using AES_DECRYPT In SELECT Query Builder - Laravel

- 1 answer

I'm trying to decrypt the data from mysql database provided by our partner. They encrypted it using this in mysql AES_ENCRYPT(fld_code, md5(CONCAT(fld_id, 'somekey')))

In my case, I'm trying to use the AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'somekey'))) as f1 in my laravel code.

Like this:

$query = DB::connection('db2')->table('tbsample')->select("fld_id", "AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'somekey')))")->get();

And I'm getting this kind of error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'RA3019')))' in 'field list' (SQL: select `fld_id`, `AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'RA3019')))` from `tbsample`) 

I've been searching how to use the AES_Decrypt in my query but luck is not on my side. Thank in advance.

Ad

Answer

Use \DB::raw() or selectRaw:

$query = DB::connection('db2')->table('tbsample')->select("fld_id", \DB::raw("AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'somekey')))"))->get();

Or

$query = DB::connection('db2')->table('tbsample')->selectRaw("fld_id, AES_DECRYPT(fld_code, md5(CONCAT(fld_id, 'somekey')))")->get();
Ad
source: stackoverflow.com
Ad