Update query how to Update a column getting values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query how to Update a column getting values

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-16 : 11:49:39

Can you please help, how to update a table column(HashPwd) getting all data from the same table another column(password), but convert to MD5 Hash(binary) from varchar data from the column password same table.

update myusertable set HashPWD = @password

Thank you very much for the information.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:55:24

update myusertable set HashPWD = dbo.MyOwnFunctionThatConvertsPasswordToHashWithMD5(password)

Peter Larsson
Helsingborg, Sweden

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-16 : 11:59:01

Hello Peso,
What is dbo.MyOwnFunctionThatConvertsPasswordToHashWithMD5?

quote:
Originally posted by Peso

update myusertable set HashPWD = dbo.MyOwnFunctionThatConvertsPasswordToHashWithMD5(password)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 14:06:58

That is the name of the function that converts plain password text to a MD5 hash.
There are commercial products for this, or you could write your own.

Peter Larsson
Helsingborg, Sweden

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-16 : 14:20:21

Peso,
I tried to execute the update statement you provided me: but it does'nt recognize this dbo.MyOwnFunctionThatConvertsPasswordToHashWithMD5 at all.

is that a real function which is avaiable on SQL server or are you referring me to create a function for that purpose.

Thank you...

quote:
Originally posted by Peso

That is the name of the function that converts plain password text to a MD5 hash.
There are commercial products for this, or you could write your own.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 14:37:51

No, it is not a real function name available in SQL Server.
As I wrote before, you have to write this function for yourself, ot buy a commercial product.

Or search the great Internet with your favorite search engine looking for a function that does this conversion for you.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-16 : 14:58:59

i've been using this with no problems at all:
http://www.codeproject.com/database/xp_md5.asp

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-16 : 16:03:44

Since this is SQL Server 2005, it is probably easier to just use the built-in MD5 hash function.


declare	@HashIn	varchar(20)
select	@HashIn	= 'Hello world!'
select	MyHash = HashBytes('MD5', @HashIn)

MyHash
------------------------------------
0x86FB269D190D2C85F6E0468CECA42A20

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources