Vietnam DB von TwiLight

Darki1 · 29. März 2010, 17:17

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

Quellcode

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 · 29. März 2010, 17:29

[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 · 29. März 2010, 17:40

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 · 29. März 2010, 17:46

Till1993;100858 schrieb:

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 · 29. März 2010, 18:18

Hier einfach neue Abfrage

Quellcode

/****** 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 · 29. März 2010, 18:43

Luoo;100878 schrieb:

Hier einfach neue Abfrage

Quellcode

/****** 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 · 29. März 2010, 18:52

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

Etzel · 29. März 2010, 19:17

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

Vietnam DB von TwiLight

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

Ninpa · 29. März 2010, 19:20

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

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 · 29. März 2010, 20:23

Ninpa;100923 schrieb:

[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 · 29. März 2010, 20:37

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

itakage · 29. März 2010, 20:43

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 · 29. März 2010, 21:10

Thema kann geclosed werden da Problem gelöst ist.

Quellcode

Quellcode

Quellcode

Teilen