[DataBase] 13_PreparedStatement 객체사용

PreparedStatement 객체사용

#01. SQL Injection 공격의 이해

1) 일반적인 DATABASE 연동과정

• 사용자가 HTML Form에서 입력한 내용이 action 페이지로 전송된다.
• action 페이지에서는 입력 값을 받아 Java 언어의 SQL 문장을 구성한다.
• 웹 프로그래밍에서 데이터베이스 연동처리는 사용자가 입력한 내용을 기반으로 하여 SQL 구문을 완성하는 것이다.

String sql = "INSERT INTO department (deptno, dname, loc) VALUES (%d,'%s','%s')";

• “300, 컴퓨터과,공학관”을 입력한 경우의 조합 결과

String sql = "INSERT INTO department (deptno, dname, loc) VALUES (300,'컴퓨터과','공학관')";

• “ 300, ‘컴퓨터과’, ‘공학관’ ”을 입력한 경우의 조합결과

String sql = "INSERT INTO department (deptno, dname, loc) VALUES (300,''컴퓨터과'','공학관')";

    - 컴퓨터에 홑따옴표가 2개가 붙게 되면서 SQL구문의 형식이 맞지 않게 된다. 이런한 경우를 이용한 해킹이 SQL Injection 공격이다.

2) SQL Injection 공격의 이해

• SQL Ingection 공격이란?
  • 응용프로그램 보안 상의 허점을 의도적으로 이용해 개발자가 생각지 못한 SQL문을 실행되게 함으로써, 데이터베이스를 비정상적으로 조작하는 공격 방법이다.(웹 사이트 해킹의 가장 기초적인 방법)
• SQL Injection 공격 방어를 위한 처리
  • 사용자의 입력값을 SQL문과 조합할 때 홑따옴표(‘) 앞에 역슬레시를 붙여, SQL구문이 홑따옴표를 특수문자로 인식하도록 처리해야 한다.
    • 사용자의 모든 입력값을 String으로 생성한 뒤, eplace() 메서드를 사용하면 처리할 수 있겠지만, 모든 입력값을 이런식으로 처리하는 것은 매우 비효율적이다.

#02. PreparedStatement 객체 이해하기

• SQL Injection 공격을 방어하는 기능을 내장한 객체
  • SQL 구문 내에 사용자가 입력한 내용으로 대체할 부분을 “?”로 정의한 템플릿을 준비하고, 그 템플릿에 변수값을 치환하는 기능을 한다.
  • SQL Injection 공격에 대한 방어 처리가 자동으로 수행된다.
  • 템플릿으로 사용되는 SQL 구문은 자료형에 상관 없이 모두 홑따옴표를 사용하지 않는다.

#03. PstmtEx 프로젝트

• 프로젝트 설정하기

  • DBHelper 클래스와 JDBC 라이브러리를 복사해온다.

  • Jar 파일에 대한 JAVA Build Path를 설정한다.

예제 - INSERT 하기 위한 작업 단계 구성하기

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import study.java.helper.DBHelper;

public class Main01 {
    public static void main(String[] args) {
        /** 저장할 데이터와 SQL 구문 정의하기 */
        // 저장할 데이터
        String dname = "정보통신과";
        String loc = "공학관";

        // 'department' 테이블에 '컴퓨터정보과'를 추가하기 위한  SQL의 템플릿
        // deptno 컬럼은 AUTO_INCREMENT 이므로 INSERT에서 명시하지 않아도 된다.
        String sql = "INSERT INTO department (dname, loc) VALUES (?, ?)";

        /** DBHelper를 통한 DB 접속 처리 */
        DBHelper db = DBHelper.getInstance();
        Connection conn = db.open();

        /** SQL 구문 실행하기 */
        // SQL 문의 템플릿을 사용하여 쿼리 실행을 준비하는 객체
        PreparedStatement pstmt = null;
        // 처리 결과를 받을 객체
        ResultSet rs = null;

        // 결과값 (저장된 데이터의 수)
        int result = 0;
        // 결과값 (생성된 AUTO_INCREMENT 값)
        int autoGeneratedID = 0;

        try {
            // pstmt 객체 할당 --> AUTO_INCREMENT 조회 옵션 사용함
            // import java.sql.Statement;
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            // 템플릿에 데이터 설정
            pstmt.setString(1, dname);
            pstmt.setString(2, loc);

            // SQL문 실행하기 --> 결과 행 리턴됨
            result = pstmt.executeUpdate();

            // 생성된 AUTO_INCREMENT 값 얻기
            rs = pstmt.getGeneratedKeys();
            rs.next();
            autoGeneratedID = rs.getInt(1);
        } catch (SQLException e) {
            System.out.println("MySQL SQL Fail : " + e.getMessage());
        } finally {
            if ( rs != null) {
                // 객체 닫기
                try {
                    rs.close();
                } catch (Exception e) {}
            }
            if (pstmt != null) {
                // 객체 닫기
                try {
                    pstmt.close();
                } catch (SQLException e) {}
            }
        }
        /** 결과 출력 */
        System.out.println(result + " Record Insert");
        System.out.println("New Deptno=" + autoGeneratedID);
        /** DB 접속 해제 */
        db.close();
    }
}

• 예제 결과

=== DATABASE Connect Success ===
1 Record Insert
New Deptno=303
=== DATABASE Disconnect Success ===

예제 - DELETE하기 위한 처리 단계 구성하기

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import study.java.helper.DBHelper;

public class Main02 {
    public static void main(String[] args) {
        /** 삭제할 데이터와 SQL 구문 정의하기 */
        // 삭제할 데이터
        int deptno = 301;

        // 'department' 테이블에서 301번 학과를 삭제하기 위한 SQL 템플릿
        String sql = "DELETE FROM department WHERE deptno=?";

        /** DBHelper를 통한 DB 접속 처리 */
        DBHelper db = DBHelper.getInstance();
        Connection conn = db.open();

        /** SQL 구문 실행하기 */
        // SQL 문의 템플릿을 사용하여 쿼리 실행을 준비하는 객체
        PreparedStatement pstmt = null;

        // 결과값 (삭제된 데이터의 수)
        int result = 0;



        try {
            // pstmt 객체 할당 --> AUTO_INCREMENT 값을 받을 필요가 없기 때문에 옵션을 설정하지 않는다.
            // --> INSERT 처리와 비교해 보세요.
            pstmt = conn.prepareStatement(sql);

            // 템플릿에 데이터 설정
            pstmt.setInt(1, deptno);

            // SQL문 실행하기 --> 결과 행 리턴됨
            result = pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("MySQL SQL Fail : " + e.getMessage());
        } finally {
            if ( pstmt != null) {
                // 객체 닫기
                try {
                    pstmt.close();
                } catch (Exception e) {}
            }
            if (pstmt != null) {
                // 객체 닫기
                try {
                    pstmt.close();
                } catch (SQLException e) {}
            }
        }
        /** 결과 출력 */
        System.out.println(result + " Record Deleted");

        /** DB 접속 해제 */
        db.close();
    }
}

• 예제 결과

=== DATABASE Connect Success ===
1 Record Deleted
=== DATABASE Disconnect Success ===

• 예제 결과 - 2번실행(조건이 맞는 데이터가 없어 삭제된 행이 없는 경우)

=== DATABASE Connect Success ===
0 Record Deleted
=== DATABASE Disconnect Success ===

예제 - SELECT 구문의 처리 단계 정의하기

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import study.java.helper.DBHelper;

public class Main03 {
    public static void main(String[] args) {
        /** 조회할 검색어와 SQL 구문 정의하기 */
        // 검색어
        String keyword = "김도훈";

        // 교수 정보 조회하기
        String sql = "SELECT profno, name, userid, position, sal, hiredate, comm, deptno FROM professor WHERE name=?";

        /** DBHelper를 통한 DB 접속 처리 */
        DBHelper db = DBHelper.getInstance();
        Connection conn = db.open();

        /** SQL 구문 실행하기 */
        // SQL 문의 템플릿을 사용하여 쿼리 실행을 준비하는 객체
        PreparedStatement pstmt = null;

        // SELECT 결과를 저장할 객체
        ResultSet rs = null;



        try {
            // SQL 문을 실행할 수 있는 객체 생성
            // AUTO_INCREMENT 값을 받을 필요가 없기 때문에 옵션을 설정하지 않는다.
            // --> INSERT 처리와 비교해 보세요.
            pstmt = conn.prepareStatement(sql);

            // 템플릿에 데이터 설정
            pstmt.setString(1, keyword);

            // SQL문 실행하기 --> ResultSet 리턴됨
            rs = pstmt.executeQuery();

            // 조회 결과의 첫번째 줄로 이동
            boolean first = rs.next();

            if (first) {
                // SELECT절에 명시된 컬럼 이름을 사용하여 데이터 추출
                int profNo = rs.getInt("profno");
                String name = rs.getString("name");
                String userId = rs.getString("userid");
                String position = rs.getString("position");
                int sal = rs.getInt("sal");
                String hiredate = rs.getString("hiredate");
                int comm = rs.getInt("comm");
                int deptno = rs.getInt("deptno");
                // 결과 출력
                System.out.printf("교수번호: %d\n", profNo);
                System.out.printf("이름: %s\n", name);
                System.out.printf("아이디: %s\n", userId);
                System.out.printf("직급: %s\n", position);
                System.out.printf("급여: %d\n", sal);
                System.out.printf("입사일: %s\n", hiredate);
                System.out.printf("보직수당: %d\n", comm);
                System.out.printf("학과번호: %d\n", deptno);
            } else {
                System.out.println("조회 결과가 없습니다.");
            }

        } catch (SQLException e) {
            System.out.println("MySQL SQL Fail : " + e.getMessage());
        } finally {
            if ( pstmt != null) {
                // 객체 닫기
                try {
                    pstmt.close();
                } catch (Exception e) {}
            }
            if (pstmt != null) {
                // 객체 닫기
                try {
                    pstmt.close();
                } catch (SQLException e) {}
            }
        }

• 예제 결과

=== DATABASE Connect Success ===
교수번호: 9901
이름: 김도훈
아이디: capool
직급: 교수
급여: 500
입사일: 1982-06-12 00:00:00
보직수당: 20
학과번호: 101
=== DATABASE Disconnect Success ===