CODEWITHSUNDEEP
mysqlsql
ateenraw
ateenraw

Reputation: 51

AES_ENCRYPT in mysql returns '

I'm using the following query to get AES encrypted value:

SELECT AES_ENCRYPT( 'REMYA UNNIKRISHNAN', 'nirvana1987' ) AS encString

and I'm getting the value as: 

'UT�\Z��*���F��R�đtFn�X/�~J�

now when I try to enter this value to another table it shows error in SQL syntax becuase of the precceeding '. I have to decrypt the values again to display it. Does anybody have any suggestion on how to solve this issue? Any other encryption which doesn't returns ' or any way to store the encrypted value in database without removing the single quote?

Upvotes: 1

Views: 3072

Answers (1)

Balazs Vago
Balazs Vago

Reputation: 651

The AES_ENCRYPT and AES_DECRYPT functions use and returns binary data that is most probably not usable in a copy-paste style when displayed in your browser (for example phpmyadmin).

You must pass return value of both functions to column that can store binary data, or encode the return value of the functions to hexadecimal representation.

To be able to copy-paste the data returned, you may use the following SQL query

SELECT HEX( AES_ENCRYPT( 'REMYA UNNIKRISHNAN', 'nirvana1987' ) ) AS encString

This will return: 275554F1BABE1AAEEE0F2AEBBDDEC146EFFA52F3C49174466EA5582FCF7E4AFA

Now to decrypt this value again in SQL, you can use the query

SELECT AES_DECRYPT( UNHEX( '275554F1BABE1AAEEE0F2AEBBDDEC146EFFA52F3C49174466EA5582FCF7E4AFA' ) , 'nirvana1987' )

This will give you back the original string.

Now to store the encrypted data in a column, the column must be BLOB, BINARY or their respective storage formats. Let us assume you have a column "encData" in table "myData". You may use the following query to store it into

INSERT INTO myData (encData) VALUES (AES_ENCRYPT( 'REMYA UNNIKRISHNAN', 'nirvana1987' ))

To retrieve the decrypted information stored in the table you may execute the query

SELECT AES_DECRYPT(encData, 'nirvana1987') as decString FROM myData

Upvotes: 7

Related Questions