SQL Server 2008 Insert with Hashbytes & SALT

Hello,

I would be grateful if you could sense check my code please, I'm trying to create an insert SP that SHa1 hashes the password and salt combination,  and then returns the data to a recordset.....

I got some of the code from here
http://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-hashbytes-function/

Although unlike the source above i read that the Sha1 hashed password is always binary 20 characters, so my database table reflects this....

I have a red line under SCOPE_IDENTITY on line 46...

Here is my code,

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CandidateRegister]
@Username nvarchar(350),
@Password nvarchar(50),
@FirstName nvarchar(50),
@LastName nvarchar(80)
AS
SET NOCOUNT ON;

/*Declare additional variables */
DECLARE @Result int
DECLARE @UserID NVARCHAR(225)
DECLARE @UserAuth NVARCHAR(225)
DECLARE @SALT NVARCHAR(25)
DECLARE @Seed int
DECLARE @LCV tinyint
DECLARE @CTime DateTime
DECLARE @PWDWITHSALT NVARCHAR(125)

BEGIN

IF EXISTS (SELECT ID from dbo.Candidate where Username = @Username)
BEGIN
SET @Result = '1'
END
ELSE
BEGIN
SET @CTime = GETDATE()
SET @Seed = (DATEPART(HH, @CTime) * 10000000) + (DATEPART(n, @Ctime) * 100000) + (DATEPART(s, @CTime) * 1000) + DATEPART(ms, @CTime)
SET @LCV = 1
SET @SALT = CHAR(ROUND((RAND(@Seed) * 94.0) + 32, 3))
WHILE (@LCV < 25)
BEGIN
SET @SALT = @SALT + CHAR(ROUND((RAND() * 94.0) + 32, 3))
SET @LCV = @LCV + 1
END
SET @PWDWITHSALT = @Password + @SALT
SET @Result = '2'
INSERT INTO dbo.Candidate (Username, [Password], FirstName, LastName, DateRegistered, Authorised, JobAlertFrequesncy, LastAccess, SALT)
VALUES (@Username, HASHBYTES('SHA1', @PWDWITHSALT), @FirstName, @LastName, GETDATE(), 'N', 1, GETDATE(), @SALT)
SET @UserID = SCOPE_IDENTITY
SET @UserAuth = HASHBYTES('SHA1', @PWDWITHSALT)
INSERT INTO dbo.Profile(AccountID, DateCreated)
Values (@UserID, GETDATE())
END
Select 
@Result as Result, 
@UserID as UserID, 
@UserAuth as UserAuth
END
GO

Open in new window


Really grateful for any help.

Thank you
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan McCauleyData and Analytics ManagerCommented:
I think you've generally got it (though I've typically seen the salt prepended to a password - however, it works the same either way and is just convention), except that you're misunderstanding the requirements a bit for your salt value. There's no need that it be random or secure - it can even be predictable or well-known, like the user's id - the only requirements are that it be unchanging and that it be relatively unique for each user (though complete uniqueness isn't required). The salt doesn't make the password any more secure or any harder to guess - that's not its purpose. As such, you can swap out the code that loops through 25 times and just replace it with something that generates a single GUID (or even use something else about the user that's non-changing, like their user id). It can even be pretty short - I believe most Linux implementations use just a two-digit random character string.

The purpose of a salt value is only to prevent rainbow attacks - if somebody obtains your list of usernames and password hashes, they won't have everybody's password (since hashes are one-way, and can't be reversed into the original password). However, if you don't have a salt in place, then the attacker could compare the list of password hashes against a pre-computed (or obtained) list of potentially millions of hashes of known passwords - if he finds a match, then he knows the user's password without the need to do any more work. Also, since all users with the same password will have the same hash, he now knows the password of potentially multiple users of the system.

Compare that to adding a salt value - by attaching just a few additional characters to a password that are unique to that particular user, the password hashes are not different for every user (even those with matching passwords), and the passwords are slightly longer (such that a rainbow table becomes infeasible, or at the very least, so that a user's easily-guessed password is now attached to some random characters). Even if the attacker can match a single password hash against their rainbow table, they get only that single user's password - other users who happen to have the same password will have a different salt value, so their password will not be compromised by the broken hash.

I apologize if I'm explaining something you already know, but I just want to make it clear that you can remove some of the complexity from your code - generating a single random value is sufficient for the salt, and there's no need to continually re-seed the random number generator while doing it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
SCOPE_IDENTITY() is a function, thus the brackets are mandatory.

And somehow Ryan is right. I would simply use NEWID(). E.g.

ALTER PROCEDURE [dbo].[CandidateRegister]
    @Username NVARCHAR(350) ,
    @Password NVARCHAR(50) ,
    @FirstName NVARCHAR(50) ,
    @LastName NVARCHAR(80)
AS
    SET NOCOUNT ON;

    DECLARE @Result INT;
    DECLARE @UserID NVARCHAR(225);
    DECLARE @UserAuth NVARCHAR(225);
    DECLARE @SALT NVARCHAR(25);

    BEGIN
        IF EXISTS ( SELECT  ID
                    FROM    dbo.Candidate
                    WHERE   Username = @Username )
            BEGIN
                SET @Result = '1'
            END
        ELSE
            BEGIN
                SET @Result = '2';
                SET @SALT = CAST(NEWID() AS NVARCHAR(255));                
                SET @UserAuth = HASHBYTES('SHA1', @Password + @SALT);
                INSERT  INTO dbo.Candidate
                        ( Username ,
                          [Password] ,
                          FirstName ,
                          LastName ,
                          DateRegistered ,
                          Authorised ,
                          JobAlertFrequesncy ,
                          LastAccess ,
                          SALT
                        )
                VALUES  ( @Username ,
                          @UserAuth ,
                          @FirstName ,
                          @LastName ,
                          GETDATE() ,
                          'N' ,
                          1 ,
                          GETDATE() ,
                          @SALT
                        );
                SET @UserID = SCOPE_IDENTITY();               
                INSERT  INTO dbo.Profile
                        ( AccountID, DateCreated )
                VALUES  ( @UserID, GETDATE() );
            END;

        SELECT  @Result AS Result ,
                @UserID AS UserID ,
                @UserAuth AS UserAuth;
    END;

Open in new window


btw, stored procedures have an defined return value. I would not use a custom one as you did.
0
garethtnashAuthor Commented:
Wow - thank you both :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.