How to Ban IP By CharName :D

[nukertube]

Credits to @Caipi

My Query to Ban Player

--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]           
GO
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @CharName VARCHAR(64); 
DECLARE @AccJID INT;           
---------------------------------
SET @CharName =    'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason    =    'Ban Reason Here'
SET @Begin    =    '2012-05-01 00:00:00.000' --    Time Begin    --
SET @End    =    '2040-01-01 00:00:00.000' --    Time End    --
---------------------------------
---------------------------

INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
--By Leandro ( nukertube ) -- 

Querry to Unban Player

--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]           
GO
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @CharName VARCHAR(64); 
DECLARE @AccJID INT;           
---------------------------------
SET @CharName =    'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
--------------------------------
USE [SRO_VT_ACCOUNT]
--------------------------------
DELETE FROM [dbo].[_BlockedUser]
WHERE UserJID = @AccJID
DELETE FROM [dbo].[_Punishment]
WHERE UserJID = @AccJID

--By Leandro ( nukertube ) --  

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
----------------------------------------------------------------- 

(Querry to ban and Unban by me! +1 Like :D)

Credits to @Caipi ->

query to create table to store the ip of the players

USE Log_DB

CREATE TABLE _IPLogs ( 

[No.] int IDENTITY(1,1) PRIMARY KEY, 
[CharID] int, 
[Charname] varchar(max), 
[IP] varchar(max), 
[Date] datetime 
); 

stored product to record the ip of the players at the table _IPlogs

USE [Log_DB] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 


ALTER   procedure [dbo].[_AddLogChar]  
@CharID        int, 
@EventID        tinyint, 
@Data1        int, 
@Data2        int, 
@strPos        varchar(64), 
@Desc        varchar(128) 
as 

    IF(@EventID = 4 OR @EventID = 6) 
    BEGIN 

    declare @len_pos     int 
    declare @len_desc    int 
    set @len_pos = len(@strPos) 
    set @len_desc = len(@Desc) 
    if (@len_pos > 0 and @len_desc > 0) 
    begin     
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)     
    end 
    else if (@len_pos > 0 and @len_desc = 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos) 
    end 
    else if (@len_pos = 0 and @len_desc > 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc) 
    end 
    else 
    begin 
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2) 
    end 
     
    --For the new IPLog table 
    Declare @DynIP varchar(max); 
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) 
    -- END 

    END 

Okay, now the ip will be archived and it will be possible to display the name of the char
EDITED
--> Only if the _IPLogs table doesn't contain already IP's of the Char you want to ban

USE [Log_DB]

Declare @Charname varchar(max);

SET @Charname = 'CharNameHere'

SELECT Data2 INTO #temp_table FROM _LogEventChar 
WHERE CharID = (SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16 like @Charname) AND (EventID = 4 OR EventID = 6)
GROUP BY Data2
ORDER BY Data2 asc

Declare @Counter int = (SELECT MIN(Data2) FROM #temp_table), @IP varchar(max);

CREATE TABLE #temp_table_result (
[IP] varchar(max)
);

WHILE (@Counter <= (SELECT MAX(Data2) FROM #temp_table))
    BEGIN
        exec @IP = SRO_VT_ACCOUNT.dbo.split_ip @Counter
        INSERT INTO #temp_table_result SELECT @IP
        SET @Counter = (SELECT MIN(Data2) FROM #temp_table WHERE Data2 > @Counter)
    END

SELECT * FROM #temp_table_result

DROP TABLE #temp_table
DROP TABLE #temp_table_result 

Now the table where the IP will be blocked

CREATE TABLE _BannedIPs (
[No.] int PRIMARY KEY IDENTITY (1,1),
[IP] varchar(max) NOT NULL
);   

Now the stored product to block all accounts that connect the blocked ip

USE [SRO_VT_ACCOUNT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_BannPlayerSP]         

@CharName varchar(max),
@Reason varchar(max)

as
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @AccJID INT;           
---------------------------------

SET @ChrID=(SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM SRO_VT_SHARD.dbo._User WHERE CharID=@ChrID)

----------------------

DECLARE 
        @ID VARCHAR(64) = (Select STRuserID FROM TB_User Where JID = @AccJID),
        @Begin DATETIME = GETDATE()-1,
        @End DATETIME = GETDATE()+3650


INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
----------------------------------------------------------------- 

Last stored product

USE [Log_DB] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 


ALTER   procedure [dbo].[_AddLogChar]  
@CharID        int, 
@EventID        tinyint, 
@Data1        int, 
@Data2        int, 
@strPos        varchar(64), 
@Desc        varchar(128) 
as 

    IF(@EventID = 4 OR @EventID = 6) 
    BEGIN 

    declare @len_pos     int 
    declare @len_desc    int 
    set @len_pos = len(@strPos) 
    set @len_desc = len(@Desc) 
    if (@len_pos > 0 and @len_desc > 0) 
    begin     
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)     
    end 
    else if (@len_pos > 0 and @len_desc = 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos) 
    end 
    else if (@len_pos = 0 and @len_desc > 0) 
    begin      
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc) 
    end 
    else 
    begin 
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2) 
    end 
     
    --For the new IPLog table 
    Declare @DynIP varchar(max); 
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) 
    -- END
    -- Banned IP stuff
    IF exists (SELECT IP FROM _BannedIPs WHERE IP like @DynIP)
    BEGIN
    Declare @Charname varchar(max) = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID)
    
    exec SRO_VT_ACCOUNT.dbo._BannPlayerSP @Charname,'usage of a banned IP'
    -- END banned IP stuff
    END

    END 

After Run all tables and stored products, to ban an ip you just run the query to find the ip by the name of the character, get the ip, and paste the table _bannedips (in log_DB)


Plz THX for me and Caipi[/FONT][/COLOR][/SIZE]

[MentaL]

[epvp2]

thanks very helpful

[nukertube]

:D Like This

[xxNukertube]

Nice !

[fabien31]

Too bad this is not works correctly :(

[trytokillme]

Msg 1046, Level 15, State 1, Procedure _AddLogChar, Line 39
Subqueries are not allowed in this context. Only scalar expressions are allowed.

[fabien31]

try to use mssql 08 :)

Only think do not working here it's if you add a IP on bannedIPs the query do not check it and do not ban any other char added :)

IF anyone have a solution to this issue that will be helpfull for all :)

[xxNukertube]

Original Thread

[farawy]

Thanks Works prefect
you are Rock

[xxNukertube]

xD you welcome