0

Data table: people_t

Columns:

Username - nvarchar(200)
PasswordHash - nvarchar(1000)

Query:

I want to change multiple passwords of type hash to be the usernames. After the command, the passwords should still be hashed but the actual password will be the user's username. E.g.

  • Username: johndoe
  • PasswordHash: iamjohn

Will become:

  • Username: johndoe
  • PasswordHash: johndoe

I am trying the following:

DECLARE @UserPass SHA1 --Var for storage of username
SET @UserPass=UserName --Add current Username's to UserPass var

UPDATE people_t --Update the people_t
SET PasswordHash=@UserPass --Do the job

Do I even need a WHERE clause or what am I doing wrong here?

Thanks in advance folks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ParttimeCoder
  • 56
  • 10

2 Answers2

1

I think what you might want (for sql server) is this:

update people_t set passwordhash =  HASHBYTES('SHA1', username)

Your pseudo code seems to set a single hash (which would be based on one username) and then is updating all people with that single username.

Not sure what the SHA1 type is in your code above - don't recognize that.

Paddy
  • 33,309
  • 15
  • 79
  • 114
  • Thanks so far guys! You can tell am new on SQL Server and T-SQL. So Paddy, are you saying all I need is the SET and update as so for passwords to be changed: SET @UserPass=UserName --Add current Username's to UserPass var UPDATE people_t set PasswordHash = HASHBYTES('SHA1', UserPass) – ParttimeCoder Nov 27 '13 at 14:09
  • And I use SHA1 hashing even if my current data types are both nvarchar? – ParttimeCoder Nov 27 '13 at 14:12
  • I can't tell you what hashing algorithm to use, or if this is correct for your system, sorry. You may need to be careful that your hashes haven't been salted, otherwise all this is going to do is break your logins. Comment isn't really a good place for code, not sure exactly how that reads.. – Paddy Nov 27 '13 at 15:25
  • Thanks for the updated Paddy. What does 'salted' actually mean in relation the hashes? – ParttimeCoder Nov 28 '13 at 15:50
  • Found this salt/hash heaven https://crackstation.net/hashing-security.htm, but all I can say is I need to find out a way to see if they are salted, this website gets too complex for me. As an example, I took two random users with different usernames and passwords, I copied the passwordhash from e.g. usernameA to append to another user e.g. usernameB as a test, and the same password worked based on this hash when I tried to login for both users. I take there is no salting done then since the same passwordhash worked or am I wrong? – ParttimeCoder Nov 28 '13 at 16:31
  • That sounds about right. You should be able to look at the code in your authentication system and see how they check the password. If they just hash it for comparison, then that'll do it. – Paddy Nov 28 '13 at 16:42
  • Old thread but I left this as I couldn't check any further in the authentication system! Thanks for the help you gave Paddy and the rest of you. – ParttimeCoder Jan 28 '14 at 10:08
0

You could use any algorithm : MD2 , MD4 , MD5 , SHA , SHA1 , SHA2_256 , SHA2_512

Your Query : 

UPDATE people_t set PasswordHash =  HASHBYTES('ALGORITHM', UserName)

Replace ALGORITHM with any one mentioned above.

Documentation
How to choose an encryption algorithm

Sahil Sareen
  • 1,813
  • 3
  • 25
  • 40
  • Note - probably important to stick with whatever algorithm originally hashed the password, otherwise you're going to run into difficulties with your auth code checks... – Paddy Nov 27 '13 at 15:26
  • Thanks...Is there anyway of finding out what algorithm of these data types? That way I can then amend accordingly carefully. – ParttimeCoder Nov 28 '13 at 15:50
  • @ParttimeCoder U can lookup on Wikipedia for these algorithms. Comment if u dont find them. – Sahil Sareen Nov 29 '13 at 10:11