Solved

Password Column to SHA1 as a String

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 2 30
SQL Server Count where two id types exist in column 8 27
Sql server insert 13 28
string fuctions 4 25
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard 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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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