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();
    $customerCollection = $this->collection->getItems();
    /** @var $customer Customer */
    foreach ($customerCollection as $customer) {
        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));
  • Please consider accepting the answer from @7ochem. This question is 3 years old now and no accepted answer. Oct 9, 2019 at 14:06

6 Answers 6


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;
  • I need to update all the customers of one db with generated password, is there a way to do this for all the table ? Jun 20, 2017 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, 2017 at 12:23
  • 1
    You probably should be using the correct customer entity id as in @Robban's answer rather than the customers email address. In multi-store mode its possible to have the same email address appear in the customer_entity table multiple times and you don't want to update them all, unless you intend to update them all!
    – Dom
    Mar 1, 2020 at 12:36

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. Sep 11, 2017 at 19:52
  • Any way! This solution is working.. Thanks @Robban Oct 3, 2017 at 6:08

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.


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:


this is the format:



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
    – Jackson
    Oct 18, 2016 at 7:59

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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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