Solved

Password Column to SHA1 as a String

Posted on 2010-08-27
4
1,491 Views
Last Modified: 2012-05-10
I have a Members table with a plain text password column (varchar20).  I want to convert every existing password to SHA1 encryption, but I need to keep the password field a varchar field.  My understanding of the HashBytes function is that should be used with a binary field.

Can I get some help to how to convert this column to a string that is appropriate for a varchar column?  This is only a 1 time event... future passwords will be created by a system that already applies the hash as described.

My table is "Members"... the column is  "WebPassword"... I have created a new varchar(40) column called "WebPassword1" to update the encrypted passwords to.

This is what I have so far:

UPDATE MEMBERS
SET WebPassword2= HashBytes('SHA1', WebPassword)

Open in new window


Unfortuantely, this just creates a bunch of odd looking characters...I am assuming because it expects the field to be binary.

I would appreciate any help.

SQL Server 2005 Standard
0
Comment
Question by:brianmisty
  • 2
4 Comments
 
LVL 1

Expert Comment

by:snoy
ID: 33547716
Try this
UPDATE MEMBERS
SET WebPassword2= SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', WebPassword)), 3, 32)

Regards,
H.Y.
www.w3consulting.ma
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33547978
Syntax is off a little in snoy's, but here's what it should be...


UPDATE MEMBERS
SET WebPassword2= SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', WebPassword)), 3, 32)



SELECT is not valid.
0
 

Author Comment

by:brianmisty
ID: 33548056
First, an SHA1 hash is actually 40 characters as a string, not 32. So I changed the 32 to a 40.  (My mistake.)

I added a WHERE clause to perform the UPDATE on a single row which had a password of "truck"...all lowercase, no spaces. The output was 5d7ce0f0b9bcdb476567c62f162d8a537e90a3e8.  According to several SHA1 converters, and my login software, it should be 7a4d63b1ba7178fac94e51fc1b9b56869a2df9d6.

Here was my code...
UPDATE MEMBERS
SET WebPassword2= SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', WebPassword)), 3, 40)
WHERE MemberID= 123

So, I removed the Where clause and ran the script on the entire table, and I compared numerous passwords with their hashes, and they were all off.  The were all consistent, however... where I had the password "test"... for instances, they all matched.  

Strangley enough, if I replace "WebPassword" with the literal  "' truck'" , and rerun (as below), the output is correct...

UPDATE MEMBERS
SET WebPassword2= SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', 'truck')), 3, 40)
WHERE MemberID= 123

So, it would appear that the "WebPassword" is somehow getting messaed up before it gets converted.

Any thoughts?

0
 

Author Closing Comment

by:brianmisty
ID: 33550081
I discovered what was going wrong... my password field was nvarchar and not varchar.  This apprently does make a difference when using HashBytes.   Thank you for the solution.

Also, here is a link to the site that explains how different datatype produce different results using HashBytes.  

http://weblogs.sqlteam.com/mladenp/archive/2009/04/28/Comparing-SQL-Server-HASHBYTES-function-and-.Net-hashing.aspx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now