asked
convert varbinary to varchar... it works in select statement but not update statement why?
Hi My table name testuidpwd have User: Uid Password Passwordhash 121 wrwrwr 0x0013531FA845AEA1E840BDE787336ED3CAEDFE6E 122 etetxw 0x79BAEFD23C2F4D146F9BD2FDF6C14CA25AC5D6AA uid char data type password nvarchar(100) data type passwordhash nvarchar(100) data type My problem is when i use function hashbytes('SHA1',password) **update testdb.dbo.testuidpwd set pwdhash=HashBytes('SHA1', password)** it will convert nvarchar to varbinary value but it stores the data in some format like here 硽贕ڷד瘃快ﭙ寱캏엞 see figure for full details.![alt text][1] **But My problem is when i use the select statement use cast function to convert varbinary to varchar means it displays correctly but when use update statement means it did nt work (i.e) it shows data like 멹틯⼼ᑍ魯쇶ꉌ앚 or did not get back the varbinary value why??????????** it convert the varchar to varbinary in select statement but it did not update/store in table of varchar to varbinary statement why? **how can i convert and store in passwordhash column of string to varbinary data type** ?** or **how can i convert and store in passwordhash column of varbinary data type to string?** select uid,password,passwordhash,cast(pwdhash as varbinary) as varbiny, from testdb.dbo.testuidpwd--------------work properly--see the figure..it shows correctly when i use cast statement... update testdb.dbo.testuidpwd set pwdhash=cast(hashbytes('SHA1',Password) AS varbinary)---did nt work [1]: /storage/temp/459-to.jpg
To start with, I am not able to understand the whole process? what is the background? why you need to store the varbinary data in nvarchar format? Any reason for all this? Coming to the solution part, it would have been simple if you had SQL 2008. You would have then use an explicit conversion to nvarchar with format 1 OR 2 i.e. CONVERT(nvarchar(100),varbinarystring, 1) But in SQL 2005, neither implicit conversion nor explicit conversion does the trick. But there are two workarounds 1. Using Xquery functionality 2. Using fn_varbintohexstr system function Following is the example /*======= DATA INSERTION PART =======================*/ DECLARE @table TABLE ( Uid INT ,[PASSWORD] NVARCHAR(100) ,Passwordhash NVARCHAR(100) ) INSERT @table ( [Uid] ,[PASSWORD] ,[Passwordhash] ) SELECT 121 , '1234wrwrwr' , NULL UNION ALL SELECT 122 , '4etetxwacb' , NULL /*==============================================================*/ /*========= Hashed Ourtput in varbinary format ================*/ SELECT [Uid] , [PASSWORD] , HashBytes('SHA1', [password]) [Passwordhash] FROM @table AS T /*====== UPDATEd hashed value by using XQuery functionality =====*/ UPDATE @table SET [Passwordhash] = N'0x' + CONVERT(XML, N'').value('xs:hexBinary(sql:column("HASHEDPASSWRD") )', 'nvarchar(100)') FROM @table AS T CROSS APPLY ( SELECT HASHBYTES('SHA1', [password]) HASHEDPASSWRD ) x SELECT * FROM @table AS T /*====== UPDATEd hashed value by using fn_varbintohexstr system function =====*/ UPDATE @table SET [Passwordhash] = master.sys.fn_varbintohexstr(HASHBYTES('SHA1', [password])) SELECT * FROM @table AS T Hope it helps.
9 People are following this question.
