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