Magento 2: How to reset Customer Password from Database

Question

It's hash for customer password in DB. So MD5 & Sha1 is not working.

UPDATE `customer_entity` SET `password` = MD5('test123') WHERE `email` = '[email protected]';

So how to update password using database query. May be MD5(Sha1('test123'))?

How Magento is doing via code. go to vendor\magento\module-customer\Console\Command\UpgradeHashAlgorithmCommand.php

protected function execute(InputInterface $input, OutputInterface $output)
{
    $this->collection = $this->customerCollectionFactory->create();
    $this->collection->addAttributeToSelect('*');
    $customerCollection = $this->collection->getItems();
    /** @var $customer Customer */
    foreach ($customerCollection as $customer) {
        $customer->load($customer->getId());
        if (!$this->encryptor->validateHashVersion($customer->getPasswordHash())) {
            list($hash, $salt, $version) = explode(Encryptor::DELIMITER, $customer->getPasswordHash(), 3);
            $version .= Encryptor::DELIMITER . Encryptor::HASH_VERSION_LATEST;
            $customer->setPasswordHash($this->encryptor->getHash($hash, $salt, $version));
            $customer->save();
            $output->write(".");
        }
    }
    $output->writeln(".");
    $output->writeln("<info>Finished</info>");
}

Answer 1

Never thought of using SHA hashing in SQL directly until I saw Robban's answer. I'd like to add that you could generate the salt hash in SQL too, leaving only the password that should be added. You can use variables (set-statement) to set all the necessary values upfront:

SET @email='[email protected]', @passwd='test@123', @salt=MD5(RAND());

UPDATE customer_entity
    SET password_hash = CONCAT(SHA2(CONCAT(@salt, @passwd), 256), ':', @salt, ':1')
    WHERE email = @email;

Answer 2

This SQL works just fine to update the customer password. Tested with Magento 2.1.5.

Just change "YOURPASSWORD" below (keep the xxx:es) and voila!

UPDATE `customer_entity`
SET `password_hash` = CONCAT(SHA2('xxxxxxxxYOURPASSWORD', 256), ':xxxxxxxx:1')
WHERE `entity_id` = 1;

Answer 3

You can generate a Magento 2 style password hash quite easily via PHP on command line (CLI).

Use this command to generate a hash, as example for password test123 (change that into your own password):

php -r '$salt=md5(time());
  echo hash("sha256", $salt.$argv[1]).":$salt:1\n";' test123

It is using MD5 of current Epoch time (time()) as a salt, but you can also use anything else.

Copy this generated hash and paste it into your query or database management tool on a customer record's password_hash column.

Answer 4

I don't think it's possible to set the password from inside the DB. You need SHA256 hashing for customer passwords. Here's how Magento generates it:

example password in DB:

7fe8104daf9ebd5c2ac427ec7312cd9456195b1a8ade188fa8bfd35e43bc0614:7ilBNt4q5xYUSMyv8UX2a7gkmwv051Pm:1

this is the format:

A:B:C

Where

B = $salt = random string of 32 characters

A = hash('sha256', $salt . $password);

C = Hashing algorithm version (default = 1)

Answer 5

Please look at this, if you want to do this from SQL queries, and if you are using Magento 2.4.0 and above, by logging into the PHPMyAdmin or from SSH, and find the table customer_entity and select the customer you want,

The SQL query is -

update customer_entity set password_hash = CONCAT(md5('testing@123'),"::0")where entity_id = 1234;

The entity id is where you give the customer id, if you don't know, please find it from the admin panel, inside the customer menu.

After the query runs successfully, try to log in from the store using the customer email id and recently changed password testing@123, the password_hash will also change after you do so which u can again check in the table.

If the changed password, does not work please run "bin/magento s:up" in your SSH or terminal, it will affect the changes immediately, for me the same happened and using s: up, solved the issue.

Answer 6

Just try the below mysql query

update customer_entity set password_hash = CONCAT(md5('test123'),"::0") where entity_id = 233;

Where entity_id is your user id There are 3 values separated by : sign In our case

1. First is the md5 of password
2. Second is empty or null as we are not using any salt
3. Third is 0 to indicate use md5

Once you run this query in db and then login using the mentioned password and go back to database table and check the password you will notice that magento automatically have changed the password to standard magento2 password ie xxxxxx:yyyyyy:1