Vietnam DB von TwiLight

Darki1

So da ich gerade dabei bin die Vietnam DB von Twilight zu entbuggen
habe ich eine kleine Frage wie ich master.dbo.xp.crypt aus der Prozedur usp.dbo.creatnewaccount bekomme damit man Accounts machen kann ohne Xp Crypt. Hier mal die Account DBF

Code

USE [ACCOUNT_DBF]
GO
/****** Object:  User [ongate]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [ongate] FOR LOGIN [ongate] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [billing]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [billing] FOR LOGIN [billing] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [account]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [account] FOR LOGIN [account] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [acc_on9@te]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [acc_on9@te] FOR LOGIN [acc_on9@te] WITH DEFAULT_SCHEMA=[acc_on9@te]
GO
/****** Object:  Schema [acc_on9@te]    Script Date: 10/28/2009 17:14:28 ******/
CREATE SCHEMA [acc_on9@te] AUTHORIZATION [account]
GO
/****** Object:  Table [dbo].[CashHistory]    Script Date: 10/28/2009 17:14:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CashHistory](
	[account] [varchar](32) NULL,
	[beforeCash] [int] NULL,
	[afterCash] [int] NULL,
	[regdate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ACCOUNT_TBL]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL](
	[account] [varchar](32) NOT NULL,
	[password] [char](32) NOT NULL,
	[isuse] [char](1) NULL,
	[member] [char](1) NULL,
	[id_no1] [char](6) NULL,
	[id_no2] [char](255) NULL,
	[realname] [char](1) NULL,
	[reload] [char](1) NULL,
	[cash] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [ACCOUNT_ID1] ON [dbo].[ACCOUNT_TBL] 
(
	[account] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'123', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT_TBL', @level2type=N'COLUMN',@level2name=N'account'
GO
EXEC sys.sp_addextendedproperty @name=N'123', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT_TBL', @level2type=N'COLUMN',@level2name=N'password'
GO
/****** Object:  Table [dbo].[ACCOUNT_TBL_DETAIL]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL_DETAIL](
	[account] [varchar](32) NOT NULL,
	[gamecode] [char](4) NOT NULL,
	[tester] [char](1) NOT NULL,
	[m_chLoginAuthority] [char](1) NULL,
	[regdate] [datetime] NOT NULL,
	[BlockTime] [char](8) NULL,
	[EndTime] [char](8) NULL,
	[WebTime] [char](8) NULL,
	[isuse] [char](1) NULL,
	[secession] [datetime] NULL,
	[email] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [ACCOUNT_TBL_DETAIL_ID1] ON [dbo].[ACCOUNT_TBL_DETAIL] 
(
	[account] DESC,
	[gamecode] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PCZone]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCZone](
	[PCZoneID] [int] IDENTITY(1,1) NOT NULL,
	[PCZoneName] [varchar](100) NOT NULL,
	[Address] [varchar](100) NOT NULL,
	[Phone] [varchar](100) NOT NULL,
	[CPU] [varchar](100) NOT NULL,
	[VGA] [varchar](100) NOT NULL,
	[RAM] [varchar](100) NOT NULL,
	[Monitor] [varchar](100) NOT NULL,
	[Comment] [varchar](1000) NOT NULL,
	[Grade] [tinyint] NOT NULL,
	[Account] [varchar](32) NOT NULL,
	[RegDate] [datetime] NOT NULL,
 CONSTRAINT [PK_iCafeInfo_1] PRIMARY KEY CLUSTERED 
(
	[PCZoneID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[mydata]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mydata](
	[line] [varchar](8000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_MessengerList]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_MessengerList]
@serverindex char(2),
@m_szName varchar(32)
as


set nocount on


declare @q1 nvarchar(4000)
declare @m_idPlayer char(7)
set @q1 = '
select @m_idPlayer = m_idPlayer from CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.CHARACTER_TBL where m_szName = ''' + @m_szName + ''''
exec sp_executesql @q1, N'@m_idPlayer char(7) output', @m_idPlayer output


set @q1 = '
select c.m_szName, m_idPlayer
from CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.tblMessenger as m
	inner join CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.CHARACTER_TBL as c on m.idFriend = c.m_idPlayer
where m.idPlayer = ''' + @m_idPlayer + ''' and isblock = ''F'' and chUse = ''T'' order by m.idFriend'
exec sp_executesql @q1
GO
/****** Object:  Table [dbo].[AccountPlay]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AccountPlay](
	[Account] [varchar](32) NOT NULL,
	[PlayDate] [int] NOT NULL,
	[PlayTime] [int] NOT NULL,
 CONSTRAINT [PK_AccountPlay] PRIMARY KEY CLUSTERED 
(
	[Account] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PCZoneIP]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCZoneIP](
	[IPID] [int] IDENTITY(1,1) NOT NULL,
	[PCZoneID] [int] NOT NULL,
	[IPFrom1] [tinyint] NOT NULL,
	[IPFrom2] [tinyint] NOT NULL,
	[IPFrom3] [tinyint] NOT NULL,
	[IPFrom4] [tinyint] NOT NULL,
	[IPTo4] [tinyint] NOT NULL,
	[IsUse] [tinyint] NOT NULL,
	[RegDate] [datetime] NOT NULL,
	[OperID] [varchar](32) NULL,
	[OperDate] [datetime] NULL,
 CONSTRAINT [PK_iCafeIP_1] PRIMARY KEY CLUSTERED 
(
	[IPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateCashInfo]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_UpdateCashInfo]
@account varchar(32),
@cash int
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin
	begin tran
		insert into CashHistory (account, beforeCash, afterCash)
		select @account, cash, @cash from ACCOUNT_TBL where account = @account


		update ACCOUNT_TBL
		set cash = @cash
		where account = @account
	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[ACCOUNT_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROC [dbo].[ACCOUNT_STR]
@iaccount   	VARCHAR(32),
@ipassword VARCHAR(16)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************






	ACCOUNT_STR 스토어드
	작성자 : 최석준
	작성일 : 2004.01.18


	ex) ACCOUNT_STR 'beat','1234'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on
IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount AND gamecode = 'A000')
	BEGIN
		IF EXISTS(SELECT account FROM ACCOUNT_TBL  
                        WHERE account = @iaccount  AND password = @ipassword )
-- 			BEGIN
-- 			
-- 				DECLARE @birthyear CHAR(4),@currdate CHAR(8)
-- 				SELECT  @birthyear = 	CASE 	WHEN  SUBSTRING(id_no2,1,1) IN ('9','0') THEN '18' + SUBSTRING(id_no1,1,2)
-- 							             							WHEN  SUBSTRING(id_no2,1,1) IN ('1','2') THEN '19' + SUBSTRING(id_no1,1,2)
-- 																	WHEN	 SUBSTRING(id_no2,1,1) IN ('3','4') THEN '20' + SUBSTRING(id_no1,1,2)
-- 																	WHEN  SUBSTRING(id_no2,1,1) IN ('5','6') THEN '21' + SUBSTRING(id_no1,1,2)			
-- 																	WHEN  SUBSTRING(id_no2,1,1) IN ('7','8') THEN '22' + SUBSTRING(id_no1,1,2)
-- 														END			 
-- 				   FROM ACCOUNT_TBL
-- 				WHERE account = @iaccount
-- 
-- 				SELECT @currdate = CONVERT(CHAR(8),GETDATE(),112)
-- 
-- 				SELECT	fError = 	CASE 	WHEN a.id_no1 = 'a00000' 
-- 																THEN '0' 
-- 								 							WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
-- 										   						THEN '3' 		
-- 															WHEN a.realname = 'F' 
-- 																THEN '4' 										 			
-- 												 			WHEN  @birthyear >  DATEADD(Year,-13,@currdate)
-- 																THEN '5'
-- 												 			WHEN  @birthyear >=  DATEADD(Year,-11,@currdate) AND b.tester = '0'
-- 																THEN '6'															
-- 															ELSE '0' 	END,
-- 
-- 								fText 	=	CASE 	WHEN a.id_no1 = 'a00000' 
-- 																THEN '사원 및 기자'	
-- 												 			WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
-- 														   		THEN '계정블럭이거나 유료화 초과'			
-- 															WHEN a.realname = 'F' 
-- 																THEN '실명처리가 안된것'									 			
-- 												 			WHEN  @birthyear >  DATEADD(Year,-13,@currdate)
-- 																THEN '프리프는 12세 이상 이용가 이므로 게임접속을 할수 없습니다.'	
-- 												 			WHEN  @birthyear >=  DATEADD(Year,-11,@currdate) AND b.tester = '0'
-- 																THEN '14세 미만 가입자 분들은 부모님 동의서를 보내주셔야 게임 접속이 가능합니다'																
-- 															ELSE  '정상사용자 14세 이상' END
-- 				   FROM 	ACCOUNT_TBL a, ACCOUNT_TBL_DETAIL b 
-- 				WHERE 	a.account = b.account AND b.account = @iaccount AND a.[password] = @ipassword
-- 			END
			BEGIN
				SELECT fError = '0', fText = 'OK'
			END
		ELSE
			BEGIN
				SELECT fError = '1', fText = 'Wrong Password !!'
			END
	END	
ELSE
	BEGIN
		SELECT fError = '2', fText = 'Account Not Exists !!'
	END
RETURN


-- 계정 검사 Rule
-- 1. (fError=2 리턴) 계정있는지 확인. 계정확인시 "gamecode = A000" 만 프리프 회원.
-- 2. (fError=1 리턴) 암호확인.
-- 3. (fError=0 리턴) 사원 및 기자 확인( id_no1가 a0000 )으로 확인.계정.암호만 확인
-- 4. (fError=3 리턴) 블럭타임과, 과금타임 확인.
-- 5. (fError=4 리턴) 실명확인.
-- 6. (fError=5 리턴) 12세 이상인지 확인. 이하이면 "프리프는 12세 이상 이용가 이므로 ]
--                            게임접속을 할수 없습니다." 라고 알림.
-- 7. (fError=6 리턴) 부모동의서가 없는 애덜 확인 tester = 0 이면 "14세 미만 가입자 분들은 
--                            부모등의서를 보내주셔야 게임 접속이 가능합니다"라고 알림.
-- 8. (fError=0 리턴) 위의 예말고는 정상 사용자로 인정.
set nocount off
GO
/****** Object:  StoredProcedure [dbo].[usp_CreateNewAccount]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_CreateNewAccount]
@account varchar(32),
@pw varchar(32),
@cash int = 0,
@email varchar(100) = ''
as
set nocount on
set xact_abort on


if not exists (select * from ACCOUNT_TBL where account = @account)
begin
	declare @hash char(32), @tpw varchar(40)
	set @tpw = 'dlrjsdn' + @pw


	exec master.dbo.xp_crypt @tpw, @hash output


	begin tran
	INSERT ACCOUNT_TBL(account,password,isuse,member,id_no1,id_no2,realname, cash)
	VALUES(@account, lower(@hash), 'T', 'A', '', '', '', @cash)
	INSERT ACCOUNT_TBL_DETAIL(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession, email)
	VALUES(@account,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,10,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL, @email)
	insert AccountPlay (Account, PlayDate)
	select @account, convert(int, convert(char(8), getdate(), 112))


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateAccountPassword]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_UpdateAccountPassword]
		@account		varchar(32),
		@password	varchar(32)
AS
SET NOCOUNT ON




	Update ACCOUNT_TBL SET Password = @password Where Account=@account
GO
/****** Object:  StoredProcedure [dbo].[usp_ChangePW]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangePW]
@account varchar(32),
@pw varchar(32)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin
	declare @hash char(32), @tpw varchar(40)
	set @tpw = 'tpgk' + @pw


	exec master.dbo.xp_crypt @tpw, @hash output


	begin tran
	update ACCOUNT_TBL
	set [password] = lower(@hash)
	where account = @account


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[LOGIN_RELOAD_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE       PROC [dbo].[LOGIN_RELOAD_STR]
/**********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************
	LOGIN_RELOAD_STR 스토어드
	작성자 : 최석준
	작성일 : 2005.01.24
	ex) LOGIN_RELOAD_STR


**********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/


AS
set nocount on
SELECT account FROM ACCOUNT_TBL WHERE reload='T'
set nocount off


RETURN
GO
/****** Object:  StoredProcedure [dbo].[CREATE_ACCOUNT_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE    PROC  [dbo].[CREATE_ACCOUNT_STR] 
@iGu CHAR(1) = 'A',
@k INT = 100
AS
DECLARE @i INT,@j INT,@oSeq CHAR(6)
IF @iGu = 'A'
BEGIN	
	SET @j = 1
	SELECT @i = ISNULL(CONVERT(INT,(MAX(account))),0) + 1 FROM ACCOUNT_TBL
	SELECT CONVERT(INT,(MAX(account))) + @k  FROM ACCOUNT_TBL
	WHILE @j < @k + 1
	BEGIN
		SET @oSeq = RIGHT('000000' + CONVERT(VARCHAR(6),@i),6)
		INSERT ACCOUNT_TBL
		(account,password,isuse,member,id_no1,id_no2,realname)
		VALUES
		(@oSeq,@oSeq,'T','A','','','')
		INSERT ACCOUNT_TBL_DETAIL
		(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession)
		VALUES
		(@oSeq,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,6,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL)
		SET @i = @i + 1
		SET @j = @j + 1
		IF (@j  % 100 = 0)
		PRINT @oSeq
	END
RETURN
END
GO
/****** Object:  StoredProcedure [dbo].[usp_og_ChangePassWord]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_og_ChangePassWord]
@account varchar(32),
@password varchar(32)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin	
	begin tran			
		Update ACCOUNT_TBL SET Password = @password Where account=@account
		if @@error <> 0
		begin
			rollback tran
			select -1
		end
		else
		begin
			commit tran
			select 1
		end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[LOGINJOIN_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROC [dbo].[LOGINJOIN_STR]
	@iGu        		CHAR(2) 			= 'A1', 
	@iaccount   	VARCHAR(32)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************


	LOGINJOIN_STR 스토어드
	작성자 : 송현석
	작성일 : 2004.01.30 
	수정일 : 


	ex) LOGINONOFF_STR 'A1','1234'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/


AS
set nocount on


IF @iGu = 'A1' -- ON
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'J'
		WHERE account = @iaccount
	RETURN
	END


ELSE
IF @iGu = 'A2' -- OFF
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'O'
		WHERE account = @iaccount	
	RETURN
	END


ELSE
IF @iGu = 'A3' -- ALL OFF
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'O'
	RETURN
	END


set nocount off
GO
/****** Object:  StoredProcedure [dbo].[usp_ChangeEmail]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangeEmail]
@account varchar(32),
@email varchar(100)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL_DETAIL where account = @account)
begin
	begin tran
	update ACCOUNT_TBL_DETAIL
	set email = @email
	where account = @account


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[USP_PCZoneIP_Check]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check '218.38.238.131', @o_Grade output
select @o_Grade


exec LOGIN_STR 'chipi', '85158c7b8a7f3113b0f32b70ed936b2c', '127.0.0.0'
*/
create   proc [dbo].[USP_PCZoneIP_Check]
     @i_IPAddress varchar(15)
    , @o_Grade tinyint output
as
set nocount on
set transaction isolation level read uncommitted


declare @i_IP1 tinyint
declare @i_IP2 tinyint
declare @i_IP3 tinyint
declare @i_IP4 tinyint


declare @index int


--@i_IP1
set @index = charindex('.', @i_IPAddress)
set @i_IP1 = left(@i_IPAddress, @index-1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress)-@index)


--@i_IP2
set @index = charindex('.', @i_IPAddress)
set @i_IP2 = left(@i_IPAddress, @index-1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress)-@index)


--@i_IP3, @i_IP4 
set @index = charindex('.', @i_IPAddress)
set @i_IP3 = left(@i_IPAddress, @index-1)
set @i_IP4 = right(@i_IPAddress, len(@i_IPAddress)-@index)


--PCZoneIP_Check
select @o_Grade = b.Grade
from PCZoneIP a
        inner join PCZone b
            on a.PCZoneID = b.PCZoneID
where IsUse in (1, 9)
and IPFrom1 = @i_IP1
and IPFrom2 = @i_IP2
and IPFrom3 = @i_IP3
and IPFrom4 <= @i_IP4 and IPTo4 >= @i_IP4


select @o_Grade = isnull(@o_Grade, 0)
GO
/****** Object:  StoredProcedure [dbo].[LOGIN_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOGIN_STR]
	@iaccount   	VARCHAR(32),
	@ipassword 		char(32)
   -- Ver. 14 PCZoneIP
   ,@i_IPAddress varchar(15) = '0.0.0.0'
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************






	ACCOUNT_STR 스토어드
	작성자 : 최석준
	작성일 : 2004.01.18


	ex) ACCOUNT_STR 'beat','1234'
SELECT * FROM ACCOUNT_TBL_DETAIL WHERE account='aeonsoft'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on


-- Ver 14. PCZoneIP_Check
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check @i_IPAddress, @o_Grade output      


IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount ) -- AND gamecode = 'A000') 
BEGIN
	
	DECLARE @curDate char(8)


		IF EXISTS(SELECT account FROM ACCOUNT_TBL  
                        WHERE account = @iaccount  AND password = @ipassword) BEGIN


			SELECT 	@curDate=CONVERT(CHAR(8), getdate(), 112)
			
			SELECT 	fError=CASE 
							-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN '91'
							WHEN BlockTime>=@curDate THEN '9' 
							ELSE '0' END,
				   	fText= CASE 
							-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN 'Session Expired'
							WHEN BlockTime>=@curDate THEN 'Block' ELSE 'OK' END,
				   	fCheck=tester,
					f18='1' 
					-- Ver14. PCZoneIP
					 ,fPCZone = @o_Grade
			FROM ACCOUNT_TBL a INNER JOIN ACCOUNT_TBL_DETAIL b ON (a.account=b.account)
			WHERE a.account=@iaccount
		END
		ELSE BEGIN
			SELECT fError = '1', fText = 'Wrong Password !!',fCheck ='',f18='1', fPCZone = '0'			-- PCZoneIP (, fPCZone = '0' 추가)
		END
END	
ELSE BEGIN
	SELECT fError = '2', fText = 'Account Not Exists !!',fCheck ='',f18='1', fPCZone = '0'			-- PCZoneIP (, fPCZone = '0' 추가)
END


RETURN


-- 계정 검사 Rule
-- 1. (fError=2 리턴) 계정있는지 확인. 계정확인시 "gamecode = A000" 만 프리프 회원.
-- 2. (fError=1 리턴) 암호확인.
-- 3. (fError=0 리턴) 사원 및 기자 확인( id_no1가 a0000 )으로 확인.계정.암호만 확인
-- 4. (fError=3 리턴) 블럭타임과, 과금타임 확인.
-- 5. (fError=4 리턴) 실명확인.
-- 6. (fError=5 리턴) 12세 이상인지 확인. 이하이면 "프리프는 12세 이상 이용가 이므로 ]
--                            게임접속을 할수 없습니다." 라고 알림.
-- 7. (fError=6 리턴) 부모동의서가 없는 애덜 확인 tester = 0 이면 "14세 미만 가입자 분들은 
--                            부모등의서를 보내주셔야 게임 접속이 가능합니다"라고 알림.
-- 8. (fError=0 리턴) 위의 예말고는 정상 사용자로 인정.
set nocount off
GO
/****** Object:  Default [DF_CashHistory_regdate]    Script Date: 10/28/2009 17:14:39 ******/
ALTER TABLE [dbo].[CashHistory] ADD  CONSTRAINT [DF_CashHistory_regdate]  DEFAULT (getdate()) FOR [regdate]
GO
/****** Object:  Default [DF_ACCOUNT_TBL_cash]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[ACCOUNT_TBL] ADD  CONSTRAINT [DF_ACCOUNT_TBL_cash]  DEFAULT ((0)) FOR [cash]
GO
/****** Object:  Default [DF_ACCOUNT_DETAIL_email]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[ACCOUNT_TBL_DETAIL] ADD  CONSTRAINT [DF_ACCOUNT_DETAIL_email]  DEFAULT ('') FOR [email]
GO
/****** Object:  Default [DF_PCZone_Grade]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_Grade]  DEFAULT ((1)) FOR [Grade]
GO
/****** Object:  Default [DF_PCZone_Account]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_Account]  DEFAULT ('') FOR [Account]
GO
/****** Object:  Default [DF_PCZone_RegDate]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_RegDate]  DEFAULT (getdate()) FOR [RegDate]
GO
/****** Object:  Default [DF_AccountPlay_Account]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_Account]  DEFAULT ('') FOR [Account]
GO
/****** Object:  Default [DF_AccountPlay_PlayDate]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_PlayDate]  DEFAULT ('') FOR [PlayDate]
GO
/****** Object:  Default [DF_AccountPlay_PlayTime]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_PlayTime]  DEFAULT ((0)) FOR [PlayTime]
GO
/****** Object:  Default [DF_PCZoneIP_IsUse]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP] ADD  CONSTRAINT [DF_PCZoneIP_IsUse]  DEFAULT ((0)) FOR [IsUse]
GO
/****** Object:  Default [DF_PCZoneIP_RegDate]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP] ADD  CONSTRAINT [DF_PCZoneIP_RegDate]  DEFAULT (getdate()) FOR [RegDate]
GO
/****** Object:  ForeignKey [FK_iCafeIP_iCafeInfo1]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP]  WITH CHECK ADD  CONSTRAINT [FK_iCafeIP_iCafeInfo1] FOREIGN KEY([PCZoneID])
REFERENCES [dbo].[PCZone] ([PCZoneID])
GO
ALTER TABLE [dbo].[PCZoneIP] CHECK CONSTRAINT [FK_iCafeIP_iCafeInfo1]
GO

Alles anzeigen

Invitation

[COLOR="DeepSkyBlue"]WTF Vietnam?

Gibts etwa ein Vietnamesisches Flyff?
Währe mir was neues xD
Zu deinem Problem kann ich leider nichts beitragen.

mfg[/COLOR]

Till1993

entbuggen`?
Die Db ist Bugfrei!
Es fehlen legendlich ein paar Einträge die nötig sind.
Die hat er raus gelassen.
XP_CRYPT auszubauen ist easy. Aber am besten nimm einfach die Prozeduren aus der allinone db, das solltest du schaffen.

Mfg
Till

Darki1

Zitat von Till1993;100858

entbuggen`?
Die Db ist Bugfrei!
Es fehlen legendlich ein paar Einträge die nötig sind.
Die hat er raus gelassen.
XP_CRYPT auszubauen ist easy. Aber am besten nimm einfach die Prozeduren aus der allinone db, das solltest du schaffen.
Mfg
Till

Meinste warscheinlich die Prozedur usp.dbo.createnewaccount durch die Prozedur dbo.creataccount von der AllInone Db denk ich mal. Korregier mich wenn ich falsch liege.

Und die Xp Crypt Prozedur ist nicht ausgelassen

Edit:So ich habe die Prozedur ausgetauscht gegen die von der AllinOne aber jetzt wirft er einige andere Errors aus.

Luoo

Hier einfach neue Abfrage

Code

/****** Object:  StoredProcedure [dbo].[usp_CreateNewAccount]    Script Date: 01/11/2010 14:58:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[usp_CreateNewAccount]
@account varchar(32),
@pw varchar(32),
@cash int = 0,
@email varchar(100) = ''
as
set nocount on
set xact_abort on


if not exists (select * from ACCOUNT_TBL where account = @account)
begin


	begin tran
	INSERT ACCOUNT_TBL(account,password,isuse,member,id_no1,id_no2,realname, cash)
	VALUES(@account, @pw, 'T', 'A', '', '', '', @cash)
	INSERT ACCOUNT_TBL_DETAIL(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession, email)
	VALUES(@account,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,10,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL, @email)
	insert AccountPlay (Account, PlayDate)
	select @account, convert(int, convert(char(8), getdate(), 112))


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO

Alles anzeigen

Creditz to HDBlacker

Darki1

Zitat von Luoo;100878

Hier einfach neue Abfrage

Code

/****** Object:  StoredProcedure [dbo].[usp_CreateNewAccount]    Script Date: 01/11/2010 14:58:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[usp_CreateNewAccount]
@account varchar(32),
@pw varchar(32),
@cash int = 0,
@email varchar(100) = ''
as
set nocount on
set xact_abort on


if not exists (select * from ACCOUNT_TBL where account = @account)
begin


	begin tran
	INSERT ACCOUNT_TBL(account,password,isuse,member,id_no1,id_no2,realname, cash)
	VALUES(@account, @pw, 'T', 'A', '', '', '', @cash)
	INSERT ACCOUNT_TBL_DETAIL(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession, email)
	VALUES(@account,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,10,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL, @email)
	insert AccountPlay (Account, PlayDate)
	select @account, convert(int, convert(char(8), getdate(), 112))


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO

Alles anzeigen

Creditz to HDBlacker

Ich seh da kein Unterschied In der der andere ACCOUNT_DBF ist genau an der gleichen Stelle genau die Gleiche Prozedur nur das der 1 Abschnitt mit master.dbo.xp.xrypt rausgenommen wurde das hatte ich voher auch nur dann kann ich kein account erstellen bzw wirft er mir Errors das man den Salt nicht als Pw benutzen kann also den Wert 0 nicht in die Account_TBL erstellen kann

Luoo

Di funkt bei mir die alte Prozedur muss vorhnaden sein ;)

Etzel

[FONT="Comic Sans MS"][COLOR="DarkGreen"][SIZE="3"]

Zitat

Vietnam DB von TwiLight

Die Vietnam DB haben doch yojo ( oder so ) und Ankashi geholt , oder irre ich mich da ?[/SIZE][/COLOR][/FONT]

Ninpa

[FONT="Comic Sans MS"][COLOR="White"][SIZE="2"]

Zitat

Die Vietnam DB haben doch yojo ( oder so ) und Ankashi geholt , oder irre ich mich da ?

Was denkst du woher Yojo die LoginDaten hatte :)
Von Twi : D[/SIZE][/COLOR][/FONT]

Till1993

Zitat von Ninpa;100923

[FONT="Comic Sans MS"][COLOR="White"][SIZE="2"]
Was denkst du woher Yojo die LoginDaten hatte :)
Von Twi : D[/SIZE][/COLOR][/FONT]

Von wegen von Twi. Die Daten hat er von YannickMAMA bekommen.

Mfg
Till

Luoo

Yojo war doch vorher Team und dann hat Rauschi vergessen pw zu ändern ^^ oder so

itakage

irgendwie kommen wa hier doch vom Thema ab oder net xDD
und warum soll grad twi ihn die Daten geben oO

noch was zu der Datenbank...
keine ahnung aber ist vietnam anders? xDDD
kanng grad net googlen oder so :|

Darki1

Thema kann geclosed werden da Problem gelöst ist.

Vietnam DB von TwiLight

Jetzt mitmachen!

SCHNELLZUGRIFF

HILFE & COMMUNITY

RECHTLICHES