Recommended SQL datatype for BCRYPT hash

[NotionCommotion]

http://php.net/manual/en/function.password-hash.php shows a simple example to hash a password using BCRYPT.  I've read different posts recommending CHAR(60), BINARY(60), BINARY, and even BINARY(40).

 

What are the pros and cons of using one datatype over another?

<?php
/**
 * In this case, we want to increase the default cost for BCRYPT to 12.
 * Note that we also switched to BCRYPT, which will always be 60 characters.
 */
$options = [
    'cost' => 12,
];
echo password_hash("rasmuslerdorf", PASSWORD_BCRYPT, $options)."\n";
?>  

 

The above example will output something similar to:

$2y$12$QjSH496pcT5CEbzjD/vtVeH03tfHKFy36d4J0Ltp3lRtee9HDxY3K

 

 

[hansford]

Notion,

 

I've been working on this too since our last post regarding this.

password_hash() returns a string.

The manual posts this:

/**
 * We just want to hash our password using the current DEFAULT algorithm.
 * This is presently BCRYPT, and will produce a 60 character result.
 *
 * Beware that DEFAULT may change over time, so you would want to prepare
 * By allowing your storage to expand past 60 characters (255 would be good)
 */

So I would assume we would need a varchar() or char() capable of holding 255 characters.

Edited December 27, 2014 by hansford

[QuickOldCar]

I use varchar(255) because mine vary in length

If the values are always the same length go with char, if they will vary use varchar

varchar will trim the spaces if is less characters than assigned, char will not and pad with spaces

[NotionCommotion]

Hi Hansford and QuickOldCar,

 

I ended up going away from using PASSWORD_DEFAULT, and will explicitly use PASSWORD_BCRYPT.  As such, it will always be 60 characters.  If I later elect to change algorithms, I will change the database schema at that time.

 

That being the case, I would expect I would want to use CHAR(60) or maybe some sort of BINARY.

[QuickOldCar]

Stick with char(60) then, no sense doing as binary if is not binary data, would need to do string to binary conversions

[NotionCommotion]

I would expect BINARY would be more efficient, however, wouldn't it need to be converted using either PHP's hex2bin() or MySQL's UNHEX()?

<?php
$password='rasmuslerdorf';
echo($password."<br>");
$hash=password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
echo($hash."<br>");
$binary_hash=hex2bin($hash);
echo($binary_hash."<br>");
?>

rasmuslerdorf
$2y$12$GkCH8rRmTfHuBFG/WEdPbuULI29p7vKCHlt2o7w3dkvtgeXlFsLja

Warning: hex2bin(): Input string must be hexadecimal string in /var/www/main/html/testing/hashing2.php on line 6

[QuickOldCar]

It's said that char is 50% faster than varchar, I've never done any performance tests on it.

Although if stored larger with varchar and the data is less, it will only use as large as the data is, so some space savings and room to breath, possible less problems (like if had a space and didn't trim, change in code,etc...).

[QuickOldCar]

Warning: hex2bin(): Input string must be hexadecimal string in /var/www/main/html/testing/hashing2.php on line 6

 

 

That's why having it a string would be better, don't need to deal with these issues.

[Jacques1]

A binary data type is wrong in this case, because all crypt-based hashes are ASCII-encoded strings, not raw bytes. If you (ab)use BINARY or VARBINARY for the hashes, you lose type safety and may end up with garbage data. A raw byte sequence is entirely unrestricted, but ASCII only ranges from 0x00 to 0x7F. So while your database will accept any input, you may later find out that the data you've stored isn't even valid (due to a bug, a confused admin or whatever).

 

Always choose the correct type for the content so that the database system can do its job. Don't try any hacks to optimize performance. Even if one of them actually works out and saves you some microseconds, you risk the integrity of your data. That's not worth it.

 

In this particular case, the performance argument makes even less sense:

• Password hashing is slow by design, so performance is not a concern here.
• The last thing you want is a bug in the password check, so any kind of hack is out of the question.

The correct type for bcrypt is CHAR(60). If the algorithm is unspecified, use TEXT or VARCHAR with a sufficient length (whatever that may be).

Edited December 27, 2014 by Jacques1

[benanamen]

@Jacques1,

I was just having this very discussion with another programmer and your post came up in a search trying to find an answer to the correct column type for password storage. An internet search does not provide any definitive answers as to what and why. Your post here is the only one I found that gives any reasons.

 

Can you expound on what you already said and is there any definitive sources as to the correct column type? As much as I would love to just say, "Beacuse Jacques1 says so", I need something more concrete and so far I cannot find it.

[Jacques1]

The output of bcrypt has the following pattern:

$2y$<cost>$<encoded salt><encoded hash>

The <cost> part consists of exactly two decimal digits, <encoded salt> has exactly 22 Base64 digits (to encode 128 salt bits), and <encoded hash> has exactly 31 Base64 digits (to encode 184 hash bits). That's a total of 60 ASCII characters, hence CHAR(60). The definite source is the implementation. Or the function reference.

 

Algorithms other than bcrypt may of course need a different length.

Edited November 3, 2015 by Jacques1