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

Question

Hi guys,

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

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

Answer 2

You will not be able to simulate the MySql password() function. at least not without a lot of work and luck. Being that MySql uses their own algorithm to one-way encrypt strings, they are not going to disclose what algorithm they use; which means you would have to do trial and error, compare resulting strings, etc.

To fix your issue:

I'd replicate the DB but maintain a connection with MySql. Use a flag to identify a first time login. Ask them to use their current password; verify password with MySql password() function. If password is valid; ask them to reset it, either by entering the same password again, or a new one; at this point use your favorite encryption algorithm and store the result in your MS Sql DB.

I know it sounds like a pain, but it is the best you can do short of trying to hack your way into deciphering MySql encryption algorithm.

Good luck!

Answer 3

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("X"))
    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("X"));

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

Answer 4

For VB net, this pads with 0's to the left so that when the hex code is [for example] 'E', this function will correctly append '0E' to the result string. So that in the end, including the pre-pended '*' the resulting string will be 41 character long, as is required in MySQL

Public Function GenerateMySQLHash(ByVal strKey As String) As String

    Dim keyArray As Byte() = System.Text.UTF8Encoding.UTF8.GetBytes(strKey)
    Dim enc = New Security.Cryptography.SHA1Managed()
    Dim encodedKey = enc.ComputeHash(enc.ComputeHash(keyArray))
    Dim myBuilder As New System.Text.StringBuilder(encodedKey.ToString.Length)

    For Each b As Byte In encodedKey
        myBuilder.Append(Strings.Right("0" & b.ToString("X"), 2))
    Next

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

Answer 5

"they are not going to disclose what algorithm they use"

erm ... someone should wake and tell you that MySQL is OPEN SOURCE ... they dont need to "disclose" something because everybody can read the very structure of the database - just download the sources, search for the encryption-functions (i guess they're using C++) and start investigating ... SHA1 seems pretty insecure now - just recently a few scientists have proven that it is possible to choose up to 20% of the data without limitations and produce the same hash so one would be advised to use AES or SHA2 (therefore not the MySQL-PASSWORD-func)

Answer 6

the mysql password functions is not particularly secure (eg there's no salt or other method of protection against looking for matching hashes) the only reasons I can see for wanting to duplicate the algorithm are 1: you have a list/table of users with mysql password()s 2: you want to crack mysql passwords from the hashes in the user table.

Else use something different, eg a hash that combines both the user's name (and/or some other row invariant) and their password. thay way if two users pick the same password it won't be obvious. and if the table gets exposed the security does not decrease at the same rate (eg google for 446a12100c856ce9 which is the password() hash of a very popular password)

Answer 7

C# version modified to correct padding issue. Thanks Scott for all the initial hard work :D

    [Test]
    public void GenerateMySQLHashX2()
    {
        string password = "20iva05";

        byte[] keyArray = Encoding.UTF8.GetBytes(password);
        SHA1Managed enc = new SHA1Managed();
        byte[] encodedKey = enc.ComputeHash(enc.ComputeHash(keyArray));
        StringBuilder myBuilder = new StringBuilder(encodedKey.Length);

        foreach (byte b in encodedKey)
        {
            log.Debug(b.ToString() + "    ->   " +  b.ToString("X2"));
            myBuilder.Append(b.ToString("X2"));
        }

        Assert.AreEqual("*8C2029A96507478FD1720F6B713ADD57C66EED49", "*" + myBuilder.ToString());
    }