Keywords are not case sensitive, however it's common practice to write them in all caps. Some software is case sensitive when handling column/table names.
A simple select query
SELECT * FROM menu;
Querying MySQL flag
select * from flags
Querying Oracle flag
select * from sys.flags
Querying PostgreSQL flag
select * from flags
Querying SQL Server flag
select * from app.dbo.flags
Manual Database Enumeration
There are differences between different database software. Understand their basic syntaxes.
Enumerating MySQL Databases
MySQL Specific Functions and Tables
Getting the version of a MySQL database
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28 |
+-----------+
1 row in set (0.00 sec)
Getting the user connected to a MySQL database
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| user@% |
+----------------+
1 row in set (0.00 sec)
Getting a list of databases or schemas
mysql> select table_schema from information_schema.tables group by table_schema;
+--------------------+
| table_schema |
+--------------------+
| information_schema |
| app |
| exercise |
| mysql |
| performance_schema |
| sqlmap |
| sys |
+--------------------+
7 rows in set (0.00 sec)
Getting a list of tables in a database or schema
mysql> select table_name from information_schema.tables where table_schema = 'app';
+------------+
| table_name |
+------------+
| flags |
| menu |
| users |
+------------+
3 rows in set (0.00 sec)
Getting a list of columns and their data type in a table in MySQL
mysql> select column_name, data_type from information_schema.columns where table_schema = 'app' and table_name = 'menu';
+-------------+-----------+
| column_name | data_type |
+-------------+-----------+
| id | int |
| name | varchar |
| description | varchar |
| price | decimal |
+-------------+-----------+
4 rows in set (0.00 sec)
Enumerating Microsoft SQL Server Databases
Microsoft SQL Server Specific Functions and Tables
Selecting the version of MS SQL Server
1> select @@version;
2> GO
-------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0 (X64)
May 27 2021 17:34:14
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>
(1 rows affected)
Selecting the current user connected to MS SQL Server
1> SELECT SYSTEM_USER;
2> GO
-------------------------------------------------------------------------
sa
(1 rows affected)
Listing databases in MS SQL Server
1> SELECT name FROM sys.databases;
2> GO
name
-------------------------------------------------------------------------
master
tempdb
model
msdb
app
exercise
sqlmap
(7 rows affected)
Listing tables in MS SQL Server
1> select * from app.information_schema.tables;
2> GO
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
------------------------------------------------------------
app dbo menu BASE TABLE
app dbo users BASE TABLE
app dbo flags BASE TABLE
(3 rows affected)
Getting a list of columns and their data type in a table in SQL Server
1> select COLUMN_NAME, DATA_TYPE from app.information_schema.columns where TABLE_NAME = 'menu';
2> GO
COLUMN_NAME DATA_TYPE
------------------------------------------------------------
id int
name varchar
description varchar
price decimal
(4 rows affected)
Enumerating PostgreSQL Databases
PostgreSQL Specific Functions and Tables
Selecting the version of a PostgreSQL database
user=# select version();
version
---------------------------------------------------------------------------
PostgreSQL 13 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
(1 row)
Selecting the current user connected to a PostgreSQL database
user=# select current_user;
current_user
--------------
user
(1 row)
Listing databases in a PostgreSQL database
user=# select datname from pg_database;
datname
-----------
postgres
user
template1
template0
app
exercise
sqlmap
(7 rows)
Listing tables in a PostgreSQL database
app=# select table_name from app.information_schema.tables where table_schema = 'public';
table_name
------------
menu
users
flags
(3 rows)
Getting a list of columns and their data type in a table in a PostgreSQL database
app=# select column_name, data_type from app.information_schema.columns where table_name = 'menu';
column_name | data_type
-------------+-------------------
id | integer
price | numeric
name | character varying
description | character varying
(4 rows)
Enumerating Oracle Databases
Oracle Specific Tables
Selecting the version of an Oracle database
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2 - 64bit Production
PL/SQL Release 11.2 - Production
CORE 11.2 Production
TNS for Linux: Version 11.2 - Production
NLSRTL Version 11.2 - Production
SQL
Selecting the current user connected to an Oracle database
SQL> select user from dual;
USER
------------------------------
SYSTEM
Listing all users or schemas
SQL> select owner from all_tables group by owner;
OWNER
------------------------------
MDSYS
OUTLN
CTXSYS
HR
FLOWS_FILES
SQLMAP
SYSTEM
APEX_040000
XDB
SYS
10 rows selected.
Listing tables in a schema
SQL> select table_name from all_tables where owner = 'SYS' order by table_name;
TABLE_NAME
------------------------------
ACCESS$
ALERT_QT
APPLY$_CHANGE_HANDLERS
APPLY$_CONF_HDLR_COLUMNS
APPLY$_CONSTRAINT_COLUMNS
APPLY$_DEST_OBJ
APPLY$_DEST_OBJ_CMAP
...
MAP_SUBELEMENT$
MENU
METAFILTER$
...
939 rows selected.
Getting a list of columns and their data type in a table in an Oracle database
aSQL> select column_name, data_type from all_tab_columns where table_name = 'MENU';
COLUMN_NAME DATA_TYPE
-----------------------------------------
ID NUMBER
NAME VARCHAR2
DESCRIPTION VARCHAR2
PRICE NUMBER
