AES Encryption and Decryption not giving proper result

Question

SELECT AES_ENCRYPT('fmale', '2b7e151628aed2a6abf7158809cf4f3c') FROM tmp_categorywise_salary_rpt LIMIT 1; 

SELECT CAST(AES_DECRYPT('þ*Ë(|¸R°¢8Ç©', '2b7e151628aed2a6abf7158809cf4f3c') as UTF8) FROM tmp_categorywise_salary_rpt LIMIT 1;

AES_ENCRYPT is giving me encrypted value as þ*Ë(|¸R°¢8Ç© but using AES_DECRYPT is giving me null

Expectation : it should give me back non encrypted value as female

Answer 1

You are trying to decrypt the binary value, copying it manually will lead to miss come chars

you can use base64 for printing output like this

SELECT TO_BASE64(AES_ENCRYPT('fmale', '2b7e151628aed2a6abf7158809cf4f3c')) FROM tmp_categorywise_salary_rpt LIMIT 1;

SELECT CAST(AES_DECRYPT(FROM_BASE64('/irLKHwbuBBSGrCiOAzHqQ=='), '2b7e151628aed2a6abf7158809cf4f3c') AS CHAR);

As i have tested in my case

Encrypted Value : /irLKHwbuBBSGrCiOAzHqQ==

Decrypted Value : fmale