MS-SQL 서버에서 제공하지 못하는 기능을 추가하여 쿼리문만으로 처리를 하고 싶을때

외장 스토어드 프로시저를 VC++로 개발하여 추가하는 방법..

사용자가 스토어드 프로시저를 만들어 넣을 수도 있는데 왜 이런 난리브루스가 필요할까?
간혹 살다보면 MS-SQL 서버에서 제공되는 기본 쿼리만으로는 해결이 안되는 문제가 있기 때문이다..

얼마전 다음과 같은 상황이 있었다..

DB 상에서의 사용자 주민등록번호를 암호화 할 필요가 있었다..

즉, 사용자 정보중 주민등록번호를 DB를 열어봐도 알 수 없도록 해야한다는 것이다..

사용자가 주민등록번호를 입력하면 비교는 할 수 있돼, 푸는 방법은 없도록 암호화를 해야했으므로 해쉬가 가장 적합해보였다..

그리고 특정 소스코드의 개입 없이 쿼리문만으로 이러한 작업들이 가능해야 했다..

해서 웹 어플리케이션 소스를 수정해 처리하는 방식은 배제하였다..

(웹어플리케이션에 의존하지 않고 주민등록번호의 비교작업이나 등록, 점검 등의 작업을 쿼리문 만으로 할 수 있도록..)

어플리케이션의 소스코드에서 처리하는 것은 배제가 되었으므로,

MS-SQL이 기본으로 제공하는 CHECKSUM 함수 사용하는 것을 검토해 봤다..

이걸로 충분히 해결이 될 것으로 기대가 되었기 때문이다..

음.. 그런데, 약 4만개 정도의 주민등록번호로 테스트를 해본 결과 중복이 4개 정도 발생하는 것이다..

이런, 이런.. 중복이 발생하면 안되는데..

결국, MS-SQL에서 MD5를 사용할 수 있는 방법을 검색하게 되었고, 코드프로젝트 사이트에서 딱 맞는 소스코드를 찾을 수 있었다..
그런데 말이지 왜 checksum 함수는 4만개만으로도 중복이 생겼을까?

그리고 MD5는 사용자 수가 많아진다고 하더라도 중복이 생기지 않는걸 보장해줄 수 있을까?
일단은 MD5는 중복이 생기지 않을 것으로 기대를 하고 적용시키기로 했다..

100만명쯤 되었을때도 중복이 안되야 할텐데.. 아마 안될 것이라고 생각이 든다..

P2P 프로그램 같은데서 동영상 파일의 해쉬값을 얻기 위해 사용하는거 보면

주민등록번호 길이 정도로는 중복이 안될 것 같다는 생각이 든다.. (중복이 생기면 안돼~ 다른 방법을 다시 찾아야 한단 말이지..)


정보수집 및 장비챙기기

소스코드는 코드 프로젝트 사이트에서 MD5 Hash SQL Server Extended Stored Procedure 라는 제목의 글을 참조했다.. (http://www.codeproject.com/database/xp_md5.asp)

소스코드를 직접 수정하거나 할 경우가 아니라면 만들어 적용시키기 부분에서 처럼 사용하면 된다..

소스코드를 직접 빌드해서 사용하려면 VC++ 6.0 with SP6, 그리고 MS-SQL 서버 개발도구가 필요하다..

MS-SQL 서버 설치시 옵션에서 개발도구를 체크해서 설치하면 MS-SQL 서버에 관련된 개발시 필요한 헤더파일과 라이브러리 파일,

그리고 각종 샘플 소스코드 등이 아래의 위치에 설치가 된다.. (설치시 디폴트로 둔 경우 아래와 같았다..)

C:\Program Files\Microsoft SQL Server\80\Tools\Dev\Tools

이 폴더 하위의 include 및 lib 폴더를 include, lib 경로에 추가한다..

(메뉴에서 Tools > Options 를 선택하면 나타나는 다이얼로그 화면에서

Directories 탭을 선택하여 include와 lib 경로를 추가하고 이 경로를 맨위로 올려둔다..

그렇지 않으면 컴파일시 에러가 난다..

MS-SQL 2000 서버와 함께 제공되는 최신의 헤더파일을 사용하여 컴파일이 될 수 있도록

이 경로를 추가하고 리스트 상에서 맨 위로 올려두어야 하는 것이다..)


만들어 적용시키기

MD5 해쉬 알고리즘 스토어드 프로시저 적용방법은 간단하다..

위의 사이트에서 DLL 파일을 다운로드 받았거나, 소스코드를 다운로드 받아 빌드를 하여 만들어낸 DLL 파일만 있으면 된다..

아래와 같이 3단계만 거치면 내가 만든 스토어드 프로시저를 마치 MS SQL 서버가 원래 제공했던 함수들 같이 사용할 수 있다..

1. DLL 파일을 아래 경로에 복사해 넣는다..

        C:Program Files\Microsoft SQL Server\MSSQL\Binn

USE master;
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'

 

3. 좀 더 편하게 사용하기 위해 사용할 모든 데이터베이스에 아래와 같이 사용자정의 함수를 추가한다..

즉, 새로운 데이터베이스가 생겼다면 새로 생긴 DB에 아래의 함수를 추가해 넣어야 한다..

물론, 이 함수를 만들지 않고 등록된 스토어드 프로시져를 직접 호출해도 된다..

use 데이터베이스명
go

CREATE FUNCTION [dbo].[fnMD5] (
    @data text
)
Returns char(32)
AS
Begin
   DECLARE @hash char(32)
   Exec master.dbo.xp_md5 @data, -1, @hash OUTPUT

   Return @hash
End
go


CREATE FUNCTION [dbo].[fnM5x] (
    @data   image,
    @len    int=-1
)
Returns char(32)
AS
Begin
   DECLARE @hash char(32)
   Exec master.dbo.xp_md5 @data, @len, @hash OUTPUT

   Return @hash
End
go

4. 사용방법은 다음과 같이 간단하다.. 드디어 원하는대로 쿼리문 안에서 MD5 해쉬를 사용할 수 있게 되었다..

insert, update 등 어떤 쿼리문에서도 쉽게 사용가능하다.. 대만족~

SELECT  dbo.fnMD5('주민번호')

 

 

소스 대충 보니 MS-SQL 서버에서 동작하는 확장 스토어드 프로시저를 DLL로 작성하는건 뭐 별거 없어 보인다..

앞으로 필요한 함수가 있다면 만들어 넣으면 될 것 같다..

그동안 MS-SQL 서버를 10년 가까이 사용해 오면서 이런 방법이 있다는 것을 이제야 알다니..

(물론, 10년전에 사용하던 MS-SQL Server 6.5에도 이런게 가능했는지는 모르겠지만..)