[mysql] 암호화 & 복호화, 인코딩&디코딩

고객의 개인정보처럼 중요한 정보의 보안을 위해서 DB에 저장되는 값을 암호화해야 하는 경우가 있다. 암호화한 값을 쿼리의 입력 값으로 전달해도 되지만 mysql에서 제공하는 함수를 이용해서 암호화 작업을 할 수도 있다.

암호화 & 인코딩

#암호화
AES_ENCRYPT({암호화 할 값},{암호화 키})

#인코딩
TO_BASE64(AES_ENCRYPT({암호화 할 값},{암호화 키}))  #BASE64로 인코딩
HEX(AES_ENCRYPT({암호화 할 값},{암호화 키})) #hexadecimal string으로 인코딩

#EXAMPLE
UPDATE MEMBER
   SET MEM_NAME_ENC = TO_BASE64(AES_ENCRYPT(MEM_NAME,'testkey123'));

복호화 & 디코딩

#디코딩
FROM_BASE64({암호화된 값})
HEX({암호화된 값})

#복호화
AES_DECRYPT(FROM_BASE64({암호화된 값}), {암호화 키}) # 암호화 할 때 사용했던 키 사용

#EXAMPLE
SELECT CAST(AES_DECRYPT(FROM_BASE64(MEM_NAME_ENC), 'testkey123') AS CHAR(50)) AS MEM_NAME
  FROM MEMBER;

SELECT CONVERT(AES_DECRYPT(FROM_BASE64(MEM_NAME_ENC), 'testkey123') using UTF-8) AS MEM_NAME
  FROM MEMBER;

한글을 포함하는 경우라면 CAST나 CONVERT로 변환을 해줘야한다.

예시

## 1. hex 테스트 - 숫자
SELECT HEX(AES_ENCRYPT(123,'testkey123')) AS '암호화';
# 8DA6C1BCE3FC76CE3BA5DCF2AE0C6C71

SELECT AES_DECRYPT(unhex('8DA6C1BCE3FC76CE3BA5DCF2AE0C6C71'), 'testkey123') as '복호화';
# 123


## 2. hex 테스트 - 문자열
SELECT HEX(AES_ENCRYPT('abc-가나다','testkey123')) AS '암호화';
# 34B7FC91EA42901201A23730EDA93B8E

# 한글을 포함하는 문자열은 추가적인 변환 작업이 필요하다.
SELECT CONVERT(AES_DECRYPT(UNHEX('34B7FC91EA42901201A23730EDA93B8E'), 'testkey123') USING UTF8) AS '복호화';
SELECT CAST(AES_DECRYPT(UNHEX('34B7FC91EA42901201A23730EDA93B8E'), 'testkey123') AS CHAR(50)) AS '복호화';
# abc-가나다


## 3. BASE64 테스트 - 숫자
SELECT TO_BASE64(AES_ENCRYPT(123,'testkey123')) AS '암호화';
# jabBvOP8ds47pdzyrgxscQ==

SELECT AES_DECRYPT(FROM_BASE64('jabBvOP8ds47pdzyrgxscQ=='), 'testkey123') AS '복호화';
# 123


## 4. BASE64 테스트 - 문자열
SELECT TO_BASE64(AES_ENCRYPT('abc-가나다','testkey123')) AS '암호화';
# NLf8kepCkBIBojcw7ak7jg==

# 한글을 포함하는 문자열은 추가적인 변환 작업이 필요하다. 
SELECT CONVERT(AES_DECRYPT(FROM_BASE64('NLf8kepCkBIBojcw7ak7jg=='), 'testkey123') USING UTF8) AS '복호화';
# abc-가나다


# 5. table 조회 예시
SELECT AES_DECRYPT(FROM_BASE64(MEM_BIRTHDAY_ENC), 'testkey123') AS '생년월일'
     , CONVERT(AES_DECRYPT(FROM_BASE64(MEM_NAME_ENC), 'testkey123') USING UTF8) AS '이름1'
     , CAST(AES_DECRYPT(FROM_BASE64(MEM_ADDRESS_ENC), 'testkey123') AS CHAR(50) ) AS '이름21'
  FROM MEMBER;

 

 

암호화&복호화 

https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt

인코딩&디코딩

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64