Solved

Password Column to SHA1 as a String

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

679 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