On 1/31/08, Tom Donovan <donovant@bellatlantic.net> wrote:
Scott Sanders wrote:
> Hi,
>
> After much frustration I was able to get apr_dbd_mysql compiled and
> working on my machine. However, when testing the module I believe I
...
> but in my environment that's OK. My initial apache config to secure this
> section of the site with basic auth set the AuthDBDUserPWQuery to
> "SELECT pwd2 FROM users WHERE email=%s AND active=1". This worked and
> prevented invalid users (and inactive users) from authenticating, but if
> the pwd2 field was empty then any password would succeed. If pwd2 was
> NULL then the user was rejected, and if pwd2 had a string in it the user
> was rejected (excepting, of course, when pwd2 had the proper encrypted
> content).

I find that only an empty password works when AuthDBDUserPWQuery retrieves a value of ''.  Entering
a non-empty password (in Firefox) gives a "Password Mismatch" error.  I'm using Apache 2.2.8 on Windows.

I am able to reproduce this bug on another machine. Apache 2.2.8, MySQL 5.0.22, APR 1.2.12, APR-Util 1.2.12 installed on CentOS 5 x86. Test table and data created with:

CREATE TABLE accounts (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(64),
pass VARCHAR(64) NOT NULL);

INSERT INTO accounts (user) VALUES ('scott');

This results in a 'SELECT * FROM accounts' returning:

+----+-------+------+
| id | user  | pass |
+----+-------+------+
|  1 | scott |      |
+----+-------+------+


> This has got to be a bug in the apr_dbd_mysql code, but I unfortunately
> don't have time right now to track it down and provide a patch. To solve

Not really.  An empty string is not prohibited as a password unless you prohibit it.
An empty-string is also the valid hash of an empty-string password.

On Windows, BEOS, and Netware - plain-text passwords are used if there is no prefix.
'' is a valid plain-text password.

On Linux, crypt is used if there is no prefix.

   hash = crypt_r(password, salt, buffer)

returns an empty-string as the hash when called with an empty-string password and an empty-string
salt.  I expect most other Unix systems do the same.

Having an empty password is perfectly valid, however, the bug is that as long as the user is valid (i.e. the AuthDBDUserPWQuery doesn't return an empty set) and the password field in the database is empty, any password will authenticate the user.

To show this, I set up apache's default config and used a config like follows (note that this dir is my docroot):

DBDriver mysql
DBDParams "dbname=test,user=root,pass=password"

DBDMin  1
DBDKeep 2
DBDMax  10 
DBDExptime 60

<Directory "/home/ssanders/dev/apr_dbd_mysql-bug_proof/local/http/htdocs">
    Options Indexes FollowSymLinks

    AllowOverride None

    Order allow,deny
    Allow from all
  AuthType Basic
  AuthName "Company RSS Feed"
  AuthBasicProvider dbd
  AuthzUserAuthoritative On

  AuthDBDUserPWQuery "SELECT pass FROM accounts WHERE user=%s"
  Require valid-user
</Directory>

After starting apache, access the server, and attempt to auth using the user scott and any password you choose; it will succeed and you will be taken to the "It works!" page.

> my problem I changed my query to "SELECT pwd2 FROM users WHERE email=%s
> AND active=1 AND pwd2 !=''" but this is an ugly hack. Where is the
> bugzilla to file this?

It doesn't seem ugly to me.  If you insist on storing '' in pwd2, yet you don't want it treated as a
valid password; then enforcing your rule in SQL seems reasonable.

Existing sites may already use an empty-string password when they want the user to voluntarily enter
a (valid) username but they don't require any password to verify it.  Not the usual case, but it
doesn't seem necessary to break their scheme just to allow for storing empty passwords and then
denying them.  As you pointed out, storing a NULL as the password hash will always reject a login
attempt.

-tom-

I admit I have not turned on debugging and traced into the problem yet. Any tips on where to start looking would be appreciated.

-Scott