[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를 선언하여 칼럼 값을 저장한다.
혹시 더 좋은 방법이 있다면 댓글로 피드백 부탁드리겠습니다! 감사합니다.