MYSQL

DEFINITIONS:

INFORMATION SCHEMA: The INFORMATION_SCHEMA database contains metadata about the databases and tables present on the server. Tables in this DB can't be called directly using SELECT statements.

DOT OPERATOR: This is used to reference a table present in another DB. e.g,

SELECT * FROM myDatabase.users;

SCHEMATA: The SCHEMATA table in the INFORMATION_SCHEMA database contains information about all databases on the server. It is used to obtain database names so we can then query them. The SCHEMA_NAME column contains all the database names currently present.

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| ilfreight          |
| dev                |
+--------------------+
6 rows in set (0.01 sec)
 * The first three databases (mysql, information_schema_performance_schema, along with sys) are default MySQL databases and are present on any server. they are usually ignore during DB enumeration.
 

RETRIEVING DATA

VERSION INFO

MySQL [(none)]> select version();
 +-------------------------+
 | version()               |
 +-------------------------+
 | 8.0.27-0ubuntu0.20.04.1 |
 +-------------------------+
 1 row in set (0.001 sec)

DATABASES

MySQL [(none)]> show databases;                                                                          
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
 4 rows in set (0.006 sec)

TABLE

MySQL [(none)]> use mysql;
MySQL [mysql]> show tables;
 +------------------------------------------------------+
 | Tables_in_mysql                                      |
 +------------------------------------------------------+
 | columns_priv                                         |
 | component                                            |
 | db                                                   |
 | default_roles                                        |
 | engine_cost                                          |
 | func                                                 |
 | general_log                                          |
 | global_grants                                        |
 | gtid_executed                                        |
 | help_category                                        |
 | help_keyword                                         |
 | help_relation                                        |
 | help_topic                                           |
 | innodb_index_stats                                   |
 | innodb_table_stats                                   |
 | password_history                                     |
 ...SNIP...
 | user                                                 |
 +------------------------------------------------------+
 37 rows in set (0.002 sec)

mysql> SELECT * FROM {tableName};
 * the asterisk symbol acts as a wildcard & selects all the columns
 * the from keyword is used to denote the table to select from
 
mysql> SELECT column1, column2 FROM {tableName};

DISPLAYING COLUMNS

mysql> SHOW COLUMNS from {tableName} 

mysql> select host, unique_users from host_summary;
 +-------------+--------------+                   
 | host        | unique_users |                   
 +-------------+--------------+                   
 | 10.129.14.1 |            1 |                   
 | localhost   |            2 |                   
 +-------------+--------------+                   
 2 rows in set (0,01 sec)  

SORTING OUTPUT

mysql> SELECT * FROM {tableName} ORDER BY {columnName};
 * by default, sorting is in ascending order
 
mysql> SELECT * FROM {tableName} ORDER BY {columnName} DESC;
 * sort descending

#sorting by multiple columns
mysql> SELECT * FROM {tableName} ORDER BY columnName1 DESC, columnName2 ASC;

FILTERING OUTPUT

mysql> SELECT * FROM {tableName} LIMIT 2;
 * the LIMIT keyword is used to filter results to a small number of records
 
mysql> SELECT * FROM {tableName} LIMIT 1, 2;
 * the offset marks the order of the 1st record to be included, starting
   from 0.
    - the example above starts & includes the 2nd record & return two values

SEARCHING FOR SPECIFIC DATA

mysql> SELECT * FROM {tableName} WHERE {condition};
 * select * from logins where id > 1;
 * select * from logins where username = 'admin';
 * strings & date data type should be surrounded by single quotes or double quotes
   while numbers can be used directly

mysql> SELECT * FROM {tableName} WHERE {columnName} LIKE '{criteria};
 * SELECT * FROM logins WHERE username LIKE 'admin%';
 * like keyword is used to match a certain pattern
 * the % symbol acts like a wildcard & matches all characters after the word 'admin'
   it is used to match zero or more characters
 * the _ symbol is used to match exactly one character

LOGICAL OPERATORS

#AND|&&
mysql> SELECT * FROM employees WHERE first_name LIKE 'Bar%' AND hire_date = "1990-01-01";
mysql> SELECT * FROM logins WHERE username != 'john' && id > 1;
  * in MySQL, any non-zero value is considered true

#OR/||
mysql> SELECT * FROM employees WHERE first_name LIKE 'Bar%' OR hire_date = "1990-01-01";

#NOT/!
mysql> SELECT * FROM employees WHERE first_name != 'Bar%'


SELECT * FROM titles WHERE emp_no > 10000 OR title NOT LIKE '%engineer%';

COUNTING

mysql> SELECT COUNT(*) AS record_count FROM titles WHERE emp_no >
10000 OR title NOT LIKE '%engineer%';

DISPLAYING RESULTS OF MULTIPLE SELECT STATEMENTS

mysql> SELECT * FROM ports;
 +----------+-----------+
 | code     | city      |
 +----------+-----------+
 | CN SHA   | Shanghai  |
 | SG SIN   | Singapore |
 | ZZ-21    | Shenzhen  |
 +----------+-----------+
 3 rows in set (0.00 sec)

mysql> SELECT * FROM ships;
 +----------+-----------+
 | Ship     | city      |
 +----------+-----------+
 | Morrison | New York  |
 +----------+-----------+
 1 rows in set (0.00 sec)

mysql> SELECT * FROM ports UNION SELECT * FROM ships
 +----------+-----------+
 | code     | city      |
 +----------+-----------+
 | CN SHA   | Shanghai  |
 | SG SIN   | Singapore |
 | Morrison | New York  |
 | ZZ-21    | Shenzhen  |
 +----------+-----------+
 4 rows in set (0.00 sec)
 
 * entries from the ports table and the ships table were combined into a single output with four rows
 * with UNIONs the data types of the selected columns on all positions should be the same
 * also, UNION statement can only operate on SELECT statements with an equal number of columns
   else, an error will occur
 * Once we have two queries that return the same number of columns, we can use the UNION operator to extract data from other tables and databases
 

COMBINING DATA FROM MULTPLE TABLES/DATABASES

---

the Union clause is used to combine results from multiple SELECT statements. This means that through a UNION injection, we will be able to SELECT and dump data from all across the DBMS, from multiple tables and databases

---

SELECT * FROM products WHERE product_id = 'user_input'

SELECT * from products where product_id = '1' UNION SELECT username, password from passwords-- '
 * the '1' is used to represent the 'user_input' column
 * the UNION SELECT then dumps and return the username and password entries from the passwords table, assuming the products table has two columns.
    - this is possible as it is a valid secondary SQL statement
    
 * for uneven columns, junk data (either strings, numbers, or NULL) can be passed for the remaining required columns so that the total number of columns we are UNIONing with remains the same as the original query
    - using numbers as junk data is preferable as it is handy way of tracking the payloads positions.
    -  when filling other columns with junk data, ensure that the data type matches the columns data type, otherwise the query will return an error
 * for advanced SQL injection, we may want to simply use 'NULL' to fill other columns, as 'NULL' fits all data types.   

IMPLEMENTATION

root@oco:~$ mysql -h {targetIP} -P {targetPort} -u {username} -p{password}

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

MariaDB [(none)]> USE employees;

MariaDB [employees]> SHOW TABLES;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+

MariaDB [employees]> DESCRIBE employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

MariaDB [employees]> DESCRIBE departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

MariaDB [employees]> SELECT * FROM employees LIMIT 5;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello     | M      | 1986-06-26 |
|  10002 | 1952-12-03 | Vivian     | Billawala   | F      | 1986-12-11 |
|  10003 | 1959-06-16 | Temple     | Lukaszewicz | M      | 1992-07-04 |
|  10004 | 1956-11-06 | Masanao    | Rahimi      | M      | 1986-12-16 |
|  10005 | 1962-12-11 | Sanjay     | Danlos      | M      | 1985-08-01 |
+--------+------------+------------+-------------+--------+------------+

MariaDB [employees]> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+

#DISPLAYING BOTH employees table records & departments via UNION
MariaDB [employees]> SELECT * FROM employees UNION SELECT dept_no, dept_name, NULL, NULL, NULL, NULL FROM departments;
+--------+--------------------+--------------+-----------------+--------+------------+
| emp_no | birth_date         | first_name   | last_name       | gender | hire_date  |
+--------+--------------------+--------------+-----------------+--------+------------+
|---------SNIP--------------------SNIP------------------------SNIP-------------------|
| 10652  | 1961-08-03         | Berhard      | Lenart          | M      | 1986-04-21 |
| 10653  | 1956-09-05         | Patricia     | Breugel         | M      | 1993-10-13 |
| 10654  | 1958-05-01         | Sachin       | Tsukuda         | M      | 1997-11-30 |
| d009   | Customer Service   | NULL         | NULL            | NULL   | NULL       |
| d005   | Development        | NULL         | NULL            | NULL   | NULL       |
| d002   | Finance            | NULL         | NULL            | NULL   | NULL       |
| d003   | Human Resources    | NULL         | NULL            | NULL   | NULL       |
| d001   | Marketing          | NULL         | NULL            | NULL   | NULL       |
| d004   | Production         | NULL         | NULL            | NULL   | NULL       |
| d006   | Quality Management | NULL         | NULL            | NULL   | NULL       |
| d008   | Research           | NULL         | NULL            | NULL   | NULL       |
| d007   | Sales              | NULL         | NULL            | NULL   | NULL       |
+--------+--------------------+--------------+-----------------+--------+------------+

IDENTIFIYING DB USER

mysql > SELECT USER()
 * ALT: SELECT CURRENT_USER()
 * ALT: SELECT user from mysql.user

IDENTIFYING DB USER PRIVILEGES

mysql > SELECT super_priv FROM mysql.user

READING LOCAL SYSTEM FILES

mysql > SELECT LOAD_FILE('/etc/passwd');

IDENTIFYING READING/WRITING FILE LOCATION

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
 * ALT: SELECT variable_name, variable_value FROM information_schema.global_variables where variable_name="secure_file_priv"
 * The secure_file_priv variable is used to determine where to read/write files from
    - empty value: means can read files from the entire file system
    - directory set: means can only read from the folder specified by the variable
    - null: can't read/write from any directory
 * MariaDB has this variable set to empty by default. 
    - However, MySQL uses /var/lib/mysql-files as the default folder which means that 
      reading files through a MySQL injection isn't possible with default settings. 
    - modern configurations default to NULL which means reading/writing files 
      anywhere within the system is not allowed

IDENTIFYING WRITE ACCESS

mysql> SELECT * from users INTO OUTFILE '/tmp/credentials';
 *  this saves the output of the users table into the /tmp/credentials file in the back-end server
 * The SELECT INTO OUTFILE statement can be used to write data from select queries 
   into files. This is usually used for exporting data from tables.
root@sa:~$ cat /tmp/credentials
1 admin   392037dbba51f692776d6cefb6dd546d
2 newuser 9da2c9bcdf39d8610954e0e11ea8f45f

mysql> SELECT 'this is a test' INTO OUTFILE '/tmp/test.txt';
 * this write the specified string into a text file in the back-end server