반응형

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 

 

하나 이상의 문자를 찾는 [] 와일드카드 - SQL Server (Transact-SQL)

와일드카드를 사용하여 하나 이상의 문자를 찾습니다.

docs.microsoft.com

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

 

Mssql - 대칭키를 활용한 데이터 암복호화 처리하기

Mssql에서 암호화, 복호화 처리하기 암호화, 복호화를 하기위해서 먼저 인증서와 인증서를 통한 대칭키를 생성해야합니다. 인증서와 대칭키 생성하기 --인증서 생성 CREATE CERTIFICATE testCert --인증

myhappyman.tistory.com

 

 

대칭키 열기

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

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사양 부분을 클릭해서 예로 변경 후 시작값 및 증가값을 설정합니다.

 

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

 

 

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

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

 

도구 > 옵션

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

반응형
반응형

기존 mysql 5.5.40에서 사용시엔 인코딩 관련 옵션만 적어서 연결을 했는데, 5.7로 올리자 연결은 되지만 한글과 같은 데이터만 전달하면 정상적으로 인식이 되지 않는 현상이 있었다.

 

 

5.7로 올라오고 SSL 옵션이 기본적으로 true로 변경되었고, 이부분을 false처리하지 않으면 정상동작하지 않으니 수정하도록 한다.

 

 useSSL=false 

jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&defaultFetchSize=1000&useSSL=false

 

반응형
반응형

insert 쿼리를 날리고 해당 쿼리의 Primary key값을 가져오고 싶은 경우가 있는데, 불필요하게 select를 날릴 필요없이 mybatis옵션을 통해 방금 insert한 값의 pk값을 가져올 수 있다.

 

useruseGeneratedKeys, keyProperty 속성을 추가하면 처리한 paramter값에 넣어주는 것을 볼 수 있다.

<insert id="inserTableData" parameterType="java.util.HashMap" useGeneratedKeys="true" keyProperty="id">
	INSERT INTO table(
      column1,
      ...
    ) VALUES (
      #{column1},
      ...
	)
	<selectKey keyProperty="lastPk" resultType="Integer" order="AFTER">
		SELECT LAST_INSERT_ID()
	</selectKey>
</insert>

hashmap parameter를 통해 insert를 처리하였고, insert dao가 끝난 후 hashmap데이터를 확인해보면 키값으로 지정한 <selectKey>를 통해 지정한 lastPk에 값이 들어간것을 볼 수 있었다.

 

 

반응형
반응형

Mysql 또는 Maria를 리눅스 서버에 설치하고 사용하다보면 테이블을 대소문자 구분하도록 설정이 되어 있는 경우가 있다.

 

해당 설정을 확인하는 방법은 아래 명령어를 통해 알 수 있다.

 

대소문자 구분상태 확인하기

show variables like 'lower_case_table_names';

Value값이 0이면 구분, 1이면 구분하지 않는 상태이다.

 

구분하지 않도록 설정하기 위해선 설정값을 변경하고 재기동이 필요하다.

 

설정 변경하기

vi, vim 등의 에디터를 통해 설정 파일을 먼저 열어준다.

vim /etc/my.cnf

 

설정 값 중에 [mysqld] 영역이 존재할텐데, 최하단에 아래의 설정값을 입력한다.

[mysqld]
lower_case_table_names=1

* [mysqld] 자체가 없다면 추가하고 입력하면 된다.

설정을 추가한다.

 

 

 

이후 서비스를 재기동하면 구분하지 않는 것을 볼 수 있다.

systemctl restart mariadb   #maria 재기동
service mysql restart       #mysql 재기동

1은 구분하지 않음

반응형
  1. 11 2022.06.09 11:16

    이거 하면 mysql이 멈추는데요?