Member Avatar for keithyg

SHA1 my password fields all at once by running a query, I need to know how to write the query

What I want to do is update all password fields in my database to be SHA1

Can anyone help me to run a query to update all fields as I dont want to do this manually as it wll take forever to do.

SELECT * FROM `tblclients` WHERE 1`password` = SHA1

I have a table in mydatbase called `tblclients` and A password field called `password` all the passwords are plain text I need them to be SHA1 and update them all at once and need to run a command to update this.

Can anyone help or advise?

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 3 Replies
  • 2K Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by drjohn

Dani AI

Generated

Quick way to get this done in phpMyAdmin without risking double-hashing. As suggested, work in a new column first so you can verify everything before swapping it in. And always take a backup before any mass update, .

-- 1) Backup
CREATE TABLE tblclients_backup AS SELECT * FROM tblclients;

-- 2) Add a new column to hold the hash
ALTER TABLE tblclients ADD COLUMN password_sha1 CHAR(40) NOT NULL;

-- 3) Hash only rows that are not already 40-char hex
UPDATE tblclients
SET password_sha1 = SHA1(password)
WHERE password IS NOT NULL
  AND password NOT REGEXP '^[0-9A-Fa-f]{40}$';

-- 4) Swap columns after spot-checking a few rows
ALTER TABLE tblclients DROP COLUMN password,
CHANGE COLUMN password_sha1 password CHAR(40) NOT NULL;

Notes and gotchas:

  • Step 3 protects you if someone re-runs the query later, so you do not hash an already-hashed value.
  • If you prefer smaller storage, create password_bin BINARY(20) and use UNHEX(SHA1(password)) instead, then compare using the raw-binary form in code.

For anyone landing here today (Oct 14, 2025): SHA-1 is no longer acceptable for new password storage. If you can, migrate to bcrypt/Argon2 using PHP rather than SQL. One simple path is to add password_hash VARCHAR(255) and run a one-off PHP script:

// Pseudocode: run once from CLI, not on every request
$rows = $db->query('SELECT id, password FROM tblclients');
foreach ($rows as $r) {
    $hash = password_hash($r['password'], PASSWORD_DEFAULT); // bcrypt/argon2id
    $stmt = $db->prepare('UPDATE tblclients SET password_hash=? WHERE id=?');
    $stmt->execute([$hash, $r['id']]);
}
// After verifying logins, drop the old column and rename password_hash -> password

That keeps the database change minimal while giving you modern, salted, slow hashing.

Recommended Answers

All 3 Replies

Member Avatar for drjohn

First, use alter table to add a new field called password2.

Then run an update query to set password2=sha1(password)

I strongly suggest you make a practice table first, of course.

Then, use alter table again, this time to remove the password field and then to rename password2 to password.

You could perhaps make this into a script in your programming language - PHP probably - where you set it the table name and it does all this.

Again after practising the first set of commands, make a script to do this and run on another practise table first, before letting it loose on the real thing. Because you'd better be sure it all works before deleting the original password field.

Edited by drjohn because: n/a
Member Avatar for keithyg

This is what I need help with.

I have selected the database and Table I want to update

SELECT passwords FROM `tblclients`

Now the Table, password has normal text in each field.

I need to update each field so each password has a sh1 value.

I can do this manually but I have lots of fields to update.

I want to run a query that can do this right away.

So what I am looking for is to be able to run a query to update the password fields to SHa1 passwords. I have already produced passwords for each field but they need to become sha1.

Doing this manually is not a problem apart from the time to complete this.

Can anyone show me structured query to run such as: -

SELECT passwords FROM `tblclients`

at this poing I need to now update all passwords in the fields to have a sh1 value

Hope you can reply promptly.

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
Member Avatar for drjohn 
UPDATE tblclients
SET password=sha1(password)

That's it done in a single query.

The value from sha1() is returned as a binary string of 40 hex digits, or NULL if the argument was NULL.

SELECT is to find things, not alter them.

Edited by drjohn because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.