question

askmlx121 avatar image
askmlx121 asked

hashbytes password update

hi my table empl have 1000 records sample: userid password passwordencrypt 123 54er4s null 245 null null 897 658e null i want to replace the encrypted password using function of HashBytes('SHA1', password) column to passwordencrypt field... sample t-sql hints: update testdb.dbo.empl set passwordencrypt=HashBytes('SHA1', password) above query update the hashpassword of all empl table but My requirement query should satify the 2 condition below 1)when user change password we have to update new_password field correspondingly 2)if user didnt change we can leave it as old.... My output sample look like below userid password passwordencrypt 123 54er4s 0x2E694536A55AD55F0054ECA9FCEDF5CB8FB44450 245 null null 897 658ews 0x492FAB3E44FCB48274570EE30D1D863F3A619981 so any SQL experts...or.......**Any one can give me the Script or CURSOR or TSQL script** which is best cursor or functions or tsql.... quickly may highly appriciated..............
sql-server-2005tsqlperformancepassword
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
This setup is begging for problems, unfortunately. Let me enumerate the ways: 1. SHA1 is not a particularly strong hashing algorithm. One round of SHA1 can be broken pretty easily with a rainbow table or with a GPU farm. 2. You have a plaintext password already on the table. That makes hashing entirely moot. There is exactly **one** valid scenario for this: you're fixing historically bad code and will drop the column very, very soon. 3. If you do use a hashing algorithm (which you should), you probably should use something like PBKDF2. It's not built into SQL Server, but [Troy Hunt has a great blog post on the topic][1] if you are in a .NET environment. This setup lets you do solid security practicies, such as salting, key stretching, and using an extended number of rounds. 4. Hashing isn't encrypting. The "passwordencrypt" field is a bit of a misnomer. For password storage, hashing is superior to encrypting because a hash is supposed to be a one-way function; this means that your login validation mechanism can operate without direct knowledge of the plaintext password. This really isn't something that SQL Server is well-equipped to do on its own. SHA1 and MD5 are very fast comparison hashes. They're meant to act as checksums rather than security, and so modern hardware can rip right through them, making your "secure" hash rather insecure in the event that somebody gets ahold of the table with hashed passwords (like what happened to LinkedIn). Of course, that's assuming that the plain-text Password field isn't there... But if you do want to go ahead with hashing inside SQL Server (something I very highly recommend against, as you can tell), the query you're looking for is: update empl set passwordencrypt = hashbytes('sha1', password); That will update every row in your table. When a user changes his password, you could create a stored procedure that looks like: create procedure [yourprocedurename] @UserId int, @NewPassword varchar(128) as update empl set passwordencrypt = hashbytes('sha1', @NewPassword) where UserId = @UserId; After doing this, you could drop the plain-text password column and move the level of security from "none" to "practically none." For legitimate security, you'll either need to go outside of SQL Server or use the CLR to run a .NET DLL that uses bcrypt or PBKFD2. **Edit** The follow-up questions are for handling insertion and selection. These are separate procedures which are pretty simple insert and select statements that depend upon the exact contents of your table. The insert procedure would look something like: CREATE PROCEDURE [insertion procedure name] [columns as variables] AS insert into empl([columns]) values([columns as variables]); The select procedure is slightly more complex, only because it has two purposes: either simply looking up by username (to check for existence) or by username and password hash (to check that this is a valid login attempt). The easiest way would probably be to break it out into two separate procedures, although it's only slightly more complex to create one procedure which does both. I'll keep it simple. CREATE PROCEDURE [user lookup procedure name] @UserName varchar(whatever) AS select case when exists (select * from empl where username = @UserName) then 1 else 0 end; CREATE PROCEDURE [user validation procedure name] @UserName varchar(whatever), @PasswordHash varbinary(whatever) AS select top 1 [columns] from empl where username = @UserName and passwordencrypt = @PasswordHash; [1]: http://www.troyhunt.com/2012/07/stronger-password-hashing-in-net-with.html
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hi kevin feasel.......thanking you very much for your response but if new user come to empl table means we have to check the user is already present or not then if user not present means we have to insert passwordencrypt column...to the empl table if user present means we have to check the passwordencrypt is correct or not then i have to update...so how will i achieve this...? kindly give me the script.......thanking you in advance
0 Likes 0 ·
This is a beautiful answer that I would upvote twice if I could. Let me just reemphasize the importance of salting. Hashing is much better than not hashing, but without salting and/or key stretching it is still quite weak.
0 Likes 0 ·
askmlx121 avatar image
askmlx121 answered
Thanking you very much.............kevin I have created stored procedure look like below create procedure useridpasswordupdatecheck as begin SET NOCOUNT ON declare @userid as int, @passwordencrypt as varbinary(max), @temp as varbinary(max) declare userpwd_cursor cursor for select userid,passwordencrypt from testdb.dbo.userstabletest open userpwd_cursor fetch next from userpwd_cursor into @userid,@passwordencrypt while(@@fetch_status=0) begin update testdb.dbo.userstabletest set passwordencrypt=HashBytes('SHA1', password) where userid=@userid fetch next from userpwd_cursor into @userid,@passwordencrypt end close userpwd_cursor deallocate userpwd_cursor SET NOCOUNT OFF end Above SP change the all user password to be hashbytes format when a particular user change the password means again we run the SP Then only that user hash password will change otherwise table will show the mismatch record of that particular user and also it will process again all of the userids with changed user. if 1000 record means ok but if its 5lakh record means ??????? performance will slow So can u alter the above my stored procedure for how can i detect/check and update that particular changed user password only? kindly alter my stored procedure in the answer format.............
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why would you update all of the records when one record changes? Look at the process which changes the user's password and modify that to update your table, like in the single-user update stored procedure. The only time you would need to update all of the records is to get an initial load (after which point I sincerely hope you drop that plain-text password column, or any 'security' measure is entirely for naught).
0 Likes 0 ·
I have update the all records because we cant able to know which user change the password ?thats why i updated the all records... could u help me for add the query/alter above SP for how can i update changed userid's password of particular user only?
0 Likes 0 ·
What you need to do is look at the process which caused the password to change: where the SQL call to update the password field originated. Change that process to, instead of updating the password field, update the passwordencrypt directly using a hash of the new password. If you're just reacting to changes in a column, you could create a trigger to update passwordencrypt whenever password gets updated, but again, there's absolutely no value to having a password hash if you have the password in plain text right next to it.
0 Likes 0 ·
askmlx121 avatar image
askmlx121 answered
![alt text][1] [1]: /storage/temp/455-pu.jpg for example when userid 67235 is his password is 123 and password encrypt 0xB53752EE50C23692DE6C4D4B114CE383FCE0C4E7

pu.jpg (34.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
![alt text][1] [1]: /storage/temp/456-pj.jpg for example when userid 67235 changed his password is 456 and but password encrypt 0xB53752EE50C23692DE6C4D4B114CE383FCE0C4E7 as same as before his password 123 once we run the SP then only change the all users password. How can I check and update that particular user password only....

pj.jpg (35.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
**For bulk update for changed users see here below:** update testdb.dbo.userstabletest set passwordencrypt =b.[New_Password] from testdb.dbo.userstabletest u inner join ( select userid,password,passwordencrypt ,HashBytes('SHA1', password)[New_Password] from testdb.dbo.userstabletest where passwordencrypt<>HashBytes('SHA1', password) )as b on u.userid=b.userid and u.password=b.password i thing it is correct........any one....give me the suggestion
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.