CODEWITHSUNDEEP
mysqlencryptionsqldatatypes
Saqib
Saqib

Reputation: 1129

using AES_ENCRYPT with VARCHAR in mysql

I am using AES_ENCRYPT() and AES_DECRYPT() for encription and decription of mysql data. Things are working fine no matter if I use VARCHAR() or VARBINARY() but this is just for small amount of data I am checking in. I have records which are already in VARCHAR() format and I prefer them to be as it is plus the detail I want to encrypt against those users are their Contacts and SMS, (which can as many as they want, to give you idea about possible size of data). Now if I use VARCHAR() with reasonable amount of characters length, would it be better to stick with VARCHAR() thing or somewhere/sometime I would have to shift to VARBINARY() due to some reasons or problems? Please give me good suggestions so that I leave this issue of VARBINARY() behind and keep using my VARCHAR() columns type ahead. Issue that made me to thing about it is when I google about it, I found mixed response from users, some say user VARCHAR() and some VARBINARY() while some has no issue at all and more worrying thing that MYSQL documentation prefers or what I say, VARBINARY(). I know that could be the reason because of data returns is in BINARY/BLOB fromat, but then why VARCHAR() type allowed at all? Hope will clear the idea of my question, thanks

Upvotes: 1

Views: 3132

Answers (1)

eggyal
eggyal

Reputation: 125965

As documented under AES_ENCRYPT():

AES_ENCRYPT() encrypts a string and returns a binary string.

It seems pretty clear to me that the result should be stored in a binary string type column, i.e. VARBINARY.

As for problems that could arise from using VARCHAR:

  1. depending on your character set, it's possible that some byte sequences are invalid and may be rejected; and

  2. it's possible that unintentional character set conversion may munge your ciphertext.

Upvotes: 3

Related Questions