Jump to content

Recommended SQL datatype for BCRYPT hash


NotionCommotion

Recommended Posts

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

 

 

Link to comment
Share on other sites

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 by hansford
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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...).

Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

  • 10 months later...

@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.

Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.