How can i fix this any ideea?

[[ADM]Throne.]

looks like when some players are porting to Opalus's Territory i get this on PS_Gamelog_System

the fix is : to remove the 's from ( opalu's)

problem is : where do i find it?


2013-06-10 04:10:54 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 's'., SQL STATE: 42000, NATIVE ERROR: 102 (0x66)

2013-06-10 04:10:54 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'XXXX',54505,251511,'[VIP]XXXX', 85,90000000,81, 438.675385,16.923771,524.210938, '2013-06-10 04:10:54' ,164, 0,433999530,72,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '(NPC)Opalus's Territory',NULL,NULL,NUL

[shakalaka_boom]

what is .Opalus's Territory? this is the gate keeper, or other NPC ...look at NpcQuest.SData

[castor4878]

Originally Posted by [ADM]Throne.

the fix is : to remove the 's from ( opalu's)

it is definitively NOT the solution.

except of course if you consider that any weakness in coding shall result in violation of grammar or vocabulary's rules; still storing correct English sentences is possible !

1st mistake:
the single quote character must be escaped when it's present in text fields of a SQL query. the caller of these SP should have send the "(NPC)Opalus''s Territory" string and not "(NPC)Opalus's Territory", but because he used to manage Korean strings (or any other poor reasons) he didn't do a reliable work and transmit string as they are.

2nd mistake:
the stored procedure uses a parametrized request pattern: the SQL request is built in a string:

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

and then that string-request is executed with the SP parameters as arguments:

EXEC sp_executesql @Sql, N'@UserID varchar(18), ...'

(recall, the 'N' prefix stands for "National language character set" and indicates that the string is in Unicode).

these 2 operations are basic string operations, the parameter @Text2 whose value is "(NPC)Opalus's Territory" is inserted within the built string without care and thus the single quote in "'s" defines the end of the currently built string, causing the error.

solution:
you have to manage correct building of the SQL statement (the language constraints) into the stored procedure. and you should do that for all string parameters received by the SP, none of them are protected against this incorrect coding (and also a lot of them make invalid - when not 100% useless - date conversion).

a possible fix is:

ALTER Proc [dbo].[usp_Insert_Action_Log_E]

	@UserID varchar(18),
	@UserUID int,
	...
	@Value10 int = null,
	@Text1 varchar(100) = '',
	@Text2 varchar(100) = '',
	@Text3 varchar(100) = '',
	@Text4 varchar(100) = ''
	
AS

DECLARE @Sql nvarchar(4000) = ''

-- some other variables may exist there to implement an anti-trade dup system

-- add new variables
DECLARE @find nchar(1) = char(39)
DECLARE @repl nchar(2) = char(39) + char(39)

-- fix strings
SET @Text1 = replace(@Text1, @find, @repl)
SET @Text2 = replace(@Text2, @find, @repl)
SET @Text3 = replace(@Text3, @find, @repl)
SET @Text4 = replace(@Text4, @find, @repl)

-- follow with existing code

in short, we search for all occurences of a single-quote in the TextI variables and we replace them by two single-quote; the result are valid escaped SQL strings.

[[ADM]Throne.]

hey Castor, ty for reply, there are some lil problems

Msg 102, Level 15, State 1, Procedure usp_Insert_Action_Log_E, Line 5
Incorrect syntax near '.'.
Msg 137, Level 15, State 2, Procedure usp_Insert_Action_Log_E, Line 23
Must declare the scalar variable "@Text1".
Msg 137, Level 15, State 2, Procedure usp_Insert_Action_Log_E, Line 24
Must declare the scalar variable "@Text2".
Msg 137, Level 15, State 2, Procedure usp_Insert_Action_Log_E, Line 25
Must declare the scalar variable "@Text3".
Msg 137, Level 15, State 2, Procedure usp_Insert_Action_Log_E, Line 26
Must declare the scalar variable "@Text4".

and also im getting this when i try to edit the store procedures

Msg 156, Level 15, State 1, Procedure usp_Insert_Action_Log_E, Line 124
Incorrect syntax near the keyword 'Proc'.
Msg 134, Level 15, State 1, Procedure usp_Insert_Action_Log_E, Line 142
The variable name '@Sql' has already been declared. Variable names must be unique within a query batch or stored procedure.

[hiddengosu]

stop ddosing

[[ADM]Throne.]

Originally Posted by troll1337

stop ddosing

hey you ******* moron **** of ****
in last 5 months On what server YOU ******* saw me ddosing again?
NON
THEN WTF you are trying to DO ?
IF i stay OFF leave everyone ONLINE i get ******* BLAMED
if i go and ddos any ******* shaiya P-server i see = you say im bad

1 thing
just go and ******* hang your self

for Epvpers Mod ( after this i know this account will get blocked)

but

big **** OFF, selfish nubs MOD fuckedup comunity with fuckedup Mod's
to all Mod's **** YOU I WISH YOU SAME THING HAPPEN TO JHON

[FuckCoke]

He never changed, the last server you ddos`d? Euphoria.

And your a fail attempt on everything at the moment.

[shakalaka_boom]

Originally Posted by castor4878

it is definitively NOT the solution.

in an item and NPC sdata I found a total of 4 such items
simply removed from the title once the sign, and there is no such errors more.

[[ADM]Throne.]

Originally Posted by FuckCoke

He never changed, the last server you ddos`d? Euphoria.

And your a fail attempt on everything at the moment.

fail
go and ask chris if i ddosing him

Originally Posted by shakalaka_boom

in an item and NPC sdata I found a total of 4 such items
simply removed from the title once the sign, and there is no such errors more.

already did that no more errors with NPC

also 1 bug is



013-06-10 16:02:02 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'a5176',73770,325096,'hang', 85,90000000,42, 51.585922,1.847024,45.899261, '2013-06-10 15:58:05' ,213, 3845168874468016128,14138,100171,38350204637471703 04,NULL,NULL,NULL,NULL,NULL,NULL,'Grudges Crossbow','0,0,0,0,0,0 (Option:00000500072600000000)','0,0,0,0,0,0 (Option:04000004000800000000)','2013-05-14 12:34:58'

2013-06-10 16:02:02 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:02 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'a5176',73770,325096,'hang', 85,90000000,42, 51.585922,1.847024,45.899261, '2013-06-10 15:58:05' ,112, 3835020463747170304,100171,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Recreation Rune','use_item','0,0,0,0,0,0 (Option-2013-04-28 11:43:11','bag=1,slot=0'

2013-06-10 16:02:02 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:02 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'grenade',88489,352131,'yullou', 85,99999998,64, 874.075684,23.505714,791.768677, '2013-06-10 15:58:06' ,121, 3849601345141473280,30116, 255,4,5, NULL,44,NULL,NULL,NULL,'Dual Fortune Lapis Lv5',NULL,'0,0,0,0,0,0 (Option','2013-05-22 08:32:07'

2013-06-10 16:02:03 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:04 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'geanfsts',54791,277404,'--IceBlue--', 15,3816,18, 550.500122,5.066016,390.968384, '2013-06-10 15:58:06' ,112, 3860990619157594112,46014,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,'?? ????','0,0,0,0,0,0 (Option:02020100000000000000)','2013-06-10 15:58:03','bag=1,slot=20,Count=1'

2013-06-10 16:02:03 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:04 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'Burn',90684,362007,'Rolly_ro', 80,1000000,2, 1020.790222,55.879997,1041.255981, '2013-06-10 15:58:07' ,112, 3860986208226181120,100082,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Operators Exclusive','use_item','0,0,0,0,0,0 (Option-2013-06-10 15:42:00','bag=1,slot=12'

2013-06-10 16:02:03 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:04 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'Burn',90684,362007,'Rolly_ro', 80,1000000,2, 1020.790222,55.879997,1041.255981, '2013-06-10 15:58:07' ,119, 3860985383592525824,9170,30079,3860988463084208128 ,0,137880,1,NULL,NULL,NULL,'Shrewd Blade2','Assault Lapis LV9','3,4,6,79,0,0 (Option:27140000000100000000)','2013-06-10 15:39:01'

2013-06-10 16:02:04 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:04 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'jack10',32627,354267,'Profana', 30,9364628,30, 800.549011,3.176123,224.652878, '2013-06-10 15:58:08' ,211, 365,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N ULL,NULL,NULL,NULL

2013-06-10 16:02:04 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:04 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'jack10',32627,354267,'Profana', 30,9364628,30, 800.549011,3.176123,224.652878, '2013-06-10 15:58:08' ,108, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL, 'money=565202801, bank=111671964, cash=0, firstmoney=565202801','level=30,skp=72,stp=0','s=2 71,d=44,i=9,w=10,r=9,l=12'

2013-06-10 16:02:05 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:05 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'grenade',88489,352131,'yullou', 85,99999998,64, 874.075684,23.505714,791.768677, '2013-06-10 15:58:08' ,121, 3849601345141407744,30115, 255,4,4, NULL,45,NULL,NULL,NULL,'Dual Wise Lapis Lv5',NULL,'0,0,0,0,0,0 (Option','2013-05-22 08:32:07'

2013-06-10 16:02:06 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:02:06 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'a5176',73770,325096,'hang', 85,90000000,42, 51.585922,1.847024,45.899261, '2013-06-10 15:58:09' ,213, 3845168874468016128,14138,100171,38350204637471703 04,NULL,NULL,NULL,NULL,NULL,NULL,'Grudges Crossbow','0,0,0,0,0,0 (Option:04000004000800000000)','0,0,0,0,0,0 (Option:00080006050000000000)','2013-05-14 12:34:58'

2013-06-10 16:10:30 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:30 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'charler',88835,360548,'thor_charler', 85,90000000,70, 1050.128418,32.748390,575.153320, '2013-06-10 16:08:59' ,112, 3858772788240252928,100001,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Etain pot','use_item','0,0,0,0,0,0 (Option-2013-06-06 17:53:40','bag=1,slot=3'

2013-06-10 16:10:32 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:32 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:32 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'Yuffie1',74692,329279,'Mrs-Muffin-', 85,90000000,70, 206.096802,39.051445,156.386871, '2013-06-10 16:09:01' ,119, 3860971682647048192,36067,30004,386098261333907865 6,0,137880,1,NULL,NULL,NULL,'Hades Walkers','Craft Lapis LV9','3,31,4,0,0,0 (Option:00001200000033020070)','2013-06-10 14:53:10'

2013-06-10 16:10:32 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'Yuffie1',74692,329279,'Mrs-Muffin-', 85,90000000,70, 206.096802,39.051445,156.386871, '2013-06-10 16:09:01' ,112, 3860982540324110336,100082,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Operators Exclusive','use_item','0,0,0,0,0,0 (Option-2013-06-10 15:28:42','bag=1,slot=6'

2013-06-10 16:10:32 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:32 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:32 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'kuba9876512',91747,361612,'Heross', 85,90000000,42, 50.839981,1.891644,44.342613, '2013-06-10 16:09:02' ,112, 3860530499311239168,100171,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Recreation Rune','use_item','0,0,0,0,0,0 (Option-2013-06-09 21:48:10','bag=3,slot=18'

2013-06-10 16:10:32 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'kuba9876512',91747,361612,'Heross', 85,90000000,42, 50.839981,1.891644,44.342613, '2013-06-10 16:09:02' ,213, 3860466276666310656,17066,100171,38605304993112391 68,NULL,NULL,NULL,NULL,NULL,NULL,'Zeus Armor','187,32,34,4,3,39 (Option:00000000142125000070)','187,32,34,4,3,39 (Option:00000000000100161670)','2013-06-09 18:06:33'

2013-06-10 16:10:34 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:34 err=-1, [Microsoft][ODBC SQL Server Driver]Query timeout expired, SQL STATE: HYT00, NATIVE ERROR: 0 (0x0)

2013-06-10 16:10:34 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'username',91616,361559,'Cafe...', 85,90000000,47, 404.493134,15.540762,542.551819, '2013-06-10 16:09:04' ,112, 3860328622963359744,100001,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Etain pot','use_item','0,0,0,0,0,0 (Option-2013-06-09 10:17:46','bag=1,slot=6'

2013-06-10 16:10:34 DBWrite::LogGame: err=-1, query=EXEC usp_Insert_Action_Log_E 'username',47848,298922,'[VIP]Ras-Al-Ghul', 85,99999998,70, 807.777588,45.753632,782.715637, '2013-06-10 16:09:04' ,112, 3690960916972634112,100046,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'Movement Rune','use_item','0,0,0,0,0,0 (Option-2012-12-28 14:53:38','bag=5,slot=22'

[andr3y_you96]

USE [PS_GameLog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
DECLARE @find nchar(1) = char(39)
DECLARE @repl nchar(2) = char(39) + char(39)
SELECT @ActionTime = CONVERT(datetime, @ActionTimeZ, 120)
SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)
SET @Text1 = replace(@Text1, @find, @repl)
SET @Text2 = replace(@Text2, @find, @repl)
SET @Text3 = replace(@Text3, @find, @repl)
SET @Text4 = replace(@Text4, @find, @repl)


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

IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
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

try this one

[infinite59]

Originally Posted by troll1337

stop ddosing

First useless post

Originally Posted by FuckCoke

He never changed, the last server you ddos`d? Euphoria.

And your a fail attempt on everything at the moment.

Second useless post, i don't see the point on flamming here, he asked something, now just try to give him some help or simply don't post...

[Dreameh]

Can we show a little maturity children.