SQL - REGEXP

REGEXP는 LIKE를 이용한 검색과 달리 REGULAR EXPRESSION을 이용해 검색한다.

REGEXP를 이용하면 SQL에서 정규 표현식을 활용하여 기본 연산자보다 복잡한 문자열 조건을 걸어 데이터를 검색 할 수 있다. => 쿼리를 많이 줄여 줄 것 같다.

 

하지만 정규 표현식 검색을 이용할 때 절대 사용자에게 정규식 기능을 제공해선 안된다. 각종 오류를 포함 할 수 있고, SQL INJECTION에 취약해지기 때문에, 정규표현식 검색을 개발자가 미리 정한 테두리 안에서 행해져야 한다고 하는데 아직 현업에서 일해본 경험이 없어서 무슨말인지 와닿지가 않아서 조금 찾아봤다.

 

예를 들어 내가 만든 보안이 아주 취약한 사이트에 접근하려는 사람이 로그인 시도를 하는데 ID입력을 'AAA' PW = 'BBB' 라 입력한다면, 파라미터가 넘어가면서 SELECT ID, PW FROM USER WHERE ID ='AAA' AND PW='BBB'와 같은 쿼리가 수행 될 것이다.

 

하지만, ID=AAA', PW='AAAA'' 와 같이 입력한다면, MYSQL의 ESCAPE문인 " 을 제대로 처리하지 못한 소스코드로 인해 에러페이지가 출력되게 되며, 이로 인해 관련 정보 및 사용 DB, 에러 내용들이 노출 되게 될 수도 있다.

 

이를 방지하려면, DB에 입력되는 문자열을 최종적으로 한번 더 검사해서 허용된 형식으 ㅣ문자열을 제외하고는 모두 삭제하는 코드가 필요할 것이다.

 

대표적으로 숫자, 알파벳, 알파벳과 숫자 등이 있는데 나이나 년도, 제품 가격 같은 경우는 숫자만 필요하므로 숫자를 제외한 다른 문자열은 모두 제거하는 방식등이 있을 것이다.

 

그래서 REGEXP는?

패턴	기능	사용 예시	설명
.	문자 하나	"..."	문자열의 길이가 세 글자 이상인 것을 찾음.
|	또는 (OR). |로 구분된 문자에 해당하는 문자열을 찾음.	"데이터|데이타"	‘데이터’ 또는 ‘데이타’에 해당하는 문자열을 찾음.
[]	[] 안에 나열된 패턴에 해당하는 문자열을 찾음.	"[123]d"	대상 문자열에서 ‘1d’ 또는 ‘2d’ 또는 ‘3d’인 문자열을 찾음.
^	시작하는 문자열을 찾음.	"^시작"	대상 문자열에서 ‘시작’으로 시작하는 문자열을 찾음.
$	끝나는 문자열을 찾음.	"끝$"	대상 문자열에서 ‘끝’으로 끝나는 문자열을 찾음.

 

▣ 횟수 (times)

패턴	기능	사용 예시	설명
*	0회 이상 나타나는 문자	"a*"	‘a’가 0번 이상 등장하는 문자열을 찾음. ‘b’, ‘a’, ‘aa’ 모두 해당.
+	1회 이상 나타나는 문자	"찌개+"	‘국’이 1번 이상 등장하는 문자열을 찾음. ‘김치찌개’, ‘된장찌개’, ‘부대찌개’ 모두 해당.
{m,n}	m회 이상 n회 이하 나타나는 문자	"치{1,2}"	‘치’가 1회 이상 2회 이하 등장하는 문자열을 찾음. ‘치커리’, ‘치카치카’ 모두 해당.
?	0~1회 나타나는 문자	"[가나다]?"	‘가’ 또는 ‘나’ 또는 ‘다’가 0~1회 등장하는 문자열을 찾음. ‘가지마’, ‘나라’, ‘안녕’ 모두 해당.

 

▣ 그룹 형태(group)

패턴	기능	사용 예시	설명
[A-z] 또는 [:alpha:] 또는 \a	알파벳 대문자 또는 소문자인 문자열을 찾음	"[A-z]+"	대상 문자열에서 알파벳이 한 개 이상인 문자열을 찾음
[0-9] 또는 [:digit:] 또는 \d	숫자인 문자열을 찾음	"^[0-9]+"	한 개 이상의 숫자로 시작하는 문자열을 찾음
[^문자]	괄호 안의 문자를 포함하지 않은 문자열을 찾음	"[^부정]"	‘부’ 또는 ‘정’을 포함하지 않는 문자열을 찾음. ‘부자’, ‘정말’ 모두 제외됨

 

 

# '길' 또는 '로" 또는 '그'가 포함된 문자열을 찾고 싶을 때

# 정규표현식을 사용하지 않을 때
SELECT *
FROM tbl
WHERE data like '%길%'
OR data like '%로%'
OR data like '%그%'

# 정규표현식을 사용할 때
SELECT *
FROM tbl
WHERE data REGEXP '길|로|그'


# ‘안녕’ 또는 ‘하이’로 시작하는 문자열을 찾고 싶을 때

# 정규표현식을 사용하지 않을 때 
SELECT *
FROM tbl  
WHERE data LIKE '안녕%' OR data LIKE '하이%';

# 정규표현식을 사용할 때 
SELECT *
FROM tbl
WHERE data REGEXP ('^안녕|^하이');

-----------------------------------------------

# 길이 7글자인 문자열 중 2번째 자리부터 abc를 포함하는 문자열을 찾고 싶을 때

# 정규표현식을 사용하지 않을 때
SELECT *
FROM tbl
WHERE CHAR_LENGTH(data) = 7 AND SUBSTRING(data, 2, 3) = 'abc';

# 정규표현식을 사용할 때
SELECT *
FROM tbl
WHERE data REGEXP ('^.abc...$');

-----------------------------------------------

# 텍스트와 숫자가 섞여 있는 문자열에서 숫자로만 이루어진 문자열을 찾고 싶을 때

# 정규표현식을 사용하지 않을 때
SELECT *
FROM tbl
WHERE data LIKE ??????????

# 정규표현식을 사용할 때
SELECT *
FROM tbl
WHERE data REGEXP ('^[0-9]+$'); 
-- OR data REGEXP ('^\d$') 
-- OR data REGEXP ('^[:digit:]$');