[MSSQL/ERROR] MSSQL IN절 2100개 파라미터 제한 문제 해결: 대량 데이터 안전하고 효율적으로 조회하기

에러 발생

 

신나게 개발하던 중 마주한 에러..

2024-07-04 10:48:58 [http-nio-9091-exec-5] [WARN ] o.h.q.i.QueryParameterBindingsImpl - HHH000443: Dialect [com.~~~] limits the number of elements in an IN predicate to 2100 entries.  However, the given parameter list [memberIds] contained 3417 entries, which will likely cause failures to execute the query in the database
2024-07-04 10:48:58 [http-nio-9091-exec-5] [WARN ] o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 8003, SQLState: S0001
2024-07-04 10:48:58 [http-nio-9091-exec-5] [ERROR] o.h.e.jdbc.spi.SqlExceptionHelper - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

 

대충 읽어봐도 쿼리 IN절에 들어가는 파라미터가 너무 많아서 발생하는 에러다.

 

데이터베이스 방언(Dialect)에서 IN 절의 요소 수를 2100개로 제한하고 있다.

하지만 memberIds라는 매개변수 목록에는 3417개의 항목이 포함되어 있어 쿼리 실행에 실패한 것이다.

 

좀 더 찾아보니 SQL Server (MSSQL)에서는 쿼리 당 최대 2100개의 파라미터를 허용한다고 한다.

IN 절의 요소 수 제한은 SQL Server 자체의 제한 사항이며 이 제한을 초과하면 오류가 발생한다!

 

이 제한은 SQL Server의 내부 아키텍처로 인해 발생하며, 특정 방언 설정이나 Hibernate 설정을 통해 변경할 수 없다고 한다.

 

따라서 2100개 이상의 파라미터를 사용해야 하는 경우, 쿼리를 여러 배치로 나누어 실행해야 하는 등 다른 방법이 필요하다.

 

 

해결 방법

방법 1

memberIds 리스트를 SQL IN절에 사용할 수 있는 문자열 형식으로 변환하는 것이다.

toString() 메서드를 통해 할 수 있다.

 

/**
 * 회원들 조회 by memberIds -> MSSQL 파라미터 2100개 이상일 경우 에러 발생해 toString으로 파라미터 변환
 * @param memberIds
 * @return
 */
public List<Member> findByMemberIds(List<String> memberIds){
    TypedQuery<Member> query;

    String memberIdListToString = new ArrayList<>(memberIds) {
        @Override
        public String toString() {
            Iterator<String> it = iterator();
            if (!it.hasNext())
                return "()";

            StringBuilder sb = new StringBuilder();
            sb.append("('");
            for (; ; ) {
                String e = it.next();
                sb.append(e);
                if (!it.hasNext()) return sb.append("')").toString();
                sb.append("',").append(" '");
            }
        }
    }.toString();

    query = em.createQuery("select m" +
            " from Member m" +
            " where m.memberId in " + memberIdListToString, Member.class);

    return query
            .getResultList();
}

 

memberIdListToString 이 부분은 ‘memberIds’를 예를 들면 ‘(’김철수’, ‘이영희’, ‘박민수’)’ 형식의 문자열로 변환한다.

 

이렇게 IN절에 긴 리스트를 문자열로 변환해 직접 삽입하는 방식으로 사실 동작은 한다!

하지만 몇 가지 문제점과 위험이 있다는 점을 알아야 한다.

 

1) SQL 인젝션 위험

파라미터를 직접 문자열로 변환해 쿼리에 삽입하는 것은 SQL 인젝션의 위험이 있다.

특히 리스트에 포함된 값이 외부 입력으로부터 온다면 매우 위험할 수 있다.

 

'SQL 인젝션'에 대해 조금 더 자세히 말하자면,

공격자가 애플리케이션의 데이터베이스 쿼리에 악의적인 SQL 코드를 삽입해 데이터베이스를 조작하거나 비인가된 데이터 접근을 시도하는 보안 취약점이다.

이를 통해 공격자는 데이터베이스의 데이터 조회, 수정, 삭제 등의 작업을 수행할 수 있으며, 심지어 데이터베이스 서버에 명령을 실행할 수도 있어 위험하다.

 

2) 유지보수 어려움

코드가 복잡하고, 문제가 발생했을 때 디버깅이 어렵다.

 

 

방법 2

방법 1의 대안으로 리스트를 청크(chunk)로 나누어 여러 번 쿼리를 실행하고, 동적 SQL을 사용해 파라미터를 바인딩하는 방법을 사용할 수 있다.

 

/**
 * 회원들 조회 by memberIds
 * @param memberIds
 * @return
 */
public List<Member> findByMemberIds(List<String> memberIds) {
    List<Member> result = new ArrayList<>();
    List<List<String>> partitions = partitionList(memberIds, 2000); // MSSQL 파라미터 2100개 이상일 경우 에러 발생해 chunk size 2000개로 함

    for (List<String> partition : partitions) {
        result.addAll(findByMemberIdsChunk(partition)); // 각 chunk들 결합
    }

    return result;
}

private List<Member> findByMemberIdsChunk(List<String> memberIds) {
    TypedQuery<Member> query = em.createQuery("select m" +
            " from Member m" +
            " where m.memberId in :memberIds", Member.class);
    query.setParameter("memberIds", memberIds);
    return query.getResultList();
}

/**
 * 전체 리스트를 size만큼 chunk로 나눔
 * @param list
 * @param size
 * @return
 */
private List<List<String>> partitionList(List<String> list, int size) {
    List<List<String>> partitions = new ArrayList<>();
    for (int i = 0; i < list.size(); i += size) {
        partitions.add(list.subList(i, Math.min(i + size, list.size())));
    }
    return partitions;
}

 

우선 전체 리스트를 chunk로 나눈다.

 

partitionList 메서드를 사용해 memberIds 리스트를 2000개 이하의 chunk로 나누었는데,

2000으로 사이즈 설정을 한 것은 MSSQL 파라미터가 2100개 이상일 경우 에러가 발생하니 2100 미만으로 사이즈를 잡아야 하기 때문!

나는 안전하게 2000개로 잡았다.

 

그리고 배치 쿼리를 실행한다.

각 chunk에 대해 findByMemberIdsChunk 메서드를 호출해 DB 쿼리를 실행한다.

setParameter 메서드를 사용해 파라미터를 안전하게 바인딩한다!

 

이 방법을 사용하면 SQL 인젝션의 위험을 피하고, 코드가 더 간결하고 유지보수가 용이하다.

IN절을 안전하게 사용하고, 긴 리스트를 처리할 때는 이렇게 chunk로 나누어 처리하는 것이 더 안전하고 효율적이다!