[데이터베이스] Ch5. SQL 고급1 - 준비된 구문과 Injection Attack, 메타데이터, 주 키 찾기

SQL문을 프로그램에서 직접 문자열을 이어붙이는 방식 대신
준비된 구문 ( Preparedd Statement ) 를 이용하여 작성할 수도 있다.

준비된 구문을 이용하는 데이터베이스 갱신

: PreparedStatement 인터페이스의 pStmt 객체에 conn.prepareStatment 값을 저장함.

: 밑의 코드에 적힌 쿼리문의 경우 총 4열을 insert함.

PreparedStatement pStmt = conn.prepareStatement( "insert into instructor values(?,?,?,?)" );

pStmt.setString(1, "88877"); // 1열에 "88877" String 문자열 삽입
pStmt.setString(2, "Perry"); // 2열에 "Perry"
pStmt.setString(3, "Finance"); // 3열에 "Finance"
pStmt.setInt(4, 125000); // 4열에 Int 125000 삽입
pStmt.executeUpdate(); // 업데이트해야 반영된다. 위의 4개 업데이트
pStmt.setString(1, "88878");
pStmt.executeUpdate();

※ 사용자로부터 입력을 받아 질의에 추가할 때는 항상 준비된 구문을 사용해야함.
※ 그렇지 않을 경우 보안상의 문제가 발생할 수 있음( → SQL Injection Attack )

e.g) SQL Injection Attack 예시1

stmt.executeUpdate( "insert into instructor values ('77987' , 'Kim', 'Physics', 98000)" );
stmt.executeUpdate( "insert into instructor values ('" + ID + "', '" + name + "', '" + dept_name + "', '" + balance + ")"
);
// 의 경우 name 의 입력 값이 "D'Souza" 라면?
e.g)
stmt.executeUpdate( "insert into instructor values ('" + 77889 + "', '" + "D'Souza" + "', '" + CompSci + "', '" + 3838 + ")"
);
// "D'Souza"가 되어 입력 오류 발생
// "','" 의 경우 ',' 를 문자열로 삽입하는 걸 의미함.

"select * from instructor where name = '" + name + "'"
// 의 경우 공격자가 name의 값으로 X' or 'Y' = 'Y 를 입력한다면
"select * from instructor where name ='" X' or 'Y' = 'Y' 가 된다.
"select * from instructor where name = 'X' or 'Y' = 'Y' 가 된다는 것.
// 항상 참의 결과를 가져온다.

• 준비된 구문을 사용하면 사용자가 입력한 값이 SQL문을 구성하는데 바로 사용되지 않고 분리된 데이터로 인식되므로, 위와 같은 SQL Injection Attack 의 위험성이 없어짐!

• 위의 예시의 경우, 준비된 구문을 이용했다면 다음과 같이 SQL문이 구성된다.

select * from instructor where name = 'X\' or \'Y\' = \'Y'

e.g) SQL Injection Attack 예시2

// 공격자가 name을 다음 값으로 입력할 경우.
// X'; update instructor set salary = salary + 10000; --
// -- 는 이후부터 comment로 처리하도록 함.
"select * from instructor where name = '" + "X'; update instructor set salary = salary + 10000; --" +"'"
즉,
select * from instructor where name = 'X'; update instructor set salary = salary + 10000; --' 의 의미가 된다.
cf. 일부 시스템은 세미콜론으로 구분된 구문을 사용하여 한 번의 JDBC execute 메서드 호출로 여러 개의 구문이 실행되는 것을 허용한다.

메타데이터

• excuteQuery() 메서드를 사용해 질의를 전달할 땐 질의 결과가 ResultSet 인터페이스의 객체에 저장된다.

• ResultSet은 질의 결과에 대한 메타데이터를 제공하는 ResultSetMetaData 객체를 반환하는 getMetaData() 메서드를 가지고 있다.

ResultSetMetaData rsmd = rs.getMetaData();
// ResultSetMetaData 인터페이스의 객체 rsmd
// ResultSet 인터페이스의 객체 rs
for ( int i = 1; i <= rsmd.getColumnCount(); i++ ) {
   System.out.println(rsmd.getColumnName(i));
   System.out.println(rsmd.getColumnTypeName(i));
   // 결과집합의 각 튜플의 열의 이름과 타입을 순서대로 출력
}

• 데이터베이스에 관련한 메타데이터(ex. 데이터베이스 스키마)는 SQL DDL에 의해 선언된다.
• 응용 프로그램은 JDBC를 통해 데이터베이스에 대한 메타데이터를 얻을 수 있다.
• 초기에는 모든 릴레이션의 단일한 이름 공간(name space)를 가지고 있었다.
• 현재는 3단계 계층 구조를 제공한다.
• 카탈로그(catalog) > 스키마(schema) > 릴레이션/뷰
• 일부 DBMS(ex. PostgreSQL)에서 카탈로그는 데이터 베이스와 동의어이다.
• 데이터베이스에 대한 메타데이터는 DatabaseMetaData 를 통하여 얻을 수 있다.

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables("","","%",new String[] {"TABLE"});
// 현재 카탈로그, 현재 스키마, 모든 테이블, TABLE 타입의 테이블
whlie(rs.next()){
  System.out.println(rs.getString("TABLE_NAME"));
}
// "" 는 현재 Catalog/Schema를 의미한다.
// new String[] {"TABLE"} 은 사용자가 만든 테이블만 반환되도록 한다. VIEW, SYSTEM TABLE, null 등이 사용될 수 있으며,
// null 이 사용되면 시스템 내부 테이블을 포함한 모든 테이블이 반환된다.

주 키 찾기

• DatabaseMetaData가 제공하는 getPrimaryKey()를 사용하면 릴레이션의 주 키를 찾을 수 잇다.

DatabaseMetaData dmd = connection.getMetaData();
ResultSet rs = dmd.getPrimaryKeys("","",tableName); // 현재 카탈로그, 현재 스키마, tableName 이름을 가진 Table
whlie(rs.next()) {
   System.out.println(rs.getString("KEY_SEQ"), rs.getString("COLUMN_NAME");
}