generate apache compatible password with mysql

Question

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

Since you stored the password hash, there should be no way to find the password itself. What you can do, if you receive a password, is to compare its hash with the one stored, which makes sense. I read the mod_authn_dbd documentation, and it seems it needs the password to be stored readable. That's not very wise, is it? — Gerard H. Pille, Aug 17 '20 at 14:19

score 2 · Accepted Answer · answered Aug 17 '20 at 14:27

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.

Brilliant. That works. I tried to convert the statement myself but only tried `cast(sha1('test') as BINARY)` and this obviously didn't work. — Jürgen Steinblock, Aug 18 '20 at 11:22

score 0 · Answer 2 · answered Aug 17 '20 at 14:07

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

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.

generate apache compatible password with mysql

2 Answers2