MySQL

Query that retrieves an MD5 hash of a password from the seeded MD5 hash

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2005

Posts: 1,075

Reputation: digital-ether is just really nice

Solved Threads: 66

digital-ether digital-ether is offline

Offline

Veteran Poster

Query that retrieves an MD5 hash of a password from the seeded MD5 hash

Jul 28th, 2008

Please excuse the length of this post:

I'm trying to integrate OpenFire (XMPP Server) with a Joomla (CMS) user database.
Openfire allows authentication against an external database by editing its configuration.

In the configuration you have to supply the query that will retrieve the password from the external database, given the username.

eg:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT password FROM jos_users WHERE username=?
SELECT password FROM jos_users WHERE username=?

The password supplied by the client will be plain text. The comparison with the query result can be plain text or MD5/SHA-1 hash.

quoting the docs:

•

# jdbcAuthProvider.passwordSQL -- the SQL String to select a user's password. The SQL statement should contain a single "?" character, which will be dynamically replaced with a username when being executed.
# jdbcAuthProvider.passwordType -- the type of the password. Valid values are "plain" (the password is stored as plain text), "md5" (the password is stored as a hex-encoded MD5 hash) and "sha1" (the password is stored as a hex-encoded SHA-1 hash). If this value is not set, the password type is assumed to be plain.

The problem is that Joomla saves passwords as the MD5 hash of the plaintext password and a random seed. The seed is then appended to the end of the resulting hash in the password column.

eg:

If the user password is "test". Then Joomla will generate a random seed, 'xyz' and generate the hash:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
HASH = MD5( CONCAT('test', 'xyz') )
hash = md5( concat('test', 'xyz') )

It will then save the password field as:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CONCAT('hash', ':xyz');
concat('hash', ':xyz');

So when a user authenticates, they send in a plain text password.

'test'. The password for that user is retrieved from the db, and split into the hash and seed.
The plain text pass is then again concatenated with the seed, MD5 hashed and compared with the existing hash in the DB.

Now for the question:

At the moment, I have integration set to plain-text and the SQL Query to:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT password FROM jos_users WHERE username=?
SELECT password FROM jos_users WHERE username=?

But this means the whole password column must be supplied into the IM client to log into an account. What I want is to configure a query that will return just the MD5 hash of the password, or at least work within the restrictions given for the query, to allow using the actual password in the IM client.

My understanding is it isn't possible since MD5 cannot be reversed within a MySQL query, but I'd just like to pose the question in case there is a work around...

www.fijiwebdesign.com - web design and development and fun
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!