PreparedStatement 쿼리를 사용하는 이유

• 값 변환을 자동으로 하기 위해
• 간결한 코드를 위해

Statement

Statement 객체는 Connection 클래스의 createStatement() 메소드를 호출함으로써 얻어진다.
Statement 객체를 생성하면 Statement 객체의 executeQuery() 메소드를 호출하여 SQL 쿼리를 실행시킬 수 있다.

DB가 하나의 statement를 받을 때 DB 엔진은 제일 먼저 문장을 parse 시키고 잘못된 문법을 검사한다. 그 후 DB는 statement를 실행하기 위한 가장 효율적인 방법을 탐색한다. 이때 계산상 아주 큰 비용이 들 수 있다. DB는 어떤 인덱스를 쓸 것인지, 혹은 테이블에 있는 모든 row를 다 읽어야 하는 지를 확인한다. 일단 쿼리 플랜 (query plan)이 이루어지면 이것은 DB 엔진에 의해 수행된다. 이런 데이터에 대한 접근 계획의 생성은 cpu 자원을 소모하는 일이다.

Statement Cache

데이터 베이스는 Statement Cache를 가지고 있다. 이 Cache는 Statement 자체를 키로 사용하고 데이터에 대한 접근 계획은 Cache 속에 대응되는 Statement와 함께 저장된다. 이는 데이터 베이스 엔진이 이전에 실행되었던 statement 들에 대한 플랜을 재사용하게 된다. 예를 들어 select a, b from t where c = 2 와 같은 Statement를 보냈다면 계산된 실행 플랜은 Cache에 저장된다. 만약 위와 동일한 Statement를 또 다시 보내면 DB는 지난 번에 쓴 접근 계획을 재사용할 수 있게 돼서 cpu 사용률을 줄일 수 있게 된다. 하지만 select a, b from t where c = 3 이라는 Statement를 보내면? Cache에서 발견할 수 없다 왜냐면 c = 3이 Cached에 저장된 c=2와 다르기 때문이다. 하지만 PreparedStatement를 써서 select a, b from t where c = ? 라는 문장으로 DB에 쿼리를 보냈다면 ?에 대응되는 파라미터가 달라지더라도 Cache에 이미 저장된 접근 계획을 사용할 수 있게 된다.

PreparedStatement

PreparedStatement 객체는 Connection 객체의 preparedStatement() 메소드를 통해서 생성한다.
PreparedStatement와 Statement의 가장 큰 차이점은 캐시(Cache) 사용 여부이다.
1) 쿼리 문장 분석
2) 컴파일
3) 실행
Statement를 사용하면 매번 쿼리를 수행할 때마다 1) ~ 3) 단계를 거치게 되고, PreparedStatement는 처음 한 번만 세 단계를 거친 후 캐시에 담아 재사용한다. 만약 동일한 쿼리를 반복적으로 수행한다면 PreparedStatement가 DB에 훨씬 적은 부하를 주며, 성능도 좋다.

동일한 쿼리를 특정 값만 바꾸어서 여러번 실행해야 할 때 많은 데이터를 다루기 때문에 쿼리를 정리해야할 필요가 있을 때, 인수가 많아서 쿼리를 정리해야할 필요가 있을 때 사용하면 유용하다. Statement 객체의 SQL은 실행될 때 매번 서버에서 분석되어야 하는 반면 PreparedStatement 객체는 한번 분석되면 재사용이 용이하다. (미리 컴파일 되기 때문에 쿼리의 수행속도도 Statement 객체에 비해 빠르다)

statement 쿼리를 사용해서 작은따옴표 처리를 하려면 다음과 같이 해야한다.

stmt.executeQuery("select * from member where name = '" + "p8299'911".replaceAll("'",""") + "'";

PreparedStatement의 경우에는 setString() 메서드를 호출할 때 알아서 값을 변경해주기 때문에 작은따옴표를 변환할 필요가 없다.

TIMESTAMP나 DATE, TIME 타입의 경우는 더 복잡해서 DBMS마다 날짜와 시간을 표현하는 방식이 다르기 때문에 Statement 쿼리를 이용해서 직접 값을 지정하면 DBMS마다 코드가 달라진다. 반면 PreparedStatement를 사용하면 DBMS에 상관없이 다음과 같은 동일한 코드를 사용하면 된다.

Timestamp time = new Timestamp(System.currentTimeMillis());
pstmt.setTimestamp(3, time)

또한 PreparedStatement를 사용하면 코드가 깔끔해진다.

stmt.executeQuery("update member set NAME = '" + name + "' where " + "MEMBERID = "' + id + "'";

지정할 값이 많아질 경우 따옴표가 복잡하게 얽히기 때문에 코드 작성 과정에서 오류가 발생할 수 있다.

PreparedStatement pstmt = conn.preparedStatement(
    "update member set NAME = ? where MEMBERID = ?");
pstmt.setString(1, name);
pstmt.setString(2, id);

SQL INJECTION

"select * from t where a = '" + str + "'";

str 변수의 값을 외부에서 받아서 사용한다고 할 때 str에 a' or 1 = 1# 을 집어넣는다면 어떻게 될까? (#은 mysql 주석)

select * from t where a = 'a' or 1 = 1 #'

위와 같은 쿼리가 된다. a가 ‘a’가 아니더라도 1=1은 항상 참이므로 테이블 t에 있는 모든 데이터가 출력이 된다.

str의 값을 a'; delete from t; # 로 넣으면??

select * from t where a = 'a'; delete from t; #'

jdbc의 allowMultiQueries 옵션은 ; 을 이용해서 여러 개의 구문이 실행되는 것을 허락하는 옵션이다. (기본값 false )
이 옵션을 true 로 하고 sql 쿼리를 실행해보면 t 테이블의 모든 레코드가 지워진다.

PreparedStatement는 이러한 sql injection을 막아준다
똑같이 위의 문장을 str에 넣으면

String SQL = "select * from t where a ?";PreparedStatement pstmt = conn.preparedStatement(SQL);
pstmt.setString(1, str);
ResultSet rs = pstmt.executeQuery();

‘가 escaping되어 (\’ ) sql에 적용된다.

select * from t where a = 'a\' or 1 = 1#'

a가 문자열 a\’ or 1 = 1# 과 같은 레코드만 찾아온다.
두번째 경우도 마찬가지로 ‘가 escaping 되어

select * from t where a = 'a\'; delete from t; #'

a가 문자열 a\’; delete from t; # 과 같은 레코드만 찾아오게 된다.

참고

 

Prepared Statements의 중요성

 

[Comporison] PreparedStatement 와 Statement