Home / server / Questions / 1030430
Accepted
Jürgen Steinblock
Asked: 2020-08-18 03:33:47 +0800 CST

generate apache compatible password with mysql

  • 772

I am currently in the process of migrating an old webserver.

The old server uses basic auth with users stored in a mysql table and mod_auth_mysql enabled.

The password ist stored with apaches build in sha1 function SELECT sha1('secret') which works for mod_auth_mysql

Unfortunately mod_auth_mysql is not supported anymore. That's why I found mod_authn_dbd as an alternative.

https://documentation.help/httpd-2.4-es/mod_authn_dbd.html

I alreay managed to get everything up and running until the point that I can login with basic auth and a fixed password

<Location />
        AuthType Basic
        AuthName "Test"
        AuthBasicProvider dbd
        Require valid-user
        AuthDBDUserPWQuery "SELECT '{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=' FROM users WHERE user = %s"
</Location>

I only store the sha1 hash in the user table. While the old mod_auth_mysql accepted hashes generated by mysql (SELECT sha1('test')=a94a8fe5ccb19ba61c4c0873d391e987982fbbd3) mod_authn_dbd doesn't.

According to https://documentation.help/httpd-2.4-es/password_encryptions.html

SHA1 "{SHA}" + Base64-encoded SHA-1 digest of the password.

Passwords generated by htpasswd are accepted.

htpasswd -bns user test
user:{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=

Now I need to convert this value a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 (stored in my db for password test) to qUqP5cyxm6YcTAhz05Hph5gvu9M= (accepted value for password test) inside mysql

I already tried

SELECT to_base64(sha1('test')), to_base64(ucase(sha1('test'))), to_base64(lcase(sha1('test')));`

but none produces the expected results.

I either need a way to convert my existing password hashes to the expected format or convince mod_authn_dbd to accept my existing sha1 hash.

Any help would be appreciated

mysql apache2 htpasswd mod-auth-mysql

2 Answers

  1. Best Answer

    You answered your own question with the PHP example. The pack() function in PHP converts hexadecimal representations of binary data (which is what SHA-1 hashes are typically displayed as to be human readable) into their original binary (non-human readable) form. You need to run to_base64() against this binary form of the SHA-1 hash you generate like so:

    mysql> SELECT to_base64(unhex(sha1('test')));
+--------------------------------+
| to_base64(unhex(sha1('test'))) |
+--------------------------------+
| qUqP5cyxm6YcTAhz05Hph5gvu9M=   |
+--------------------------------+
1 row in set (0.00 sec)

    In this case, you will get the desired output with only MySQL.

    • 2

  2. This is not a direct answer to my question but might be a workaround I found a blog post where someone ran into the same problem.

    http://nileshbansal.blogspot.com/2008/06/password-encryption-mysql-apache-and.html

    screenshot

    in the comments there is a link on how to create a htpasswd compatible hash with php. I confirmed that this gives the same result as htpasswd -bns command

    function sha1_to_htpasswd ( $user, $sha )
{
return $user . ':{SHA}' . base64_encode( pack( "H" . strlen( $sha ), $sha ) );
}

    If nobody knows a better solution (just mysql and without php) I would propably extend my user table with a column where the password has is stored this way. A bit redundand but it should work.

    • 0