Orsdxy

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 vendormagentomodule-customerConsoleCommandUpgradeHashAlgorithmCommand.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>");

}

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;

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 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)

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:1n";' 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.

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