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.
| Author |
Topic |
|
stegothdump
Starting Member
9 Posts |
 Posted - 2012-01-17 : 07:10:47
|
| I've been banging my head against a wall on this one...written the following SQL:DECLARE @Salt NVARCHAR(MAX)EXEC dbo.sp_GenerateRandomString 8, @Salt OUTPUTUPDATE RegisteredUsersSET NopPassword =SUBSTRING(UPPER(master.dbo.fn_varbintohexstr(CAST(HASHBYTES('SHA1', Password + @Salt)as varbinary ))),0,40), PasswordSalt = @SaltI have 2 main issues with this..1. The @Salt output is only being run once and everypassword is getting the same Salt random string, how can i get this to run for every row?2. Noppassword is coming out wrong...I'm getting 0X66282D0024753D447401600362C9E55213DEA (exmaple) where if i use an online tool to generate the hash ([url]http://www.tools4noobs.com/online_php_functions/sha1/[/url]) i get the correct value - 28ea10d764bf99d5cce7564e6ea410ea119fc5e7Getting very confused on this one...if i dont use the function fn_varbintohexstr the output is weird symbols...Cany anyone shed any light?S |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-17 : 07:15:16
|
| Why not use some attribute of the record for the SALT (e.g. an IDENTITY column, or the PK value - if it never changes)? You are storing the SALE in [PasswordSalt] column anyway.If you want random values in a set-based process like UPDATE then using NewID() to get a GUID (per row processed) is the only Random method that I know of. |
 |
|
|
stegothdump
Starting Member
9 Posts |
Posted - 2012-01-17 : 07:54:56
|
| Hey there, managed to sort out the @Salt output but using a cursor...the ONLY issue i have is the HASHBYTE.If i just use "HASHBYTES('SHA1', Password + @Salt" the output i get it weird symbols...when i try to CAST i get a HEX value instead the HASHED value, this also happens when i use convert??Closest i get is - 0X66282D0024753D447401600362C9E55213DEABut i should be - 28ea10d764bf99d5cce7564e6ea410ea119fc5e7Thanks for looking...S |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-17 : 08:33:45
|
| Note that the return datatype from HASHBYTES is varbinary(8000), you are casting it as "varbinary" with no size, I don;t know what the default is but I personally would want to explicitly state the size I wanted - but truncating it is going to be risky anyway, isn't it?Why not change your [NopPassword] column to be VARBINARY datatype, rather than VARCHAR ? then you can store it natively."managed to sort out the @Salt output by using a cursor."I suppose that's OK if its a one-off, but otherwise cursors are slow and will not scale well."But i should be - 28ea10d764bf99d5cce7564e6ea410ea119fc5e7"Including the specific random SALT that was generated for your test case?Does it matter if it is repeatable? |
|
|
|
stegothdump
Starting Member
9 Posts |
Posted - 2012-01-17 : 08:57:21
|
| Thanks for the reply Kristen...yeah the cursor is fine, its a one off script that i will run (once i get it right!)Without any conversion the output of the HASBYTES is "‰~S´�£ U Æ[ÀP¹ÄJ�"The whole reason I am trying to do this is to match up to a new ecommerce system, they use a Hashed password and Salt Key. Saly Key is fine it just getting the hashed version of the password that's giving me the headache.TIA.S |
|
|
|
stegothdump
Starting Member
9 Posts |
Posted - 2012-01-17 : 09:13:26
|
| As an example...taking the password "Test123" and the random Salt "w$[}Q[XP" my output in SQL is = 95F70A3A5315E5E378CA92FA0874448CA88FCCF8 (this is incorrect)using the online tool above i get 61f3a492d67af333d930e936e305c1ab66308543 (this is correct), how can these be different?S |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-17 : 09:29:31
|
| Ah, I see, you are trying to generate it to be the same as a 3rd party package.NVARCHAR will have two-bytes-per-character, which presumably HASHBYTES() will include in the calculation.If you just want to process ASCII characters use VARCHAR definition [i.e. for Password and SALT] instead of NVARCHARNOTE: you may get a different result, compared to the 2rd party app, if a password includes "£" or similar ASCII characters outside the 32-127 range. You might want to check that no passwords include such characters, but beware that the Collation you use may well "disguise" attempts to find them using Regular Expressions and LIKE ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-17 : 09:39:27
|
| Finding non-ascii cahracters in a column:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164618 |
|
|
|
stegothdump
Starting Member
9 Posts |
Posted - 2012-01-17 : 11:54:45
|
| Thanks Kristen, by changing all of the fields involved to VarChar its all wokring as it should, thanks for the pointer on this one!Cheers,S |
|
|
|
|
|
|
|
|
Create Hashed password and salt from text password