MySQL, MSSQL, ORACLE 별 테이블명/컬럼명 조회 (스키마 객체)

해당 글은 유현수님의 SQL Injection 공격 자동화 기법 책을 공부하며 정리한 글입니다.

---

DB 내 데이터를 조회하기 위해선?

▶ SELECT 구문 사용

▶ SELECT 구문에 사용할 TABLE과 COLUMN 정보 필요

▶ 어떻게 획득해야 하는가?

 

DB에 직접 연결이 가능하면, SHOW 또는 DESC로 확인 가능.

그게 아니라면?

▶ 해당 정보들은 데이터베이스 별 스키마 객체로서 조회 가능한 형태로 제공됨.

 

 

스키마(Schema)

데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조. DBMS가 주어진 설정에 따라 데이터베이스 스키마를 생성.

데이터베이스 사용자가 자료를 저장/조회/삭제/변경할 때 DBMS는 자신이 생성한 데이터베이스 스키마를 참조하여 명령을 수행한다.

 

객체(Object)

데이터베이스에서 객체는 테이블, 인덱스, 프로시저, 트리거, 뷰, 시퀀스 등 데이터베이스 내부에서 생성되어 관리되는 모든 대상을 의미한다.

 

=> 스미카 객체 정보를 조회하는 SQL 명령어를 확인하면 테이블 및 칼럼 목록을 획득할 수 있다.

해당 정보는 데이터 딕셔너리에 저장되어 있음.

 

 

데이터 딕셔너리(Data Dictionary)

- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블.

- 사용자가 테이블을 생성하거나 변경하는 등 작업 시 데이터베이스 서버에 의해 자동으로 갱신되는 테이블임.

- 사용자의 직접적인 수정/삭제 불가능

- 사용자가 데이터 딕셔너리 조회 시 암호같은 기호만 보여질 뿐 내용은 알 수 없음.

- 데이터를 설명하기 위한 데이터. 메타 데이터의 집합

 

데이터베이스	스키마 객체
MSSQL	Information_schema, sys.objects, sys.columns, sysobjects, syscolumns
MySQL	Information_schema
Oracle	all_tables, all_tab_columns, tabs, cols, tab, col

mysql database로서 존재하는 information_schema

 

---

MySQL

MySQL의 스키마에서 테이블 및 칼럼정보 획득에 연관된 객체

- TABLES: 현재 데이터베이스에 존재하는 테이블의 정보 제공

- COLUMNS: 현재 데이터베이스에 존재하는 테이블 칼럼의 정보 제공

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS;

 

TABLES의 칼럼들이 궁금해졌다.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='TABLES';

 

SELECT TABLE_NAME,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;

TABLE TYPE

- BASE TABLE: 일반적인 테이블

- VIEW: 뷰

- SYSTEMm VIEW: Information_schema 테이블

 

DBMS에서 자동으로 생성한 SYSTEM VIEW는 DB를 관리하기 위한 데이터가 저장됨.

▶ 정보 획득에는 불필요.

정보 획득에 필요한 BASE TABLE만 조회해보자.

SELECT TABLE_NAME,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';

 

이전에 따로 만들어 둔 member 테이블의 칼럼 정보를 조회해보자.

 

---

MSSQL

MSSQL의 스키마에서 테이블 및 칼럼정보 획득에 연관된 객체

- TABLES: 현재 데이터베이스에서 현재 사용자가 접근할 수 있는 테이블 정보

- COLUMNS: 현재 데이터베이스에서 현재 사용자가 접근할 수 있는 칼럼 정보

- SYS.OBJECT: 고유하게 컴파일 된 스칼라 사용자 정의 함수를 포함하는 데이터베이스 내에서 만든 각 사용자 정의 스키마 범위 개체에 대한 행 

- SYS.COLUMNS: 뷰 또는 테이블과 같이 열을 갖고 있는 개체의 각 열에 대한 행.

 

MSSQL도 MySQL과 동일하게 information_schema 스키마 객체를 통해 테이블과 칼럼의 목록을 조회할 수 있다.

SELECT table_name FROM information_schema.tables WHERE table_type='base table'
select column_name, data_type from information_schema.columns where table_name='member'

 

다른 방식으로는 SYSTEM VIEW를 통한 조회가 있다.

시스템 카탈로그 뷰인 SYS.OBJECTS와 SYS.COLUMNS

 

TABLE명 알아내기

- 테이블명 알아내기: SYS.OBJECTS의 NAME칼럼

- 시스템 테이블 등의 다양한 테이블 조회를 피하기 위해 TYPE='u'

- U 타입: 사용자 정의 테이블(user-defined table)

 

COLUMN 명 알아내기

COLUMN명을 알아내기 위해서는 테이블의 object_id가 필요하다.

SELECT name, object_id FROM sys.objects WHERE TYPE='u'

그리고 SYS.COLUMNS의 NAME 칼럼을 통해 칼럼명을 알 수 있다.

SELECT name FROM sys.columns WHERE object_id=885578193

해당 칼럼의 타입을 알아내고 싶다면, USER_TYPE_ID 칼럼을 통해 조회하면 됨.

그러나 해당 칼럼은 데이터 타입을 숫자로 표기하고 있다. 이 때 TYPE_NAME() 함수를 사용

유형의 숫자 표기

유형의 문자 표기

 

그러나 이러한 시스템 뷰를 이용한 방법은 테이블 명으로 칼럼명 조회가 불가하고, OBJECT_ID를 추가로 획득해야 함. SQL Injection 공격 시 사용하기엔 너무 복잡하다. 

이 때.. SQL 쿼리의 셀프 조인(self join)을 이용하면 테이블 명을 이용해서 컬럼 명 조회가 가능하다.

 

SELECT col.name, TYPE_NAME(col.user_type_id) FROM sys.columns col, sys.objects obj WHERE col.object_id=obj.object_id AND obj.name='member'

 

---

ORACLE

오라클 데이터베이스의 테이블과 칼럼 조회는 MySQL과 MSSQL과는 좀 다름.

 

오라클 CREATE TABLE

create table lab_member (
idx number(11) primary key, 
name varchar(20), 
userid varchar(40), 
phone varchar(20), 
email varchar(40), 
address varchar(128)
);

idx에 auto_increment 기능을 줄 것. 그러나 오라클은 해당 기능이 없다.

시퀀스를 이용해야 함.

CREATE SEQUENCE tmp_seq START WITH 1 INCREMENT BY 1 MAXVALUE 999 CYCLE NOCACHE;

- CREATE (스키마명.시퀀스명) START WITH (시퀀스 생성이 시작되는 값) INCREMENT BY (시퀀스 증가 단위) MAXVALUE (시퀀스 최대 숫자)

- NOCYCLE은 초기값부터 사시 시작하지 않음을 의미함.

그리고 idx 값을 삽입할 때 시퀀스를 사용.

INSERT INTO member VALUES(tmp_seq.NEXTVAL)

NEXTVAL 함수를 통해 자동으로 1씩 증가되어 값이 추가

CLI는 너무 짤려서 QueryBox로 옮겼다.

 

오라클 테이블명 조회

테이블 정보 조회가 가능한 스키마 객체는 다음과 같다.

- ALL_TABLES: 현재 사용자가 접근할 수 있는 모든 테이블에 관한 정보

- USER_TABLES: 현재 사용자 소유의 관계형테이블에 관한 정보 (TABS: USER_TABLES의 동의어)

- DBA_TABLES: 모든 데이터베이스 내의 관계형테이블에 관한 정보 (DBA에 의해서만 조회 가능)

SELECT table_name FROM tabs

- 테이블 명이 표기되는 칼럼은 TABLE_NAME

 

오라클 칼럼명 조회

- ALL_TAB_COLUMNS: 현재 사용자가 접근할 수 있는 모든 테이블, 뷰, 클러스터에 대한 칼럼 정보

- USER_TAB_COLUMNS: 현재 사용자 소유의 모든 테이블, 뷰, 클러스터에 대한 칼럼 정보 (동의어 COLS)

- DBA_TAB_COLUMNS: 모든 테이블, 뷰, 클러스터에 대한 칼럼정보 (DBA에 의해서만 조회 가능)

SELECT column_name FROM cols WHERE table_name='lab_member'

왜그런진 모르겠는데 table_name 부분은 대소문자 구분을 하는 듯함.

컬럼명을 조회할 수 있는 테이블 COLS 대신 COL 가능 - COL 이용한 칼럼정보 조회는 CNAME 칼럼 통해

물론 테이블명 조회할 수 있는 테이블 TABS 대신 TAB 가능 - TAB 이용한 테이블명 조회는 TNAME 칼럼 통해

(구버전과의 하위호환성 때문에 사용 가능)

SELECT tname FROM tab
SELECT cname FROM col WHERE tname='lab_member'