Java 정리 36

07 Jan 2019

Reading time ~9 minutes

Java 정리 36 - JDBC(3), SQL Injection, PreparedStatement

DB 사용한 로그인

비대면 인증방식 중 가장 많이 사용하는 기법
- 인증 : 나임을 증명하는 것
- 아이디, 비번으로 인증
DB Table
- login 이란 임시 테이블

DB에서 유저를 조회하는 쿼리

SELECT name
FROM login
WHERE id='kim' AND pass='1234';

public boolean login(String id, String pass) {
    ...
    StringBuilder sb = new StringBuilder();

    sb.append("SELECT name FROM login ")
    .append("WHERE id='").append(id)
    .append("' AND pass='").append(pass)
    .append("'");

    ...
}

로그인 예제

SQLInjection 예제
test_login DB 생성

CREATE TABLE test_login (
     id VARCHAR2(16) CONSTRAINT pk_test_login PRIMARY KEY,
     pass VARCHAR2(16) NOT NULL,
     name VARCHAR2(30)
);

INSERT INTO test_login(id, pass, name)
VALUES ('jung','1234','정택성');
INSERT INTO test_login(id, pass, name)
VALUES ('lee','1234','이재현');
INSERT INTO test_login(id, pass, name)
VALUES ('gong','1234','공선의');
INSERT INTO test_login(id, pass, name)
VALUES ('kim','1234','김건하');

@SuppressWarnings("serial")
public class StatementLogin extends JFrame {
    
    private JTextField jtfId;
    private JPasswordField jpfPass;
    private JButton jbtLogin;
    private JButton jbtCancel;
    
    public StatementLogin() {
        super("Statement객체를 사용한 로그인");
        
        jtfId = new JTextField();
        jpfPass = new JPasswordField();
        jbtLogin = new JButton("로그인");
        jbtCancel = new JButton("취소");
        
        setLayout(new GridLayout(3, 1));

        JPanel panel = new JPanel();
        panel.add(jbtLogin);
        panel.add(jbtCancel);
        
        jtfId.setBorder(new TitledBorder("아이디"));
        jpfPass.setBorder(new TitledBorder("비밀번호"));
        
        add(jtfId);
        add(jpfPass);
        add(panel);
        
        StatementLoginEvt sle = new StatementLoginEvt(this);
        jtfId.addActionListener(sle);
        jpfPass.addActionListener(sle);
        jbtLogin.addActionListener(sle);
        jbtCancel.addActionListener(sle);

        addWindowFocusListener(sle);
        
        setBounds(400, 300, 370, 150);
        setResizable(false);
        setVisible(true);
    }
    
    public static void main(String[] args) {
        new StatementLogin();
    }
    
    // getters

// 사용자 정의 예외처리 클래스
// Exception(또는 RuntimeException)을 상속받는다.
@SuppressWarnings("serial")
public class LoginException extends Exception {
     public LoginException() {
           this("로그인 실패");
     }
     public LoginException(String msg) {
           super(msg); // 예외처리 객체를 사용하여  예외메세지를 출력할 수 있다.
     }
}

public class StatementLoginEvt extends WindowAdapter implements  ActionListener {
  
  private StatementLogin sl;
  
  public StatementLoginEvt(StatementLogin sl) {
        this.sl = sl;
  }
  
  @Override
  public void actionPerformed(ActionEvent e) {
      if (e.getSource() == sl.getJtfId() || e.getSource()  == sl.getJpfPass()
                || e.getSource() == sl.getJbtLogin()) {
          
          String id = sl.getJtfId().getText().trim();
          String pass = new  String(sl.getJpfPass().getPassword()).trim();
          
          if (chkNull(id, pass)) {
                try {
                      String name = login(id, pass);
                      
                      if (name.isEmpty()) {
                          JOptionPane.showMessageDialog(sl, "아이디나 비밀번호를  확인해주세요.");
                          sl.getJtfId().setText("");
                          sl.getJpfPass().setText("");
                          sl.getJtfId().requestFocus();
                          return;
                      }
                      
                      JDialog jd = new JDialog(sl, "로그인  성공", true);
                      jd.add(new JLabel(name+"님 로그인  하셨습니다."));
                    jd.setBounds(sl.getX()+100,sl.getY()+100, 300, 200);
                      jd.setResizable(false);
                      jd.setVisible(true);
                    jd.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                      
                } catch (SQLException se) {
                      JOptionPane.showMessageDialog(sl,  "DB작업 중 문제가 발생했습니다.");
                      se.printStackTrace();
                }
          }
      }
      if (e.getSource() == sl.getJbtCancel()) {
          sl.getJtfId().setText("");
          sl.getJpfPass().setText("");
          sl.getJtfId().requestFocus();
      }
  }
  public String login(String id, String pass) throws  SQLException {
      String name = "";
      
      // 1. 드라이버 로딩
      try {
          Class.forName("oracle.jdbc.OracleDriver");
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      }
      
      // 2. Connection 얻기
      String url = "jdbc:oracle:thin:@localhost:1521:orcl";
      String dbo_id = "scott";
      String dbo_pass = "tiger";
      
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      
      try {
          con = DriverManager.getConnection(url, dbo_id,  dbo_pass);
          // 3. 쿼리문 생성객체 얻기
          stmt = con.createStatement();
          
          StringBuilder selectName = new StringBuilder();
          selectName
          .append("SELECT name ")
          .append("FROM test_login ")
          .append("WHERE id='").append(id).append("' AND  ")
          .append("pass='").append(pass).append("'");
          
          // 4. 쿼리문 수행 후 결과 얻기
          rs = stmt.executeQuery(selectName.toString());
          
          if (rs.next()) { 
            // 입력된 아이디와 비밀번호에  맞는 이름이 존재 => 로그인 성공
                name = rs.getString("name");
          }
      
      } finally {
          // 5. 연결 끊기
          if (rs != null) { rs.close(); }
          if (stmt != null) { stmt.close(); }
          if (con != null) { con.close(); }
      }
      
      return name;
  }
  
  public boolean chkNull(String id, String pass)  { // *****
      boolean flag = false;
      
      try {
          if (id.equals("")) {
                JOptionPane.showMessageDialog(sl,  "아이디는 필수 입력");
                sl.getJtfId().requestFocus();
                flag = true;
                // 예외를 강제로 발생, throw new  예외처리클래스명(); *****
                throw new LoginException("아이디 공백");
          }
          if (pass.equals("")) {
                JOptionPane.showMessageDialog(sl,  "비밀번호는 필수 입력");
                sl.getJpfPass().requestFocus();
                flag = true;
                throw new LoginException("비밀번호 공백");
          }
      } catch (LoginException le) {
          le.printStackTrace();
      }
      
      return !flag;
  }
  
  
  @Override
  public void windowClosing(WindowEvent e) {
      sl.dispose();
  }
}

OWASP (Open Web Application Security Project)

OWASP에서는 매년 보안위협 TOP 10을 발표함
- https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project * Injection 공격은 항상 상위권

로그인 창 SQLInjection 예시

프로그램 외부에서 쿼리문을 입력하여 정보를 탈취하는 공격
- 입력할 수 있는 공간을 “Attack Point”라 한다.
쿼리가 어떻게 만들어져 있는가를 예측해서 값을 집어넣는 공격

String sql = "SELECT name FROM login WHERE id='"+id+"' AND pass='"+pass+"'";

아래값을 입력하면 비밀번호 무시하고 뚫림
- 쿼리문을 항상 참으로 만드는 WHERE 1=1

-- id는 항상 참으로, pw 주석으로 없애는 SQL Injection
-- 변경된 SQL : SELECT name FROM login WHERE id = '' OR 1=1 -- 
' or 1=1 --

방어 코드 : 작은 따옴표, 주석, 공백이 못 들어오게 막아야 한다.

// SQL Injection 방어코드
// 회원가입 받을 때 부터 공백, 작은 따옴표, 하이픈 못 쓰게  막아야 한다.
public String blockSqlInjection(String data) {
     return data.replaceAll(" ",  "").replaceAll("'","").replaceAll("--", "");
}

StringBuilder selectName = new StringBuilder();

selectName
.append("SELECT name ")
.append("FROM test_login ")
.append("WHERE  id='").append(blockSqlInjection(id)).append("' AND ")
.append("pass='").append(blockSqlInjection(pass)).append("'");

또 다른 SQL Injection 예시

지번 동을 입력받아 우편번호, 주소를 출력하는 창

String dong = jtf.getText().trim();

sql = "SELECT zipcode, sido, gugun, dong, bunji
       FROM zipcode
       WHERE dong LIKE '"+dong+"%'";

@SuppressWarnings("serial")
public class ZipcodeView extends JFrame {
  
  private JTextField jtfDong;
  private JButton jbtSearch;
  private DefaultTableModel dtmZipcode;
  
  public ZipcodeView() {
      super("지번주소 검색");
      jtfDong = new JTextField(10);
      jbtSearch = new JButton("검색");
      
      String[] columnNames = { "우편번호", "주소" };
      dtmZipcode = new DefaultTableModel(columnNames,0);
      
      JTable tabZipcode = new JTable(dtmZipcode);
      tabZipcode.getColumnModel().getColumn(0).setPreferredWidth(50);
      tabZipcode.getColumnModel().getColumn(1).setPreferredWidth(400);
      
      // 컬럼 이동 막기
      tabZipcode.getTableHeader().setReorderingAllowed(false);
      
      // 행의 높이 조절
      tabZipcode.setRowHeight(24);
      
      JScrollPane jsp = new JScrollPane(tabZipcode);
      
      JPanel panelNorth = new JPanel();
      panelNorth.add(new JLabel("동 이름"));
      panelNorth.add(jtfDong);
      panelNorth.add(jbtSearch);
      
      add(BorderLayout.NORTH, panelNorth);
      add(BorderLayout.CENTER, jsp);
      
      ZipcodeViewEvt zve = new ZipcodeViewEvt(this);
      jtfDong.addActionListener(zve);
      jbtSearch.addActionListener(zve);
      addWindowListener(zve);
      
      setBounds(400, 300, 600, 500);
      setResizable(false);
      setVisible(true);
  }

  public static void main(String[] args) {
      new ZipcodeView();
  }
  // getters
}

public class ZipcodeVO {
  private String zipcode, sido, gugun, dong, bunji;
  public ZipcodeVO() { }
  
  public ZipcodeVO(String zipcode, String sido, 
    String  gugun, String dong, String bunji) {
        this.zipcode = zipcode;
        this.sido = sido;
        this.gugun = gugun;
        this.dong = dong;
        this.bunji = bunji;
  }
  // getters

public class ZipcodeViewEvt extends WindowAdapter 
  implements ActionListener {

  private ZipcodeView zv;
  
  public ZipcodeViewEvt(ZipcodeView zv) {
    this.zv = zv;
  }
  
  public List<ZipcodeVO> selectZipcode(String dong) throws SQLException {
    List<ZipcodeVO> list = new ArrayList<ZipcodeVO>();
    
    // 1. 드라이버 로딩
    try {
        Class.forName("oracle.jdbc.OracleDriver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
        
    // 2. Connection 얻기
    try {
        
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String id = "scott";
        String pass = "tiger";
        
        con = DriverManager.getConnection(url, id, pass);
        
        // 3. 쿼리문 생성객체 얻기
        stmt = con.createStatement();

        StringBuilder selectZipcode = new StringBuilder();
        selectZipcode
        .append(" SELECT zipcode, sido, gugun, dong, NVL(bunji,' ') bunji ")
        .append(" FROM zipcode ")
        .append(" WHERE dong LIKE '").append(dong).append("%'");
        
        // 4. 쿼리 수행 후 결과 얻기
        rs = stmt.executeQuery(selectZipcode.toString());
        
        ZipcodeVO zv = null;
        
        while(rs.next()) { // 조회된 레코드가 존재한다면
          // VO에 값을 할당하고
          zv = new ZipcodeVO(rs.getString("zipcode"), rs.getString("sido")
                , rs.getString("gugun"), rs.getString("dong"), rs.getString("bunji"));
          // 같은 이름의 객체를 여러개 관리하기 위해 List에 추가
          list.add(zv);
        }
        
    } finally {
        // 5. 연결끊기
        if (rs != null) { rs.close(); }
        if (stmt != null) { stmt.close(); }
        if (con != null) { con.close(); }
    }
    
    return list;
  }
  
  public void searchZipcode(String dong) {
    try {
        // DB에서 조회한 결과를 받아서
        List<ZipcodeVO> listZip = selectZipcode(dong);
        
        // DefaultTableModel에 추가 => JTable에 반영된다.
        DefaultTableModel dtm = zv.getDtmZipcode();
        
        // D.T.M 초기화
        dtm.setRowCount(0);
        
        String[] rowData = null;
        // D.T.M에 행(Row : 우편번호, 주소 ) 추가
        // 조회된 결과를 가지고
        for(ZipcodeVO zv : listZip) {
          // 배열에 넣고
          rowData = new String[2];
          rowData[0] = zv.getZipcode();
          rowData[1] = zv.getSido()+" "+zv.getGugun()
            +" "+zv.getDong()+" "+zv.getBunji();
          dtm.addRow(rowData);
        }
        
    } catch (SQLException se) {
        JOptionPane.showMessageDialog(zv, "DB에서 문제 발생");
        se.printStackTrace();
    }
  }
  
  @Override
  public void actionPerformed(ActionEvent e) {
    String dong = zv.getJtfDong().getText().trim();
        
    if (!dong.equals("")) {
      searchZipcode(dong);
    }
  }
  
  @Override
  public void windowClosing(WindowEvent e) {
    zv.dispose();
  }
}    

발생 가능한 SQL Injection
- UNION을 사용하여 공격을 수행. * UNION은 컬럼의 수와 데이터형이 맞지않으면 에러남
  - 컬럼수를 계속 늘려가면서 시도한다.
- 테이블 이름을 얻는 SQL Injection

' UNION SELECT tname, '0', '0', '0', '0' FROM tab --

이렇게 뚫리면 오라클 DB란 의미
- 여러 dictionary들을 알 수 있게됨
테이블의 이름을 알 수 있는 쿼리

select column_name from user_tab_cols where table_name='TEST_LOGIN';

특정 테이블의 컬럼명 얻는 SQL Injection

' UNION SELECT column_name,data_type,'0','0','0' FROM user_tab_cols WHERE table_name='TEST_LOGIN' ---

레코드 검색 SQL Injection

' UNION SELECT id,name,pass,'0','0' FROM test_login --

방어코드
- 로그인 때와 동일하게 작은 따옴표, 주석코드를 막음
- 추가로 필요하다면 SQL관련된 명령을 막는다(SELECT, UNION 등)

public String blockSqlInjection(String sql) {
     return sql.replaceAll("'", "").replaceAll("--", "");
}

selectZipcode
.append(" SELECT zipcode, sido, gugun, dong, NVL(bunji,' ')  bunji ")
.append(" FROM zipcode ")
.append(" WHERE dong LIKE  '").append(blockSqlInjection(dong)).append("%'");

단위테스트 하기

메소드 동작을 확인하는 단위테스트
- 메인메소드 생성하여 작동여부 확인하는 작업
- QA(Quality Assurance)가 하는 업무

public void insertCpEmp2(CpEmp2VO cevo) throws  SQLException {
  Connection con = null;
  PreparedStatement pstmt = null;
  try {
      
      // 1. 드라이버 로딩
      // 2. Connection 얻기
      con = getCon();
      // 3. 쿼리문 생성객체 얻기 :  PreparedStatement객체는 실행되는 쿼리문을 알고 있다.
      String insertCpEmp2 =
                "INSERT INTO  cp_emp2(empno,ename,hiredate,sal) VALUES(?,?,SYSDATE,?)";
      pstmt = con.prepareStatement(insertCpEmp2);
      
      // 4. 바인드 변수에 값 설정
      pstmt.setInt(1, cevo.getEmpno());
      pstmt.setString(2, cevo.getEname());
      pstmt.setInt(3, cevo.getSal());
      
      // 5. 쿼리 수행 후 결과 얻기
      pstmt.executeUpdate();
      
  } finally {
      // 6. 연결끊기
      if (pstmt != null) { pstmt.close(); }
      if (con != null) { con.close(); }
  }
}

public static void main(String[] args) {
  UsePreparedStatementDAO u = new  UsePreparedStatementDAO();
  CpEmp2VO c = new CpEmp2VO(3,5000,"범이");
  try {
      u.insertCpEmp2(c);
  } catch (SQLException e) {
      e.printStackTrace();
  }
}

화이트박스 테스트, 블랙박스 테스트

화이트박스 테스트
- 직접 코드를 보며 분석하는 테스트
블랙박스 테스트
- 제시된 체크리스트 보며 작동여부를 확인하는 테스트

PreparedStatement

쿼리문을 객체가 알고있다.
- 쿼리문이 만들어진 상태에서 실행
반복 수행 시 Statement보다 속도가 빠르다.
SQL Injection 공격을 할 수 없다.
bind변수를 사용할 때 ‘를 넣을 수 없다.
- bind변수란 SQL문 내에 값을 대체하여 들어가는 변수
- 쿼리문의 작성이 단순해진다.

// ? - bind 변수
String sql1 = "INSERT INTO 테이블명 VALUES(?, ?, ?)";
String sql2 = "SELECT name FROM 테이블명 WHERE 컬럼명=?";

컬럼명, 테이블명에는 bind변수를 사용할 수 없다.
바인드변수의 인덱스는 1번부터 시작한다.
사용법
- PreparedStatement 얻기

// 3. 쿼리문 생성 객체 얻기
PreparedStatement pstmt = con.prepareStatement(sql);

// 4. bind 변수에 값 넣기
pstmt.setInt(바인드변수인덱스, 정수값); // 정수인 경우
pstmt.setString(인덱스, 문자열); // 문자열인 경우

// 5. 쿼리문을 실행하여 결과 얻기
// PreparedStatement는 Statement와 다르게 
// 쿼리를 실행할 때 쿼리를 파라미터로 넣지 않는다.
boolean flag = pstmt.execute();
ResultSet rs = pstmt.executeQuery();
int cnt = pstmt.executeUpdate();

오랜만에 숙제

접속한 계정의 모든 Table명을 JComboBox에 출력

public class RunSelectTable {
     
  public JComboBox<String> setTab() throws  SQLException {
      JComboBox<String> jcbTab = new  JComboBox<String>();
      jcbTab.addItem("테이블명");
      
      try {
          Class.forName("oracle.jdbc.OracleDriver");
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      }
      
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      
      try {
          String url =  "jdbc:oracle:thin:@localhost:1521:orcl";
          String id = "scott";
          String pwd = "tiger";
          
          con = DriverManager.getConnection(url, id,  pwd);
          stmt = con.createStatement();
          
          String sql = "SELECT tname FROM tab";
          
          rs = stmt.executeQuery(sql);
          
          while (rs.next()) {
                jcbTab.addItem(rs.getString("tname"));
          }
          
      } finally {
          if (rs != null) { rs.close(); }
          if (stmt != null) { stmt.close(); }
          if (con != null) { con.close(); }
      }
      
      return jcbTab;
  }
  public static void main(String[] args) {
      RunSelectTable rst = new RunSelectTable();
      try {
          new SelectTableView(rst.setTab());
      } catch (SQLException e) {
          System.out.println("DB 접속 실패");
          e.printStackTrace();
      }
  }
}

@SuppressWarnings("serial")
public class SelectTableView extends JFrame {
  
  private JButton jbSelect;
  private JComboBox<String> jcbTab;
  
  public SelectTableView(JComboBox<String> jcbTab) {
      super("테이블 선택");
      
      this.jcbTab = jcbTab;
      JLabel jl = new JLabel("테이블 선택");
      jbSelect = new JButton("선택");
      
      JPanel panel = new JPanel();
      
      panel.add(jl);
      panel.add(jcbTab);
      panel.add(jbSelect);
      
      add(BorderLayout.CENTER, panel);
      
      SelectTableViewEvt stve = new  SelectTableViewEvt(this);
      jbSelect.addActionListener(stve);
      addWindowListener(stve);
      
      setBounds(400, 300, 350, 100);
      setResizable(false);
      setVisible(true);
  }
}

public class SelectTableViewEvt extends WindowAdapter  implements ActionListener {
    private SelectTableView stv;
    
    
    public SelectTableViewEvt(SelectTableView stv) {
        this.stv = stv;
    }
    
    @Override
    public void actionPerformed(ActionEvent e) {
        // 선택했을 때 이벤트 처리
    }
    @Override
    public void windowClosing(WindowEvent e) {
        stv.dispose();
    }
}

Java