15

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

UPDATE `customer_entity` SET `password` = MD5('test123') WHERE `email` = 'X@X.com';

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>");
}
  • Please consider accepting the answer from @7ochem. This question is 3 years old now and no accepted answer. – Darren Felton Oct 9 at 14:06
33

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;
  • 4
    Note that this will essentially create an unsalted password. It's fine as a testing procedure, but shouldn't be used as a regular method in production or it will significantly weaken security. See @7ochem's answer for a more secure approach that generates unique salts. – Scott Buchanan Sep 11 '17 at 19:52
  • Any way! This solution is working.. Thanks @Robban – Irfan Momin Oct 3 '17 at 6:08
25

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 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='emailaddress@example.com', @passwd='test@123', @salt=MD5(RAND());

UPDATE customer_entity
    SET password_hash = CONCAT(SHA2(CONCAT(@salt, @passwd), 256), ':', @salt, ':1')
    WHERE email = @email;
  • I need to update all the customers of one db with generated password, is there a way to do this for all the table ? – Christophe Ferreboeuf Jun 20 '17 at 8:42
  • This is a slightly different question, maybe worth of answering it separately. Can you Ask this as a new question? I'm happy to answer that. Please do not forget to add your Magento version in the question – 7ochem Jun 20 '17 at 12:23
6

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)

  • Can u give with example @Aaron. Suppose password is test. PHP/Magento Example – Ankit Shah Oct 18 '16 at 7:59
6

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.