☑️MySQL

Structured Query Language (SQL)

SQL syntax can differ from one RDBMS to another. However, they are all required to follow the ISO standard for Structured Query Language. SQL can be used to perform the following actions:

• Retrieve data

• Update data

• Delete data

• Create new tables and databases

• Add / remove users

• Assign permissions to these users

The mysql utility is used to authenticate to and interact with a MySQL/MariaDB database. The -u flag is used to supply the username and the -p flag for the password. The -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.

[!bash!]$ mysql -u root -p

Enter password: <password>
...SNIP...

mysql> 

Note: The default MySQL/MariaDB port is (3306), but it can be configured to another port. It is specified using an uppercase P, unlike the lowercase p used for passwords.

mysql -u root -h docker.hackthebox.eu -P 3306 -p 

Creating A Database

Once we log in to the database using the mysql utility, we can start using SQL queries to interact with the DBMS. For example, a new database can be created within the MySQL DBMS using the CREATE DATABASE statement.

mysql> CREATE DATABASE users;

Query OK, 1 row affected (0.02 sec)

End every command with a semi-colon ;

We can view the list of databases with SHOW DATABASES, and we can switch to the users database with the USE statement:

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| users              |
+--------------------+

mysql> USE users;

Database changed

Tables

DBMS stores data in the form of tables. A table is made up of horizontal rows and vertical columns. The intersection of a row and a column is called a cell. Every table is created with a fixed set of columns, where each column is of a particular data type.

A data type defines what kind of value is to be held by a column. Common examples are numbers, strings, date, time, and binary data. There could be data types specific to DBMS as well. A complete list of data types in MySQL can be found here. For example, let us create a table named logins to store user data, using the CREATE TABLE SQL query:

CREATE TABLE logins (
    id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    date_of_joining DATETIME
    );

The SQL queries above create a table named logins with four columns. The first column, id is an integer. The following two columns, username and password are set to strings of 100 characters each. Any input longer than this will result in an error. The date_of_joining column of type DATETIME stores the date when an entry was added.

mysql> SHOW TABLES;

+-----------------+
| Tables_in_users |
+-----------------+
| logins          |
+-----------------+
1 row in set (0.00 sec)

A list of tables in the current database can be obtained using the SHOW TABLES statement. In addition, the DESCRIBE keyword is used to list the table structure with its fields and data types.

mysql> DESCRIBE logins;

+-----------------+--------------+
| Field           | Type         |
+-----------------+--------------+
| id              | int          |
| username        | varchar(100) |
| password        | varchar(100) |
| date_of_joining | date         |
+-----------------+--------------+
4 rows in set (0.00 sec)

Table Properties

Within the CREATE TABLE query, there are many properties that can be set for the table and each column. For example, we can set the id column to auto-increment using the AUTO_INCREMENT keyword, which automatically increments the id by one every time a new item is added to the table:

id INT NOT NULL AUTO_INCREMENT,

The NOT NULL constraint ensures that a particular column is never left empty 'i.e., required field.'

We can also use the UNIQUE constraint to ensures that the inserted item are always unique. For example, if we use it with the username column, we can ensure that no two users will have the same username:

username VARCHAR(100) UNIQUE NOT NULL,

Another important keyword is the DEFAULT keyword, which is used to specify the default value. For example, within the date_of_joining column, we can set the default value to Now(), which in MySQL returns the current date and time:

date_of_joining DATETIME DEFAULT NOW(),

Finally, one of the most important properties is PRIMARY KEY, which we can use to uniquely identify each record in the table, referring to all data of a record within a table for relational databases.

We can make the id column the PRIMARY KEY for this table:

    PRIMARY KEY (id)

The final CREATE TABLE query will be as follows:

CREATE TABLE logins (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    date_of_joining DATETIME DEFAULT NOW(),
    PRIMARY KEY (id)
    );

SQL Statements

INSERT

The INSERT statement is used to add new records to a given table. The statement following the below syntax:

mysql> INSERT INTO logins VALUES(1, 'admin', 'p@ssw0rd', '2020-07-02');

Query OK, 1 row affected (0.00 sec)

The example above shows how to add a new login to the logins table, with appropriate values for each column. However, we can skip filling columns with default values, such as id and date_of_joining. This can be done by specifying the column names to insert values into a table selectively:

mysql> INSERT INTO logins(username, password) VALUES('administrator', 'adm1n_p@ss');

Query OK, 1 row affected (0.00 sec)

We inserted a username-password pair in the example above while skipping the id and date_of_joining columns.

Note: The examples insert cleartext passwords into the table, for demonstration only. This is a bad practice, as passwords should always be hashed/encrypted before storage.

Adding multiple records in a table

mysql> INSERT INTO logins(username, password) VALUES ('john', 'john123!'), ('tom', 'tom123!');

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT

Now that we have inserted data into tables let us see how to retrieve data with the SELECT statement. This statement can also be used for many other purposes, which we will come across later. The general syntax to view the entire table is as follows:

mysql> SELECT * FROM logins;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)

The asterisk symbol (*) acts as a wildcard and selects all the columns. The FROM keyword is used to denote the table to select from.

It is possible to view data present in specific columns as well:

mysql> SELECT username,password FROM logins;

+---------------+------------+
| username      | password   |
+---------------+------------+
| admin         | p@ssw0rd   |
| administrator | adm1n_p@ss |
| john          | john123!   |
| tom           | tom123!    |
+---------------+------------+
4 rows in set (0.00 sec)

DROP

We can use DROP to remove tables and databases from the server.

mysql> DROP TABLE logins;

Query OK, 0 rows affected (0.01 sec)


mysql> SHOW TABLES;

Empty set (0.00 sec)

The 'DROP' statement will permanently and completely delete the table with no confirmation, so it should be used with caution.

ALTER

Finally, We can use ALTER to change the name of any table and any of its fields or to delete or add a new column to an existing table. The below example adds a new column newColumn to the logins table using ADD:

mysql> ALTER TABLE logins ADD newColumn INT;

Query OK, 0 rows affected (0.01 sec)

To rename a column, we can use RENAME COLUMN:

mysql> ALTER TABLE logins RENAME COLUMN newColumn TO oldColumn;

Query OK, 0 rows affected (0.01 sec)

We can also change a column's datatype with MODIFY:

mysql> ALTER TABLE logins MODIFY oldColumn DATE;

Query OK, 0 rows affected (0.01 sec)

Finally, we can drop a column using DROP:

mysql> ALTER TABLE logins DROP oldColumn;

Query OK, 0 rows affected (0.01 sec)

We can use any of the above statements with any existing table, as long as we have enough privileges to do so.

UPDATE

While ALTER is used to change a table's properties, the UPDATE statement can be used to update specific records within a table, based on certain conditions. Its general syntax is:

UPDATE table_name SET column1=newvalue1, column2=newvalue2, ... WHERE <condition>;

mysql> UPDATE logins SET password = 'change_password' WHERE id > 1;

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0


mysql> SELECT * FROM logins;

+----+---------------+-----------------+---------------------+
| id | username      | password        | date_of_joining     |
+----+---------------+-----------------+---------------------+
|  1 | admin         | p@ssw0rd        | 2020-07-02 00:00:00 |
|  2 | administrator | change_password | 2020-07-02 11:30:50 |
|  3 | john          | change_password | 2020-07-02 11:47:16 |
|  4 | tom           | change_password | 2020-07-02 11:47:16 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)

Note: we have to specify the 'WHERE' clause with UPDATE, in order to specify which records get updated.

Query Results / Filtering

Sorting Results

We can sort the results of any query using ORDER BY and specifying the column to sort by. By default, the sort is done in ascending order, but we can also sort the results by ASC or DESC:

mysql> SELECT * FROM logins ORDER BY password DESC;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)

It is also possible to sort by multiple columns, to have a secondary sort for duplicate values in one column:

mysql> SELECT * FROM logins ORDER BY password DESC, id ASC;

+----+---------------+-----------------+---------------------+
| id | username      | password        | date_of_joining     |
+----+---------------+-----------------+---------------------+
|  1 | admin         | p@ssw0rd        | 2020-07-02 00:00:00 |
|  2 | administrator | change_password | 2020-07-02 11:30:50 |
|  3 | john          | change_password | 2020-07-02 11:47:16 |
|  4 | tom           | change_password | 2020-07-02 11:50:20 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)

Limit Results

In case our query returns a large number of records, we can LIMIT the results to what we want only, using LIMIT and the number of records we want:

mysql> SELECT * FROM logins LIMIT 2;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

If we wanted to LIMIT results with an offset, we could specify the offset before the LIMIT count:

mysql> SELECT * FROM logins LIMIT 1, 2;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

Clause - WHERE

To filter or search for specific data, we can use conditions with the SELECT statement using the WHERE clause, to fine-tune the results:

mysql> SELECT * FROM logins WHERE id > 1;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  3 | john          | john123!   | 2020-07-02 11:47:16 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM logins where username = 'admin';

+----+----------+----------+---------------------+
| id | username | password | date_of_joining     |
+----+----------+----------+---------------------+
|  1 | admin    | p@ssw0rd | 2020-07-02 00:00:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)

Clause - LIKE

Another useful SQL clause is LIKE, enabling selecting records by matching a certain pattern. The query below retrieves all records with usernames starting with admin:

mysql> SELECT * FROM logins WHERE username LIKE 'admin%';

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  4 | administrator | adm1n_p@ss | 2020-07-02 15:19:02 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

The % symbol acts as a wildcard and matches all characters after admin. It is used to match zero or more characters.

Similarly, the _ symbol is used to match exactly one character. The below query matches all usernames with exactly three characters in them, which in this case was tom:

mysql> SELECT * FROM logins WHERE username like '___';

+----+----------+----------+---------------------+
| id | username | password | date_of_joining     |
+----+----------+----------+---------------------+
|  3 | tom      | tom123!  | 2020-07-02 15:18:56 |
+----+----------+----------+---------------------+
1 row in set (0.01 sec)

SQL Operators

AND

The AND operator takes in two conditions and returns true or false based on their evaluation:

condition1 AND condition2

In MySQL terms, any non-zero value is considered true, and it usually returns the value 1 to signify true. 0 is considered false.

OR

The OR operator takes in two expressions as well, and returns true when at least one of them evaluates to true

NOT

The NOT operator simply toggles a boolean value 'i.e. true is converted to false and vice versa'.

Symbols

The AND, OR and NOT operators can also be represented as &&, || and !, respectively.

Examples In Queries

mysql> SELECT * FROM logins WHERE username != 'john';

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  1 | admin         | p@ssw0rd   | 2020-07-02 00:00:00 |
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM logins WHERE username != 'john' AND id > 1;

+----+---------------+------------+---------------------+
| id | username      | password   | date_of_joining     |
+----+---------------+------------+---------------------+
|  2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
|  4 | tom           | tom123!    | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)

Precendence

SQL supports various other operations such as addition, division as well as bitwise operations. Thus, a query could have multiple expressions with multiple operations at once. The order of these operations is decided through operator precedence.

Here is a list of common operations and their precedence, as seen in the MariaDB Documentation:

• Division (/), Multiplication (*), and Modulus (%)

• Addition (+) and subtraction (-)

• Comparison (=, >, <, <=, >=, !=, LIKE)

• NOT (!)

• AND (&&)

• OR (||)

Operations at the top are evaluated before the ones at the bottom of the list.