SUBSTRING call used in the Postfix/Dovecot/MySQL guide

I followed the directions in the Email with Postfix, Dovecot, and MySQL Linode Library guide several months ago and everything is working well so far. One thing I never fully understood was the encryption process used when setting user passwords. Here's the table schema:

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

  1. A SHA1 hash is created from it

  2. The SUBSTRING function throws away the first 16 characters and keeps the rest of the hash string

  3. The remaining characters in the hash string are appended to the literal string '$6/list>

  4. That combined value is used as the salt for the ENCRYPT function

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

* https://library.linode.com/email/postfix/postfix2.9.6-dovecot2.0.19-mysql#sph_adding-data

7 Replies

The $6$ at the beginning of the string indicates that SHA-512 password hashes are to be used.

Sixteen characters of the SHA-1 hash of the random number are the salt. A maximum of 16 characters are used as salt in the SHA-512 hashing algorithm, so there's no reason to store more. (The $6$ is not really used as part of the salt; it's just there to indicate the hash type to use.) This choice was made by the designers of the SHA-512 hashing algorithm.

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.

Vance,

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.

Vance,

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?

Vance,

Looking around further I found this page where Geoff had this to say:

> 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?

Sorry for the long delay. If you're still around, the password hashing algorithm base-64 encodes the hash, while PHP's hash() function outputs it as hexadecimal digits.

Thanks Vance. I appreciate you taking the time to confirm the details!

You can also strengthen the password security (number of rounds): https://forum.linode.com/viewtopic.php?f=11&t=10558#p61282

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.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct