Solved

Password Column to SHA1 as a String

Posted on 2010-08-27
4
1,515 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 59
SQL Log size 3 18
Error when creating a table from a function 6 20
Where is the Help Section? 8 28
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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