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.

ADMINISTRATION: CREATING USERS

MariaDB [(none)]> CREATE USER 'reader'@'localhost';
 Query OK, 0 rows affected (0.002 sec)
 * add a new MariaDB user named reader who is granted only SELECT privileges on the ports table

MariaDB [(none)]> GRANT SELECT ON ilfreight.ports TO 'reader'@'localhost' IDENTIFIED BY 'p@ssw0Rd!!';
 Query OK, 0 rows affected (0.000 sec)

AUTHENTICATION & INTERACTION (LOCAL)

#authentication & interaction
root@oco:~$ mysql -u {username} -p{password}
 * the -u is used to supply the username
 * the -p is used to supply the password
    - -p flag should be passed empty, so we are prompted to enter the 
      password and do not pass it directly on the command line since it could
      be stored in cleartext in the bash_history file, logs or terminal history
    - there shouldn't be any spaces between the -p and the password!
 * when no host is specified, login will default to the localhost server.
 
mysql> show grants
 * view which privileges the logged-in user have

AUTHENTICATION & INTERACTION (REMOTE)

root@oco:~$ mysql -u root -h {docker.cnd.dev} -P 3306 -p 
 * the -h is used to supply the remote host DBMS server
 * the -P refers to the remote port
    - 3306 is the default MySQL/MariaDB port

CREATING DATABASES

mysql> CREATE DATABASE {dbName};
mysql> SHOW DATABASES;
 * this cmd displays all the DBs
mysql> USE {dbName};
 * this cmd switches to the specified DB Name

CREATING TABLES

#creates a table w/ four columns 
mysql> CREATE TABLE {tableName}(
  id INT AUTO_INCREMENT NOT NULL,
  username VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(100) NOT NULL,
  dateOfJoining DATETIME DEFAULT NOW()
  PRIMARY KEY (id)
);
 * the DATETIME data type stores the date when an entry was added
 * the auto_increment property automatically increments the id column
 * the not null property ensures that a particular column is never left empty
 * the unique property ensures that the inserted item are always unique
 * the default keyword is used to speficy the default value
    - the default value will be the current DTG
 * the primary key property is used to uniquely identify each redord in the
   table referring to all data of a record within a table for RDB
 
mysql> SHOW TABLES;
 * this cmd lists tables in the current db
 
mysql> DESCRIBE {tableName};
 * this cmd lists the table structure w/ its fields & data types

ADDING RECORDS (SINGLES)

mysql> INSERT INTO {tableName} VALUES (column1Value, column2Value, column3Value, ...);
 * 1, 'admin', 'password', '2020-07-02'


mysql> INSERT INTO logins(username, password) VALUES('admin', 'adminPass');
 * can skip filling in columns with default values since they are auto filled
    - i.e., id and DTG
 * in this example, the password is in cleartext and not hashed or encrypted.
   it is always recommended to hash/encrypted the password prior to storage in the DB

ADDING RECORDS (MULTIPLE)

mysql> INSERT INTO {tableName}(username, password) VALUES ('john', 'johnPasswd'), ('tom', 'tomPasswd');

RETRIEVING DATA

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};

mysql> SHOW COLUMNS from {tableName}

REMOVING TABLES & DATABASES

mysql> DROP TABLE {tableName};
 * the DROP statement permanently deletes tables & databased w/o confirmation
    - proceed w/ caution
    
mysql> DROP DATABASES {databaseName};

ALTERATIONS/MODIFICATIONS: ADDING NEW COLUMNS

mysql> ALTER TABLE {tableName} ADD newColumnName INT;
 * the alter keyword is used to rename any tables or to rename any of
   the table's fields as well as to delete or add new columns to
   existing tables

ALTERATIONS/MODIFICATIONS: RENAME COLUMNS

mysql> ALTER TABLE {tableName} RENAME COLUMN newColumnName TO oldColumnName;

ALTERATIONS/MODIFICATIONS: CHANGING COLUMN DATA TYPE

mysql> ALTER TABLE {tableName} MODIFY oldColumnName DATE;
 * date is the new data type for the column

ALTERATIONS/MODIFICATIONS: DROPPING COLUMNS

mysql> ALTER TABLE {tableName} DROP oldColumnName;

ALTERATIONS/MODIFICATIONS: CHANGING TABLE PROPERTIES

mysql> UPDATE {tableName} SET column1=newValue1, column2=newValue2, ...WHERE {condition};
 * UPDATE logins SET password = 'changePassword' WHERE id > 1;
 * this updates all passwords in all records where the id is > 1
 * the UPDATE statement can be used to update specific records within a
   table, based on certain conditions   

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