개발 흔적 남기기

Mssql로 동작하는 프로젝트에서 게시판 검색 기능이 잘 되지 않는다는 확인 요청건이 들어왔다.

실제로 '[공지]' 라는 키워드로 검색을 해보니 원하는 결과가 나오지 않았다.

아래는 작성한 쿼리이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + 검색어(@PARAM) + '%' 
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

간단한 like절 검색 쿼리인데, 여기에 [공지]를 검색하게되면 와일드카드가 적용되면서 아래와 같은 쿼리로 변경되어 동작된다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%[공지]%' 
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

like절에는 %%라는 와일드카드뿐만 아니라 []라는 와일드카드도 존재한다.

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/wildcard-character-s-to-match-transact-sql?view=sql-server-ver15 

ms에서 설명하고 있듯이 [a-z][0-9]와 같이 사용하여 우편번호라던지 특정 키워드 글자가 있는것을 찾아오는 기능인데,

해당방식으로 찾기때문에 우리가 원하는 결과가 나오지 않는다.

[]와 같은 와일드카드 강제 문자열 처리하기

[와 ]의 키워드를 강제 문자열로 처리하기 위해 REPLACE함수로 강제 치환 처리를 추가하였다.

(프로시져를 생성해서 return함수를 사용해도 좋을 것 같다.)

아래는 사용한 결과물이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + REPLACE(REPLACE(검색어(@PARAM),'[','\['),']','\]') + '%' ESCAPE '\'
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

1. [로 시작하는 문자가 존재하면 \+[ 조합으로 변경한다.

2. ]로 시작하는 문자가 존재하면 \+[ 조합으로 변경한다.

3. ESCAPE를 통해 \를 제외처리하여 와일드카드가 동작하지 않도록 변경하고 \[공지\]라는 문자열로 자동 처리가 되도록 한다.

이후 정상적으로 1개가 검색이 되는 것을 확인했다.

REPLACE 변환처리 함수 생성하여 적용하기

와일드카드인 [, ] 뿐만 아니라 %, ^ 등도 추가를 해야하면 매번 REPLACE항목이 늘어나서 아래처럼 변경될 것이다.

REPLACE(REPLACE(REPLACE(REPLACE(@str,'[','\['),']','\]'), '%', '\%'), '^', '\^')

이런 쿼리를 mybatis등에 정의해두면 가독성도 안좋을 뿐더러, 수정도 어렵고 추가 사항이 발생하면 여간 골치 아픈게 아니다.

이런 REPLACE 기능 자체를 함수로 만들어서 like절마다 함수로 치환하면 좀 더 가독성도 좋고, 유지보수도 쉽게 처리 할  수 있다.

- 함수 만들기

먼저 REPLACE 치환 처리를 해주는 함수를 생성한다.

CREATE FUNCTION SP_ESCAPE_PROC(@str VARCHAR(300))
RETURNS VARCHAR(400)
AS
BEGIN
	DECLARE @RESULT VARCHAR(400)
	SET @RESULT = REPLACE(REPLACE(@str,'[','\['),']','\]')
RETURN @RESULT
END;

SP_ESCAPE_PROC이라는 함수명으로 함수를 생성했다.

1개의 Input을 받고 1개의 문자열을 Output하도록 만들었다. (문자열(300자 제한) 하나를 받고, 문자열(400자 제한)을 리턴한다.)

SET부분에선 실제로 처리할 로직을 입력한다. 여기선 '[]' 만 치환하도록 하였다.

F5키등을 통해 실행한다.

실제로 생성한 함수를 통해 사용한 쿼리이다.

SELECT * FROM 테이블명
WHERE 컬럼 LIKE '%' + dbo.SP_ESCAPE_PROC('[공지]') + '%' ESCAPE '\'
OFFSET 0 ROW FETCH NEXT 5 ROW ONLY

깔끔해지기도 했고, 추가사항이 생기면 함수를 수정해주면 된다.

대칭키를 생성하고 암호화, 복호화 함수를 매번 만들어서 사용하는게 복잡하여 해당 방식을 함수화하여 필요한 값만 파라미터로 넘겨서 처리하도록 구성하였습니다.

암복호화 만들기는 아래 링크를 통해 확인 할 수 있습니다.

myhappyman.tistory.com/229

대칭키 열기

프로시저를 활용하여 대칭키를 열수 있도록 생성합니다.

CREATE PROCEDURE OpenKeys
AS
BEGIN
    BEGIN TRY
        OPEN SYMMETRIC KEY testKey
		DECRYPTION BY CERTIFICATE testCert
    END TRY
    BEGIN CATCH
        -- Handle non-existant key here
    END CATCH
END

암호화 함수 만들기

문자열 형태로 데이터를 전달받아서 암호화된 바이너리 값을 return하는 함수를 만들었습니다.

CREATE FUNCTION getTestEncryt(@str varchar(400))
RETURNS varbinary(256)
AS
BEGIN
	DECLARE @Result varbinary(256)
	SET @Result = EncryptByKey(Key_GUID('testKey'), @str)

RETURN @Result
END

복호화 함수 만들기

암호화와 반대로 암호화된 데이터를 받아서 복호화 처리후 캐스팅하여 return하는 함수입니다.

CREATE FUNCTION getTestDecryt(@enc varbinary(256))
RETURNS varchar(400)
AS
BEGIN
	DECLARE @result varchar(400)
	SET @result = CONVERT(varchar, DecryptByKey(@enc))

RETURN @result
END

사용하기

암호화 테스트

EXEC OpenKeys
INSERT INTO user_info (name, phone) VALUES ('test', dbo.getTestEncryt('010-1234-1234'));

프로시저 OpenKeys를 사용 후 암호화된 데이터를 삽입합니다.

복호화 테스트

EXEC OpenKeys
SELECT
	seq,
	name, 
	phone,
	dbo.getKisaDecryt(phone) as 'decryptPhone'
FROM user_info;

함수를 만들고 사용할 때, 'getTestDecryt'은(는) 인식할 수 없는 기본 제공 함수 이름입니다. 와 같은 오류가 발생했는데, dbo.를 붙여주고 정상적으로 사용되는 모습을 확인했다.

Mssql에서 암호화, 복호화 처리하기

암호화, 복호화를 하기위해서 먼저 인증서와 인증서를 통한 대칭키를 생성해야합니다.

인증서와 대칭키 생성하기

--인증서 생성
CREATE CERTIFICATE testCert   --인증서명
WITH SUBJECT = 'TestCert';    --인증서 제목 생성
GO

--인증서를 통해 대칭키 생성하기
CREATE SYMMETRIC KEY testKey    --대칭키명
    WITH ALGORITHM = AES_256    --사용알고리즘 AES256
    ENCRYPTION BY CERTIFICATE testCert;  --사용할 인증서
GO

쿼리문을 동작시킵니다.

해당 데이터베이스의 보안-인증서, 보안-대칭키에 생성된 것을 확인 할 수 있습니다.

다음은 대칭키를 사용하여 데이터를 삽입해보고 삭제해보겠습니다.

대칭키를 사용하여 암호화하기

-임시 테이블 생성

테스트할 임시 테이블 user_info라는 테이블을 생성합니다.

--임시테이블 생성
CREATE TABLE dbo.user_info
(
	seq int IDENTITY(1,1) PRIMARY KEY,
	name nvarchar(10) NOT NULL,
	phone varbinary(128)
)

seq : PRIMARY KEY, Auto Increment 옵션을 주었습니다.

name : 이름을 입력할 컬럼입니다.

phone : 암호화된 핸드폰 번호가 입력될 컬럼입니다.(varbinary)

테이블을 생성했으니 암호화된 데이터를 삽입해보겠습니다.

-암호화 데이터 삽입하기

--암호화를 할때는 인증서를 통해 대칭키를 열어줍니다.
OPEN SYMMETRIC KEY testKey
   DECRYPTION BY CERTIFICATE testCert;

--임시데이터삽입
INSERT INTO user_info (name, phone) VALUES ('김민수', EncryptByKey(Key_GUID('testKey'), '010-1234-5678'));
INSERT INTO user_info (name, phone) VALUES ('이유나', EncryptByKey(Key_GUID('testKey'), '010-7777-1234'));
INSERT INTO user_info (name, phone) VALUES ('박민지', EncryptByKey(Key_GUID('testKey'), '010-4321-4321'));
INSERT INTO user_info (name, phone) VALUES ('최철수', EncryptByKey(Key_GUID('testKey'), '010-1111-1111'));
INSERT INTO user_info (name, phone) VALUES ('홍길동', EncryptByKey(Key_GUID('testKey'), '010-2222-2222'));
INSERT INTO user_info (name, phone) VALUES ('홍길순', EncryptByKey(Key_GUID('testKey'), '010-3333-3333'));

SELECT * FROM user_info;

암호화를 할때는 항상 인증서를 통해 대칭키를 열어주고 사용합니다.

seq컬럼은 자동증가이므로 제외하고, name, phone 컬럼에만 해당하는 데이터를 삽입하였습니다.

EncryptByKey(Key_GUID('대칭키 이름'), '암호화 할 값')

함수 EncryptByKey를 통해 암호화를 진행합니다.

첫번째 파라미터에는 Key_GUID 함수를 사용하고 파라미터로 대칭키 이름을 문자열 형태로 입력합니다.

두번째 파라미터에는 암호화할 문자열 값을 입력합니다.

대칭키를 사용하여 복호화하기

-암호화 데이터 복호화하기

OPEN SYMMETRIC KEY testKey
   DECRYPTION BY CERTIFICATE testCert;

SELECT
	seq,
	name, 
	phone,
	CONVERT(varchar, DecryptByKey(phone)) as 'decryptPhone'
FROM user_info;

DecryptByKey 함수를 통해 데이터를 복호화하고 자료형을 알맞게 캐스팅합니다.

아래 글을 통해 암복호화 커스텀 함수 만들기로 이어집니다.

myhappyman.tistory.com/230

Mssql 새로 설치 후 한글 데이터를 삽입해보니 전부 ???로 처리되어 나왔습니다.

한글 인코딩을 맞춰주기 위해 아래와같이 데이터베이스 옵션을 변경하였습니다.

select * from sys.databases where name = '데이터베이스명';

collation_name 영역이 한국어로 되어 있지 않았습니다.

아래 쿼리를 통해 변경해주었습니다.

alter database 데이터베이스명 set single_user with rollback immediate;

alter database 데이터베이스명 collate Korean_Wansung_CI_AS 

alter database 데이터베이스명 set multi_user;

이후 정상적으로 삽입이 되는지 확인합니다.

테이블 우클릭 - 디자인

Primary Key 설정

설정하고자 하는 컬럼 우클릭 후 기본 키 설정 선택

Auto increment 설정

해당 하는 컬럼을 클릭하면 하단에 열 속성이 생깁니다.

ID사양 부분을 클릭해서 예로 변경 후 시작값 및 증가값을 설정합니다.

설정이 끝나면 저장을 하면 적용이 가능합니다.

저장을 하려고 하는데 아래와 같은 경고창이 뜬다면 설정을 통해 변경 후 저장을 한다.

'변경 내용을 저장할 수 없습니다. 변경 내용을 적용하려면 다음 테이블을 삭제하고 다시 만들어야 합니다.'

도구 > 옵션

테이블을 다시 만들어야 하는 변경 내용 저장 안함 옵션을 체크 해제한다.