[Fix] Automated Punishment 10 Minutes!

Jangan · Oct 24, 2011

This will fix this error:

Jangan - [Fix] Automated Punishment 10 Minutes! - RaGEZONE Forums

The Way to do it:

PHP:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_RegisterAutomatedPunishment]    Script Date: 10/24/2011 05:46:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_RegisterAutomatedPunishment]
    @Account    VARCHAR(128),
    @Type          TINYINT,
    @Executor    VARCHAR(128),
    @Guide        VARCHAR(512),
    @Description    VARCHAR(1024),
    @BlockTimeElapse    INT
--    @BlockStartTime    DATETIME,
--    @BlockEndTime    DATETIME
AS
   
    DECLARE @BlockStartTime    VARCHAR(128)
    DECLARE @BlockEndTime    VARCHAR(128)

    SET @BlockStartTime     = getdate()
    SET @BlockEndTime    = dateadd(minute, 10, getdate())
    ------------------------------------------------------------------------------------

    DECLARE @UserJID INT
    SET @UserJID = 0
    DECLARE @NewSerialNo int    
    SET @NewSerialNo = 0
    -- Á¸ÀçÇÏ´Â Ä³¸¯ÅÍ ÀÌ¸§ÀÎÁö? JID ¾÷¾î¿ÀÀÚ.
    SELECT @UserJID = JID FROM TB_User WITH (NOLOCK) WHERE strUserID = @Account
    IF @@ERROR <> 0 OR @UserJID = 0
    BEGIN
        SELECT -1
        RETURN
    END

    -- ÀÌ¹Ì ºí·° °É¸° À¯ÀúÀÌ³Ä?
    IF( EXISTS ( SELECT UserJID FROM _BlockedUser WITH (NOLOCK) WHERE UserJID = @UserJID AND Type = @Type))
    BEGIN
        select -2
        RETURN    
    END

    
    
    INSERT _Punishment values( @UserJID, @Type, @Executor, 0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime, 0)
    set @NewSerialNo = @@identity
    if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
    BEGIN
        SELECT -3
        RETURN        
    END

    INSERT _BlockedUser values( @UserJID, @Account, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
    if( @@rowcount = 0 or @@error <> 0 )
    BEGIN
        SELECT -4
        RETURN        
    END

    SELECT @UserJID

Results:

SGA · Oct 24, 2011

works for me thx

LastThief · Oct 24, 2011

good job

stewie316 · Oct 24, 2011

You sir, are a star!

There is also a problem with it actually reading the 100 year bans as well in _RegisterPunishment?

dbo._RegisterPunishment

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- 
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
ALTER procedure [dbo].[_RegisterPunishment]
	@UserJID 	int,
	@Type	  	tinyint,
	@Executor	varchar(128),
	@Shard		smallint,
	@CharName	varchar(16),
	@CharInfo	varchar(256),
	@PosInfo	varchar(64),
	@Guide		varchar(512),
	@Description	varchar(1024),
	@RaiseTime	datetime,
	@BlockStartTime	datetime,
	@BlockEndTime	datetime,
	@PunishTime	datetime
as
	-- 현재 유저가 Blocking중인지 검사해본다
	if( exists( select UserJID from _BlockedUser with(NOLOCK) where UserJID = @UserJID and Type = @Type))
	begin
		select -1	-- 이전 징계가 아직 풀리지 않았다
		RETURN
	end
	declare @NewSerialNo int	
	set @NewSerialNo = 0
	
	insert _Punishment values( @UserJID, @Type, @Executor, @Shard, @CharName, @CharInfo,
				@PosInfo, @Guide, @Description, @RaiseTime, @BlockStartTime, @BlockEndTime, @PunishTime, 0)
	set @NewSerialNo = @@identity
	if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
	begin
		select -2	-- insert하다가 실패 났다
		return
	end
	declare @UserID varchar(128)
	select @UserID = sr_id from sr_member1 where sr_code = @UserJID
	if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
	begin
		select - 2
		return
	end
	-- 새로운 blocking을 넣어준다
	insert _BlockedUser values( @UserJID, @UserID, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
	select 0

I could be looking at the incorrect table but i imagine the table is not set correct as well here.

Jangan · Oct 25, 2011

give me the consol error and i will trace it

stewie316 · Oct 25, 2011

Under SMC all you get in the SR_UserPunishment is register db is failed along with update db is failed. "db error" so it doesn't block any accounts.

CONSOLE ERROR

Code:

Query Error!!! Query Result : -1, Query : EXEC _UpdatePunishment 2476,745531,1,'sro_gm2', 64, '', '', '', 'Blocked for 10 minutes due to incorrect password entered 5 times', 'Blocked for 10 minutes due to incorrect password entered 5 times', '2011-10-24 23:49

Code:

EXEC _UpdatePunishment 2476,745531,1,'sro_gm2', 64, '', '', '', test ban for me', 'test ban for me', '2011-10-24 23:49:44', '2011-10-24 23:49:37', '2111-10-24 2

Code:

Query Error!!! Query Result : -1, Query : EXEC _RegisterPunishment 745531,2,'sro_gm2',64,'','','','','','2011-10-25 21:37:07','2011-10-25 21:37:05','2111-10-25 21:37:05','2011-10-24 23:19:00'

Code:

SQL Error State:42S02, Native Error Code: D0, ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sr_member1'.

I think some requirements are missing in these two tables just as there was for _AutomatedPunishment.

Hope this helps Jangan. Let me know if you need any other errors.

ItachiSNS · Oct 25, 2011

Jangan said:

This will fix this error:

The Way to do it:

PHP:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_RegisterAutomatedPunishment]    Script Date: 10/24/2011 05:46:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_RegisterAutomatedPunishment]
    @Account    VARCHAR(128),
    @Type          TINYINT,
    @Executor    VARCHAR(128),
    @Guide        VARCHAR(512),
    @Description    VARCHAR(1024),
    @BlockTimeElapse    INT
--    @BlockStartTime    DATETIME,
--    @BlockEndTime    DATETIME
AS
   
    DECLARE @BlockStartTime    VARCHAR(128)
    DECLARE @BlockEndTime    VARCHAR(128)

    SET @BlockStartTime     = getdate()
    SET @BlockEndTime    = dateadd(minute, 10, getdate())
    ------------------------------------------------------------------------------------

    DECLARE @UserJID INT
    SET @UserJID = 0
    DECLARE @NewSerialNo int    
    SET @NewSerialNo = 0
    -- Á¸ÀçÇÏ´Â Ä³¸¯ÅÍ ÀÌ¸§ÀÎÁö? JID ¾÷¾î¿ÀÀÚ.
    SELECT @UserJID = JID FROM TB_User WITH (NOLOCK) WHERE strUserID = @Account
    IF @@ERROR <> 0 OR @UserJID = 0
    BEGIN
        SELECT -1
        RETURN
    END

    -- ÀÌ¹Ì ºí·° °É¸° À¯ÀúÀÌ³Ä?
    IF( EXISTS ( SELECT UserJID FROM _BlockedUser WITH (NOLOCK) WHERE UserJID = @UserJID AND Type = @Type))
    BEGIN
        select -2
        RETURN    
    END

    
    
    INSERT _Punishment values( @UserJID, @Type, @Executor, 0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime, 0)
    set @NewSerialNo = @@identity
    if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
    BEGIN
        SELECT -3
        RETURN        
    END

    INSERT _BlockedUser values( @UserJID, @Account, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
    if( @@rowcount = 0 or @@error <> 0 )
    BEGIN
        SELECT -4
        RETURN        
    END

    SELECT @UserJID

Results:

Is this requiring a server restart?

stewie316 · Oct 25, 2011

ItachiSNS said:
Is this requiring a server restart?

I've also noticed another issue that is the Auto-ban for incorrect password only works once. If the user was to ever block there account again you end up with this error.

Code:

_RegisterAutomatedPunishment Query failed!: [ret: -2, User: sro_gm2]

Jangan · Oct 26, 2011

oh right... 2 things:

1) About getting banned twice, i forgot that i have system checks that deletes the tables if
Time Now > Punishment Time. on the php site.

I will do another fix release for this and make it more upgraded

just need to wait for some testers hehe!

@ Stewie316:
and about -1 returns, arrg... how do i get that? what do i need to do to get myself banned for -1 return?

stewie316 · Oct 26, 2011

Code:

Query Error!!! Query Result : -1, Query : EXEC _RegisterPunishment 745549,1,'sro_gm2',64,'','','','','','2011-10-26 09:49:21','2011-10-26 09:49:19','2111-10-26 09:49:19','2011-10-26 06:06:08'

SQL Error State:42S02, Native Error Code: D0, ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sr_member1'.

EXEC _RegisterPunishment 745549,1,'sro_gm2',64,'','','','','','2011-10-26 09:49:21','2011-10-26 09:49:19','2111-10-26 09:49:19','2011-10-26 06:06:08'

hmm this is the error i got from doing it in SMC & Above

i get this if i try to duplicate the automated 10 minute ban for incorrect password.

Code:

_RegisterAutomatedPunishment Query failed!: [ret: -2, User: sro_gm2

It seems the -1 is coming at the same time as the 1 error. So it seems to be when registering a new ban through SMC which always fails.

Hope this helps.

stemale · Oct 27, 2011

Some 1 fixd?
_RegisterAutomatedPunishment Query failed!

and the second ban success?

driver13 · Oct 29, 2011

very good

K4bo · Jul 6, 2012

thx

:

nevetS · Apr 12, 2015

Error still showing after put it on my database.

Welcome!

[Fix] Automated Punishment 10 Minutes!

Jangan

Laravel Core Programmer

SGA

Newbie Spellweaver

LastThief

Junior Spellweaver

stewie316

Newbie Spellweaver

Jangan

Laravel Core Programmer

stewie316

Newbie Spellweaver

ItachiSNS

Junior Spellweaver

stewie316

Newbie Spellweaver

Jangan

Laravel Core Programmer

stewie316

Newbie Spellweaver

stemale

Newbie Spellweaver

driver13

Newbie Spellweaver

K4bo

Experienced Elementalist

nevetS

Newbie Spellweaver

About Us

Online statistics

Forum statistics

RaGEZONE Sponsor