SUBSTRING call used in the Postfix/Dovecot/MySQL guide
Email with Postfix, Dovecot, and MySQL
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`password` varchar(106) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and here is the query used to insert data:
INSERT INTO `mailserver`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('firstpassword', CONCAT('$6/r>, SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
('2', '1', ENCRYPT('secondpassword', CONCAT('$6/r>, SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');
After doing some reading I think I have a very basic understanding of what's happening:
4. A random number is generated
A SHA1 hash is created from it
The SUBSTRING function throws away the first 16 characters and keeps the rest of the hash string
The remaining characters in the hash string are appended to the literal string '$6/list>
That combined value is used as the salt for the ENCRYPT function
The resulting value is stored in the password field.
Assuming I have that right, why is that password encryption routine (for the lack of a proper term) throwing away some of the characters? Is it so the resulting password will always fit in the varchar(106) field?
Thanks in advance for your help!
References
http://stackoverflow.com/questions/24186158/sha512-crypt-mysql-and-dovecot
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring
http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_encrypt
http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha1
http://stackoverflow.com/questions/24186158/sha512-crypt-mysql-and-dovecot
http://stackoverflow.com/questions/16485906/mysql-encrypt-password-but-how-to-decrypt-it/16485963#16485963
7 Replies
SHA-512 password hashes
Sixteen characters of the SHA-1 hash of the random number are the salt
The password field stored in the database is "$id$salt$hashedpassword" - it includes other information besides just the hashed password. So the name may be slightly misleading.
Thanks for the correction and the thorough explanation. I was going to post a follow-up question and ask if you knew why the SHA() function was called against the result of RAND(), but I think I figured it out. My initial thought was that the result of RAND() was a random character and running SHA() against that would strip out non-alphanumeric characters and actually weaken the value used as the salt, but it's somewhat the other way around: The result of RAND() is a floating-point number which is then hashed to provider a stronger salt.
Assuming I'm right, I think I see what's going on now. Very clever.
Thanks again for the explanation, I appreciate it.
I notice that the password field length is set to 106 characters. Looking around online I see several mentions that a SHA-512 hash has a length of 128. However I can clearly see that the output from the provided MySQL query is 106 characters every time.
3 characters for the '/r> separators
1 character for the password ID value
86 characters for the password
16 characters for the salt
Any idea where others are getting 128 from?
That example PHP code I found prints 128. Am I seeing a limitation of crypt() on my system or is MySQL storing the 128 length hash in a 106 character field somehow?
Looking around further I found this page
> Good catch, it actually needs to be varchar(106) to work on Linux systems. I use this all the time in my setups but my documentation here had a typo. You might also prefer to use char(106), since the password will always be fixed at 106 characters if you use the SQL statement listed above to create new users.
The way you arrive at 106 characters is: 86-character encrypted password + 4 separator characters + 16-character salt.
As for why the password is always 86 characters, the full ENCRYPT(‘firstpassword’, CONCAT(‘$6$’, SUBSTRING(SHA(RAND()), -16))) statement tells MySQL to:
a) Generate a random floating point value between 0 and 1.0
b) Calculate an SHA1 checksum for the random value, expressed as a string of 40 hexadecimal digits, e.g., ‘a9993e364706816aba3e25717850c26c9cd0d89d’
c) Select a substring of (b), starting with the character in the 25th position and running to position 40 (16 total characters)
d) Concatenate ‘$6$’ and your substring from ©, so you end up with a value like ‘$6$7850c26c9cd0d89d’
e) Encrypt ‘firstpassword’ into a binary string using your concatenated randomized value from (d) as the salt. Per the MySQL documentation, ENCRYPT() relies on the crypt() Unix system call, so results will vary by platform, but on current Linux systems this gives you a string like ‘$6$7850c26c9cd0d89d$encrypted-password’. On Linux systems, the ‘$6$’ appended to the salt also tells crypt() to use SHA-512 encryption, giving you an 86-character encrypted string (see the crypt man page at
http://man7.org/linux/man-pages/man3/crypt.3.html ). So now you have ‘$6$’ + 16-character salt + ‘$’ + 86-character encrypted password = 106 characters.f) Return the final result to the INSERT statement.
It's basically what you already said earlier, but said slightly different. Unfortunately it still doesn't answer the question of where the additional 22 characters comes from.
Is this a case where the crypt() system call (at least for my distro) has this limitation whereas PHP does not?
hash()
You don't really need that though if the password is strong enough anyway, but you can use a TEXT column in MySQL to not worry about the length.