What is the MySQL function for exp_members password column?

Question

I forgot to save my password and am unable to get MAMP Pro's postfix working so I can't resend the password to myself from my local machine, unfortunately.

What is the MySQL function used to generate the encrypted string in the exp_members password column?

Is it either MD5 or SHA1 or a combination of something else all together?

Answer 1

As far as I know ExpressionEngine will use one or the other, SHA1 or MD5 (I believe SHA1 is the default). In my test it actually worked to use either one when replacing the password, allowing me to login just fine in both cases.

You can use MySQL to generate a new password hash for yourself. For example either SELECT SHA1('pass123') or SELECT MD5('pass123') will generate a string for you.

The following MySQL query would set your new password to "pass123" (where your member_id is 1):

UPDATE exp_members
SET password = SHA1('pass123')
WHERE member_id = 1

Or if you don't want to use MySQL then this online SHA1 hash generator or MD5 hash generator tool also works. Just copy the string and then using PHPMyAdmin, Sequel Pro, or similar, find your user record in the exp_members table and replace your current password with the new string .

Answer 2

Why not just use the password reset / forgot password function. It's generally accessible at

http://{domain}/{member_trigger}/forgot_password

Where {domain} is your domain name, and {member_trigger} is whatever term you set to trigger member registration and login. Default is 'member' but I believe you're encourage to set it to something else.

Answer 3

I want to post an update to this question for EE 5 because the current answer is pretty outdated.

In order to set a member's password in the DB, you'll need to know what the hash algorithm is. From /system/ee/legacy/libraries/Auth.php we can see the possible options that EE might use:

private $hash_algos = array(
    128     => 'sha512',
    64      => 'sha256',
    40      => 'sha1',
    32      => 'md5'
);

When updating a password, the system will use the first available hash algorithm (in order of most to least secure) to encrypt the password in the database. This array has keys defining the length of the salt value, and values are the name of a valid algorithm to be used by PHP's hash().

---

Aside: if you need to reset a user's salt too, it needs to be a string of length matching the above key. Example: if the hash algorithm being used is sha256, the salt needs to be 64 characters long (as seen in the key/value above), md5 would require a 32 character string, etc.

The Auth library uses the following logic to generate the salt:

for ($i = 0; $i < $h_byte_size; $i++)
{
    $salt .= chr(mt_rand(33, 126));
}

which equates to a string of length key with random ASCII characters between ! (33) and ~ (126).

---

The following is returned from hash_password():

return array(
    'salt'      => $salt,
    'password'  => hash($this->hash_algos[$h_byte_size], $salt.$password)
);

which gets called from update_password() and the values are written directly to the database.

In summary, if you need to manually reset a member's password (ex: test) directly in the database, you can run the following MySQL query:

UPDATE exp_members
SET password = SHA2(CONCAT(salt, 'test'), 512)
WHERE member_id = 1234;

which is the MySQL equivalent to what hash_password() does.

This query uses the existing salt, and you may need to replace 512 to match the algorithm used on the server (from https://stackoverflow.com/questions/12612279/hashing-an-entire-column-using-sha512).