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)