지난 번에는 SQL Injection과 관련하여, 다각도로 알아보던 중... PreparedStatement에 대해서 알아보았습니다. 이 경우에는 where 조건절 등 query문에서 변수에 해당하는 부분에 parameter를 binding하는 부분이 전부였습니다. 하지만 다양한 경우가 존재하고... 테이블명 자체를 변수화하여 binding이 필요한 상황이 생길 수 있습니다.
따라서, 이번에는 PreparedStatement에서 테이블명을 변수로 쓰기위한 방법을 알아보겠습니다.
PreparedStatement를 사용하게 되면 setString( ) 등을 통해서 관련 값이 그 자체로 전달되는 것이 아닌 ' ' (홑따옴표)로 한번더 감싸져서 넘겨지게 됩니다. 그렇기 때문에 SQL Injection의 방어가 가능하게 되는 것 입니다. 하지만 이를 테이블명에 적용하게 되면... 결국 테이블 명에 ' ' (홑따옴표)가 붙게 되어서 Query가 불가능해 집니다.
실질적으로 발생하는 Error는 아래와 같습니다.
[Test Code]
try {
con = dataSource.getConnection();
stmt = con.prepareStatement("SELECT * FROM ? WHERE tDate = ?");
stmt.setString(1, tN);
stmt.setString(2, stocksId);
rs = stmt.executeQuery();
while(rs.next()) {
cnt++;
}
}catch (Exception e) {
System.err.println(e);
}다음과 같이 테이블명이 들어가는 부분과, 조건절에 들어가는 변수를 모두 binding하도록 구성했습니다. 이렇게 구성하고 행복한 생각에 Application을 구동하면 아래의 Error를 마주할 수 있습니다.
INFO 10140 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
INFO 10140 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
java.sql.SQLSyntaxErrorException:
You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax
to use near ''TableName' WHERE tDate = '20210129'' at line 1원인은 테이블명에 ' ' (홑따옴표)가 추가되서 SQL syntax Error가 발생하는 것 입니다. 따라서, 이를 방지하기 위해서는 몇가지 장치를 추가해야 합니다.
- SQL Query를 String의 합으로 재조합 하는 로직으로 변경
- SQL Injection을 방지하기 위하여 Table Name에 escape하는 클래스 생성 및 적용
우선... SQL Injection은 나중에 생각하고, Query에 조건절의 변수만 나두고 나머지는 String의 합으로 재조합하도록 코드를 변경해 줍니다.
[Change Code]
String query = "SELECT * FROM " + tN + " WHERE tDate = ?";
System.out.println(query);
int cnt = 0;
try {
con = dataSource.getConnection();
stmt = con.prepareStatement(query);
stmt.setString(1, stocksId);
rs = stmt.executeQuery();
while(rs.next()) {
cnt++;
}
System.out.println("Local stmt REQ size : " + cnt);
}catch (Exception e) {
System.err.println(e);
}query를 table name을 앞뒤로 String을 지정하고 재조합 해 줍니다. 그리고, setString( )의 경우 parameter에 해당하는 변수에 지정해 줍니다.
[결과]
INFO 7756 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
INFO 7756 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
Mapper REQ size : 1
SELECT * FROM [tablename] WHERE tDate = ?
Local stmt REQ size : 1아주 정상적으로 동작하는 것을 확인하였습니다. 그럼 이번에는 2번째 장치인... SQL Injection을 막기 위해서 escape 역할을 하는 Class를 생성하여 적용해 보겠습니다.
[main > commons > EscapeOther.java]
public class EscapeOther {
public static String escapeTableName(String str) {
if(str == null) {
return null;
}
return str.replace(" ", "").replace(";", "").trim();
}
}
입력값이 들어오면... null 인 경우를 제외하고, 공백과 세미콜론(;)의 경우는 없애주고 trim( )을 적용한 결과를 return해 줍니다. 물론 공백이 들어오면 다 없애주었기 때문에 trim( )은 생략해도 됩니다.
[최종 Service.java Code]
String query = "SELECT * FROM " + EscapeOther.escapeTableName(tN) + " WHERE tDate = ?";
System.out.println(query);
int cnt = 0;
try {
con = dataSource.getConnection();
stmt = con.prepareStatement(query);
stmt.setString(1, stocksId);
rs = stmt.executeQuery();
while(rs.next()) {
cnt++;
}
System.out.println("Local stmt REQ size : " + cnt);
}catch (Exception e) {
System.err.println(e);
}다음과 같이, 별다른 코드의 차이는 보이지 않지만... Table Name으로 오는 값을 해당 Class의 method에 태워서 결과를 받아 query를 완성합니다.
[결과]
입력Table 명 : "T ;abl; eN ;me;"
INFO 13724 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
INFO 13724 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
Mapper REQ size : 1
SELECT * FROM TableName WHERE tDate = ?
Local stmt REQ size : 1Table Name에 아무리 공백과 세미콜론이 들어가도 정상적으로 동작합니다. 보통 SQL Injection의 경우는 공백과 세미콜론과 작은따옴표 등이 해당됩니다. 하지만 테이블명이 대상이라고 한다면... 공백과 세미콜론이 그 역할을 수행한다고 생각됩니다.
이렇게 SQL Injection에 대비하여, 동적쿼리 / 동적테이블명을 구현하는 방법에 대해서 알아보았습니다. 사실 생각해 보면 테이블명만 동적으로 구현한다고 하면... Mybatis에 ${ } 를 사용해도 되지 않을까 싶습니다. 단, 입력값에 대해서 철저하게 Escape를 적용한다면요.
- Ayotera Lab -
댓글