MySQL 사용자 정의 함수 > DB 암복호화

1. 암호화

CREATE DEFINER=`poa_dev`@`%` FUNCTION `poa_dev`.`ENC`(input TEXT, key_str TEXT) RETURNS text CHARSET latin1 COLLATE latin1_swedish_ci
    DETERMINISTIC
BEGIN
    DECLARE encrypted_data BLOB;
    
    -- 입력 데이터의 문자셋을 UTF-8로 설정
    SET input = CONVERT(input USING utf8);

    -- AES_ENCRYPT로 암호화 (BLOB 타입으로 암호화)
    SET encrypted_data = AES_ENCRYPT(input, key_str);

    -- 암호화된 데이터를 Base64로 인코딩하여 반환
    RETURN TO_BASE64(encrypted_data);
END

자세한 설명

1. 함수 정의 구문
   • 함수 이름: ENC
   • 인자: input(TEXT), key_str(TEXT)
   • 리턴 타입: TEXT (문자셋은 latin1, 정렬은 latin1_swedish_ci)
   • DETERMINISTIC: 같은 입력에 대해서 항상 같은 결과를 반환하는 함수임을 의미합니다. (즉, 외부 상태나 시간 등에 따라 결과가 바뀌지 않는 함수로 선언)
2. 함수 내용
   • DECLARE encrypted_data BLOB;
     • 암호화 수행 후 결과를 임시로 저장할 변수를 선언(BLOB 타입).
   • SET input = CONVERT(input USING utf8);
     • 들어온 input 텍스트를 UTF-8 문자셋으로 변환.
     • MySQL에서 AES_ENCRYPT를 사용할 때, 입력 데이터를 올바른 인코딩(예: UTF-8)으로 맞춰 주는 것이 중요합니다.
   • AES_ENCRYPT(input, key_str);
     • input(평문)을 key_str(암호화 키)로 AES 알고리즘을 이용하여 암호화(결과값은 BLOB).
   • RETURN TO_BASE64(encrypted_data);
     • AES_ENCRYPT 결과(이진 데이터)를 Base64로 인코딩하여 TEXT 형태로 반환.
3. 결과적으로
   • 이 함수는 문자열과 키를 입력으로 받아, input을 AES 알고리즘으로 암호화한 뒤, 그 암호화 결과를 Base64 문자열로 돌려줍니다.
   • 즉, 암호화 + 인코딩 과정을 하나의 함수로 손쉽게 호출할 수 있도록 한 것입니다.

---

2. 복호화

CREATE DEFINER=`poa_dev`@`%` FUNCTION `poa_dev`.`DECRYPT`(input TEXT, key_str TEXT) RETURNS text CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    DETERMINISTIC
BEGIN
    DECLARE decrypted_data BLOB;

    -- AES_DECRYPT 함수로 복호화된 데이터를 BLOB 형식으로 얻기
    SET decrypted_data = AES_DECRYPT(FROM_BASE64(input), key_str);

    -- BLOB 데이터를 UTF-8로 변환하여 반환 (UTF-8로 변환하여 한글 등 멀티바이트 처리)
    RETURN CONVERT(decrypted_data USING utf8mb4);
END;

자세한 설명

1. 함수 정의 구문
   • 함수 이름: DECRYPT
   • 인자: input(TEXT), key_str(TEXT)
   • 리턴 타입: TEXT (문자셋은 utf8mb4, 정렬은 utf8mb4_unicode_ci)
   • DETERMINISTIC: 동일한 입력에 대해 언제나 동일 결과가 반환되는 함수임을 명시합니다.
2. 함수 본문
   • AES_DECRYPT 함수에, Base64로 인코딩된 암호문(input)을 FROM_BASE64 함수로 디코딩한 결과와 암호화 키(key_str)를 인자로 넘겨, 복호화 결과를 decrypted_data라는 BLOB 변수에 담습니다.
   • 복호화된 이진 데이터를 utf8mb4 문자셋으로 변환( CONVERT(decrypted_data USING utf8mb4) )하여 반환합니다.
   • 이렇게 하면 한글, 이모지 등 멀티바이트 문자를 비롯한 다양한 문자셋을 제대로 처리할 수 있습니다.
3. 종합
   • 이 함수 DECRYPT는 Base64로 인코딩된 AES 암호문을 넘겨받아, 이를 디코딩( FROM_BASE64 )하고 AES_DECRYPT를 통해 평문을 추출한 뒤, 최종적으로 utf8mb4로 인코딩된 텍스트로 반환해 줍니다.