5

I have a piece of information which is encoded using aes-256-cbc encryption. How should I store it in the database? Currently I'm using VARCHAR(255) utf8_bin. Is this OK or should I use other field type like VARBINARY(255)? Is there a possibility of losing some data using VARCHAR in this case? Thanks.

superjos
  • 10,834
  • 4
  • 79
  • 120
Rafael Sedrakyan
  • 2,171
  • 9
  • 31
  • 41
  • 4
    You should never encrypt your user's passwords. You need to use hashing instead with some strong ones being PBKDF2, bcrypt and scrypt. Since hash functions are one-way function, you won't be able to "decrypt" the hashes. In order to authenticate your user, you can run the password through the hash function again in order to compare with the hash that is stored in the database. See more: [How to securely hash passwords?](http://security.stackexchange.com/q/211/45523) – Artjom B. Nov 13 '15 at 21:24
  • 2
    It's not possible to stress this enough: **do not EVER store passwords**. – xxbbcc Nov 13 '15 at 21:30
  • 2
    I never said I'm encrypting user's account password. It's just some sensitive data that needs to be stored not in a plain text. A nice to have feature for our users. – Rafael Sedrakyan Nov 13 '15 at 21:33
  • 3
    @RafaelSedrakyan You said exactly _"I have a password field..."_ – xxbbcc Nov 13 '15 at 22:57
  • 3
    @xxbbcc Let's say user defines a password for a virtually generated machine on a hypervisor and our application needs to read it in order to setup such a machine. We need to keep this data not in a plain text format. That is why I'm saying it is not an account(profile) password. And to be more clear on that I edited my question and removed the 'password' part not to cause misunderstanding. – Rafael Sedrakyan Nov 13 '15 at 23:07
  • See the tag wiki for [tag:password-encryption] for why you shouldn't do this, and all the discussions in [this question](http://stackoverflow.com/questions/2283937/how-should-i-ethically-approach-user-password-storage-for-later-plaintext-retrie/2287672#2287672). – user207421 Nov 13 '15 at 23:36

1 Answers1

10

The possible (in)appropriateness of storing encrypted (as opposed to hashed) passwords in a database notwithstanding, AES ciphertext is binary data, and therefore should be stored as such, i.e. in a BINARY / VARBINARY column or a BLOB.

It's also possible to encode the ciphertext e.g. as base64, and then store it in a text (i.e. CHAR / VARCHAR / TEXT) column. This is less space-efficient, but it may sometimes be more convenient, e.g. when inspecting the data visually or passing it between programs that may have trouble dealing with fields containing arbitrary binary data.

Community
  • 1
  • 1
Ilmari Karonen
  • 44,762
  • 9
  • 83
  • 142
  • I'm trying to understand what are the consequences of storing such data as UTF8 text. varchar -> text string; varbinary -> binary string. Why shouldn't I keep it as UTF8 text if it's a string anyway. – Rafael Sedrakyan Nov 13 '15 at 23:00
  • 2
    AES ciphertext can contain byte sequences that are not valid UTF-8, so I would not attempt to treat it as such. I have not tested to see if any data corruption *will* occur, but there's no guarantee that it *won't*. Using `VARCHAR` with a single-byte character set like `latin1` *may* be safer, but again, I would not use it, given that a more appropriate tool (`VARBINARY`) is available. – Ilmari Karonen Nov 13 '15 at 23:06