MySQL Hashing Function Implementation

I originally stumbled across this question in my own search for a PHP implementation of the two MySQL password hashing functions. I was unable to find any implementations, so I adapted my own from the MySQL source code (sql/password.c). The following are tested and working in PHP 5.2:

// The following is free for any use provided credit is given where due.
// This code comes with NO WARRANTY of any kind, including any implied warranty.

/**
 * MySQL "OLD_PASSWORD()" AKA MySQL323 HASH FUNCTION
 * This is the password hashing function used in MySQL prior to version 4.1.1
 * By Defines Fineout 10/9/2009 9:12:16 AM
**/
function mysql_old_password_hash($input, $hex = true)
{
  $nr = 1345345333; $add = 7; $nr2 = 0x12345671; $tmp = null;
  $inlen = strlen($input);
  for ($i = 0; $i < $inlen; $i++) {
    $byte = substr($input, $i, 1);
    if ($byte == ' ' || $byte == "\t") continue;
    $tmp = ord($byte);
    $nr ^= ((($nr & 63) + $add) * $tmp) + (($nr << 8) & 0xFFFFFFFF);
    $nr2 += (($nr2 << 8) & 0xFFFFFFFF) ^ $nr;
    $add += $tmp;
  }
  $out_a = $nr & ((1 << 31) - 1);
  $out_b = $nr2 & ((1 << 31) - 1);
  $output = sprintf("%08x%08x", $out_a, $out_b);
  if ($hex) return $output;
  return hex_hash_to_bin($output);
} //END function mysql_old_password_hash

/**
 * MySQL "PASSWORD()" AKA MySQLSHA1 HASH FUNCTION
 * This is the password hashing function used in MySQL since version 4.1.1
 * By Defines Fineout 10/9/2009 9:36:20 AM
**/
function mysql_password_hash($input, $hex = true)
{
  $sha1_stage1 = sha1($input, true);
  $output = sha1($sha1_stage1, !$hex);
  return $output;
} //END function mysql_password_hash

/**
 * Computes each hexidecimal pair into the corresponding binary octet.
 * Similar to mysql hex2octet function.
**/
function hex_hash_to_bin($hex)
{
  $bin = "";
  $len = strlen($hex);
  for ($i = 0; $i < $len; $i += 2) {
    $byte_hex = substr($hex, $i, 2);
    $byte_dec = hexdec($byte_hex);
    $byte_char = chr($byte_dec);
    $bin .= $byte_char;
  }
  return $bin;
} //END function hex_hash_to_bin

Hopefully someone else will find this useful as well :)

If you are interested in the algorithm of this function, download the source code and see the file sql/password.c, or check this implementation.

Yes, too late but I just came up this implementation on that page: http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

Here is the equivalent php function to mysql password;

function mysql_41_password($in) {
    $p = sha1($in, true);
    $p = sha1($p);
    return '*'. strtoupper($p);
} 

Why do you want to use mysql password() function? Even the Mysql documentation advises against this:

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

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications

You can use md5() for example, wich is present in almost every programming language, php and perl included.

Based on the PHP implementation above, here's a Perl example that works.

use Digest::SHA1 qw(sha1 sha1_hex);
sub password { "*".uc(sha1_hex(sha1($_[0]))) }

The password function returns the same as the MySQL5 PASSWORD() function.

In answer to "why would anyone want to do this?", I use it to generate SQL "CREATE USER" statements that don't contain plain-text passwords.

Bad boys do that in bash with sha1sum ;)

PHRASE="password"; P1=`echo -n "${PHRASE}"|sha1sum`; P2="*`echo -en $(echo -n ${P1%% *}|sed -E 's/([0-9a-f]{2})/\\\x\1/g')|sha1sum -b`"; PASS="${P2%% *}"; echo "${PASS^^}"

OT, but anyway... :)

Perl 5 implementation of old_password() based on the PHP example.

sub old_hash_password {
    my ($password) = @_;

    my $nr = 1345345333;
    my $nr2 = 0x12345671;
    my $add = 7;

    for (my $i = 0; $i < length($password); $i++) {
        my $byte = substr($password, $i, 1);

        next if ($byte eq ' ' || $byte eq "\t");

        my $ord_b = ord($byte);
        $nr ^= ((($nr & 63) + $add) * $ord_b) + (($nr << 8) & 0xFFFFFFFF);
        $nr2 += (($nr2 << 8) & 0xFFFFFFFF) ^ $nr;
        $add += $ord_b;
    }

    my $out_a = $nr & ((1 << 31) - 1);
    my $out_b = $nr2 & ((1 << 31) - 1);

    return sprintf("%08x%08x", $out_a, $out_b);
}