NotionCommotion Posted December 27, 2014 Share Posted December 27, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/ Share on other sites More sharing options...
hansford Posted December 27, 2014 Share Posted December 27, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500820 Share on other sites More sharing options...
QuickOldCar Posted December 27, 2014 Share Posted December 27, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500821 Share on other sites More sharing options...
NotionCommotion Posted December 27, 2014 Author Share Posted December 27, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500823 Share on other sites More sharing options...
QuickOldCar Posted December 27, 2014 Share Posted December 27, 2014 Stick with char(60) then, no sense doing as binary if is not binary data, would need to do string to binary conversions Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500824 Share on other sites More sharing options...
NotionCommotion Posted December 27, 2014 Author Share Posted December 27, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500825 Share on other sites More sharing options...
QuickOldCar Posted December 27, 2014 Share Posted December 27, 2014 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...). Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500826 Share on other sites More sharing options...
QuickOldCar Posted December 27, 2014 Share Posted December 27, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500827 Share on other sites More sharing options...
Jacques1 Posted December 27, 2014 Share Posted December 27, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1500831 Share on other sites More sharing options...
benanamen Posted November 3, 2015 Share Posted November 3, 2015 @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. Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1525329 Share on other sites More sharing options...
Jacques1 Posted November 3, 2015 Share Posted November 3, 2015 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/293405-recommended-sql-datatype-for-bcrypt-hash/#findComment-1525332 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.