How should I store SHA512 hash and PASSWORD_BCRYPT in MySQL?

Question

I try to figure a way to store SHA512 generated hash in my database. I've already searched on google "sql store hash" but nothing matched, so... should I use binary? perhaps varbinary? perhaps something else? what about password_hash(..., PASSWORD_BCRYPT) - how should I store this one? through varchar?

Many thanks

The `password_hash()` can generate some very lengthy text (the current default is 60 characters), so making the field larger now will allow for the length needed. Secondly the PHP team is adding more algorithms to the method which means the hash can and will grow. We also do not want to limit our user's ability to use the password or passphrase of their choice. It's best to leave room for the changes. I recommend `TEXT` to allow for any length updated PHP hashing methods may generate. — Jay Blanchard, Jan 30 '17 at 21:30
everything that you posted relates to passwords, yet in a comment below you state: *"SHA512 is not used for storing password in my project "* - what is this used for then? `password_hash()` is for passwords, 99.9% of the time. — Funk Forty Niner, Jan 30 '17 at 21:33
@Fred-ii- e.g. someone requests password reset / account activation so I need a temp key, and then I use SHA512 (for the token) — , Jan 30 '17 at 21:34
then don't use any of those then. `$random_hash = md5(uniqid(rand(), true));` is good enough for that. — Funk Forty Niner, Jan 30 '17 at 21:34
SHA512 also generates a string, typically 128 characters wide. — Jay Blanchard, Jan 30 '17 at 21:34
I always allow a little extra, so 132 should be good. All of this information is easily found online. — Jay Blanchard, Jan 30 '17 at 21:35
Frank. just because you see MD5 in my example doesn't mean it can't be used for a one-time activation key in conjunction with `uniqid(rand())`. — Funk Forty Niner, Jan 30 '17 at 21:39
for a password reset its as secure as any random 32-character hexadecimal number — , Jan 30 '17 at 21:41
TBH, I don't know where to throw myself here *lol*. You want to generate a one-time/unique activation key, and/or store a password? — Funk Forty Niner, Jan 30 '17 at 21:41
@Fred-ii- one-time/unique of course. i'm not stupid. but what's the difference if both sha512 and md5 are okay for one-time key? — , Jan 30 '17 at 21:42
sorry bro but you'll have to take it up with the answers below. I don't know what you want, sorry. BTW, I didn't say you were stupid, I just don't know what you want. — Funk Forty Niner, Jan 30 '17 at 21:43
If you're actually *not* trying to set passwords and just need a one time activation key, such as those used by email links to reset passwords, you could use `base64_encode` and `base64_decode` functionality to obscure the data previously set with your `md5`/[`random_bytes`](http://php.net/manual/en/function.random-bytes.php)/[`random_int`](http://us2.php.net/manual/en/function.random-int.php) (or similar) functionality — Martin, Jan 30 '17 at 21:45
@Fred-ii- no, it's alright I wasn't mad I just wanted to ask if there is any difference between the two, I mean, if they're stored in the same way so does it really matter if it's md5 or SHA512? I just ask, not mad or angry, just to learn and do the right thing — , Jan 30 '17 at 21:46
if its onetime\unique why then accept the answer dealing with passwords? — , Jan 30 '17 at 21:49
@nogad never mind my english sucks and I don't really find a good way to explain myself so... no accepted answer*. but I got my answer so thank you guys anyway — , Jan 30 '17 at 21:51
@FrankGallagher Not my downvote for the question; I removed something about those in an edit. That is a sure fire way of getting those though. — Funk Forty Niner, Jan 30 '17 at 22:00
@FrankGallagher you can write your own answer, detailing the information you actually found `:-)` — Martin, Jan 31 '17 at 09:49

Niclas M · Answer 1 · 2017-01-30T21:33:13.617

2

I would not recommend SHA512 since it's pretty fast and bruteforcing won't take as long.
My recommendation is password_hash() using PASSWORD_BCRYPT, which returns a 60 character long string. So varchar(60) would be the way to go.

If you're at it use PASSWORD_DEFAULT (currently bcrypt) though and make the varchar a little bigger if it changes. This will hopefully keep you using a secure hashing algorithm.
As @nogad pointed out in his comment: 255 should be a good size for the field in the database.

edited Jan 30 '17 at 21:33

answered Jan 30 '17 at 21:29

Niclas M

192
2
9

SHA512 is not used for storing password in my project – Jan 30 '17 at 21:30
1

**Therefore, it is recommended to store the result in a database column that can expand beyond 60 characters (255 characters would be a good choice).** http://php.net/manual/en/function.password-hash.php – Jan 30 '17 at 21:30
Better yet when using PHP use `password_hash` and `password_verify`. – zaph Jan 30 '17 at 23:27

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

Password hashes can be stored in pretty much by any field that can store a full string character set, typically a good idea to use a field that supports UTF-8 (any form, even MySQL UTF-8), so you can use VARCHAR, or TEXT fields to store the string, as that's all it is, is a string. It's not special.

There is a length issue that the column will need to be long enough to store the data and not Truncate it. Off the top of my head I think PHP password_hash using PASSWORD_BCRYPT is limited to 60 characters, but other hashing mechanisms will be longer.

ALTER TABLE <name> ADD `passwd` TEXT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci NOT NULL ;

As a side note password is a MySQL Keyword and while not reserved, it can make things easier to maintain and easier for your IDE to understand if you don't name the column password.

edited May 23 '17 at 12:16

Community

1
1

answered Jan 30 '17 at 21:34

Martin

22,212
11
70
132

*"@Fred-ii- e.g. someone requests password reset / account activation so I need a temp key, and then I use SHA512 (for the token) – Frank Gallagher"*. – Funk Forty Niner Jan 30 '17 at 21:36
@Fred-ii- for all intents and purposes, that activity is still the same action as a password would have. If someone has that key, they have access to that account (for instance being able to hijack password reset PHP files). – Martin Jan 30 '17 at 21:37
it's just for a one-time activation key. `password_hash()` sha512 IMHO, is overkill. – Funk Forty Niner Jan 30 '17 at 21:38
@Fred-ii- Who doesn't love a bit of [overkill](https://www.youtube.com/watch?v=BEYwNeBD4Uk) from time to time :) – Martin Jan 30 '17 at 21:39
`VARCHAR(255)` works in almost all cases. `TEXT` is not a good choice here. – tadman Jan 30 '17 at 22:39
I do agree with that @tadman and my original thought was to use a VARCHAR column, but without knowing the specific hash the user wants to use, I felt it easier to leave the option open and not have them need to worry about length at all. People who use VARCHAR often feel a need to give it "just enough" length so are more likely to give a length that fits `BCRYPT` (60) but that would then truncate `sha512`, for instance. – Martin Jan 31 '17 at 09:48

How should I store SHA512 hash and PASSWORD_BCRYPT in MySQL?

2 Answers2