LIKE Wildcard 검색을 막아보자(MySQL, MyBatis)

크리스마스 기념으로 글을 발행하려고 한다. 다들 크리스마스라고 어디 나가지말고 집에서 개발 공부를 해보자!! (경험치 2배 이벤트)

 

이전 글에서 SQL Injection에 대해 자세하게 알아보았다.

SQL Injection (MyBatis, SQL LIKE 절)

다양한 공격 방법 중, 실제 프로젝트에 적용한 LIKE wildcard 공격을 막을 수 있는 방법을 소개하고자 한다.

하지만, 이전 글을 보지 않고 바로 해당 게시물에 진입한 독자들도 있으니, 간단하게 LIKE Injection이란 무엇인지 짚고 넘어가자.

---

LIKE Injection

 

대부분 LIKE 조건을 아래와 같이 사용하고 있을 것이다.

SELECT id, name
  FROM members
 WHERE name LIKE '%킹%'

 

만약 사용자가 '킹' 대신 '%'를 입력했다면 쿼리는 어떻게 발행될까?

SELECT id, name
  FROM members
 WHERE name LIKE '%%%'

사용자는 '%'가 포함된 모든 문자열을 결과로 원했지만, LIKE '%%%' 의 결과는 전체 검색과 동일하게 수행된다. 이처럼 의도치 않게 전체 검색을 노출하는 것은 보안상 문제가 발생할 수 있고 데이터베이스에 부하를 줄 수 있다.

 

그래서 LIKE Injection을 방어하는 방법을 소개하고 어떻게 서비스에 녹였는지 소개하겠다.

---

ESCAPE의 활용

 

MySQL에서는 탈출의 의미를 지닌 ESCAPE를 제공한다. SQL에서는 일부 문자가 특수한 의미를 가지기 때문에 이를 문자로 처리할 필요가 있다. 그래서 ESCAPE를 특수문자 앞에 선언하여 특수문자의 역할에서 탈출시켜 문자로 인식하게 해준다.

 

참고로 MySQL에서는 DEFAULT ESCAPE가 역슬래시(\)로 지정되어있다.

SELECT id, name
  FROM members
 WHERE name LIKE '%\%%'
 
/* 참고) 백슬래쉬 말고 ESCAPE 지정하는 방법 */
SELECT id, name
  FROM members
 WHERE name LIKE '%!%%' ESCAPE '!'

ESCAPE 지정 문자(\, !) 뒤의 %는 문자로 인식하므로 %를 포함한 모든 문자열이 검색될 것이다.

 

그런데 만약 '%__%_%' 를 검색하고 싶을 때, 정확한 결과를 얻기 위해서는

SELECT id, name
  FROM members
 WHERE name LIKE '%\%\_\_\%\_\%%'

요런식의 형태가 되어야 한다. 이를 sql 구문에서 처리하기에는 한계가 있다. 그래서 서비스 로직에서 해결하려고 한다.

 

 

1. Apache StringEscapeUtils

누군가는 나와같이 고민하여 라이브러리를 만들었을 것이라고 생각했고 외부 라이브러리를 찾아보았다. 

검색 중, apache.commons.lang3와 apache.commons.text에서 제공하는 StringEscapeUtils를 알게 되었다.

그러나 대부분이 deprecated되었다. 이유에 대해 찾아보니, 모든 이스케이프 작업(html, xml, java, sql ...)을 하나의 클래스에서 처리하다보니 유틸클래스의 역할이 명확하지 않다라고 판단했기 때문이다.

 

2. Custom Util Class

외부 라이브러리를 활용할 수 없으니, 직접 util class를 개발해야한다. util class를 개발하기 전, 3가지 규칙을 지정했다.

1️⃣ 다양한 ESCAPE 문자열 처리 가능 
ESCAPE로 다양한 문자열을 지정할 수 있기 때문에 범용성을 고려해야 한다.

2️⃣ UNESCAPE는 과감하게 패스
SQL 조회할 때만 사용하기 때문에, 굳이 unescaping하는 과정은 필요없다고 생각했다.

3️⃣ ESCAPE로 지정된 문자도 막아야 함
예를 들어 ESCAPE 문자열이 '!'라고 가정해보자. 사용자가 '!!' 를 입력하게 되면, 
!(ESCAPAE역할)!(문자역할) 형태가 되어 '!' 검색과 동일하게 수행될 것이다.

정확한 결과를 얻기 위해서 '!!' 이렇게 입력한 경우 '!!!!' 형태가 되어야 한다.
그렇기에, ESCAPE 문자 앞에도 ESCAPE를 지정해주어야 한다.

 

아래는 실제 프로젝트에 구현한 util class이다.

public class SqlEscapeUtil {
    private static final String DEFAULT_ESCAPE = "\\";
    private static final char[] WILDCARD_CHARACTERS = {'%', '_'};

    private SqlEscapeUtil() {
    }

    public static String escape(String param) {
        return convertEscapeString(param, DEFAULT_ESCAPE);
    }

    public static String escape(String param, String escape) {
        return convertEscapeString(param, escape);
    }

    private static String convertEscapeString(String param, String escape) {
        if (param == null || param.isEmpty()) {
            return param;
        }
        // escape 문자열이 있는 경우, 앞에 escape 문자를 하나 더 붙여줘서 문자열로 인식하도록 함
        String includeEscapeParam = param.replace(escape, escape + escape);
        // wildcard 문자열 escape
        StringBuilder escapeString = new StringBuilder();
        for (int i = 0; i < includeEscapeParam.length(); i++) {
            char ch = includeEscapeParam.charAt(i);
            if (WILDCARD_CHARACTERS[0] == ch || WILDCARD_CHARACTERS[1] == ch) {
                escapeString.append(escape);
            }
            escapeString.append(ch);
        }
        return escapeString.toString();
    }
}

 

해당 util class를 서비스 로직에서 사용하는 것을 추천하기는 하지만, MyBatis 환경에서 사용할 수 있는 방법도 소개 하겠다.

• 예제 1) Service layer에서 사용

public ProductsResponse getProducts(String productName){
    String escapingName = SqlEscapeUtil.escape(productName);
    List<ProductModel> products = productsMapper.getProductsByProductName(escapingName);
    
    return new ProductsResponse(products.size(), products);
}

• 예제 2) xml mapper에서 사용 (저얼대 권장하지 않음)

<select id="getProductsByProductName" 
        resultType="com.example.demo.mapper.model.ProductModel">
    <bind name="escapingProductName" 
          value="com.example.demo.utils.SqlEscapeUtil@escape(productName)"/>
    SELECT product_id
         , product_name
         , product_code
         , create_date
         , update_date
         , quantity
      FROM products
     WHERE product_status = 1
       AND product_name LIKE CONCAT('%', #{escapingProductName}, '%');
</select>

사실 xml에서 사용하는 방법을 권장하진 않는다. 그 이유는 유지보수와 관련이 있는데, 일단 util 함수를 서비스에서도 사용하고 xml에서도 사용한다면 영향범위 파악이 어렵다. 

 

하지만 프로젝트에서는 xml에 적용하는 것으로 결정되었는데, 서비스가 어느정도 개발이 된 시점이다보니 최소한의 수정을 원했다. 그래서 xml 파일에 bind 구문만 추가해서 사용하게 되었다. 그래도 util class를 만들 수 있게 해준 것만해도 감사하고 행복하다.

---

Sql Escape Util Test

 

Escape Util을 이제 테스트해보려고 한다. 테스트 코드를 작성해서 하는 방법도 있지만, 좀 더 독자들에게 시각적으로 보여주기 위해 간단한 검색 페이지를 만들어 테스트를 해보았다.

• case1) 전체검색

 

• case2) wildcard 검색 (%, _)

• case3) ESCAPE(백슬래쉬)문자 검색

 

• 시연영상

 

• 참고) 전체코드

GitHub - myeongju-kim/blog_like_injection

---

성능에 이슈는 없을까?

 

많은 개발자가 그렇듯 성능을 생각하지 않을 수 없다. 필자도 'escape 문자를 추가해서 성능이 안좋아지면 어떡하지..?' 하며 식은땀을 흘렸다. 성능 이슈는 상황에 따라 다르지만 현재 진행한 프로젝트에서는 like 검색을 '%name%' 형태로 쓰고있다.

EXPLAIN
 SELECT * 
   FROM products 
  WHERE product_name LIKE '%킹명주%'

%가 앞 뒤로 붙어있기 때문에 range scan을 하지않고 모든 row를 탐색(full scan)하고 있다.

그렇기에 사실상 LIKE '%\킹\명\주%'를 한다하더라도 모든 row를 탐색하는건 매한가지이다 ㅎㅎ... 성능을 고려한다면 차라리 full scan이 아니라 range scan으로 어떻게 전환할지 고민하는 것이 정신건강에 해롭다.

---

결론

 

프로젝트를 진행하면서 발견했던 LIKE Injection 공격을 ESCAPE Util Class를 개발하여 극복해보았다. 회사 프로젝트를 진행하면서 생각보다 챌린지할 요소들이 많았다.

 

앞으로도 고민했던 부분이나 공부했던 부분을 블로그에 올려 지식공유에 힘쓰고 싶다..!