Ever wanted to ban a hacker that deletes his character, and changes his IP before you can ban him?
Well, you can log what character he deletes, and in what account and the date of the action.
Create a database called ban_info if you don't have one already and run this script in MSSQL:
Code:
USE [ban_info]
GO
/****** Object: Table [dbo].[deletechar] Script Date: 01/05/2011 19:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[deletechar](
[id] [int] IDENTITY(1,1) NOT NULL,
[charname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[accname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_deletechar] PRIMARY KEY NONCLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Now Go to your character table, and then go to Progammability section in the object dock (If you're using Microsoft SQL Server Management Studio 2005) And then Go to Stored Procedure,
Look for the "DBO.SP_CHAR_DELETE" stored procedure, right click it and click modify and copy and paste this script in the box:
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/******************************************************************
* 작성자 : 한지욱
* 작성일 : 2004.10.11(월)
* 내 용 : 캐릭터정보삭제
* 반환값 : -11 : user_character_secede Insert Fail
-12 : user_suit_secede Insert Fail
-13 : user_bag_secede Insert Fail
-14 : user_storage_secede Insert Fail
-15 : user_store_secede Insert Fail
-16 : user_skill_secede Insert Fail
-17 : user_slot_secede Insert Fail
-18 : user_quest_doing_secede Insert Fail
-19 : user_quest_done_secede Insert Fail
-20 : 길드캐릭정보 삭제 실패
-1 : user_character 삭제 실패
0 : 성공
* 수정자 : Hanjiwook
* 수정일 : 2005.04.27 (WED)
* 수정내역 : 캐릭터삭제시 캐릭터정보를 SECEDE Table로 복사한다.
******************************************************************
* 수정자 : Hanjiwook
* 수정일 : 2005.05.16 (MON)
* 수정내역 : 캐릭터삭제시 개인상점정보를 SECEDE Table로 복사한다.
******************************************************************
* 수정자 : Hanjiwook
* 수정일 : 2005.08.04(THU)
* 수정내역 : 캐릭터정보에 PK정보 추가 (wPKCount, wChaoticLevel, nShield)
******************************************************************
* 수정자 : Hanjiwook
* 수정일 : 2005.09.05(MON)
* 수정내역 : 스탯클리어카운트 추가 (byStatClearCount)
******************************************************************
* 수정자 : Hanjiwook
* 수정일 : 2005.09.06(TUE)
* 수정내역 : PC 기타정보 추가 (dwFlag)
******************************************************************
* 수정자 : Choijihwan
* 수정일 : 2006.02.06(MON)
* 수정내역 : user_character테이블 PVP Columns 추가.
DWORD dwPVPPoint; // PVP 점수
WORD wWinRecord; // 승리 횟수
WORD wLoseRecord; // 패배 횟수
WORD wDrawRecord; // 비긴 횟수
******************************************************************
* 수정자 : Choijihwan
* 수정일 : 2006.12.26(WEB)
* 수정내역 : Add "dwSupplyPoint" column
DWORD dwSupplyPoint;
******************************************************************/
ALTER PROCEDURE [dbo].[SP_CHAR_DELETE]
@character_no varchar(18) ,
@sp_rtn INT OUTPUT
AS
DECLARE @rowcnt int
DECLARE @v_character_name varchar(40)
DECLARE @v_guild_code varchar(10)
DECLARE @v_user_name varchar(40)
BEGIN
SET @v_character_name = dbo.FN_GetCharName(@character_no)
SET @v_guild_code = dbo.FN_GetGuildCode(@v_character_name)
SET @v_user_name = dbo.FN_GetUserID(dbo.FN_GetUserNo(@character_no))
INSERT INTO ban_info.dbo.deletechar(charname,accname,date) VALUES (@v_character_name, @v_user_name, CAST(GETDATE() as varchar));
IF LEN(@v_guild_code) > 0
BEGIN
SET @sp_rtn = -20
RETURN
END
BEGIN TRANSACTION
/* user_character_secede */
INSERT INTO user_character_secede
(
character_no ,
character_name ,
user_no ,
dwAdv ,
dwPeerage ,
dwExp ,
dwMoney ,
dwStoreMoney ,
dwStorageMoney ,
nHP ,
nMP ,
wStr ,
wDex ,
wCon ,
wSpr ,
wPosX ,
wPosY ,
wRetPosX ,
wRetPosY ,
wMapIndex ,
wRetMapIndex ,
wStatPoint ,
wSkillPoint ,
wLevel ,
byPCClass ,
byDirection ,
byRetDirection ,
bySkillClearCount ,
byStatClearCount ,
wPKCount ,
wChaoticLevel ,
nShield ,
dwFlag ,
login_flag ,
ipt_date ,
ipt_time ,
upt_time ,
login_time ,
logout_time ,
user_ip_addr ,
del_date ,
del_time ,
dwPVPPoint ,
wWinRecord ,
wLoseRecord ,
wDrawRecord ,
dwSupplyPoint
)
SELECT character_no ,
character_name ,
user_no ,
dwAdv ,
dwPeerage ,
dwExp ,
dwMoney ,
dwStoreMoney ,
dwStorageMoney ,
nHP ,
nMP ,
wStr ,
wDex ,
wCon ,
wSpr ,
wPosX ,
wPosY ,
wRetPosX ,
wRetPosY ,
wMapIndex ,
wRetMapIndex ,
wStatPoint ,
wSkillPoint ,
wLevel ,
byPCClass ,
byDirection ,
byRetDirection ,
bySkillClearCount ,
byStatClearCount ,
wPKCount ,
wChaoticLevel ,
nShield ,
dwFlag ,
login_flag ,
ipt_date ,
ipt_time ,
upt_time ,
login_time ,
logout_time ,
user_ip_addr ,
convert(char(8),getdate(),112),
GetDate() ,
dwPVPPoint ,
wWinRecord ,
wLoseRecord ,
wDrawRecord ,
dwSupplyPoint
FROM user_character
WHERE character_no = @character_no
IF (@@error <> 0 or @rowcnt<>1)
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -11
RETURN
END
/* user_suit_secede */
INSERT INTO user_suit_secede
(
character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
FROM user_suit
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -12
RETURN
END
/* user_bag_secede */
INSERT INTO user_bag_secede
(
character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
FROM user_bag
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -13
RETURN
END
/* user_storage_secede */
INSERT INTO user_storage_secede
(
character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
FROM user_storage
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -14
RETURN
END
/* user_store_secede */
INSERT INTO user_store_secede
(
character_no ,
line_no ,
dwPrice ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT character_no ,
line_no ,
dwPrice ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
FROM user_store
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -15
RETURN
END
/* user_skill_secede */
INSERT INTO user_skill_secede ( character_no,
line_no ,
info ,
ipt_time ,
upt_time )
SELECT character_no ,
line_no ,
info ,
ipt_time ,
upt_time
FROM user_skill
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -16
RETURN
END
/* user_slot_secede */
INSERT INTO user_slot_secede ( character_no,
line_no ,
info ,
ipt_time ,
upt_time )
SELECT character_no,
line_no ,
info ,
ipt_time ,
upt_time
FROM user_slot
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -17
RETURN
END
/* user_quest_doing_secede */
INSERT INTO user_quest_doing_secede ( character_no,
q_index ,
q_count_0 ,
q_count_1 ,
q_count_2 ,
q_count_3 ,
q_start_time,
upt_time )
SELECT character_no,
q_index ,
q_count_0 ,
q_count_1 ,
q_count_2 ,
q_count_3 ,
q_start_time,
upt_time
FROM user_quest_doing
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -18
RETURN
END
/* user_quest_done_secede */
INSERT INTO user_quest_done_secede ( character_no,
q_index ,
upt_time )
SELECT character_no,
q_index ,
upt_time
FROM user_quest_done
WHERE character_no = @character_no
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -19
RETURN
END
-- user_character 삭제
DELETE FROM user_character
WHERE character_no = @character_no
SELECT @rowcnt = @@rowcount
IF (@@error <> 0 or @rowcnt<>1)
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -1
RETURN
END
COMMIT TRANSACTION
SET @sp_rtn = 0
RETURN 0
END
And run EXECUTE. You should get no errors, and now when characters delete their character it will log in the ban_info.dbo.deletechar table.
As you can see I've added
Code:
DECLARE @v_user_name varchar(40)
SET @v_user_name = dbo.FN_GetUserID(dbo.FN_GetUserNo(@character_no))
INSERT INTO ban_info.dbo.deletechar(charname,accname,date) VALUES (@v_character_name, @v_user_name, CAST(GETDATE() as varchar));
The DECLARE, basically declares that variable so we can use it later in the script, it initializes it with the data type specified. The SET @v_user_name basically sets a new value based on the returned function Dekaron uses, since we already have functions to retrieve information we need it makes our job a whole lot easier to obtain the account name. The INSERT INTO inserts the information into the table.
This makes it simple and easy to find
Edit:
Also you get exact time of deletion, and you quickly know the user ID searching for the user_no in the account db
i hate to rain on ur parade buddy, but check out the 'user_character_secede' table in the characters DB, it logs all deleted characters, IP it was deleted from, exact time it was deleted, user_no, everything, all u have to do is one simple query.
select * from user_character_secede where user_no = "USERNUMBER"
I've been saying this for a while now. Don't get me wrong, I write code also and can appreciate all the work people do on here but half the things I see posted are just reinventing the wheel.
If you write your code to make use of already established "stored procedures" and database tables, you would only have to do a minimal amount of work.
The game already knows how to do things from generating unique numbers to mailing items to logging events.
If nothing else I guess it's good practice for people who want to learn how to interface with databases.
I've been saying this for a while now. Don't get me wrong, I write code also and can appreciate all the work people do on here but half the things I see posted are just reinventing the wheel.
If you write your code to make use of already established "stored procedures" and database tables, you would only have to do a minimal amount of work.
The game already knows how to do things from generating unique numbers to mailing items to logging events.
If nothing else I guess it's good practice for people who want to learn how to interface with databases.
Oh nice, I had no idea. I thought it was just a copy of the original user_character except it kept deleted characters.
![[TUTORIAL]Log Deleted Characters](https://www.elitepvpers.com/forum/iconimages/dekaron-private-server/tutorial-log-deleted-characters_ltr.gif){kind=small}
