MySQL은 POSIX 기반 정규표현식을 지원한다. 전방탐색 등의 고오급 기능을 지원하지 않는다는 얘기. 그래서 문제 발생. 다음은 SNORT DB에서 특정 탐지로그를 발생시킨 패킷 페이로드 조회 결과.

해당 로그를 텍스트로 옮기면 빈 줄 포함, 총 5개의 줄로 구성되어 있다.

줄바꿈 문자(0D0A)가 반영된 결과.

이게 왜 문제냐면 한 줄 단위가 아니기 때문에 테이블 구조로 바꾸기가 너무 빡셈. 대충 이런 식의 작업이 가능해야 하는데,

요 모양이 됨. 하나의 레코드에 들어가야 할 로그가 여러 레코드로 쪼개지면서 필드 고유성은 개나 줘버린, 망한 데이터베이스.

일일이 수작업으로 정리해야 하나? 그렇게 정성 들여봐야 IDS 로그 누가 본다고(..) 제일 쉬운 방법은 줄바꿈 문자를 없애는 것. 다음은 replace 함수를 이용해서 줄바꿈 문자(0D0A)를 공백 문자(20)로 바꾼 결과. 줄바꿈 문자가 없으니 당연히 한 줄로 표시된다.

이쯤에서 또 문제 발생

웹 트래픽에서 헤더 영역은 '0D0A'로 줄 구분을 하는데, 데이터 영역에서는 '0A'도 줄바꿈 문자로 동작한다. 다음은 웹 응답 트래픽의 줄바꿈 문자를 공백 문자로 바꾼 결과.

텍스트로 옮기면 여전히 줄 구분이 존재한다. 또 다른 줄바꿈 문자 '0A'가 남아있기 때문.

그래서 replace 함수를 한번 더 사용해서 줄바꿈 문자 '0A'도 공백 문자로 변경.

잘 된다 싶었는데 자세히 보니 데이터가 원본과 다르다.

원인은 줄바꿈 문자가 아닌 '0A'도 바뀌었기 때문. 16진수 문자는 2개의 문자가 하나의 의미를 이루기 때문에 2개 단위로 검사해야 한다. 그런데 '0A' 문자를 무조건 바꾸다 보니 마지막 16진수 문자 '40', 'A6'이 '42', '06'으로 바뀐 것.

연속되는 16진수 '0D0A'는 희소성이 커서 문자가 나열되는 과정에서 우연의 일치가 발생할 가능성이 낮지만, '0A'는 그렇지 않다는 얘기.

이런 상황을 막으려면?

전방탐색 정규표현식을 쓰면 된다. 다음 전방탐색 표현식 '(?=(..)*$)'는 짝수 단위의 문자열이 이어지거나, 아예 이어지는 문자열이 없는 '0D0A' 또는 '0A'만을 검사한다.

그런데 이미 얘기했지만 MySQL 정규표현식은 전방탐색을 지원하지 않는다(..) 다행히 정규표현식 지원을 확장시켜주는 lib_mysqludf_preg라는 라이브러리가 있어서, 추가로 설치하면 전방탐색 사용이 가능하긴 함.

그런데 이게 문자열이 좀 길어지면 잘 안 될 때가 있더라. 그래서 잘 안 썼는데 최근 MySQL 8.0에서 정규표현식 지원이 강화됐다는 소식이 들려음.

신나게 테스트.

오류는 없는지 좀 더 써봐야 알겠지만 일단 만족스럽다. MariaDB로 넘어오라는 스택오버플로 형님들 꼬임에도 꿋꿋이 버틴 보람이 있는 듯. 참고로 윈도우 버전만 테스트해본 결과 8.0.12 버전부터 해당 기능을 사용할 수 있는 듯하다.