Solved

Password Column to SHA1 as a String

Posted on 2010-08-27
4
1,535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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