PostgreSQL LIKE에서 _를 검색했는데 밑줄 없는 행도 나온다

검색창에 _를 입력했는데 axb까지 나온다면, 현재 SQL이 입력을 LIKE 패턴으로 쓰고 있는지 확인한다. LIKE '%' || $1 || '%'에서 $1을 매개변수로 전달해도 _의 의미는 바뀌지 않는다. 입력한 글자를 그대로 포함하는 검색이라면 입력 안의 패턴 문자를 먼저 이스케이프하고, 부분 검색용 %는 마지막에 붙여야 한다.

반대로 사용자가 a_b% 같은 패턴을 직접 쓰는 기능이라면 이 처리는 검색 의미를 바꾼다. 먼저 검색창이 ‘입력 그대로 포함’인지 ‘LIKE 패턴’인지 정해야 한다.

매개변수로 넘긴 밑줄도 패턴이다

PostgreSQL 문서에서 _는 임의의 한 문자, %는 길이가 0 이상인 문자열을 뜻한다. LIKE는 문자열 전체를 대조하므로 앞뒤에 %를 붙여 부분 검색을 만든다. 따라서 _를 받아 만든 %_%는 ‘밑줄 포함’이 아니라 ‘한 문자 이상 있음’이라는 조건이다. PostgreSQL 18의 LIKE 규칙↗ (새 탭에서 열림)

다음 입력은 이 차이를 확인한다. 별도 실습용 PostgreSQL DB에 연결한 psql 한 세션에서 아래 SQL을 차례로 실행한다. 임시 테이블 생성 권한이 필요하며, 테이블과 준비된 문장은 세션을 닫으면 사라진다. 직접 비교한 환경은 폐기 가능한 PostgreSQL 18.6 컨테이너, UTF8, C collation이었다.

CREATE TEMP TABLE like_probe (
  id integer PRIMARY KEY, v text COLLATE "C" NOT NULL
);
INSERT INTO like_probe VALUES
  (1, 'a_b'), (2, 'axb'), (3, 'a%b'), (4, 'a!b'), (5, 'plain'),
  (6, 'xa_by'), (7, 'a!!b'), (8, 'a!_b'), (9, 'a!xb');

PREPARE raw_contains(text) AS
SELECT id, v FROM like_probe
WHERE v LIKE '%' || $1 || '%'
ORDER BY id;
EXECUTE raw_contains('_');
EXECUTE raw_contains('%');

두 실행 모두 id 1부터 9까지 아홉 행을 반환했다. 밑줄이 없는 axb와 plain도 들어왔다. % 입력은 최종 패턴이 %%%여서 이 표의 모든 행과 맞았다.

여기서 PREPARE의 $1은 실행 시 값을 받는 자리다. 값을 SQL 문법과 분리해 전달하는 것과, 그 값을 LIKE가 어떻게 해석하는지는 별개다. 이 비교는 서버의 PREPARE·EXECUTE로 확인했으며 특정 애플리케이션 드라이버를 시험한 결과는 아니다. 매개변수를 받는 PREPARE↗ (새 탭에서 열림)

입력만 처리하고, 검색용 %는 나중에 붙인다

이 예제는 이스케이프 문자로 !를 고정한다. ESCAPE '!'를 쓰면 !_는 실제 밑줄, !%는 실제 퍼센트, !!는 실제 느낌표를 찾는다. 절을 추가하는 것만으로 입력이 변환되지는 않는다. 기본 이스케이프 문자는 역슬래시이므로, 변환 코드와 SQL이 같은 문자를 쓰도록 명시한다. ESCAPE와 이스케이프 문자 자체의 처리↗ (새 탭에서 열림)

아래는 기존 조회에서 바꿀 부분을 담은 준비된 문장이다. 앞에서 만든 같은 임시 테이블로 _, %, !가 각각 실제 글자로 검색되는지 확인한다. replace는 안쪽부터 적용된다.

PREPARE literal_contains(text) AS
SELECT id, v FROM like_probe
WHERE v LIKE
  '%' || replace(replace(replace($1, '!', '!!'), '%', '!%'), '_', '!_') || '%'
  ESCAPE '!'
ORDER BY id;
EXECUTE literal_contains('_');
EXECUTE literal_contains('%');
EXECUTE literal_contains('!');

실제 반환된 행은 다음과 같다.

• _: id 1의 a_b, id 6의 xa_by, id 8의 a!_b.
• %: id 3의 a%b.
• !: id 4의 a!b, id 7의 a!!b, id 8의 a!_b, id 9의 a!xb.

처리 순서는 원래 입력의 ! → !!, % → !%, _ → !_, 마지막에 앞뒤 % 추가다. replace는 해당 부분 문자열을 모두 바꾸므로 여러 번 등장하는 기호에도 적용된다. 앞뒤 %는 검색 기능이 의도적으로 넣는 와일드카드이며 이스케이프 대상에 넣지 않는다. replace의 치환 범위↗ (새 탭에서 열림)

느낌표 치환을 마지막으로 옮기면 왜 틀릴까

_를 먼저 !_로 만든 뒤 !를 두 배로 바꾸면, 방금 넣은 이스케이프 문자까지 변한다. 아래 입력은 그 잘못된 순서가 만든 문자열과 실제로 선택되는 행을 확인한다.

SELECT replace(replace(replace('_', '%', '!%'), '_', '!_'), '!', '!!') AS escaped;
SELECT id, v FROM like_probe
WHERE v LIKE '%!!_%' ESCAPE '!'
ORDER BY id;

첫 결과는 !!_, 다음 결과의 id는 4, 7, 8, 9였다. !!가 실제 느낌표 하나로 해석되고 _는 다시 한 문자 와일드카드가 된다. 그래서 a!b가 선택되고 정작 a_b는 빠진다. %와 _를 치환하는 두 단계의 선후보다 새 이스케이프 문자를 넣기 전에 원래 이스케이프 문자를 처리하는 것이 중요하다.

앞뒤 %를 붙인 뒤 전체를 이스케이프하는 순서도 따로 실행했다. _ 입력의 패턴이 !%!_!%가 되어 아홉 행 중 아무것도 반환하지 않았다. 부분 검색용 와일드카드까지 실제 퍼센트로 바뀌어 %_%라는 글자 배열을 찾게 된 것이다. ESCAPE ''는 이스케이프를 끄므로 _와 %를 일반 문자로 만드는 해결책이 아니다.

실제 코드에 옮길 때는 SQL의 테이블·컬럼을 자신의 조회 대상으로 바꾸고, 사용자 값은 계속 드라이버의 매개변수로 전달한다. 위 예제처럼 SQL에서 한 번 처리하거나 애플리케이션에서 한 번 처리하도록 위치를 정한다. 양쪽에서 중복 처리하면 이미 넣은 !가 다시 변한다. 이 함수는 LIKE 패턴을 위한 것이므로 SQL 문자열 인용이나 HTML 이스케이프 용도로 사용하지 않는다.

추가 합성 행에서는 !_%가 함께 든 문자열, 따옴표와 역슬래시가 든 문자열, 일반 검색어 plain, 빈 입력도 비교했다. 위 순서의 조회 결과는 입력을 실제로 포함하는 행과 일치했다. 다만 빈 입력은 %%가 되어 빈 문자열을 포함한 모든 비NULL 행을 선택했으므로, 빈 검색어를 허용할지는 별도로 정해야 한다.

이 실험은 비NULL text와 C collation에서의 패턴 해석을 검증했다. 실제 서비스 데이터, 다른 collation, ORM의 추가 변환, 인덱스 사용 여부와 검색 성능은 확인하지 않았다. 같은 아홉 행에서도 결과가 다르면 우선 원래 입력, 최종 패턴, ESCAPE의 문자와 중복 변환 여부를 함께 확인한다.