[How To] Password Hashing

[abrasive]

I wrote this during breakfast on my way to work so there isn't a ton of documentation along with it.

A quick answer to why storing passwords (in plain text) in a database is bad:



Here is what my [PS_User_Data].[dbo].[Users_Master] looked like after I implemented password hashing:

As you can see I changed the data types on a few columns, changed the primary key, and removed a/some column(s). Also the UserUID column is set to auto increment now.

Here is what my [dbo].[usp_Try_GameLogin_Taiwan] stored procedure looked like after I implemented password hashing:

 Spoiler

USE [PS_userdata]
GO
/****** Object:  StoredProcedure [dbo].[usp_Try_GameLogin_Taiwan]    Script Date: 08/26/2010 08:10:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  Stored Procedure dbo.usp_Try_GameLogin_Taiwan    Script Date: 2008-6-7 18:34:05 ******/

/*==================================================
@date	2007-12-04
@brief	Login Proc( Taiwan )
==================================================*/

ALTER  Proc [dbo].[usp_Try_GameLogin_Taiwan]

@UserID 	VARCHAR(18),
@InPassword	VARCHAR(32),
@SessionID 	BIGINT,
@UserIP 	VARCHAR(15),
@UserUID 	INT = 0,
@LoginType 	SMALLINT = 1, 
@LoginTime 	DATETIME = NULL

AS

SET NOCOUNT ON

DECLARE 

@Leave 		TINYINT,
@Status 	SMALLINT,
@TempIP 	VARCHAR(15),
@Check		INT

SET @Status = -1
SET @LoginTime = GETDATE()
SET @UserIP = LTRIM(RTRIM(@UserIP))
--------------------------------------------------

SELECT
	@UserUID=[UserUID],
	@Status=[Status],
	@Leave=[Leave]
FROM Users_Master AS UM
WHERE UM.UserID = @UserID
	AND UM.Pw = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,UM.Salt) + @InPassword));

-- No account or incorrect username and password
IF(@@ROWCOUNT = 0)
BEGIN
	SET @Status = 1
END
-- User already logged in
ELSE IF( @Leave = 1 )
BEGIN
	SET @Status = -7
END

-- BlockUser Check
IF( (@Status >= 2) AND (@Status <= 6) )
BEGIN
	-- Get Block Limit Date AND Replace date text
	DECLARE @BlockEndDate datetime
	SELECT @BlockEndDate = BlockEndDate FROM Users_Block WHERE UserUID = @UserUID
	IF ( @@ROWCOUNT <> 0 )
	BEGIN
		-- Block Release
		IF ( @BlockEndDate <= @LoginTime )
		BEGIN
			SET @Status = 0
			UPDATE Users_Master SET Status = @Status WHERE UserUID = @UserUID
		END
	END
END

-- Select 
SELECT @Status AS Status, @UserUID AS UserUID

-- Log Insert
IF( @Status = 0 OR @Status = 16 OR @Status = 32 OR @Status = 48 OR @Status = 64 OR @Status = 80 )
BEGIN
	EXEC usp_Insert_LoginLog_E @SessionID=@SessionID, @UserUID=@UserUID, @UserIP=@UserIP, @LogType=0, @LogTime=@LoginTime, @LoginType=@LoginType
END 
SET NOCOUNT OFF

To add a new user, you would need to run a stored procedure something like this (passing in the appropriate values to the stored procedure obviously):

 Spoiler

DECLARE @username VARCHAR(12);
DECLARE @password VARCHAR(50);
DECLARE @admin TINYINT;
DECLARE @salt VARBINARY(40);
DECLARE @hash VARBINARY(40);

SET @username = 'testnub';
SET @password = 'password';
SET @admin = 0;
SET @salt = CONVERT(VARBINARY,CRYPT_GEN_RANDOM(10));
SET @hash = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,@salt) + @password));

-- Add admin user
IF(@admin = 1)
BEGIN
	INSERT INTO [PS_userdata].[dbo].[Users_Master]
	([UserID],[Pw],[Admin],[AdminLevel],[Leave],[Status],[UserType],[Salt],[CreatedDate])
	VALUES(@username,@hash,1,255,0,16,'A',@salt,GETDATE());
END
-- Add regular user
ELSE IF(@admin = 0)
BEGIN
	INSERT INTO [PS_userdata].[dbo].[Users_Master]
	([UserID],[Pw],[Admin],[AdminLevel],[Leave],[Status],[UserType],[Salt],[CreatedDate])
	VALUES(@username,@hash,0,0,0,0,'N',@salt,GETDATE());
END

SELECT
	[UserUID],
	[Status],
	[Leave]
FROM Users_Master AS UM
WHERE UM.UserID = @username
	AND UM.Pw = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,UM.Salt) + @password));

Basically what is happening when you create a user is:
1. A new user gives you the user name and password they would like to use by filling out a registration form.
2. From the registration script, you pass the user name and password as arguments to a stored procedure meant to create a new user.
3. The stored procedure receives the arguments.
4. The stored procedure generates a unique salt for that user (in this case I use a random number passed into SHA1() to create the unique salt), and stores it in the @salt variable.
5. The stored procedure concatenates the user's plain text password to the salt, and passes the concatenated string into a one-way hashing algorithm (such as SHA1 or MD5), which is then stored in the @hash variable.
6. The stored procedure inserts the data into the [PS_userdata].[dbo].[Users_Master] table.
7. Now you have the password stored in a format that is unreadable and irreversible to humans, but you can still compare against it when users want to log in!

That other table in OMGGame_Web or whatever, we don't care about it anymore. It was redundant data and is now useless.

[Danco1990]

Can we implement this on a running server without overlaps or conflict? Thank you alot.

[abrasive]

Originally Posted by Danco1990

Can we implement this on a running server without overlaps or conflict? Thank you alot.

It's definitely possible as there's no need to restart the game server or web server for it to take effect.

You should do it on your test server first to understand how it works and iron out any problems you run into there. It would be helpful to copy your live user data to the test server to test implementing it with real data as well.

Make sure you back up your live server's data before making any changes obviously.

When changing the table definition on a server that has user accounts already you won't be able to set the "Allow Nulls" constraint to new columns as they won't contain any data yet, but you can add the constraint later once the data is filled in.

You'll have to generate unique salts for every user, you can either do this manually for each account or write a query to do them all at once and test it on your test server to verify it is giving you the expected results. The same goes for concatenating the salt to their current password and generating the password hash.

[ProfNerwosol]

Am I getting it right? You removed RowID and set UserUID as primary key with a clustered index so the results are sorted by it? But, isn't RowID useful here?

I am trying to make UserUID a PK and set it to Auto Increment. Just the dam MSSQL needs a column to be set to identity to allow auto increment and you can set only one identity per table >.<

EDIT:

I don't have Crypt_Gen_Random function. Is there any other way to generate random set of values?

[abrasive]

Originally Posted by ProfNerwosol

Am I getting it right? You removed RowID and set UserUID as primary key with a clustered index so the results are sorted by it? But, isn't RowID useful here?

I am trying to make UserUID a PK and set it to Auto Increment. Just the dam MSSQL needs a column to be set to identity to allow auto increment and you can set only one identity per table >.<

EDIT:

I don't have Crypt_Gen_Random function. Is there any other way to generate random set of values?

I set UserUID to be the primary key as a clustered index, as well as the identity column. RowID didn't seem to serve any purpose after that, but I could be wrong. What are you suggesting RowID's benefit is in this case?

Instead of CRYPT_GEN_RANDOM() you can use something like:

CAST(RAND() * 1000000000 AS INT)

I was just generating a sufficiently large random integer to pass into SHA1() to get a random hash for the salt.

[ProfNerwosol]

Honestly, I'm not sure. In PS_GameDefs.dbo.Items it allows to add new entries and have them all sorted. Here ... I think it had no purpose. I'm still learning and thought that someone smarter put it there and it's best to leave it :/

Btw, is there a way to change a column to Identity without having to recreate table? In SQL Books I downloaded from Microsoft website it says that you cannot make a column as Identity by ALTER.

[abrasive]

It's hard to say what all was altered by others and for what reason. I have no idea how many people tinkered with this database in the past before it landed here.

Some things seem like bad design decisions, such as duplicating the login info in that other table. Also it appears someone removed password hashing at some point based on some of the stored procedure being commented out.

I've just been changing things based on what I've learned in the past about database best practices, performance tuning, normalization, etc.

When I changed the identity column I was able to do so by right-clicking the table and clicking on design. I'm not sure if SQL Server Management Studio was just running an ALTER statement in the background in that case or not.

[ProfNerwosol]

I'm glad you joined our little Shaiya community. Someone with good knowledge of databases and willing to share was definitely needed

[abrasive]

Thanks, I hope people find this useful

[.SweeT]

Thanks

[[Admin]Snuggle]

-Bump-
More people should look into doing this.
I prefer not to let my passwords for Shaiya be known because of an unsecured server.
(Even though I change my pass on every server but a lot of people don't)

[Bаne]

Originally Posted by abrasive

I wrote this during breakfast on my way to work so there isn't a ton of documentation along with it.

Off topic, please tell me you take the subway...and don't drive to work..

[RebeccaBlack]

Just fully applied this to my live server (Nightmare), took me about 2-3 hours to understand it all, convert my entire Users_Master table with a unique SALT for each, and recreate 2 new PHP scripts (I love PHP <3) for Registration, and Pass Changing. This is an awesome tutorial and should be easy for anyone.

I love you in the dearest of ways. *bro hug*

[[Admin]Snuggle]

Originally Posted by tnelis

I love you in the dearest of ways. *bro hug*

I believe you should watch this Tyler o.o
Love you! XD
Now then back on topic
I agree that more people should start doing this.
If your passwords aren't hashed then it can all easily be stolen if someone somehow gets into your database. Meaning that ALL those players account info is out in the open. D: