Trijob Anti Cheat Query based on Scroll Usage

[Witchy Moo]

Hi there, it's been a while ;)

I'd like to share an alternative Trijob Anti Cheat (simple query in SP) based on Syloxx's idea in this thread.

The difference is, his procedure based on "item pickup" (Operation 114), and the query sequence will run on each item pickups. I wrote this one based on the usage of "Bandit den return scroll" (Operation 41), and it will only run 1 time after the bandit scroll is used.

The outcome is pretty much the same, so you can use either both.

How does it work? When a thief used bandit den return scroll, this query will detect the region where the scroll is used. If it was a safe zone, then all items will be deleted from the transport (database side). So when the character arrived in thief town all items will be gone.

First things first, make sure that:

1. Your db is vSRO db (i haven't try in other db)
2. Your server is currently writing logs to "_LogEventItem" table (IF you use Chernobyl's Evangelion GS, you have to set "disableLog = 0" on "[misc]" section in the GS configuration file, otherwise your server won't log anything and the _AddLogItem procedure won't work)
3. The MaxStack of your bandit den return scroll (ITEM_ETC_SCROLL_RETURN_THIEFDEN_01) is "1", (There's a funny part where the Game Server won't log the scroll usage IF its current stack is more than 1). If you have more, then put only 1 scroll in your inventory slot when testing this procedure.

And here's the boring part:

Add these lines into _AddLogItem Stored Procedure in your SHARD_VT_LOG database:

-- Begin: Operation 41 = Scroll is used
IF (@Operation = 41)
BEGIN
    IF (@ItemRefID = 2128) -- Bandit den return scroll (ITEM_ETC_SCROLL_RETURN_THIEFDEN_01), will work only when MaxStack is 1
    BEGIN
        /*
         * Get latest region of character
         */
        DECLARE @IsBattleField BIT
        SELECT @IsBattleField = IsBattleField
            FROM [SRO_VT_SHARD].[dbo].[_Char]
            INNER JOIN [SRO_VT_SHARD].[dbo].[_RefRegion]
            ON [SRO_VT_SHARD].[dbo].[_Char].LatestRegion = [SRO_VT_SHARD].[dbo].[_RefRegion].wRegionID
            WHERE [SRO_VT_SHARD].[dbo].[_Char].CharID = @CharID
        /*
         * Scroll is used inside town
         */
        IF (@IsBattleField = 0)
        BEGIN
            DECLARE @COSID BIGINT
            DECLARE @ReFCharID INT
            DECLARE @TypeID4 INT
            /*
             * Get any summoned COS by current character
             *
             * It's actually fine not to use CURSOR, so far I noticed that any summoned COS
             * won't set "OwnerCharID", except for transport COS.
             * But just to be sure, I put CURSOR in use.
             */
            SET NOCOUNT ON;
            DECLARE CurCOS CURSOR FOR SELECT ID, RefCharID FROM [SRO_VT_SHARD].[dbo].[_CharCOS] WHERE OwnerCharID = @CharID ORDER BY ID ASC;
            OPEN CurCOS;
            FETCH NEXT FROM CurCOS INTO @COSID, @ReFCharID;
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT @TypeID4 = TypeID4 FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WHERE ID = @ReFCharID
                /*
                 * If current COS is a transport COS, erase all items
                 * TypeID4 = 2 = Transport COS
                 */
                IF (@TypeID4 = 2)
                BEGIN
                    UPDATE [SRO_VT_SHARD].[dbo].[_InvCOS] SET ItemID = 0 WHERE COSID = @COSID
                END
                FETCH NEXT FROM CurCOS INTO @COSID, @ReFCharID;
            END;
            CLOSE CurCOS;
            DEALLOCATE CurCOS;
        END
    END
END

Don't forget to adjust database names according to your settings.

Oh well this will probably useless but what the heck :P, have fun ;)

[MentaL]

[Blacksheep25]

Glad to have you back, @Witchy Moo

This seems to be a much better way of doing it, expecially since its not looking at all items picked up and just looking for when that bandit scroll is used.

[Witchy Moo]

Thanks @blacksheep25, it's good to be back ;)

[Syloxx]

@Witchy Moo,
a cursor is a very very bad idea, its simply way 2 slow to use it that often...
if you wanna use it scroll based modify the _AddNewCOS (if i remember the name correctly) and insert CharID + COSID into a custom table.
if you use the bandit scroll check strPos (in case it exists with operation 41) and if strPos is in safe zone select COSID from custom table and clear the inventory

your current query is even if its executed more rarely slower.

you could do me a favor, how i said approve strPos

can you check whats the strPos with Operation 41 and 114 i didnt setup a Silkroad Server now... (unlike _Char strPos is live)

[Witchy Moo]

Hello Syloxx

Yep!, exactly, CURSOR is always bad for many repeated sequences, but, this will run only 1 time on each bandit scroll use :). (Can't imagine 100 users using bandit scroll at the same time :P) Besides, the CURSOR was only to find any spawned COS by the current char at the moment (The procedure will only find 1 COS if you use standard or Chernobyl's GS. Trust me :P... So it won't burden the server) Which is why I put a comment in the CURSOR sequence :)

Few times I checked, vSRO only wrote "OwnerCharID" for transport, and not other COS (Fellow pet, pick pet, so on), so it should be safe not to use CURSOR, and if you use it, it will only find exactly ONE COS, so it'll be fine. And like i i said, i wanted to be safe, not sure what other COS can be summoned and put in log belonging to the char, right? but that's just me :)

But, you can skip all the CURSOR sequence and just do this: (WITHOUT CURSOR)

-- Begin: Operation 41 = Scroll is used
IF (@Operation = 41)
BEGIN
    IF (@ItemRefID = 2128) -- Bandit den return scroll (ITEM_ETC_SCROLL_RETURN_THIEFDEN_01), will work only when MaxStack is 1
    BEGIN
        /*
         * Get latest region of character
         */
        DECLARE @IsBattleField BIT
        SELECT @IsBattleField = IsBattleField
            FROM [SRO_VT_SHARD].[dbo].[_Char]
            INNER JOIN [SRO_VT_SHARD].[dbo].[_RefRegion]
            ON [SRO_VT_SHARD].[dbo].[_Char].LatestRegion = [SRO_VT_SHARD].[dbo].[_RefRegion].wRegionID
            WHERE [SRO_VT_SHARD].[dbo].[_Char].CharID = @CharID
        /*
         * Scroll is used inside town
         */
        IF (@IsBattleField = 0)
        BEGIN
            DECLARE @COSID BIGINT
            DECLARE @ReFCharID INT
            DECLARE @TypeID4 INT
            /*
             * Get summoned COS by current character
             */
            SELECT @COSID = ID, @ReFCharID = RefCharID FROM [SRO_VT_SHARD].[dbo].[_CharCOS] WHERE OwnerCharID = @CharID ORDER BY ID ASC;
            SELECT @TypeID4 = TypeID4 FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WHERE ID = @ReFCharID
            /*
             * If current COS is a transport COS, erase all items
             * TypeID4 = 2 = Transport COS
             */
            IF (@TypeID4 = 2)
            BEGIN
                UPDATE [SRO_VT_SHARD].[dbo].[_InvCOS] SET ItemID = 0 WHERE COSID = @COSID
            END
        END
    END
END

have fun ;)

[MikroTekken]

welcome back :)

[Syloxx]

@Witchy Moo,

actually only temp pets like transport and ride pets have a OwnerCharID, anyway i "fixed" it for people they wanna work with it.

It's slow and outdated because it gets the ItemID of the COS out of _Items instead of _AddNewCOS
just google for "Syloxx OwnerCharID" and u'll find it.

Spoiler:

HOW TO AVOID CURSORS:

DECLARE @TBL TABLE (ID IDENTITY (1,1), COSID INT, RefCharID INT)
DECLARE @counter INT = 1
, @COSID INT
, @IsTransport BIT = 0

INSERT INTO @TBL
SELECT COSID, RefCharID FROM _CharCOS WHERE OwnerCharID = @CharID

WHILE @counter < (SELECT COUNT (*) FROM @TBL) AND @IsTransport = 0
BEGIN
SELECT @COSID = COSID
, @ReFCharID = RefCharID
FROM @TBL
WHERE ID = @counter

IF (SELECT TypeID4 FROM _RefObjCommon WHERE ID = @ReFCharID) = 2
SET @IsTransport = 1

SET @counter += 1
END

IF @IsTransport = 1
UPDATE _InvCOS
SET ItemID = 0
WHERE COSID = @COSID

the query is the spoiler can have some syntax errors since i wrote everything here in this reagezone textbox but the logic itself should be fine :)

anyway with this method you can avoid EVERY cursor :)

[RenePunik]

Good job. keep sharing.

[RenePunik]

Guys, what is different between WITH CURSOR or WITHOUT CURSOR?

[Syloxx]

performance

[RenePunik]

So, it is better to use WITH CURSOR or WITHOUT?

[Syloxx]

Yep!, exactly, CURSOR is always bad for many repeated sequences.

why you dont simply read the posts apporve?

[Witchy Moo]

which posts approve?

And anyway, whatever I posted is simply an alternative, I don't have problems with it, it simply worked fine in my box. After all, I never said this solution is bad or that solution is not good, perhaps by sharing different point of views ppl can chose whatever flavor they like to apply ;)

- - - Updated - - -

So, it is better to use WITH CURSOR or WITHOUT?

I like better with one :), personally. And I did that when I know the records imma gonna loop is not a bunch. Say, only 5 or 10 records in the loop.

An alternative could be by making temporary table and not relying on cursor but that's your choice.

For example, https://forum.ragezone.com/f723/miss...ng-sql-972881/ :)

[Bocc]

Change it to IF (@Operation = 16) then you can use in stack

[PlayerSRO]

nice but u can still pick up items "safe" in the Town without ( inv delete after teleport to thief town )

but maybe i can fix it lets try :)

btw : nice work