How to use AES_ENCRYPT properly?

Question

I'm trying to use AES encryption (AES_ENCRYPT in MySQL) for user passwords but I came up with a bunch of different problems.

This is the SQL query that I use to store a new user into the database:

INSERT INTO user VALUES (
    '15',
    'John',
    'Doe',
    '123 Fake St.',
    AES_ENCRYPT('mypassword', 'mysalt'),
    'mysalt'
)

Where the salt would be a random string in a real case.

It works fine. I mean, I'm able to retrieve the original password. In this example, AES_DECRYPT(user.password, 'mysalt') WHERE user.id = 15 retrieves mypassword. But I might be overlooking some things.

Is it secure to save the salt along with the password? Aside from security through obscurity thing.
What is the best format to store the hashed password? I'm using
VARBINARY but the stored string looks like 8�p��_�Z�\.
And finally, how long should the password be and how long should the salt be?

Thanks

You don't want to encrypt passwords, you want to crypto-hash them with an expensive algorithm like [PBKDF2](http://en.wikipedia.org/wiki/PBKDF2), [bcrypt](http://en.wikipedia.org/wiki/Bcrypt), or [scrypt](http://en.wikipedia.org/wiki/Scrypt) — see http://security.stackexchange.com/a/6415/27154 — Mark Fox, Nov 02 '13 at 05:30
@MarkFox I asked this question long ago. Nowadays I use [PHPass](http://www.openwall.com/phpass/), which uses bcrypt if I recall correctly. — federico-t, Nov 03 '13 at 21:12

score 7 · Accepted Answer · answered Apr 02 '12 at 06:17

7

Typically, there is no actual need to reverse encrypt a password. Having that ability inherently decreases the security of the system. Instead, use an irreversible hash function. I suggest SHA-256 (or larger) which produces a string result:

 SHA2 (CONCAT (user.name, user.password, 'some salt', user.id), 256)

I have also frustrated bulk rainbow tables from being any use by rolling in other data always known at password validation time.

SHA2 requires MySQL 5.5 or later. If you are using an earlier version, SHA1() is nearly as good, and generally much better than MD5, AES, etc.

answered Apr 02 '12 at 06:17

wallyk

56,922
16
83
148

1

Good sugestion, an irreversible hashing function is probably better in this case. One last question, should the salt be stored along with the hashed password? And it has to be a different salt for every password? – federico-t Apr 02 '12 at 19:34
@JohnDoe: If you do use a salt, there has to be some way of knowing what it is at password verification time. Storing it is a pretty easy way. Also, if using my suggestion of including `username` and `id`, a salt is much less important. Without those, a unique salt value per user is highly recommended. Otherwise a single rainbow table could be used for all users. – wallyk Apr 02 '12 at 20:37

score 2 · Answer 2 · edited May 23 '17 at 11:46

2

Please consider using a password hash instead of a cryptographic hash. The goals are different. See https://security.stackexchange.com/a/6415/25424 for more info. Password frameworks like what are mentioned on https://stackoverflow.com/a/6337021/516813 take care of a lot of details for you like the salting.

edited May 23 '17 at 11:46

Community

1
1

answered May 12 '13 at 21:51

chacham15

13,719
26
104
207

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

You should not just encrypt the password in database, but store a presentation of the password in the database.

See this question for lengthy explanation.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 02 '12 at 06:06

Kimvais

38,306
16
108
142

How to use AES_ENCRYPT properly?

3 Answers3

Linked