주민등록번호 검출하기(SQL, Python)
자유입력 텍스트 안에 섞인 개인정보(주민번호, 전화번호, 이메일)를 찾아내야 할 일이 생겼다. 근데 문제가 하나 있었다. 해당 DB 권한이 없어서 PL/SQL도 힘들고, 파이썬도 못 쓴다!!
쿼리로만 개인정보 검출하기
1. 전화번호, 이메일: SQL도 나쁘지 않음
어차피 데이터가 DB에 있으니 대용량 데이터를 1차로 거르는 건 SQL이 편하긴 했다. 전화번호나 이메일은 패턴이 단순해서 어렵지 않았다.
SELECT *
FROM 테이블명
WHERE REGEXP_LIKE(텍스트원본, '01[016789][ -]?[0-9]{3,4}[ -]?[0-9]{4}')
OR REGEXP_LIKE(텍스트원본, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}');2. 주민등록번호: 쿼리가 지저분해짐
형식에 맞는지만 확인
SELECT *
FROM 테이블명
WHERE REGEXP_LIKE(텍스트원본, '[0-9]{6}[- _]?[1-8][0-9]{6}');한 텍스트에 여러개 주민번호가 있을 경우
WITH 원본데이터 AS (
SELECT ROW_NUMBER() OVER (ORDER BY ROWID) AS 순번
, 텍스트원본
FROM 테이블명
),
주민등록번호후보 AS (
SELECT 순번
, REGEXP_SUBSTR(텍스트원본,'[0-9]{6}[- _]?[1-8][0-9]6}',1,LEVEL) AS 주민등록번호원본
FROM 원본데이터
CONNECT BY PRIOR 순번 = 순번
AND PRIOR SYS_GUID() IS NOT NULL
AND LEVEL <= REGEXP_COUNT(텍스트원본, '[0-9]{6}[- _]?[1-8][0-9]{6}')
)
SELECT 순번
, 주민등록번호원본
, REGEXP_REPLACE(주민등록번호원본, '[-_[:space:]]', '') AS 주민등록번호정리값
FROM 주민등록번호후보
WHERE 주민등록번호원본 IS NOT NULL;여기에 진짜 주민번호인지 확인하려면 유효성 검사(체크섬)을 타야 한다.
이 체크섬 로직을 SQL로 하면
WITH 원본데이터 AS (
SELECT ROW_NUMBER() OVER (ORDER BY ROWID) AS 순번
, 텍스트원본
FROM 테이블명
),
주민등록번호후보 AS (
SELECT 순번
, REGEXP_SUBSTR(
텍스트원본,
'[0-9]{6}[- _]?[1-8][0-9]{6}',
1,
LEVEL
) AS 주민등록번호원본
FROM 원본데이터
CONNECT BY PRIOR 순번 = 순번
AND PRIOR SYS_GUID() IS NOT NULL
AND LEVEL <= REGEXP_COUNT(텍스트원본, '[0-9]{6}[- _]?[1-8][0-9]{6}')
),
주민등록번호정리 AS (
SELECT 순번
, 주민등록번호원본
, REGEXP_REPLACE(주민등록번호원본, '[-_[:space:]]', '') AS 주민등록번호정리값
FROM 주민등록번호후보
WHERE 주민등록번호원본 IS NOT NULL
),
주민등록번호검증 AS (
SELECT 순번
, 주민등록번호원본
, 주민등록번호정리값
, CASE
WHEN REGEXP_LIKE(주민등록번호정리값, '^[0-9]{13}$')
AND SUBSTR(주민등록번호정리값, 7, 1) IN ('1','2','3','4','5','6','7','8')
AND MOD(
11 - MOD(
TO_NUMBER(SUBSTR(주민등록번호정리값, 1, 1)) * 2
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 2, 1)) * 3
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 3, 1)) * 4
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 4, 1)) * 5
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 5, 1)) * 6
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 6, 1)) * 7
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 7, 1)) * 8
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 8, 1)) * 9
+ TO_NUMBER(SUBSTR(주민등록번호정리값, 9, 1)) * 2
+ TO_NUMBER(SUBSTR(주민등록번호정리값,10, 1)) * 3
+ TO_NUMBER(SUBSTR(주민등록번호정리값,11, 1)) * 4
+ TO_NUMBER(SUBSTR(주민등록번호정리값,12, 1)) * 5
, 11)
, 10) = TO_NUMBER(SUBSTR(주민등록번호정리값, 13, 1))
THEN 'Y'
ELSE 'N'
END AS 주민등록번호_유효여부
FROM 주민등록번호정리
)
SELECT 순번
, 주민등록번호원본
, 주민등록번호정리값
FROM 주민등록번호검증
WHERE 주민등록번호_유효여부 = 'Y'
;이걸 쿼리에 넣으니 동작은 하는데... 속도도 느리고 유지보수도 힘들다. 그리고 2020년 부터 주민번호 생성규칙이 바뀌어서, 이것까지 정확히 검출하려면 쿼리가 더 복잡해진다. (법인번호도 섞여있어 법인번호 구분 규칙도 들어있었고... 너무 복잡해🤯)
PL/SQL 사용해서 SQL로 검증하기
함수 만들기
CREATE OR REPLACE FUNCTION is_valid_rrn(p_rrn IN VARCHAR2)
RETURN CHAR
IS
l_rrn VARCHAR2(20);
l_total NUMBER := 0;
l_check_digit NUMBER;
l_gender_code CHAR(1);
l_yy NUMBER;
l_mm NUMBER;
l_dd NUMBER;
l_year NUMBER;
BEGIN
-- 하이픈, 공백, 언더바 제거
l_rrn := REGEXP_REPLACE(p_rrn, '[-_[:space:]]', '');
-- 숫자 13자리 아니면 제외
IF NOT REGEXP_LIKE(l_rrn, '^[0-9]{13}$') THEN
RETURN 'N';
END IF;
l_gender_code := SUBSTR(l_rrn, 7, 1);
l_yy := TO_NUMBER(SUBSTR(l_rrn, 1, 2));
l_mm := TO_NUMBER(SUBSTR(l_rrn, 3, 2));
l_dd := TO_NUMBER(SUBSTR(l_rrn, 5, 2));
-- 출생연도 계산
IF l_gender_code IN ('1','2','5','6') THEN
l_year := 1900 + l_yy;
ELSIF l_gender_code IN ('3','4','7','8') THEN
l_year := 2000 + l_yy;
ELSE
RETURN 'N';
END IF;
-- 날짜 유효성 검사
BEGIN
DECLARE
v_date DATE;
BEGIN
v_date := TO_DATE(l_year || LPAD(l_mm, 2, '0') || LPAD(l_dd, 2, '0'), 'YYYYMMDD');
END;
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END;
-- 체크섬 계산
FOR i IN 1 .. 12 LOOP
l_total := l_total
+ TO_NUMBER(SUBSTR(l_rrn, i, 1))
* TO_NUMBER(SUBSTR('234567892345', i, 1));
END LOOP;
l_check_digit := MOD(11 - MOD(l_total, 11), 10);
IF l_check_digit = TO_NUMBER(SUBSTR(l_rrn, 13, 1)) THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END;
/
쿼리
WITH 주민등록번호추출 AS (
SELECT REGEXP_SUBSTR(텍스트원본, '[0-9]{6}[- _]?[1-8][0-9]{6}') AS 주민등록번호의심
FROM 테이블명
WHERE REGEXP_LIKE(텍스트원본, '[0-9]{6}[- _]?[1-8][0-9]{6}')
)
SELECT 주민등록번호의심
FROM 주민등록번호추출
WHERE is_valid_rrn(주민등록번호의심) = 'Y';파이썬으로 개인정보 검출하기
파이썬으로 짰으면 어땠을까?
import re
from datetime import datetime
# 1. 주민번호 유효성 검증
def is_valid_rrn(rrn: str) -> bool:
# 하이픈, 언더바, 스페이스바 제거
rrn = re.sub(r"[-_\s]", "", rrn)
# 13자리 숫자 검증
if not re.fullmatch(r"\d{13}", rrn):
return False
yy = int(rrn[0:2])
mm = int(rrn[2:4])
dd = int(rrn[4:6])
gender_code = rrn[6]
# 4자리 연도 확정 (1900년대생 vs 2000년대생)
if gender_code in "1256":
birth_year = 1900 + yy
elif gender_code in "3478":
birth_year = 2000 + yy
else:
return False
# ==========================================
# 주민번호 아닐 확률 높은 케이스 제외
# ==========================================
# 아직 다가오지 않은 년도 제외
current_year = datetime.now().year
if birth_year > current_year:
return False
# 달력에 없는 날짜 제외
try:
# 정상 날짜면 통과
datetime(birth_year, mm, dd)
except ValueError:
# 13월 1일, 2월 30일 같은 날짜는 제외
return False
# 2020년 10월 이후 출생자 확인
after_2020_10 = False
if gender_code in "3478":
if yy > 20 or (yy == 20 and mm >= 10):
after_2020_10 = True
# 20년 10월 이후 출생자 체크섬 연산 제외
if after_2020_10:
return True
# 2020년 9월 이전 출생자 체크섬 연산 수행
weights = [2, 3, 4, 5, 6, 7, 8, 9, 2, 3, 4, 5]
total = sum(int(rrn[i]) * weights[i] for i in range(12))
check_digit = (11 - (total % 11)) % 10
return check_digit == int(rrn[12])
# 2. 텍스트에서 주민번호 추출
def find_rrn(text: str) -> list[str]:
candidates = re.findall(r"\b\d{6}[-\s_]*[1-8]\d{6}\b", text)
return [c for c in candidates if is_valid_rrn(c)]
# ==========================================
# 3. 테스트
# ==========================================
test_text = """
2026년 3월 20일 3시에 전화오심.
매우 화남.
주민번호 변경 부탁. 900101-1234568
900101 1234568
(900101 1234568)
--- 오류 데이터 테스트 ---
270101-3123456 (2027년생)
900230-1234568 (2월 30일)
"""
print("[점검 대상 텍스트]")
print(test_text)
print("-" * 40)
result = find_rrn(test_text)
print("[검출된 주민번호]")
print(result)결과
[점검 대상 텍스트]
2026년 3월 20일 3시에 전화오심.
매우 화남.
주민번호 변경 부탁. 900101-1234568
900101 1234568
(900101 1234568)
--- 오류 데이터 테스트 ---
270101-3123456 (2027년생)
900230-1234568 (2월 30일)
----------------------------------------
[최종 검출 주민번호]
['900101-1234568', '900101 1234568', '900101 1234568']
** Process exited - Return Code: 0 **결론
단순 필터링은 SQL이 편하지만, 대용량 데이터 읽으면서 복잡한 검증을 구현할 때는 파이썬이 좋은 것 같다. 더 나아가 사후검증만 진행하는 것 보다 입력단계 검증 → 사후검증 같이 2단계에 거쳐서 검증하는 방식이 가장 좋을 것 같다. 현 상황에서는 입력단계 검증과 파이썬이 불가하기 때문에 불가피하게 SQL로 검증을 하였지만, 추후 가능하다면 입력단계에서 파이썬으로 검증 + 사후 SQL 검증으로 개인정보 검증을 구현해보고 싶다.
참고
https://m.blog.naver.com/PostView.naver?blogId=mumasa&logNo=222102707153&categoryNo=1&proxyReferer=
Data Architect🌱