Magento 2: How to reset Customer Password from Database

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>");
}

Solutions

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='[email protected]', @salt=MD5(RAND());

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

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;

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)

Similar questions

Annoying reset password error "Cannot save a new password" on Magento
I am currently working with Magento 1.11.1.0 and i am getting a strange behaviour from the reset password functionality. When I try resetting my password, I end up getting the following error "Cannot save a new password." but when I follow the same steps on a different account, it works. The accounts are exactly the same, the only difference is the...
No Email Send for Password Reminder After Reset Password in Magento1.9.2.4?
My All emails are being sent except one, which is a reminder mail after reset Password. Suggest what can I do achieve this.
Password reset updated but still able to login with old password in magento2
I have reset my password. It shows as Password updated. But still i can able to login with old password. Newly updated password is not working. My magento version is 2.2.6. Note: I am using Store session In DB instead of files. Can anyone look into this issue please.
Magento 1.7.0.2 customer password reset doens't work
I have a problem which has kept me occupied for three days already. When doing a password reset, customers get the email, and the token generation is valid, but when they click the link then they will be taken to a page which has the header, left column and footer and a blank col-main. This is the wrong layout, it should get 1 column only... I've d...
Magento customer reset password customer_flowpassword error
I currently have a error in my 1.9.3.2 store. When a customer wants to reset their password, the Magento Report error page is displayed. When check the report, I get the following error: Problem is related to the update to 1.9.3.2, where the table was not created. Should this solve the issue? How can I solve that?
Magento 2.2.6 Customer Password Reset
I have a problem with customer password reset in Magento 2.2.6 After pressing forgot password it send me an email " Set a New Password link " but when i am trying to set new password i got a message Also i checked var/logs But i dont have any problem with this all kind of emails setting are correct.. Any suggest?

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.