1 Attachment(s)
How to use encryption of account passwords.
Here is a solution to add MD5 encryption to passwords in database of all user accounts.
It does require a bit of knowledge of MSSQL
First off you'll need a plugin dll to MSSQL which i have attached to this post, put it somewhere on the server (like c:\windows\system32 would be a good choice)
Fire up MSSQL Manager and go to Master database (in MSSQL 2005 its under system databases) go to programmability->Extended Stored Procedures and rightclick on it, select New Extended Stored Procedure. A window will popup asking for a name and a dll location.
Name: xp_md5
DLL: c:\windows\system32\xp_md5.dll (or wherever you put the dll)
Click ok. You should now see your new extended stored procedure called dbo.xp_md5 under the master database.
Close the master database and open your RYL user database (youxiuser) click on New Query up in the top of the management tools and paste this into the new box that popup.
-- Snip --
CREATE FUNCTION [dbo].[fn_md5]
(
@data AS TEXT
)
RETURNS VARCHAR(30) AS
BEGIN
DECLARE @hash VARCHAR(32)
EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
RETURN @hash
END
-- End Snip --
Close user database again and open general MSSQL Security->Logins and open properties on your user account you use to access your databases. Click on User Mapping, click on Master database in the list and tick it and in the bottom window (database roles) it should only have tick in Public nothing else. Click ok.
Now you'll want to change the way passwords are inserted and read in the RYL stored procedures, so open up youxiuser again and go to programmability->stored procedures Right click on dbo.spUserAuthUID->Modify
Find the place that looks like this:
ALTER PROCEDURE [dbo].[spUserAuthUID]
@ID varchar(20),
@PASSWD varchar(30)
AS
..
Change the line: @PASSWD varchar(30) to: @PASS varchar(30)
Find the place that looks like this:
DECLARE @UID int
SELECT @UID = uid FROM usertbl WHERE account = @ID AND passwd = @PASSWD
IF @UID IS NULL
BEGIN
..
Insert these 2 lines after DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
Click on execute.
Make this change to the stored procedure you use to insert users (like spJoinUser) [Change the length of pass in spJoinUser to varchar(30) instead of varchar(20)] and insert the same 2 lines again you did in previous stored procedure:
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
(do this after DECLARE @UID int)
Hit execute again.
--
Thats all you're done. Passwords of all users are now encrypted on both read and write.
If you wish to change passwords of users manually in your database you must first encrypt the password, you can do this by hitting: New Query when you are in the youxiuser database and write this: select dbo.fn_md5("cleartextpass"); and hit execute. It will then show you the encrypted password in a result box.
NOTE: You cannot convert encrypted passwords to clear text its a one way only conversion, so if someone forget their password it cannot be retrieved and you'll have to set a new password for that user instead.
I would advise you test if things are working after you're done with these steps. To do so insert a new user by right clicking on the spJoinUser stored procedure and click Execute. Fill in the details of the user and password (in clear text) and hit ok. Then go open your usertbl table and check that the user data is correct and that the password is encrypted. Should look something like this:
uid: whatever number is the newest user
account: test
passwd: eebf768ff8f49081d02d2be127f51a
jointime: blahblah
lastlogin: blahblah
..
Phew that was alot of writing :)
Enjoy.
Re: [Share] How to use encryption of account passwords.
rly excellent work
congratulations xD
Re: [Share] How to use encryption of account passwords.
and you also have to implement the proceedure into register/change pass script?
or does the database decyrpt and encyrpt the password automatically as it enters/exits?
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
oskdemon
and you also have to implement the proceedure into register/change pass script?
or does the database decyrpt and encyrpt the password automatically as it enters/exits?
You have to do it in all stored procedures that deals with reading/writing passwords. But no biggie its only 2 lines ;)
As for doing stuff from a website to db.. Depends if you are executing a stored procedure from webpage or if you are inserting/reading data directly. If its directly then you would have to do some work around, I'd advice you setup your website scripts to use stored procedures so you dont have to change too much. But you can do it directly in script with a function already present in PHP:
-- Snip --
// Create a password hash to insert to db.
$newuser = $_POST['register_acct'];
$newpass = md5($_POST['register_pass1']);
..
@mssql_select_db("youxiuser", $dblink);
$query = @mssql_query("INSERT INTO usertbl (account, passwd) VALUES ('$newuser', '$newpass')");
if (@mssql_rows_affected($dblink) < 1);
.. Error Inserting..
-- End Snip --
Re: [Share] How to use encryption of account passwords.
ppl should use SP's cose those are meant for this job and not do things directly.
OOP!
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
alphaest
ppl should use SP's cose those are meant for this job and not do things directly.
OOP!
Yes plus they offer better protection against injection attacks instead of inserting directly to db.
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
Tyfix
Its okay bumping my thread :)
nice share, not sure if anyone pointed it out but passwords will still be xfered in cleartext (thats how ryl was designed) this just crypts the storage.
Handy for when the private server your playing on gets hacked the next day and all your passwords r h4xed
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
yoshi1337
nice share, not sure if anyone pointed it out but passwords will still be xfered in cleartext (thats how ryl was designed) this just crypts the storage.
Handy for when the private server your playing on gets hacked the next day and all your passwords r h4xed
Yes this is true, but nothing we can do about that (Well except tunnel connection through a ssh tunnel)
Re: [Share] How to use encryption of account passwords.
that's right ?
dbo.spUserAuth
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE spUserAuthUID
@ID varchar(20),
@PASS varchar(30),
@ip varchar(15)
AS
SET NOCOUNT ON
-- ?? ?? ? UID? ??
DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
SELECT @UID = uid FROM usertbl WHERE account = @ID AND passwd = @PASSWD
IF @UID IS NULL
BEGIN
SELECT 1 AS errcode, @UID AS uid, N'??? ??? ???? ??? ??? ??' AS comment
RETURN 1 -- ??? ??? ???? ??? ??? ??
END
ELSE IF @UID = 0
BEGIN
SELECT 2 AS errcode, @UID AS uid, N'UID? ??' AS comment
RETURN 2 -- UID? ??
END
-- ?? ??? ??
EXEC spUpdateLastloginUID @UID
SELECT 0 AS errcode, @UID AS uid, N'?????' AS comment
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
dbo.spJoinUser
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE spJoinUser
@ACCOUNT varchar(20),
@PASS varchar(30),
@WRITER varchar(20),
@SEX char(2),
@BIRTH char(11),
@ZIP_CODE varchar(20),
@ADDRESS1 varchar(80),
@ADDRESS2 varchar(80),
@MOBILE varchar(20),
@PHONE varchar(20),
@EMAIL varchar(50),
@JOB varchar(20),
@RESIDENT_NUM varchar(20),
@LANGUAGE varchar(30),
@broker int
AS
SET NOCOUNT ON
DECLARE @ERROR int
DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
SELECT @UID = UID FROM USERTBL WHERE ACCOUNT = @ACCOUNT
IF @UID IS NULL
BEGIN
INSERT USERTBL (ACCOUNT,PASSWD,WRITER,SEX,BIRTH,ZIP_CODE,ADDRESS1,ADDRESS2,MOBILE,PHONE,EMAIL,JOB,RESIDENT_NUM,timechk,broker) VALUES
(@ACCOUNT,@PASSWD,@WRITER,@SEX,@BIRTH,@ZIP_CODE,@ADDRESS1,@ADDRESS2,@MOBILE,@PHONE,@EMAIL,@JOB,@RESIDENT_NUM,@LANGUAGE,@broker)
SELECT @ERROR = @@ERROR
-- Add Service Points
--IF @ERROR = 0
--BEGIN
--EXEC pay_gamemyth..spAddServicePoints @ACCOUNT
--END
SELECT @ERROR AS ERRCODE
RETURN @ERROR
END
ELSE
BEGIN
SELECT 1 AS ERRCODE
RETURN 1
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
i do all rights to my user of ryl use.. i create the acc in website.. and result this in user db
http://img291.imageshack.us/img291/8813/imagemjvt.jpg
but when i try login in game... .. user or password is wrong
ty in advanced
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
Oraclebr
that's right ?
dbo.spUserAuth
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE spUserAuthUID
@ID varchar(20),
@PASS varchar(30),
@ip varchar(15)
AS
SET NOCOUNT ON
-- ?? ?? ? UID? ??
DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
SELECT @UID = uid FROM usertbl WHERE account = @ID AND passwd = @PASSWD
IF @UID IS NULL
BEGIN
SELECT 1 AS errcode, @UID AS uid, N'??? ??? ???? ??? ??? ??' AS comment
RETURN 1 -- ??? ??? ???? ??? ??? ??
END
ELSE IF @UID = 0
BEGIN
SELECT 2 AS errcode, @UID AS uid, N'UID? ??' AS comment
RETURN 2 -- UID? ??
END
-- ?? ??? ??
EXEC spUpdateLastloginUID @UID
SELECT 0 AS errcode, @UID AS uid, N'?????' AS comment
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
dbo.spJoinUser
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE spJoinUser
@ACCOUNT varchar(20),
@PASS varchar(30),
@WRITER varchar(20),
@SEX char(2),
@BIRTH char(11),
@ZIP_CODE varchar(20),
@ADDRESS1 varchar(80),
@ADDRESS2 varchar(80),
@MOBILE varchar(20),
@PHONE varchar(20),
@EMAIL varchar(50),
@JOB varchar(20),
@RESIDENT_NUM varchar(20),
@LANGUAGE varchar(30),
@broker int
AS
SET NOCOUNT ON
DECLARE @ERROR int
DECLARE @UID int
DECLARE @PASSWD VARCHAR(30)
SET @PASSWD = dbo.fn_md5(@PASS);
SELECT @UID = UID FROM USERTBL WHERE ACCOUNT = @ACCOUNT
IF @UID IS NULL
BEGIN
INSERT USERTBL (ACCOUNT,PASSWD,WRITER,SEX,BIRTH,ZIP_CODE,ADDRESS1,ADDRESS2,MOBILE,PHONE,EMAIL,JOB,RESIDENT_NUM,timechk,broker) VALUES
(@ACCOUNT,@PASSWD,@WRITER,@SEX,@BIRTH,@ZIP_CODE,@ADDRESS1,@ADDRESS2,@MOBILE,@PHONE,@EMAIL,@JOB,@RESIDENT_NUM,@LANGUAGE,@broker)
SELECT @ERROR = @@ERROR
-- Add Service Points
--IF @ERROR = 0
--BEGIN
--EXEC pay_gamemyth..spAddServicePoints @ACCOUNT
--END
SELECT @ERROR AS ERRCODE
RETURN @ERROR
END
ELSE
BEGIN
SELECT 1 AS ERRCODE
RETURN 1
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
i do all rights to my user of ryl use.. i create the acc in website.. and result this in user db
http://img291.imageshack.us/img291/8813/imagemjvt.jpg
but when i try login in game... .. user or password is wrong
ty in advanced
Try run the procedure directly in sql manager and see what happens. Rightclick on spUserAuth->Execute Stored Procedure->Input the correct user/pass and click ok. If everything works correctly it should say Query Executed Successfully and the Return Value should be 0 (Also check the authserver/uidserver logs for any issues)
Re: [Share] How to use encryption of account passwords.
Re: [Share] How to use encryption of account passwords.
looks fine, you input non existing user or password didn't match.
However, you're executing spUserAuthUID which is fine if you're sure it uses that sp on the auth method you're using.
Re: [Share] How to use encryption of account passwords.
thank you alot.. i'll change another spUserAuth for certify that is right
EDIT:
tyfix now work fine.. sp.UserAuth is that this version works
ty again bro
Re: [Share] How to use encryption of account passwords.
Quote:
Originally Posted by
Oraclebr
thank you alot.. i'll change another spUserAuth for certify that is right
EDIT:
tyfix now work fine.. sp.UserAuth is that this version works
ty again bro
Ah excellent :)
