MyBatis(마이바티스)에서 사용하는 WHERE 1=1 의 위험성 및 예방(Feat. 장애)

최근 회사에서 운영서버에 반영하던 도중 큰 장애가 발생할 뻔한 일이 있었습니다.

해당 프로젝트에서는 MyBatis 를 사용하고, Dynamic Query 를 같이 사용하면서 WHERE 절에 모두 if 태그를 사용하여 파라미터를 확인하고 있었습니다.(물론 Java 단에서도 파라미터 Validation Check 를 하고는 있었습니다)

발생한 원인은 반영 소스 중에 분기를 타면서 알맞게 Parameter 를 세팅하여 DB 를 조회하여 해당 데이터들을 삭제하는 로직이었는데, 실수로 if 문 바깥에 DB 를 조회하는 메소드가 선언되었고, Parameter 도 하나도 세팅 안되게 되었습니다.

그로 인해, WHERE 절에서 모두 null 로 세팅이 되면서 전체 조회가 되었고, 해당 데이터들을 삭제하는 로직을 타는 도중 다행히도 약 50만 건 이상의 데이터가 WHERE IN 절을 통해 지워지도록 되면서 시스템에서 설정한 TimeOut 이 발생하여 Transaction 에 의해 실행되지 않고 Rollback 이 되었습니다.(천만다행...)

큰 장애로는 발생하지 않았지만, 저희 팀에서는 Dynamic Query 에 대한 위험성을 더욱 생각하게 되었고, Java 단에서의 Validation Check 도, 코드리뷰도 더욱 꼼꼼하게 확인하기로 하였습니다.

해당 글에서는 WHERE 1=1 에 대한 위험성 및 예방 방법을 알아보고자 합니다.

---

1. WHERE 1=1 위험성

- Mybatis(마이바티스)에서 동적 쿼리로 아래와 같이 사용되는 코드들이 있었습니다.

--	1. WHERE 1=1 사용
SELECT
    member_id,
    name,
    city,
    street,
    zipcode
FROM
    member_table
WHERE 1=1
    <if test="member_id != null and member_id != ''">
    	and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
    	and name = #{name}
    </if>

--	2. <WHERE> 사용
SELECT
    member_id,
    name,
    city,
    street,
    zipcode
FROM
    member_table
<where>
    <if test="member_id != null and member_id != ''">
    	and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
    	and name = #{name}
    </if>
</where>

- 위의 예시에서는 위험성이 존재합니다.

- 만약 member_id, name 파라미터가 모두 null 로 전달이 된다면 해당 쿼리들은 모두 전체 조회가 됩니다.

- 전체 조회가 된다는 사실을 의도하지 않거나 예상하지 못한다면 데이터 양에 따라 어플리케이션 응답 지연 문제 등이 발생할 수 있습니다.

DELETE
FROM
    member_table
WHERE 1=1
    <if test="member_id != null and member_id != ''">
    	and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
    	and name = #{name}
    </if>

- 만약 해당 쿼리가 DELETE 혹은 UPDATE 였다면 전체 데이터가 변하는 상황이 발생합니다.

만약 회원 테이블이라면 전체 회원이 삭제가 되는 큰 장애가 발생하는 것이죠...

- 그렇기 때문에 전체 쿼리가 수행되는 WHERE 1=1 혹은 이와 비슷한 쿼리는 피하고 예방해야만 합니다.

2. WHERE 1=1 을 피하기

- 검색하면 WHERE 1=1 은 지양하자는 글을 많이 볼 수 있습니다.

- 그리고 WHERE 1=1 대신 trim 을 사용하자는 글도 확인하실 수 있습니다.

- 아래 예제를 통해서 간단하게 확인해보겠습니다.

2-1. <where> 사용하기

SELECT
    member_id,
    name,
    city,
    street,
    zipcode
FROM
    member_table
<where>
    <if test="member_id != null and member_id != ''">
    	and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
    	and name = #{name}
    </if>
</where>

- <WHERE> 태그를 사용하게 된다면 WHERE 1=1 은 사용하지 않을 수 있습니다.

- 그러나, 파라미터를 세팅하지 못하고 모두 Null 로 넘어오게 된다면 WHERE 1=1 과 동일하게 전체 조회 쿼리가 됩니다.

(좌) 전체 Null 인 경우, (우) 파라미터 세팅이 된 경우 

2-2. trim 사용

SELECT
    member_id,
    name,
    city,
    street,
    zipcode
FROM
    member_table
<trim prefix="WHERE" prefixOverrides="AND">
    <if test="member_id != null and member_id != ''">
        and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
        and name = #{name}
    </if>
</trim>

- <trim> 태그를 사용하면 실행하고자 하는 쿼리를 생성합니다.

- prefix 는 블록 안의 맨 첫 번째로 WHERE 을 추가해줍니다.

- prefixOverrides 는 블록 안에서 실행될 쿼리의 앞에 AND 를 추가해줍니다.

prefixOverrides는 만약 속성 값에 설정해둔 문자와 동일할 경우 문자를 제거해줍니다.
ex) WHERE AND member_id = member_id 방지

- 그러나, 파라미터를 세팅하지 못하고 모두 Null 로 넘어오게 된다면 WHERE 1=1 과 동일하게 전체 조회 쿼리가 됩니다.

(좌) 전체 Null 인 경우, (우) 파라미터 세팅이 된 경우 

3. 예방 방법

- <where> 태그를 사용하던, <trim> 태그를 사용하던 결국 파라미터 세팅에서 잘못된다면 전체 쿼리가 실행되는 위험이 존재합니다.

- 이 위험성에 예방하는 방법으로는 아래와 같습니다.

3-1. <trim> 태그를 AND 만 사용하기

- <trim> 태그를 사용할 때 WHERE 는 직접 작성하고, prefixOverrides 에 AND 만 작성하여 사용합니다.

SELECT
    member_id,
    name,
    city,
    street,
    zipcode
FROM
    member_table
WHERE
<trim prefixOverrides="AND">
    <if test="member_id != null and member_id != ''">
        and member_id = #{member_id}
    </if>
    <if test="name != null and name != ''">
        and name = #{name}
    </if>
</trim>

- 이렇게 작성을 하게 된다면, 기존의 trim 태그를 사용한 것과는 다르게 실행되게 됩니다.

- 만약 파라미터가 세팅되지 않고 넘어오게 된다면 WHERE 절 뒤에 쿼리가 작성되지 않으면서 BadSqlGrammarException 이 발생하게 됩니다.

- 이렇게 된다면, 전체 조회, 전체 삭제, 전체 수정 등에 대해서 예방할 수 있습니다.

3-2. java 단에서 검증

-  java 에서는 기본적으로 Validation Check 를 진행해야만 합니다.

Tip!!!
Front 에서 필수 파라미터를 넘겨받을 때 Server 에서는 보내주는 파라미터를 절대 포함해서 준다고 믿으면 안 됩니다.
무조건 받은 파라미터들을 검증하여 필수 파라미터가 없다면 오류를 발생시켜야만 합니다!!!

- @Valid, @Validated 등 다양하게 파라미터를 검증하여 사용하여 쿼리를 실행 시 필수 파라미터가 Null 이 되지 않도록 합니다.

(파라미터 검증에 대해서는 추후 포스팅할 예정입니다!)

---

참고

- https://covenant.tistory.com/253