[JAVA] JDBC - util생성, 활용

SQL_Injection

Statement를 사용한다면 SQL_Injection 해킹당할 수 있는 위험이 있다

ex. SQL문이 select * from bankinfo where bank_no='' or 1 = 1 --' 이 입력될 시
      1=1가 모두 참이기 때문에 전체 정보가 나오게 되는 문제점이 있음

+ union과 select문을 이용해 다른 데이터도 가져올 수 있다는 위험성이 존재

select * from bankinfo where bank_no='' union select mem_id, mem_pass, mem_name, null from member --'

 

그러므로 PreparedStatment객체를 사용해 출력하도록 한다

---

Statement 객체 사용

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/*
 	Statement객체를 사용하면 SQL_Injection 해킹을 당할 수 있는 예제
 	1. 정상 검색 ==> 계좌번호 입력 후 검색
 	2. 전체 계좌 정보 해킹 ==> ( ' or 1=1 -- ) 입력
 	3. 전체 회원 정보 해킹 ==> ( ' union select문 -- ) 입력
 	
 */
public class JdbcTest06 {

	
	public static void main(String[] args) {
		Scanner scan = new Scanner(System.in);
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","JIN96","java");
			
			System.out.println("계좌 정보 검색하기");
			
			System.out.print("검색할 계좌번호 입력 >> ");
			String bankNo = scan.nextLine();
			
			// Statement 객체를 이용한 경우
			String sql = "select * from bankinfo where bank_no='"+bankNo+"'";
			System.out.println("sql => "+sql);
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			System.out.println("  == 검색 결과 == ");
			System.out.println("계좌번호\t\t은행명\t예금주명\t개설날짜");
			System.out.println("------------------------------------------------------");
			while(rs.next()) {
				String bNo = rs.getString("bank_no");
				String bName = rs.getString("bank_name");
				String uName = rs.getString("bank_user_name");
				String bDate = rs.getString("bank_date");
				System.out.println(bNo+"\t"+bName+"\t"+uName+"\t"+bDate);
			}
			System.out.println("------------------------------------------------------");
		} catch (SQLException e) {
			// TODO: handle exception
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(rs!=null)try {rs.close();} catch (SQLException e2) {}
			if(stmt!=null)try {stmt.close();} catch (SQLException e2) {}
			if(pstmt!=null)try {pstmt.close();} catch (SQLException e2) {}
			if(conn!=null)try {conn.close();} catch (SQLException e2) {}
		}
	}
}

---

PreparedStatement 객체 사용 (SQL_Injection 해결)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JdbcTest06 {

	public static void main(String[] args) {
		Scanner scan = new Scanner(System.in);
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","JIN96","java");
			
			System.out.println("계좌 정보 검색하기");
			
			System.out.print("검색할 계좌번호 입력 >> ");
			String bankNo = scan.nextLine();
			
			// PreparedStatement객체를 이용
			String sql = "select * from bankinfo where bank_no=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bankNo);
			rs=pstmt.executeQuery();
			
			System.out.println("  == 검색 결과 == ");
			System.out.println("계좌번호\t\t은행명\t예금주명\t개설날짜");
			System.out.println("------------------------------------------------------");
			while(rs.next()) {
				String bNo = rs.getString("bank_no");
				String bName = rs.getString("bank_name");
				String uName = rs.getString("bank_user_name");
				String bDate = rs.getString("bank_date");
				System.out.println(bNo+"\t"+bName+"\t"+uName+"\t"+bDate);
			}
			System.out.println("------------------------------------------------------");
		} catch (SQLException e) {
			// TODO: handle exception
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(rs!=null)try {rs.close();} catch (SQLException e2) {}
			if(stmt!=null)try {stmt.close();} catch (SQLException e2) {}
			if(pstmt!=null)try {pstmt.close();} catch (SQLException e2) {}
			if(conn!=null)try {conn.close();} catch (SQLException e2) {}
		}
	}
}

JDBC드라이버 로딩 & Connection 객체 생성

반복되는 Class.forName()과 Connection 객체 생성하여 아래 코드로 간단히 재사용이 가능하다

conn = DBUtil.getConnection();

---

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// JDBC드라이버를 로딩하고 Connection 객체를 생성하여 반환하는 메서드로 구성된 class 만들기
public class DBUtil {
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("드라이버 로딩 실패");
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() {
		Connection conn = null;		// 반환값이 저장될 변수
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","JIN96","java");
		} catch (SQLException e) {
			// TODO: handle exception
			System.out.println("DB 연결 실패");
			e.printStackTrace();
		}
		return conn;
	}
}

CRUD기능 구현 예제

package kr.or.ddit.basic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import kr.or.ddit.util.DBUtil;

/*
 	회원을 관리하는 프로그램을 작성하시오.
 	(MYMEMBER 테이블 이용)
 	
 	아래 메뉴의 기능을 모두 구현하시오. (CRUD기능 구현하기 연습)
 	메뉴 예시)
 	=========================
 	1. 자료 추가		==> insert (C)
 	2. 자료 삭제		==> delete (D)
 	3. 자료 수정		==> update (U)
 	4. 전체 자료 출력		==> select (R)
 	0. 작업 끝
 	=========================

 	조건)
 	1. '자료 추가'에서 '회원ID'는 중복되지 않는다. (중복되면 다시 입력 받아서 처리한다)
 	2. '자료 삭제'는 '회원ID'를 입력 받아서 처리한다.
 	3. '자료수정'에서 '회원ID'는 변경되지 않는다
 	
 */
public class JdbcTest07 {
	Scanner scan = null;
	// 메서드마다 별개의 conn, pstmt, rs를 생성할 것
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	public JdbcTest07() {
		scan = new Scanner(System.in);
		conn = DBUtil.getConnection();
	}

	public static void main(String[] args) {
		new JdbcTest07().startProgram();
	}

	public void startProgram() {
		menu();
	}

	public void menu() {
		try {
			while (true) {
				System.out.println("1. 자료 추가");
				System.out.println("2. 자료 삭제");
				System.out.println("3. 자료 수정");
				System.out.println("4. 부분 수정");
				System.out.println("5. 전체 자료 출력");
				System.out.println("0. 작업 끝");
				System.out.print("메뉴 >> ");
				int sel = scan.nextInt();
				switch (sel) {
				case 1:
					insert();
					break;
				case 2:
					delete();
					break;
				case 3:
					update();
					break;
				case 4:
					update_part();
					break;
				case 5:
					print();
					break;
				case 0:
					System.out.println("시스템을 종료합니다");
					return;
				default:
					break;
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
				}
		}
	}

	private void update_part() {
		System.out.println("-- 자료 수정 --");
		System.out.println("변경할 아이디를 입력해주세요");
		System.out.print("ID >> ");
		String id = scan.next();
		try {

			int idChk = idChk(id);

			if (idChk != 1) {
				System.out.println("===================");
				System.out.println("존재하지 않는 아이디입니다");
				System.out.println("메뉴로 돌아갑니다");
				System.out.println("===================");
				return;
			}
			System.out.println();
			System.out.println("업데이트할 내용을 선택해주세요");
			System.out.println("중복선택이 가능합니다");
			System.out.println("ex. 123 >  비밀번호/이름/전화번호 수정");
			System.out.println();
			System.out.println("1. 비밀번호 수정");
			System.out.println("2. 이름 수정");
			System.out.println("3. 전화번호 수정");
			System.out.println("4. 주소 수정");
			String sel = "";
			// 없는 번호 입력시 재입력받음
			do {
				System.out.print("번호 입력 >> ");
				sel = scan.next();
			}while(!(sel.contains("1"))&&!(sel.contains("2"))&&!(sel.contains("3"))&&!(sel.contains("4")));
			
			String sql = "update mymember set ";
			ArrayList<String> temp = new ArrayList<String>();
			ArrayList<String> info = new ArrayList<String>(); 
			if (sel.contains("1")) {
				temp.add("mem_pass=? ");
				System.out.print("PW >> ");
				info.add(scan.next());
			}
			
			if (sel.contains("2")) {
				temp.add("mem_name=? ");
				System.out.print("이름 >> ");
				info.add(scan.next());
			}
			
			if (sel.contains("3")) {
				temp.add("mem_tel=? ");
				System.out.print("전화번호 >> ");
				info.add(scan.next());
			}
			
			if (sel.contains("4")) {
				temp.add("mem_addr=? ");
				scan.nextLine();
				System.out.print("주소 >> ");
				info.add(scan.nextLine());
			}
			
			// sql 넣기
			for(int i=0; i<temp.size();i++) {
				sql+=temp.get(i);
				if(i!=(temp.size()-1)) {
					sql+=", ";
				}
			}
			sql += "where mem_id=?";
			
			info.add(id);
			pstmt = conn.prepareStatement(sql);
			
			int index = 1;
			// pstmt에 값 넣기
			for(int i=0; i<info.size();i++) {
				pstmt.setString(index++,info.get(i));
			}
			int cnt = pstmt.executeUpdate();

			if (cnt > 0) {
				System.out.println("수정되었습니다");
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	private void print() {
		System.out.println("-- 전체 자료 출력 --");
		System.out.println();
		System.out.println("회원아이디\t비밀번호\t회원이름\t전화번호\t\t주소");
		System.out.println("-------------------------------------------------");
		try {
			String sql = "select * from mymember";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String id = rs.getString("mem_id");
				String pw = rs.getString("mem_pass");
				String name = rs.getString("mem_name");
				String tel = rs.getString("mem_tel");
				String add = rs.getString("mem_addr");
				System.out.println(id + "\t" + pw + "\t" + name + "\t" + tel + "\t" + add);
			}
			System.out.println("-------------------------------------------------");
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

	private void update() {
		System.out.println("-- 자료 수정 --");
		System.out.println("변경할 아이디를 입력해주세요");
		System.out.print("ID >> ");
		String id = scan.next();
		try {

			int idChk = idChk(id);

			if (idChk != 1) {
				System.out.println("===================");
				System.out.println("존재하지 않는 아이디입니다");
				System.out.println("메뉴로 돌아갑니다");
				System.out.println("===================");
				return;
			}
			String sql = "update mymember set mem_pass=?, mem_name=?, mem_tel=?, mem_addr=? where mem_id=?";
			pstmt = conn.prepareStatement(sql);

			System.out.print("PW >> ");
			String pw = scan.next();

			System.out.print("이름 >> ");
			String name = scan.next();

			System.out.print("전화번호 >> ");
			String tel = scan.next();

			scan.nextLine();
			System.out.print("주소 >> ");
			String add = scan.nextLine();

			pstmt.setString(1, pw);
			pstmt.setString(2, name);
			pstmt.setString(3, tel);
			pstmt.setString(4, add);
			pstmt.setString(5, id);
			int cnt = pstmt.executeUpdate();

			if (cnt > 0) {
				System.out.println("수정되었습니다");
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	private void delete() {
		System.out.println("-- 자료 삭제 --");
		System.out.println("삭제할 아이디를 입력해주세요");
		System.out.print("ID >> ");
		String id = scan.next();
		try {

			int idChk = idChk(id);

			if (idChk != 1) {
				System.out.println("===================");
				System.out.println("존재하지 않는 아이디입니다");
				System.out.println("메뉴로 돌아갑니다");
				System.out.println("===================");
				return;
			}
			String sql = "delete from mymember where mem_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			int cnt = pstmt.executeUpdate();

			if (cnt > 0) {
				System.out.println(id + "님이 삭제되었습니다");
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	private int idChk(String id) {
		int idChk = 0;
		String sql = "select 1 from mymember where mem_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				idChk = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return idChk;
	}

	private void insert() {
		try {
			System.out.println("-- 자료 추가 --");
			String id = "";
			String sql = "";
			while (true) {
				System.out.print("ID >> ");
				id = scan.next();

				int idChk = idChk(id);

				if (idChk == 1) {
					System.out.println("중복된 아이디입니다 다시 입력해주세요");
				} else {
					System.out.println("사용가능한 아이디입니다");
					break;
				}
			}
			System.out.print("PW >> ");
			String pw = scan.next();

			System.out.print("이름 >> ");
			String name = scan.next();

			System.out.print("전화번호 >> ");
			String tel = scan.next();
			// 버퍼 삭제
			scan.nextLine();
			System.out.print("주소 >> ");
			String add = scan.nextLine();

			sql = "insert into mymember(mem_id, mem_pass, mem_name, mem_tel, mem_addr) " + " values(?, ?, ?, ?, ?) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, pw);
			pstmt.setString(3, name);
			pstmt.setString(4, tel);
			pstmt.setString(5, add);

			int cnt = pstmt.executeUpdate();
			if (cnt > 0) {
				System.out.println(id + "님이 정상적으로 추가되었습니다");
			} else {
				System.out.println("실패");
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}