Bug 44578 - mod_authn_dbd option to let database validate password
Summary: mod_authn_dbd option to let database validate password
Alias: None
Product: Apache httpd-2
Classification: Unclassified
Component: mod_authn_dbd (show other bugs)
Version: 2.2-HEAD
Hardware: All All
: P2 enhancement with 3 votes (vote)
Target Milestone: ---
Assignee: Apache HTTPD Bugs Mailing List
Keywords: MassUpdate, PatchAvailable
Depends on:
Reported: 2008-03-11 08:13 UTC by Tom Donovan
Modified: 2018-11-07 21:08 UTC (History)
0 users

httpd trunk (r628393) patch - also applies to 2.2.8 cleanly (4.88 KB, patch)
2008-03-11 08:13 UTC, Tom Donovan
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Tom Donovan 2008-03-11 08:13:55 UTC
Created attachment 21651 [details]
httpd trunk (r628393) patch - also applies to 2.2.8 cleanly

It is a frequent problem that mod_authn_dbd cannot be used with existing SQL databases because passwords are not stored in one of the Apache formats: {$apr1$}, {SHA}, crypt (Unix), or plaintext (Windows/Netware).

This proposal is for an optional 2nd 'VALIDATE' argument to the AuthDBDUserPWQuery directive which lets the database determine whether the password is valid without relying on the APR password hashing functions.

The VALIDATE argument indicates that the plaintext password is passed as the first query parameter and the username is passed as the second parameter.

When the first column of the first returned row is a non-zero number or "T" or "TRUE" (case insensitive), the password is accepted - otherwise the password is rejected.

Note that when no rows are returned, mod_authn_dbd already reports AUTH_USER_NOT_FOUND.

A common example is when passwords are stored using the database provider's MD5 function which is incompatible with Apache encrypted password formats:

MySQL or PostgreSQL:
  AuthDBDUserPWQuery \
  "SELECT (password = MD5(%s)) FROM users WHERE username = %s"  \

  AuthDBDUserPWQuery \
  "SELECT CASE password WHEN HashBytes('MD5', %s) THEN 1 ELSE 0 END \
  FROM users WHERE username = %s" \

Oracle 10g:
  AuthDBDUserPWQuery \
  FROM users WHERE username = %s"  \

The password is passed as the 1st parameter and the username as the 2nd parameter because this order makes constructing the SQL statement considerably easier, since the username is almost always used in a SQL predicate clause.

All SQL databases which support boolean values cast them to strings as "0" or "1"; "t" or "f"; or "TRUE" or "FALSE".

This option is not useful for digest authentication because Apache does not have the plaintext password when digest authentication is used.

It may be good to note in the documentation that the security of the database connection and database SQL logging should be considered when a plaintext password is passed to the database using the VALIDATE option.
Comment 1 Tom Donovan 2008-03-11 09:23:31 UTC
Just noticed that it is redundant to check if the first character is "T", then check for the string "TRUE":

>+        /* any non-zero number or "T" or "TRUE" (case-insensitive) for OK */
>+        return (   *dbd_password == 't' || *dbd_password == 'T'
>+                ||  atoi(dbd_password)
>+                || !apr_strnatcasecmp(dbd_password, "TRUE") 
>+                ) ? AUTH_GRANTED : AUTH_DENIED;

would be better as:

>+        /* any non-zero number or "T" or "TRUE" (case-insensitive) for OK */
>+        return ( atoi(dbd_password)
>+                 || !apr_strnatcasecmp(dbd_password, "T") 
>+                 || !apr_strnatcasecmp(dbd_password, "TRUE") 
>+                ) ? AUTH_GRANTED : AUTH_DENIED;
Comment 2 christof 2016-06-29 21:37:23 UTC
this issue still persists with recent apache versions. I am not sure if this is a bug per se or a design flaw. either way a fix would be great as the current implementation requires users to maintain multiple password hashes in the database for different systems.
Comment 3 William A. Rowe Jr. 2018-11-07 21:08:48 UTC
Please help us to refine our list of open and current defects; this is a mass update of old and inactive Bugzilla reports which reflect user error, already resolved defects, and still-existing defects in httpd.

As repeatedly announced, the Apache HTTP Server Project has discontinued all development and patch review of the 2.2.x series of releases. The final release 2.2.34 was published in July 2017, and no further evaluation of bug reports or security risks will be considered or published for 2.2.x releases. All reports older than 2.4.x have been updated to status RESOLVED/LATER; no further action is expected unless the report still applies to a current version of httpd.

If your report represented a question or confusion about how to use an httpd feature, an unexpected server behavior, problems building or installing httpd, or working with an external component (a third party module, browser etc.) we ask you to start by bringing your question to the User Support and Discussion mailing list, see [https://httpd.apache.org/lists.html#http-users] for details. Include a link to this Bugzilla report for completeness with your question.

If your report was clearly a defect in httpd or a feature request, we ask that you retest using a modern httpd release (2.4.33 or later) released in the past year. If it can be reproduced, please reopen this bug and change the Version field above to the httpd version you have reconfirmed with.

Your help in identifying defects or enhancements still applicable to the current httpd server software release is greatly appreciated.