|
• الانـتـسـاب » Jan 2014
|
• رقـم العـضـويـة » 116050
|
• المشـــاركـات » 6,995
|
• الـدولـة » Egypt
|
• الـهـوايـة » ;"#string n = "C
|
• اسـم الـسـيـرفـر » No Server
|
• الـجـنـس » Male
|
• نقـاط التقييم » 191
|
|
|
رد: ازاي ادي Ban IP
Ban account
كود PHP:
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 -----------------------------------------------------------------
ban IP By CharName
كود PHP:
How to Ban IP By CharName :D
Credits to @Caipi
My Query to Ban Player
PHP Code: --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
PHP Code: --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
PHP Code: 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
PHP Code: 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
PHP Code: 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
PHP Code: 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
PHP Code: 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
PHP Code: 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]
|