2020-11-02 TIL

DBMS - 37a

JDBC API 사용 (Statement)

데이터베이스(Database) 는,

• 상호 연관이 있는 데이터의 모임을 가리킨다.
• 여러 사람이 공유하고 사용할 목적으로 통합 관리되는 정보의 집합이다.(wikipedia.org)
• 논리적으로 연관된 데이터를 검색과 갱신이 쉽도록 고도로 구조화한 것이다.
• 목적(wikipedia.og)
  • 데이터 중복 최소화
  • 데이터 공유
  • 일관성, 무결성, 보안성 유지
  • 데이터 접근 요이성
  • 데이터 저장 공간 절약

데이터베이스 관리 시스템(Database Management System: DBMS) 은,

• 데이터베이스의 목적을 달성할 수 있도록 편리하고 효율적인 환경을 제공하는 소프트웨어다.

JDBC(Java Database Connectivity) API 는,

• 자바 애플리케이션이 DBMS에 연동할 때 사용할 클래스와 호출 규칙을 정의한 라이브러리다
• DBMS 제작자는 JDBC API 의 구현체를 제공한다.

JDBC 드라이버 는,

• JDBC API 규약에 따라 만든 클래스 라이브러리다.
• 각각의 DBMS 제작자가 배포한다.

JDBC 드라이버 의 종류,

• Type 1
  • JDBC-ODBC 브릿지 드라이버(bridge driver) 라 부른다.
  • ODBC API 를 사용하여 DBMS와 연동한다.
  • ODBC API 를 제공하는 엑셀이나 액세스 등의 파일 DB에 접근할 때 용이하다.
  • 따라서 이 드라이버를 사용하는 컴퓨터에 ODBC 드라이버가 설치되어 있어야 한다.
  • 여러 계층을 경유하기 때문에 실행 속도가 느리다.
  • JRE(Java Runtime Environment) 에 기본으로 포함되어 있다.
• Type 2
  • 네이티브 드라이버(native driver) 라 부른다.
  • DBMS 제작자가 제공하는 데이터베이스 API(C/C++) 를 호출하여 DBMS와 연동한다.
  • 따라서 이 드라이버를 사용하는 컴퓨터에 데이터베이스 API 가 설치되어 있어야 한다.
  • DBMS 제작자가 별도의 써드 파티 업체에서 제공한다.
• Type 3
  • 네트워크 프로토콜 드라이버(network protocol driver) 라 부른다.
  • 미들웨어 를 경유하여 DBMS와 연동한다.
  • 따라서 특정 DBMS에 종속되지 않으며, 하나의 드라이버로 여러 DBMS에 접근할 수 있다.
  • C/C++ 함수를 호출하지 않고 미들웨어 전용 프로토콜 로 직접 통신하기 때문에 100% 순수 자바 코드로 구현되었다.
  • 미들웨어 구입 비용이 추가로 든다.
• Type 4
  • 씬 드라이버(thin driver) 라 부른다.
  • DBMS와 연동할 때 DBMS 전용 프로토콜 로 직접 통신한다.
  • DBMS와 직접 통신하기 때문에 실행 속도가 빠르다.
  • 내부적으로 C/C++ 함수를 호출하지 않기 때문에 100% 순수 자바 코드로 구현되었다.
  • 단, 특정 DBMS에 종속된다.
  • DBMS 제작자에서 제공한다.

DBMS - 37b

SQL 공격과 자바 시큐어 코딩

SQL 삽입 공격 은,

• 사용자의 입력 값으로 SQL 문을 만들 때 가능한 공격 방법이다.
• 값을 입력할 때 SQL 문에 영향을 끼치는 SQL 코드를 함께 삽입하는 방법이다.
• 예)
  • SQL 문: select * from user where username='아이디' and password='암호'
  • 아이디: hongkildong
  • 암호: ok' or 1=1 --
• 입력 값이 포함된 최종 SQL 문
  • select * from user where username='hongkildong' and password='ok' or 1=1 --'
  • 1=1 조건은 무조건 참이므로 username 과 password가 일치하지 않더라도 상관없이 결과는 true 이다.
  • -- 은 줄 끝까지 주석으로 취급하기 때문에 SQL 문의 끝에 있던 작은 따옴표를 제거하는 효과가 있다.
• 해결 방법
  • Statement 대신에 PreparedStatement 를 사용하면 된다.

Statement vs PreparedStatement,

• SQL 삽입 공격
  • Statement : 가능
  • PreparedStatement : 불가능
• 바이너리 컬럼 값 넣기
  • Statement : 불가능
  • PreparedStatement : 가능
• 코드의 간결함
  • Statement : 가독성이 떨어진다. String.format()으로 개선할 수 있다.
  • PreparedStatement : in-parameter 문법으로 SQL 문과 값을 분리하기 때문에 가독성이 좋다.
• 실행 속도
  • Statement
    • SQL을 실행할 때 마다 DBMS는 매번 SQL 구문 분석 -> 최적화 -> 실행 과정을 거친다.
  • PreparedStatement
    • 처음 SQL을 실행할 때 딱 한 번 SQL 구문 분석 -> 최적화 -> 실행 과정을 거친다.
    • 이 이후에는 내부 SQL 공유 풀 에 보관된 최적화된 실행 계획 을 바로 실행 한다.
    • 따라서 SQL을 준비한 후 반복하여 실행하는 경우에는 Statement 보다 빠르다.

SQL 실행 과정(오라클 DBMS의 예)

• SQL 구분 분석(Parsing)
  • 문법의 유효성 검사(Syntax Check)
    • 문법의 규칙을 준수하는지 검사한다.
    • 예) select * form pms_board
    • form 은 잘못된 SQL 문법이다.
  • SQL 문의 의미 검사(Semantic Check)
    • SQL 문에서 지정하는 컬럼이나 테이블, 뷰 등이 유효한지 검사한다.
    • 예) select * from pms_okok
    • pms_okok 테이블이 없다면, semantic 오류 이다.
  • SQL 공유 풀 검사(Shared Pool Check)
    • 공유 풀 은 SQL 문에 대해 생성된 실행 계획(execution plan) 등을 보관한다.
    • 먼저 SQL 문에 대해 해시(hash) 연산을 수행하여 SQL ID를 생성한다.
    • 공유 풀 에 저장된 값 중에서 SQL ID와 일치하는 값이 있는지 조사한다.
    • 만약 있다면, 즉시 해당 값을 꺼내 실행 계획 에 따라 SQL 문을 실행한다.
    • 없다면, 하드 파싱(hard parsing) 이라는 과정을 수행한다.
• 하드 파싱
  • SQL 최적화(Optimization)
    • SQL 문을 가장 효율적으로 실행할 수 있게 재구성한다.
    • 각 문장 별로 실행 계획(execution plan) 이라는 명령 코드를 생성한다.
    • 여러 개의 실행 계획 을 검토한 후 실행 비용을 계산하여 최적의 실행 계획을 생성한다.
  • SQL 컴파일(Row Source Generation)
    • 최적화 단계에서 생성된 실행 계획 을 입력으로 받는다.
    • 각 실행 단계 별로 결과 데이터(result set) 를 리턴할 바이너리 명령을 생성한다.
    • 이 바이너리 명령을 Row Source 라고 부른다.
    • Row Source 는 테이블이나 뷰, 조인 또는 그룹 연산을 가리킨다.
    • Row Source Generator 는 실행 순서에 따라 Row Source Tree 를 생성한다.
• SQL 실행
  • SQL 엔진은 Row Source Tree 에 따라가면서 Row Source 바이너리 명령을 실행한다.
  • 각 Row Source 바이너리 명령은 테이블이나 뷰, 조인 또는 그룹 연산 결과를 생성한다.
  • 최종 실행 결과는 애플리케이션에게 리턴할 결과 데이터(Result Set) 이다.

다대 다 관계

• 프로젝트는 여러명의 멤버를 가질 수 있고 멤버는 여러 프로젝트에 참여할 수 있다.
• DMBS에서 다를 수 없다.

다대 다 관계의 해소 1

• 다대 다 관계를 1대 다 관계로 변환해야 한다.
  • 다대 다 관계를 저장하는 컬럼은 FK로 선언할 수 없다.
• 멤버_프로젝트 테이블이라는 관계 테이블을 새로 만들어서 그 안에 멤버_넘버와 프로젝트_넘버라는 컬럼을 만든다.
  • 두 컬럼은 FK 인 동시에 PK 이다.
• 관계 테이블은 다대 다 관계를 일대 다 관계로 바꾼다.

테이블과 자바 객체

• 테이블과 테이블 사이의 관계가 객체와 객체의 관계로 이전된다.
• 쌍방 참조는 안된다.