MySQL Password Hashing in .NET

As I was surfing through StackOverflow today, I noticed a question that got me thinking. This developer had been working with a classic ASP application that used MySQL for it’s backend database. The project on his plate was to convert this application to ASP.NET with MS-SQL as the database. The only problem he encountered was the fact that passwords were stored in the database using a MySQL-specific hashing algorithm called via the PASSWORD() function. He needed a way to convert these passwords to .NET.

Given that I have a MySQL database laying around, I decided to poke around in the source code to see exactly how MySQL’s PASSWORD() function really works. It didn’t take me long to find inside the file “libmysql/password.c” this little gem:

/*
    MySQL 4.1.1 password hashing: SHA conversion (see RFC 2289, 3174) twice
    applied to the password string, and then produced octet sequence is
    converted to hex string.
    The result of this function is used as return value from PASSWORD() and
    is stored in the database.
  SYNOPSIS
    make_scrambled_password()
    buf       OUT buffer of size 2*SHA1_HASH_SIZE + 2 to store hex string
    password  IN  NULL-terminated password string
*/

void
make_scrambled_password(char *to, const char *password)
{
  SHA1_CONTEXT sha1_context;
  uint8 hash_stage2[SHA1_HASH_SIZE];

  mysql_sha1_reset(&sha1_context);
  /* stage 1: hash password */
  mysql_sha1_input(&sha1_context, (uint8 *) password, (uint) strlen(password));
  mysql_sha1_result(&sha1_context, (uint8 *) to);
  /* stage 2: hash stage1 output */
  mysql_sha1_reset(&sha1_context);
  mysql_sha1_input(&sha1_context, (uint8 *) to, SHA1_HASH_SIZE);
  /* separate buffer is used to pass 'to' in octet2hex */
  mysql_sha1_result(&sha1_context, hash_stage2);
  /* convert hash_stage2 to hex string */
  *to++= PVERSION41_CHAR;
  octet2hex(to, (const char*) hash_stage2, SHA1_HASH_SIZE);
}

As you can see, the password algorithm uses a double-SHA1 hash to format the password. The other little tidbit is that any passwords generated post-4.1 will be pre-pended with an asterisk (*). Given that I have spent a fair amount of time recently working with cryptography in .NET, it didn’t take long to adapt this code to C#. The following function produces the same output as the MySQL PASSWORD() function.

public string GeneralteMySQLHash(string key)
{
    byte[] keyArray = Encoding.UTF8.GetBytes(key);
    SHA1Managed enc = new SHA1Managed();
    byte[] encodedKey = enc.ComputeHash(enc.ComputeHash(keyArray));
    StringBuilder myBuilder = new StringBuilder(encodedKey.Length);

    foreach (byte b in encodedKey)
        myBuilder.Append(b.ToString("X2"));

    return "*" + myBuilder.ToString();
}

I hope this helps someone out there. I know it was kinda of fun to put on my super-slueth hat for a while :) .

Update 5/29/2009: I found a neat way to do this in T-SQL also. It’s a bit ugly, but it works!

SELECT '*' + SUBSTRING(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1',HASHBYTES('SHA1','VALUE-TO-ENCRYPT-GOES-HERE'))),1,42)

Tags:

This entry was posted on Friday, May 15th, 2009 at 12:26 pm and is filed under Development, Useful Tricks. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.