[Euphoria Dev Team Release] Illegal Transaction Detection

[nubness]

Hello everybody,

I am sure that not many people know about this, and I'm more than sure that it is not a problem for private servers, but transacting untradeable items isn't normally allowed, which is why items have the tradeable/untradeable property after all.

Detecting such transactions is extremely easy, since the logs store all the information about most things that happen in game, instantly.

First of all you need to create a new table, which will contain all the details about transactions containing items that aren't supposed to be traded. You can create the table by running the query below:

USE PS_GameLog

CREATE TABLE Illegal_Transaction_Log (
	
	RowID INT IDENTITY(1,1),
	UserUID INT NOT NULL,
	UserID VARCHAR(20) NOT NULL,
	CharID INT NOT NULL,
	CharName VARCHAR(20) NOT NULL,
	ReceiverCharID INT NOT NULL,
	ReceiverCharName VARCHAR(20) NOT NULL,
	ItemUID BIGINT NOT NULL,
	ItemID INT NOT NULL,
	ItemName VARCHAR(50) NOT NULL,
	ActionTime DATETIME NOT NULL
)

The next step is modifying usp_Insert_Action_Log_E to make it detect the transactions containing untradeable items the very second it happens.
Simply add the following lines inside the PS_GameLog.dbo.usp_Insert_Action_Log_E stored procedure:

IF (@ActionType = 116)
BEGIN
	IF EXISTS (SELECT ItemID FROM PS_GameDefs.dbo.Items WHERE ItemID = @Value2 AND ReqOg = 1)
	BEGIN
		INSERT INTO Illegal_Transaction_Log VALUES (@UserUID, @UserID, @CharID, @CharName, @Value3, @Text2, @Value1, @Value2, @Text1, @ActionTime)
		--UPDATE PS_UserData.dbo.Users_Master
		--SET Status = -5
		--WHERE UserUID = @UserUID
	END
END

You need to place this code between

IF(LEN(@dd) = 1)
BEGIN
	SET @dd = '0' + @dd
END

and

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog

As you see, I've commented 3 lines, which ban the account of the player who transacted the untradeable item. Uncomment them if you want the player to get banned.

Enjoy !

[sominus]

Nice!
We've been fighting against this lately and this comes very very useful.

I have a question:
I've seen that usp_Insert_Action_Log_E is a good weapon to hunt down many kind of cheats.

My doubt is: if you have a lot of queries there, could it cause server overload or lag due to constant queries execution? (of course talking about a dedicated server not a home server)
We had more than 10 million records on PS_GameLog.dbo.ActionLog (in 1 week) and it becomes a pain to do queries on that table.

[nubness]

Having checks for multiple ActionTypes doesn't cause any performance problems, since it gets the parameters from the ps_gameLog.exe itself, and inserts logs of any ActionType.

I recommend you process your logs and TRUNCATE the ActionLog table once a week or once a month to avoid server slowdown. By processing logs I mean getting the information that you think may be useful some time in the future, and possibly store it in other tables. As you see, I have 3 releases based on logs, and all of them requires creating a new table, which is indeed useful for server admins.

[{Skrillex}]

Hi,
What´s to do if any1 fall an Item and another player drop it?
I mine non tradable Items..^^

Regards.

--Sorry for my bad English--

[nubness]

Based on my example, you can drop an item in game, check the ActionType of the log that is inserted and write an IF statement similar to mine.

[abrasive]

This was actually a big problem in Shaiya X as we sandboxed the 1-15 zone so no items were allowed in or out. Players would take untradable items from 1-15 and use the warehouse anywhere to store them in their warehouse. Then they'd log in another toon and pull the item from the warehouse.

ec eventually debugged ps_game.exe and made it respect the ReqOg value on the server side when trading/dropping items.

You could write this line more efficiently:

IF (@Value2 IN (SELECT ItemID FROM PS_GameDefs.dbo.Items WHERE ReqOg = 1))

Like this:

IF (EXISTS(SELECT ItemID FROM PS_GameDefs.dbo.Items WHERE ItemID = @Value2 AND ReqOg = 1))

Then you are dealing with zero or one rows coming back versus many rows coming back.

[jvczxc]

I do not know if it works yet but I try not to run perfecion if anyone knows more than me for help me my skype is favorable jvczxc

USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 02/07/2014 14:00:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E FIXED ********.com Twilight ******/

ALTER Proc [dbo].[usp_Insert_Action_Log_E]



@UserID varchar(18),
@UserUID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTimeZ varchar(50),
@ActionType tinyint,
@Value1 bigint = null,
@Value2 int = null,
@Value3 int = null,
@Value4 bigint = null,
@Value5 int = null,
@Value6 int = null,
@Value7 int = null,
@Value8 int = null,
@Value9 int = null,
@Value10 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '',
@yyyy varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS
DECLARE @ActionTime as datetime
SELECT @ActionTime = CONVERT(datetime, @ActionTimeZ, 120)
SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
END
IF (@ActionType = 255)
BEGIN
IF (@Value2 IN (SELECT ItemID FROM PS_GameDefs.dbo.Items WHERE ReqOg = 1))
BEGIN
INSERT INTO Illegal_Transaction_Log VALUES (@UserUID, @UserID, @CharID, @CharName, @Value3, @Text2, @Value1, @Value2, @Text1, @ActionTime)
--UPDATE PS_UserData.dbo.Users_Master--UPDATE PS_UserData.dbo.Users_Master
--SET Status = -5--SET Status = -5
--WHERE UserUID = @UserUID--WHERE UserUID = @UserUID
END
END
SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql,
N'@UserID varchar(18), @UserUID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint,
@Value1 bigint, @Value2 int, @Value3 int, @Value4 bigint, @Value5 int, @Value6 int, @Value7 int, @Value8 int,
@Value9 int, @Value10 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4

[KawaiiiGirl]

The stuff the Euphoria Dev Team do is just.. Mind blowing ! Tryed to code something myself. lol gave my pc a blue screen

Keep the work up!

[_Diavolino_]

Nothing to tell about Nub or Phillips (that i know by script) but No question to have of Nubness Script xD Great Work ! (and for bad mind : I dont suck noone and its reallity ! if you can do better propose your way we could at moment compare the brain xD anyway i like them job <-- that for for mind in double sense just that)... Continious to propose us usefull tuto ! its Correct Clear Organisated and Coherent with your asked !

Regards