Simulating MySql's password() encryption using .NET or MS SQL

Question

I'm updating an old ASP/MySql webapp to ASP.NET/MS SQL.

We would like to keep the logins from the old website working in the new app.

Unfortunately the passwords were stored in the MySql DB using MySql's password() function.

Is it possible to simulate MySql's password() function in either .NET or MS SQL?

Any help/links are appreciated.

Answer 1

According to MySQL documentation, the algorithm is a double SHA1 hash. When examining the MySQL source code, you find a function called make_scrambled_password() in libmysql/password.c. The function is defined as follows:

/*
    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);
}

Given this method, you can create a .NET counterpart that basically does the same thing. Here's what I've come up with. When I run SELECT PASSWORD('test'); against my local copy of MySQL, the value returned is:

*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

According to the source code (again in password.c), the beginning asterisk indicates that this is the post-MySQL 4.1 method of encrypting the password. When I emulate the functionality in VB.Net for example, this is what I come up with:

Public Function GenerateMySQLHash(ByVal strKey As String) As String
    Dim keyArray As Byte() = Encoding.UTF8.GetBytes(strKey)
    Dim enc = New SHA1Managed()
    Dim encodedKey = enc.ComputeHash(enc.ComputeHash(keyArray))
    Dim myBuilder As New StringBuilder(encodedKey.Length)

    For Each b As Byte In encodedKey
        myBuilder.Append(b.ToString("X2"))
    Next

    Return "*" & myBuilder.ToString()
End Function

Keep in mind that SHA1Managed() is in the System.Security.Cryptography namespace. This method returns the same output as the PASSWORD() call in MySQL. I hope this helps for you.

Edit: Here's the same code in C#

public string GenerateMySQLHash(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();
}

Answer 2

You can encrypt strings using MD5 or SHA1 in .Net, but the actual algorithm used by MySQL is probably different to these two methods. I suspect it is based on some kind of 'salt' based on the instance of the server, but I don't know.

In theory, since I believe MySQL is open source you could investigate the source and determine how this is done.

http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

Edit 1: I believe the algorithm used is a double SHA1 with other 'tweaks' (according to this blog post).