[SQL] AES 전화번호 복호화시 [blob] 문제 해결하기

먼저 비밀번호 복호화하는데 자바 진영이 아닌 SQL에서(mapper.xml) 처리를 했다. 

SQL에서 처리 했을 때의 장점은 자바에서 for문을 돌지 않아도 된다는 장점이 있다고 한다.

툴은 DBeaver를 사용했고 전화번호 복호화 작업을 하던 중

칼럼에 [blob]이 찍히는 것을 확인했고 위와 같은 문제를 알게 되어 공부하던 중 내용을 정리해보았다.

 

 

1. DTO에 저장하나? Map에 저장하나?

---

나같은 경우는 Mapper.xml에 선언한 쿼리의 리턴 값을  Map<String, Object> param 형식으로 저장했다.

key 값을 DTO에서 String 변수에 저장하면  SQL에서 나온 값에 대해 JAVA에서 형 변환을 해주어 문제가 되지 않았지만,

Map에 저장하다보니 형 변환에 문제가 발생하여 [Blob]이 발생했다. 

 

 

2. 해결방법

---

1. SQL에서 값을 가져올 때 SQL문에서 형 변환 처리를 해주거나

2. DTO로 값을 저장하거나 

 

 

CAST(REGEXP_REPLACE(AES_DECRYPT(UNHEX(m.mbl_no), #{aes_encrypt_key}), '[^0-9]', '') as varchar(100) character set utf8)  as mbl_no   /* varchar-varchar(300)-휴대폰_번호 */

 

3. 해석

---

CAST(REGEXP_REPLACE(AES_DECRYPT(UNHEX(m.mbl_no), #{aes_encrypt_key}), '[^0-9]', '') as varchar(100) character set utf8)  as mbl_no   /* varchar-varchar(300)-휴대폰_번호 */

 

 

1. CAST함수는 데이터 형식을 다른 데이터 형식으로 변환을 해준다.

2. REGEXP_REPLACE를 사용하여 전화번호 정규식으로 치환한다.

3. AES_DECRYPT 는 암호화된 문자열을 복호화한다.

4. UNHEX 16진수를 2진수화로 변환한다??

https://learn.microsoft.com/ko-kr/azure/databricks/sql/language-manual/functions/unhex

5.m.mbl_no는 필자가 사용한 칼럼 해당 코드를 사용하려면 실제 사용하는 칼럼으로 변환해야 한다.

6. #{aes_encrypt_key} 변수를 서비스 단에서 선언하여 전달받는다.

7. alias를 사용하는데 크기, character set을 선언한다.

8. 최종적으로 alias를 선언하여 칼럼 값을 저장한다.

 

 

혹시 더 좋은 방법이 있다면 댓글로 피드백 부탁드리겠습니다! 감사합니다.