[FastTutorial]Prevent multi login from same IP

[beetols]

Since i didn't find anything released about and many admins still are not so familiar with SQL, I decided to share this easy way for prevent people to dual-box, statpad etc. that i just made for my server.

First off, if you read official rules from Shaiya, multilogin is not allowed for ANY reason from the same IP, but I'm the first that want allow everyone to play, even from the same IP, so i added an exceptions column that you can manage manually.

I know some admins allow players use multiaccount, but i prefer to prevent it instead go in the logs and spy players for see if they are using the multilogin properly!

First we need to store the UserIP on login, so we need to edit PS_UserData.dbo.UserLoginStatus:

USE [PS_UserData]
GO

/****** Object: Table [dbo].[UserLoginStatus] Script Date: 10/9/2016 1:38:53 AM ******/
DROP TABLE [dbo].[UserLoginStatus]
GO

/****** Object: Table [dbo].[UserLoginStatus] Script Date: 10/9/2016 1:38:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserLoginStatus](
[UserUID] [int] NOT NULL,
[UserIP] [varchar](15) NOT NULL, --Here we add the new column for check the User IP
[LoginStatus] [tinyint] NOT NULL,
[LoginTime] [datetime] NULL,
[LogoutTime] [datetime] NULL,
[LoginSession] [bigint] NULL,
[LogoutSession] [bigint] NULL,
[LastPlayTime] [int] NULL,
CONSTRAINT [PK_UserLoginStatus] PRIMARY KEY CLUSTERED
(
[UserUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Not less important we need that some Players will have the opportunity to login always so we add a column into PS_UserData.dbo.Users_Master:

ALTER TABLE PS_UserData.dbo.Users_Master
ADD Exception INT

ALTER TABLE [PS_UserData].[dbo].[Users_Master] ADD CONSTRAINT [DF_Users_Master_Exception] DEFAULT ((0)) FOR [Exception]


UPDATE PS_UserData.dbo.Users_Master SET Exception = 0

The column Exeption will be 0 for all players; You can set it to 1 for bypass the procedure.

Now we can start with the procedure that manage the login.
Open PS_UserData/programmability/Stored Procedures/dbo.usp_Try_GameLogin_taiwan rightclick/Modify.

On the bottom of the procedure You'll read:

-- 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

Here is where it EXECute the login after all previous checks. Let's edit it for add the check about the UserIP:

-- check Multi Login Account
DECLARE @exception INT -- REMEMBER ALWAYS TO DECLARE A VARIABLE
DECLARE @logStatus INT
SET @exception = (SELECT Exception FROM dbo.Users_Master WHERE UserUID = @UserUID)
if @exception = 0
begin
SET @logStatus = (select loginstatus from PS_userdata.dbo.UserLoginStatus where UserIP = @userip AND LoginStatus =1)
if @logStatus = 1
begin
SET @Status = -3 -- If multi login status = -3
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

if @useruid=(select useruid from PS_userdata.dbo.UserLoginStatus where UserUID = @useruid)
update PS_userdata.dbo.UserLoginStatus
set loginstatus=1,UserIP=@UserIP,LoginTime=@LoginTime, LogoutTime=@LoginTime,loginSession=@SessionID,last playtime=0
where useruid=@useruid
else
insert into PS_userdata.dbo.UserLoginStatus(UserUID,UserIP,log instatus,logintime,logouttime,loginsession,lastpla ytime) values(@useruid,@UserIP,1,@LoginTime,@LoginTime,@S essionID,0)

END

And last but not less important, let's edit the logout procedure for remove the loginstatus = 1 from the PS_userdata.dbo.UserLoginStatus.
Also in the same directory from dbo.usp_Try_GameLogin_taiwan open usp_Try_GameLogout_R

and before the last row add:

update PS_userdata.dbo.UserLoginStatus set loginstatus=0,LogoutTime=@LogTime,loginSession=@Se ssionID,lastplaytime=0 where useruid=@Useruid

(read first, if you already have something similar to this, replace it)


Only self kill Padding prevent
If you think that is to much don't allow people to multi login because the help that this give top players, but you like a bit more security, we can quickly edit this script for don't allow players to log inside the opposite faction.
If we look into the check of multi login Account:

-- check Multi Login Account
DECLARE @exception INT -- REMEMBER ALWAYS TO DECLARE A VARIABLE
DECLARE @logStatus INT
SET @exception = (SELECT Exception FROM dbo.Users_Master WHERE UserUID = @UserUID)
if @exception = 0
begin
if @logStatus=(select loginstatusfrom PS_userdata.dbo.UserLoginStatus where UserIP = @userip and loginstatus=1)
begin
SET @Status = -3 -- If multi login status = -3
end
end

We can add another if for see what faction is logged and if is different, block him:

-- check Multi Login Account
DECLARE @exception INT -- REMEMBER ALWAYS TO DECLARE A VARIABLE
DECLARE @logStatus INT
SET @exception = (SELECT Exception FROM dbo.Users_Master WHERE UserUID = @UserUID)
if @exception = 0
begin
SET @logStatus = (select loginstatus from PS_userdata.dbo.UserLoginStatus where UserIP = @userip)
if @logStatus = 1
begin
DECLARE @logUID INT
DECLARE @faction1 INT
DECLARE @faction2 INT
SET @logUID = (select UserUID from PS_userdata.dbo.UserLoginStatus where UserIP = @userip AND LoginStatus = 1)
SET @faction1 = (SELECT Country FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID = @logUID)
SET @faction2 = (SELECT Country FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID = @UserUID)
if @faction1 != @faction2
begin
SET @Status = -3 -- If multi login status = -3
end
end
end

I didn't test it yet on a open server, so for any issue just let me know..
and if you get error processing the querys READ IT BEFORE POST THE LOGS.

[Velocity.]

not working for me

[beetols]

add me on skype and let's see why


















<--- SKYPE

[Velocity.]

okay thanks

[[DEV]Arkham]

there is a small problem with this script.
if i log in with 2 or more account with the same ip, however, I use one before and one after.
the scripts receive 2 or more rows, so, is better change this

SET @logstatus = (select loginstatus from PS_userdata.dbo.UserLoginStatus where UserIP = @userip)

with

SET @logs@tatus = (select max(loginstatus) from PS_userdata.dbo.UserLoginStatus where UserIP = @userip)

so, if we have logged with multiple accounts, though, in this case we use two or more accounts, the script will work the same, or we will can loggin into accounts with the same ip

And you set lastplaytime=0, i don't like it, i have edit it for get the time in hours, is possible change "hh" to "mi" for minutes, and "ss" for seconds.

--SELECT Log in time
DECLARE @LogIN datetime = (SELECT LoginTime FROM PS_UserData.dbo.UserLoginStatus WHERE UserUID @userUID)

.....

lastplaytime=DATEDIFF(hh, @LogIN, @LogTime)

sry, for my bad english ^_^

[beetols]

Thanks for the notice, you are right about the loginstatus, need to see where is loginstatus = 1 and not only by ip, i edited the query!

[beetols]

Someone asked me in privat about the error message on login and even if I think is easy to get it, I'll explain how to change it here.

Since @ = -3 is banned we need to change it for get a not usual error, so in the procedure edit it into something like @ = 1

Now run game.exe and try the multi login, you will get a error message, open sysmsg-uni.ini in your data folder and search the text of the error, edit it with something like: "can't login with more than 1 IP. For exceptions contact the staff." and you are done.

[[GM]LibertyBelle]

Nice script.

Can we modify or add something to the script that it will apply only on a certain map?
Thanks.

[beetols]

Originally Posted by [GM]LibertyBelle

Nice script.

Can we modify or add something to the script that it will apply only on a certain map?
Thanks.

Yes, with some SQL knowledge is possible. You have to do it on the procedure from ActionLog.

PS: You cannot really stop it in game, but you can bann the accounts after they logout. Database side doesn't work on real time ingame events, if you want that players get teleported in another map if multilogin have to find someone that do it for you on CE.

[[GM]LibertyBelle]

Noted and Thank you.

[beetols]

Originally Posted by [GM]LibertyBelle

Noted and Thank you.

You are welcome, there is a "Thanks" button for thanks someone without bump the thread.

If you'll try to do it and need a bit of help, you can contact me on skype and I'll try to give you some support. I'm busy so i can just guide you a little bit.
Good Luck with it

[mervinray29]

Msg 207, Level 16, State 1, Line 8
Invalid column name 'Exception'.

[beetols]

The column name Expection is invalid, it mean that you have to make it valid :P this is the step you had error

--run firt this separated from the rest:
ALTER TABLE PS_UserData.dbo.Users_Master
ADD Exception INT
--than this:
ALTER TABLE [PS_UserData].[dbo].[Users_Master] ADD CONSTRAINT [DF_Users_Master_Exception] DEFAULT ((0)) FOR [Exception]
--And as last:
UPDATE PS_UserData.dbo.Users_Master SET Exception = 0

[theartboom]

Only self kill Padding prevent
If you think that is to much don't allow people to multi login because the help that this give top players, but you like a bit more security, we can quickly edit this script for don't allow players to log inside the opposite faction.
If we look into the check of multi login Account:

We can add another if for see what faction is logged and if is different, block him:


I didn't test it yet on a open server, so for any issue just let me know..
and if you get error processing the querys READ IT BEFORE POST THE LOGS.[/QUOTE]





I'm lost in this part, kindly help me please

[beetols]

Originally Posted by theartboom

I'm lost in this part, kindly help me please

If you read the process before you'll understand where to do this. This is only a modification of the previous script.