- JDBC 프로그래밍(com.eomcs.jdbc.*)
- insert, select, update, delete 을 다루는 법
- SQL 삽입 공격을 막는 방법: Statement와 PreparedStatement
- 트랜잭션 다루기
소프트웨어 개발 보안
JDBC API의 기본 객체 - 메서드 호출
- column 번호는 0이 아닌 1부터 시작된다.
JDBC API를 사용해 데이터를 처리
→ 캡슐화
→ DAO 클래스로 정의
JDBC API: Java ↔ DB 연결해주는 애
auto commit의 특징
x_board ← insert/commit
x_board_file ← insert/commit
x_board_file ← insert/commit ← 오류 발생
x_board_file ← insert/commit
오류 발생 시, 예외가 발생하여 나머지 쿼리가 실행되지 않는다.
⇒ 이전에 입려한 데이터는 그대로 유지된다.
⇒ executeUpdate()를 실행할 때마다 자동으로 commit하기 때문이다.
commit을 하게되면, 실제 테이블에 변경(insert/update/delete) 내용을 적용한다.
→ 오류가 발생하기 전에 수행한 데이터 변경(I/U/O)작업을 그래도 유지해야하는 경우가 있고,
→ 모든 작업이 정상적으로 수행되었을 때만 commit 해야하는 경우가 있다.
예) 주문(주문할 제품 등록, 결제 등록) → 둘 다 성공해야만 주문이 완성된다.
수동 commit의 특징
x_board ← insert
x_board_file ← insert
x_board_file ← insert ← 오류 발생 → 그 전에 수행한 작업도 취소한다. ⇒ Rollback
x_board_file ← insert
x_board_file ← commit ← 모든 작업이 성공했을 때 테이블에 적용한다.
트랜잭션(Transaction)
여러 개의 데이터 변경작업(I/U/O)을 한 단위로 묶은 것
→ JDBC API(프로그래밍)에서는?
con.setAutocommit(false);
insert
insert → 예외 발생시, con.rollback();
update
.
.
.
→ 정상 수행시,
con.commit();커넥션 객체를 공유하지 않는 경우
질의할 때마다 Connection을 생성
→ connection을 쓰고 버린다.
⇒ 실행속도 느리고, 메모리를 많이 사용한다.
⇒ 트랜잭션 작업이 종료될 때, 커넥션을 끊기 때문에 커넥션을 끊으면 임시 DB에 보관된 작업 결과가 자동 삭제된다.
⇒ rollback()을 명시적으로 호출할 필요가 없다!
어차피 임시 DB 작업이 다 사라져서 아무런 작업도 실행하지 않은 상태로 돌아간다.
커넥션 객체를 공유하는 경우
문제점
T1(Transaction1)을 실행하다가 예외가 발생했을 때 명시적으로 rollback을 수행하지 않으면,
T2에서 commit할 때 기존에 남아있던 T1작업까지 commit되는 문제가 발생한다!
⇒ connection을 공유하는 경우 주의하여야 한다!
해결책
예외가 발생하면 명시적으로 rollback하라!
SQL 삽입 공격
- 입력 문자열에 SQL 명령을 삽입하여 프로그램의 의도와 다르게 데이터를 조작하는 행위.
- 사용자가 입력한 값을 가지고 SQL 문장을 만들 때 이런 문제가 발생한다.
예;
try (Connection con = DriverManager.getConnection( //
"jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
Statement stmt = con.createStatement()) {
// => 예를 들어 이 예제를 실행할 때 다음과 같이 입력해 본다면 의도와 다른 결과를 얻게 된다!
// 번호? 1
// 제목? okok
// 내용? test', view_count = 300, created_date = '2019-3-3
int count = stmt.executeUpdate(
"update x_board set title = '" + title +
"', contents = '" + contents +
"' where board_id = " + no);
// 위에서 사용자가 입력한 값을 가지고 SQL 문장을 만들면 다음과 같다.
//
// update x_board set title = 'okok',
// contents = 'test', view_count = 300, created_date = '2019-3-3'
// where board_id = 1
//
if (count == 0) {
System.out.println("해당 번호의 게시물이 존재하지 않습니다.");
} else {
System.out.println("변경하였습니다.");
}
}Statement vs PreparedStatement
1. SQL 문장의 간결함
[Statement]
값을 가지고 문자열로 직접 SQL 문을 만들기 때문에 작성하거나 읽기 힘들다.
[PreparedStatement]
SQL 문장과 값이 분리되어 있기 때문에 작성하거나 읽기 쉽다.
2. SQL 삽입 공격
[Statement]
사용자가 입력한 값을 가지고 SQL 문장을 만들기 때문에 해킹되기 쉽다.
[PreparedStatement]
SQL 문장과 값이 분리되어 다뤄지기 때문에 해킹할 수 없다.
3. 바이너리 데이터 다루기
[Statement]
문자열로 SQL 문장을 만들기 때문에 바이너리 타입의 컬럼 값을 설정할 수 없다.
[PreparedStatement]
setXxx() 메서드를 호출하여 값을 설정하기 때문에 바이너리 타입의 컬럼 값을 설정할 수 있다.
4. 실행 속도
[Statement]
- executeUpdate()를 실행할 때 SQL 문을 파라미터로 전달한다.
- 호출될 때마다 SQL 문법을 분석하기 때문에 반복 실행하는 경우 SQL 문법도 반복 분석하므로 실행 속도가 느리다.
[PreparedStatement]
- 미리 SQL 문을 작성한 다음 DBMS 프로토콜에 맞게 파싱해 놓은 후, executeUpdate() 호출한다.
- 따라서 executeUpdate()를 호출할 때 마다 SQL 문법을 분석하기 않으므로 반복해서 실행하는 경우,
Statement 보다 실행 속도가 빠르다.
prepareStatement 예시
try (Connection con = DriverManager.getConnection(
"jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
// 값이 들어갈 자리에 in-parameter(?)를 지정한다.
// => 데이터 타입에 상관없이 ?를 넣는다.
PreparedStatement stmt =
con.prepareStatement("insert into x_board(title,contents) values(?,?)");) {
// in-parameter에 값을 설정한다.
// => 설정하는 순서는 상관없다. 하지만 유지보수를 위해 순서대로 나열해야한다!
stmt.setString(1, title);
stmt.setString(2, contents);
// 실행할 때는 SQL문을 파라미터로 넘길 필요가 없다.
int count = stmt.executeUpdate();
System.out.printf("%d 개 입력 성공!", count);
}ResultSet?
pk를 꺼내오는 도구: ResultSet
- 결과가 아니다! 서버에서 결과를 가져오는 일을 할 객체이다.
- 즉 서버의 select 실행 결과를 가져올 때 사용하는 도구이다.
RETURN_GENERATED_KEYS
입력 후 PK 값을 리턴 받고 싶다면,
PreparedStatement 객체를 얻을 때 다음과 같은 옵션을 지정하라!
⇒ prepareStatement(sql, 자동생성된 PK 값 리턴 여부)
getGeneratedKeys().getInt(1) 은 PK 값을 리턴한다.
try (Connection con = DriverManager.getConnection(
"jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
// 입력 후 PK 값을 리턴 받고 싶다면,
// PreparedStatement 객체를 얻을 때 다음과 같은 옵션을 지정하라!
// => prepareStatement(sql, 자동생성된 PK 값 리턴 여부)
//
PreparedStatement stmt = con.prepareStatement(
"insert into x_board(title,contents) values(?,?)",
Statement.RETURN_GENERATED_KEYS);) { // 자동으로 생성된 프라이머리 키 값이 있다면, 나는 리턴받겠다.
stmt.setString(1, title);
stmt.setString(2, contents);
int count = stmt.executeUpdate();
System.out.printf("%d 개 입력 성공!\n", count);
// insert 수행 후 자동 생성된 PK 값은 따로 요구해야 한다.
try (ResultSet rs = stmt.getGeneratedKeys()) {
// insert를 한 개만 했기 때문에 PK도 한 개만 생성되었다.
// 따라서 ResultSet에 대해 여러 번 반복을 할 필요가 없다.
rs.next();
// 자동 생성된 PK 값을 꺼낼 때는 컬럼 이름이나 PK 컬럼의 인덱스로 꺼낸다.
// int no = rs.getInt("board_id"); // MariaDB JDBC Driver에서는 오류 발생!
int no2 = rs.getInt(1);
System.out.printf("입력된 게시글 번호: %d\n", no2);
}
}