이클립스에서 데이터베이스를 사용하기 위해서는 언어의 사용하는 언어가 같아야 합니다.
쉽게 생각해서 두 명의 사람이 대화를 하는데 한 사람은 한국어만 사용할 수 있고, 한 사람은 일본어만 사용할 수 있다면, 두 사람 간의 공통적인 언어체계가 없기 때문에 소통이 이루어지지 않습니다.
따라서, 자바와 데이터베이스 사이에 "드라이버"를 두면 드라이버가 자바와 데이터베이스가 소통할 수 있도록 중간에서 명령어를 변환해주는 역할을 합니다.
JDBC(Java Database Connectivity) : 자바를 통해서 데이터베이스에 접근하도록 도와주는 라이브러리입니다.
각 언어로 작성한 SQL명령을 JDBC드라이버를 통해서 DB에 전달해 주면,
DB에서 실행한 결과를 드라이버를 통해 다시 각 언어로 전달해 줍니다.
---
흔히 저희가 데이터베이스를 실생활에서 사용하는 경우의 수는 데스크톱 어플리케이션을 이용하거나 웹 애플리케이션을 이용합니다. 각 경우에 대한 설명을 간단히 설명하면 다음과 같습니다.
데스크롭 애플리케이션 -> user가 한 명입니다. (혼자만 사용하는 개인 프로그램.) 일단 이것을 공부할 것임
웹 애플리케이션 -> user가 여러 명입니다. (인터넷을 사용하는 모든 사람이 사용할 수 있는 공용 프로그램)
ㄴ> 결론적으로 취업해서 사용해야할 기술은 이거다,,,,
준비사항
JDBC준비
driver manager를 이용하여 connection을 획득해야 합니다. -> 연결이 되어야 DB에 접근할 수 있습니다.
0. connection : 연결을 도와주는 객체
1. 작업을 하고자 할 때는 작업 도구를 가져옵니다. Statement : SQL명령 처리 객체
2. resultSet : 조회를 위한 결과행 처리 객체
접속 순서 (각 단계에서의 작업이 완료되면 반드시 close를 해서 자원을 반납해줘야 합니다. DB는 공유 자원이므로 내가 사용하고 close하지 않으면 다른 사용자가 접근할 수 없습니다. Ex. 내가 공용 도서관에서 책을 빌리고 반납하지 않으면 다른 사용자는 내가 빌린 책을 읽을 수 없습니다.)
1. drivermanager (클래스)를 이용하여 연결합니다.
2. connection (인터페이스) : 연결관리 객체. 이것이 끊어지면 모든 작업이 끝납니다. 모든 작업이 완료되면 반드시 close를 사용해서 연결을 해제해줘야 합니다.
3. statement or preparedstatement (인터페이스) 후자가 전자를 상속받아서 확장된 개념
4. resultset (인터페이스)
Statement와 PreparedStatement의 차이점
PreparedStatement는 Statement를 상속받는 자식클래스입니다.
따라서 PreparedStatement는 Statement의 기능을 모두 물려 받고, 거기서 조금더 확장된 기능을 가지고 있습니다.
| statement(부모) | preparedstatement(자식) |
| SQL셋팅(보안이 취약) | SQL셋팅(보안이 좋다) |
| SQL명령 -> DB전송 | SQL명령 -> DB전송 |
| 명령받고 | 명령받고 |
| 구분분석 | 구분분석(1회만 체크) |
| 실행 | 캐싱(저장) |
| 실행(처리 성능이 좋다) |
Statement
1. SQL명령을 DB에 전송합니다
2. DB에서 받은 명령을 구문오류가 없는지 분석하고 다시 돌려보냅니다.
-> 명령을 받고 분석하고 실행하는 것을 SQL명령이 도착할 때 마다 반복합니다.
-> 같은 명령이 들어와도 같은 과정을 반복합니다. -> 비교적 느린 속도
-> SQLInjecttion이 가능하다. 해커가 사용자가 전송하는 동일한 명령문을 가로채서 내용을 바꿔서 주입하는 것
PreparedStatement
전달받은 SQL명령을 처음 들어올때만 분석을 해서 오류가 없다면 저장을 합니다.
처음에 SQL명령을 보낼때 다음과 같이 변수를 보내게 됩니다.
String sql = "select * from test_member where id=? and pwd=?"
DB는 전달받은 미완성된 SQL 명령을 구문 분석하여 문법적 오류가 없다면 (캐싱) 저장합니다.
이후에 사용자는 똑같은 명령을 보내는 것이 아니라 처음에 보낸 SQL 문의 변수 값(parametor 변수)을 전달합니다.
DB는 전달받은 변숫값을 일전에 들어온 미완성된 SQL문에 매칭하여 명령을 실행합니다.
따라서, SQL Injection이 불가능하기 때문에 보안성이 좋다고 할 수 있습니다.
ㄴ> Statement와는 다르게 처음에만 미완성된 SQL명령문을 보내고 이후에는 변수를 채워주기 위한 파라미터 변수값을 전송하기 때문에 해커가 중간에 가로챌 명령문이 없기 때문입니다.
자세한 내용은 이번 포스팅의 마지막 부분에서 예시와 함께 설명하겠습니다.
이클립스에서 DB를 사용할 수 있도록 순서에 따라서 직접 연결하고 사용해보도록 하겠습니다.
JavaSE환경에서 새 자바 프로젝트를 만들고 드라이버(자바언어와 DB언어를 소통시켜줄 객체)를
해당 프로젝트에 넣어줘야 합니다.
순서는 다음과 같습니다.
프로젝트 우클릭 -> Build Path -> Configure Build Path -> Libraries탭 -> Add External JARs ->
내가 가지고 있는 jdbc 추가하기 -> apply and close
실행 순서
0. DB(Oracle) Driver를 Load 하여 메모리에 적재해야 합니다.
|
1
2
3
4
5
6
7
8
9
10
|
static {
//new OracleDriver();
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
|
cs |
메인 메서드가 실행되기 전에 연결을 위한 사전 준비가 필요합니다. 이런 경우 자바에서는 가장 먼저 실행할 수 있도록 static블록에다가 사전 설정을 해줍니다.
단, 이때 주의하실 점이 하나 있다면 위 코드 상에서 주석처리된 것처럼
참조 변수 없이 객체로 생성하면 안 됩니다.
이렇게 생성하면 드라이버 객체가 힙 영역에 생성이 되는데. 힙 영역은 가비지 컬렉터(GC)가 참조 변수가 없는 즉, 사용하지 않는 객체를 제거하는 영역입니다.
따라서 위 코드처럼 객체로 생성을 하는 것은, 잠깐 테스트 용으로 사용할 때는 사용해도 되지만, 지속적으로 드라이버를 사용해야 하는 경우 어느 시점에서 GC에 의해 삭제될 수 있기 때문에 위헙합니다.
따라서 try-catch문으로 예외처리를 미리 해주고, Class.forName()을 사용하여 드라이버가 저장되어 있는 위치를 프로그램이 실행되기 전에 메모리에 적재해줍니다.
1. Connection 얻어오기
|
1
2
3
4
5
6
7
8
9
10
11
|
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "test";
String pwd = "test";
Connection conn = DriverManager.getConnection(url, user, pwd);
System.out.println("---success connection---");
//4. 다 사용한 다음에 닫아주기
conn.close();
|
cs |
사전 준비로 드라이버의 경로를 명시해주었다면, 이제는 연결 객체 Connection을 사용해야 합니다.
이클립스에서 오라클을 처음 사용했을 때, 다음과 같이 ip주소와 DBid, 포트번호를 설정해준 적이 있을 겁니다.
아래에 설정된 Connction Url값과, 유저 아이디, 유저 비밀번호를 각각 문자열로 저장한 후에
Connection객체를 위의 코드와 같이 선언하고 실행하면 연결이 완료됩니다.
객체를 사용하고 종료할 때는 반드시 객체를 반납해야 하는데 이를 까먹지 않도록 미리 닫아주고 그 사이에 코드를 작성하겠습니다. 이런 작성방식을 샌드위치 코드라고 부릅니다.
2. Statement 객체 얻어오기
|
1
2
3
4
5
6
7
8
|
Statement stmt = conn.createStatement();
//String sql = "create table ttt(no int)";
String sql = "drop table ttt";
stmt.executeUpdate(sql);//테이블을 생성함
stmt.close();//2번과정 종료시 닫기
|
cs |
Statement 객체는 자바에서 SQL 명령을 처리해주는 객체입니다. 이 객체를 통해서 모든 SQL명령(DDL, DML, DCL)을 처리할 수 있습니다.
단, 명령어마다 처리할 수 있는 메서드가 다릅니다. 간단하게 일반화하면 다음과 같습니다.
- executeQuery(sql) : 조회 명령인 Select명령만 이 메서드를 사용합니다.
- executeUpdate(sql) : 조회 명령인 Select를 제외한 모든 SQL명령은 이 메서드를 사용합니다.
단, 한 가지 고려사항은 계정마다 처리할 수 있는 SQL명령의 권한이 다르기 때문에 사용자가 드라이버로 연결한 계정이 무엇인지에 따라서 처리할 수 있는 SQL명령의 종류가 다를 수 있습니다.
ㄴ> 현재 연결된 계정의 "권한"안에서 할 수 있는 모든 명령을 수행할 수 있습니다.
Ex)
연결한 계정이 관리자 계정이라면 -> 신규 사용자 계정을 생성할 수 있습니다.
연결한 계정이 DBO 계정이라면 -> 다른 사용자에게 자신의 영역의 데이터를 접근하고 조회할 수 있는 권한을 부여할 수 있습니다.
객체는 다음과 같은 방식으로 선언합니다.
Statement stmt = conn.createStatement();
이제 실질적으로 처리할 SQL명령을 문자열 변수 sql에 저장해줍니다.
String sql = "create table ttt(no int)";
stmt.executeUpdate(sql);
테이블 "ttt"를 생성합니다. 칼럼명은 no 자료형은 int입니다.
String sql = "insert into ttt values(1)";
stmt.executeUpdate(sql);
no 칼럼에 값 1을 추가합니다.
String sql = "update ttt set no=2 where no=1";
stmt.executeUpdate(sql);
no=1인 데이터를 2로 수정합니다.
String sql = "delete from ttt";
stmt.executeUpdate(sql);
테이블에 있는 모든 데이터를 삭제합니다.
String sql = "drop table ttt";
stmt.executeUpdate(sql);
테이블 "ttt"를 삭제합니다.
위와 같이 기본적인 SQL명령을 문자열 변수 sql에 저장하고, statement객체의 메서드인 executeUpdate메서드에 인자로 전달해주면 statement객체가 SQL명령을 수행해줍니다.
위의 명령은 명령을 처리하는 객체이기 때문에 결과를 바로 확인할 수 없습니다.
따라서 이제는 결과를 확인하기 위해 결과행을 처리해주는 객체를 만들어주어야 합니다.
3. ResultSet 객체 얻어오기 (결과행 처리 객체)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
String sql = "select * from dept";
ResultSet rs = stmt.executeQuery(sql);
//전체조회
while(rs.next()) {
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
System.out.printf("[%d-%s-%s]\n", deptno, dname, loc);
}
|
cs |
이번 단계에서도 처리할 SQL명령을 문자열 변수 sql에 저장해줍니다.
다만 이전과는 다르게 위에서 설명했다시피, 조회를 하는 select 명령만 유일하게 executeQuery를 사용한다고 했습니다.
결과행 처리 객체를 생성함과 동시에 연산하는 과정을 단계별로 설명하면 다음과 같습니다.
1. statement객체로 조회 명령을 처리하고 그 결과를 resultset의 객체인 rs에게 전달합니다.
ResultSet rs = stmt.executeQuery(sql);
2. rs라는 객체는 1에서 전달받은 전체 정보를 관리하는데, 그 내용은 BOF - 데이터 영역(테이블) - EOF로 구성되어 있습니다. 자세한 사항은 아래 이미지를 참고해주세요.
3. rs 객체는 내부적으로 Cursor를 가지고 있는데, 처리된 결과(데이터)를 처음으로 전달받았을 때 커서는 BOF를 가리키고 있습니다.
4. 전체적인 데이터를 조회하기 위해서는 이 커서를 사용해서 데이터를 조회해야 합니다.
5. 커서가 가리키는 위치를 이동하는 메서드로 rs.next() 메서드가 있습니다. 이는 반환형으로 boolean값을 반환하는데, data영역을 가리킬 때만 true, (BOF, EOF)를 가리킬 때는 false를 반환합니다.
6. 이 메서드를 응용해서 반복문을 작성하고자 할 때, 데이터의 개수에 따라 반복의 횟수가 달라지므로 정확한 반복 횟수를 알 수 없기 때문에 while문을 다음과 같이 사용하겠습니다.
7. while (rs.next) {} 이건 형식을 바꿀 수 없는 공식이기 때문에 암기해야 합니다.
8. 가장 처음 커서는 BOF를 가리키고 있다고 했습니다. 이 시점에서 next 메서드를 실행하면 바로 데이터 영역의 첫 번째를 가리키므로 현재 커서가 가리키는 값을 수행하다가, 커서의 위치가 EOF에 도달하면 next의 반환 값으로 false를 반환하여 반복문이 종료됩니다.
9. 커서가 가리키고 있는 위치의 필드 값을 얻어오는 코드는 다음과 같습니다.
필자는 연습 DB 중에서 다음과 같은 dept테이블을 읽어왔습니다.
모든 데이터를 스트링으로 받아도 되지만, 혹여나 정수형을 문자열로 받았다면, 나중에 정수형 데이터를 연산을 해야 할 때, 캐스팅을 해줘야 합니다.
따라서 테이블에 저장된 칼럼의 자료형을 확인하시고, 초기에 정확한 자료형을 일치시켜주시는 게 가장 좋습니다.
메서드의 인자로는 칼럼의 이름 or 헤딩을 지정했다면 헤딩명(조회 시 부여되는 임시 필드명)을 전달해줍니다.
웬만하면 헤딩 명을 지정하지 않고 사용하는 것이 헷갈리지 않을 수 있습니다.
나중에 연산을 하게 되는 경우가 생기는 경우에는 연산 필드명이 굉장히 길어지기 때문에 이럴 때는 헤딩을 사용해주는 것이 효율적입니다.
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
좀 더 단순하게 결과를 조회할 수도 있습니다. 테이블에 칼럼의 인덱스를 전달해주는 방법도 있습니다. (DB는 1 base)
하지만 이 방법은 보편적으로 사용하기엔 위험한 방식입니다.
예를 들어, 사용자가 조회를 할 때, 칼럼의 순서를 바꿔서 조회 명령을 실행하면, 값이 섞일 우려가 있습니다.
int deptno = rs.getInt(1);
String dname = rs.getString(2);
String loc = rs.getString(3);
4. 사용이 종료된 객체 닫기
|
1
2
3
|
rs.close(); //3번과정 종료시 닫기
stmt.close();//2번과정 종료시 닫기
conn.close();//1번과정 종료시 닫기
|
cs |
각 단계에서의 작업이 완료되면 반드시 close를 해서 사용했던 객체를(자원) 반납해줘야 합니다.
대신 반납의 순서는 반드시 선언한 순서의 역순서로 해야 합니다.
DB는 공유 자원이므로 내가 사용하고 close하지 않으면 다른 사용자가 접근할 수 없습니다.
Ex. 내가 공용 도서관에서 책을 빌리고 반납하지 않으면 다른 사용자는 내가 빌린 책을 읽을 수 없습니다.
5. SQL Injection
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
create table test_member
(
idx int,
id varchar2(100) not null,
pwd varchar2(100) not null,
name varchar2(100) not null
)
--기본키
alter table test_member
add constraint pk_test_member_idx primary key(idx);
--unique
alter table test_member
add constraint unique_test_member_id primary key(id);
--sample date
insert into test_member values(1, 'one', '1234', '일길동');
insert into test_member values(2, 'two', '1234', '이길동');
insert into test_member values(3, 'three', '1234', '삼길동');
insert into test_member values(4, 'four', '1234', '사길동');
insert into test_member values(5, 'five', '1234', '오길동');
commit
|
cs |
기본적으로 위와 같이 아주 간단한 데이터베이스를 구현했습니다.
test_member가 관리하는 DB는 아래 이미지와 같습니다.
이제부터 SQL명령을 처리하는 객체를 두 개로 나누어서 사용한 코드를 보고 그에 따른 결과를 비교하면서 살펴보겠습니다.
StateMent로 간단한 Login DB 구현하기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MyMain_DBTest3_SQLInjection_YES {
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "test";
String pwd = "test";
Connection conn = DriverManager.getConnection(url, user, pwd);
System.out.println("---success connection---");
//2.Statment객체 얻어오기
Statement stmt = conn.createStatement();
String sql = "select * from test_member where id='one' and pwd='0000'";
ResultSet rs = stmt.executeQuery(sql);
//전체조회
while(rs.next()) {
int idx = rs.getInt("idx");
String id = rs.getString("id");
String pwd1 = rs.getString("pwd");
String name = rs.getString("name");
System.out.printf("[%d-%s-%s-%s]\n", idx, id, pwd1, name);
}
//4. 다 사용한 다음에 닫기(열린 역순으로 닫는다.)
rs.close(); //3번과정 종료시 닫기
stmt.close();//2번과정 종료시 닫기
conn.close();//1번과정 종료시 닫기
}
}
|
cs |
Statement는 PreParedStateMent와 다르게 두 가지의 특징이 있습니다.
- SQL 명령문을 매번 보낸다.
- DB는 명령을 받을 때마다 구문을 분석하고 실행합니다.
위 두 가지 특징으로 인해 해커로부터 SQL Injection을 당할 위험이 있습니다.
위의 소스 코드를 보면 전달하는 SQL명령문은 "test_member에 있는 데이터를 조회할 건데, where조건에 맞는 데이터만 보여달라는 의미입니다."
그런데 사용자가 같은 명령어를 여러 번 보내다 보면, 해커가 중간에 그 명령을 가로채서 임의의 명령을 추가할 수 있습니다. 이러한 과정을 SQLInjection이라고 합니다.
위의 소스코드에서 해커가 "or 1 = 1"이라는 명령만 추가해서 DB로 보내도 다음과 같이 비밀번호 혹은 아이디를 틀려도 DB에 저장되어 있는 모든 데이터가 조회가 됩니다.
이는 데이터베이스를 관리하는 입장에서 아주 치명적인 보안 약점입니다. 그래서 보안성이 좋은 PreParedStateMent을 사용하는데 다음 예시에서 살펴보겠습니다.
PreParedStateMent로 간단한 Login DB 구현하기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class MyMain_DBTest4_SQLInjection2_NO {
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "test";
String pwd = "test";
Connection conn = DriverManager.getConnection(url, user, pwd);
System.out.println("---success connection---");
// 1 2 <- parameter index(전달인자)
String sql = "select * from test_member where id=? and pwd=?" + " or 1=1";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "one");
pstmt.setString(2, "1224");
ResultSet rs = pstmt.executeQuery();
//전체조회
while(rs.next()) {
int idx = rs.getInt("idx");
String id = rs.getString("id");
String pwd1 = rs.getString("pwd");
String name = rs.getString("name");
System.out.printf("[%d-%s-%s-%s]\n", idx, id, pwd1, name);
}
//4. 다 사용한 다음에 닫기(열린 역순으로 닫는다.)
rs.close(); //3번과정 종료시 닫기
pstmt.close();//2번과정 종료시 닫기
conn.close();//1번과정 종료시 닫기
}
}
|
cs |
위의 코드와 같은데 PreParedStateMent을 사용한 것만 다릅니다.
먼저 위와 같이 PreParedStateMent만의 특징을 두 가지만 꼽아보자면
- SQL명령을 처음에 단 한 번만 보내는데, 이 명령문은 변수가 포함되어 있어 미완성 되어 있습니다.
- PreParedStateMent객체로 보낸 SQL명령은 처음 보냈을 때 단 한번만 구문분석을 합니다.
- 이후에 사용자가 같은 정보를 보낼 때는 변수의 값을 세팅하여 미완성된 명령문을 완성할 수 있도록 파라미터 변수를 전송합니다.
Statement를 사용한 방법과의 차이점
1. 동일한 명령문의 전달하는 경우
Statement는 동일한 명령문을 계속해서 보내게 됩니다.
ㄴ> 해커가 명령문을 가로채 주입할 수 있는 기회가 있습니다.
PreParedStateMent는 초기에 단 한 번만 보내고 그 뒤에는 변수를 보냅니다. 즉 명령문은 단 한번 보냅니다.
ㄴ> 해커가 명령문을 가로챌 수가 없습니다. [처음 이후로 보내지 않기 때문입니다..]
2. 구문 분석의 횟수
Statement는 같은 명령이라도 전달할 때마다 구문을 분석합니다.
ㄴ> 이로 인해서 해커가 가로채고 주입한 명령문을 새롭게 또 분석하기 때문에 보안성이 떨어진다.
PreParedStateMent는 초기에 단 한 번만 분석하여 저장하고, 이후에는 전달받은 파라미터 변수로 명령문을 완성시켜 실행합니다.
ㄴ> DB는 초반에 도착한 미완성된 명령문으로 단 1회만 구문분석을 합니다. 이후에는 전달받은 파라미터 변수로 명령문을 완성시키면서 실행만 합니다.
'데이터베이스' 카테고리의 다른 글
| Tomcat다운로드&환경설정_HTML_국비_DAY40 (0) | 2022.04.26 |
|---|---|
| JDBC 실습_국비_DAY39 (0) | 2022.04.22 |
| DB 모듈화 (0) | 2022.04.22 |
| Join (0) | 2022.04.20 |
| ERD(Entity_Relation_Diagram) (0) | 2022.04.20 |
