MySQL Security
What is MySQL?
MySQL is an open source SQL relational database management system developed by Oracle. It uses the SQL database language and operates on a client server architecture. The MySQL server manages data storage and retrieval, organizing information in tables with columns, rows, and various data types. These databases are typically stored in files with .sql extension.
MySQL Clients
MySQL clients use SQL queries to interact with the database engine, supporting insert, delete, modify, and retrieve operations simultaneously across multiple clients. Access can be restricted to internal networks or exposed to the public Internet depending on configuration needs.
A common example is WordPress, which stores posts, usernames, and passwords in its own MySQL database.
MySQL Databases
MySQL excels in dynamic web applications, commonly used in the LAMP stack (Linux, Apache, MySQL, PHP). It stores headers, user credentials, metadata, forms, and other application data.
Sensitive data should always be encrypted by the application layer rather than stored in plain text.
MySQL Commands & Related Systems
SQL commands manage data operations (insert, update, delete, retrieve) and database structure. Error messages from SQL queries can be informative but may also reveal sensitive details exploitable via SQL injection attacks.
MariaDB is a popular MySQL fork that maintains compatibility while offering additional features.
Default Configuration
Proper MySQL configuration is essential for both functionality and security. Here’s an example of default settings:
MySQL Configuration File
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ sudo apt install mysql-server -y
$ cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -v "#" | sed -r '/^\s*$/d'
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
skip-host-cache
skip-name-resolve
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
symbolic-links=0
!includedir /etc/mysql/conf.d/
Dangerous Settings
Key security relevant configuration options include:
| Setting | Risk |
|---|---|
user / password |
Stored in plain text; if exposed, attackers gain full database access |
debug / sql_warnings |
Verbose error output can leak sensitive system information |
admin_address |
Listening on public interfaces increases attack surface |
secure_file_priv |
Controls data import/export restrictions |
Plain text credentials in config files are a critical vulnerability. Improper file permissions allow unauthorized access to these credentials, compromising the entire database and user data.
Footprinting & Enumeration
MySQL typically runs on TCP port 3306. Exposed instances often result from temporary configurations left in production or network misconfiguration.
Scanning MySQL Server
1
$ sudo nmap <target> -sV -sC -p3306 --script mysql*
Nmap can enumerate version information, identify weak credentials, and list available databases. Results should be verified manually, as false-positives are common.
Connecting & Querying MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
$ mysql -u root -p<password> -h <target>
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 150165
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.006 sec)
MySQL [(none)]> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.27-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.001 sec)
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 |
|... |
| user |
+------------------------------------------------------+
37 rows in set (0.002 sec)
Key system databases include sys and information_schema, which contain metadata and management information essential for MySQL operations.
Essential MySQL Commands
Some of the commands we should remember and write down for working with MySQL databases are described below in the table:
| Command | Description |
|---|---|
mysql -u <user> -p<password> -h <IP address> |
Connect to the MySQL server. There should not be a space between the ‘-p’ flag, and the password. |
show databases; |
Show all databases. |
use <database>; |
Select one of the existing databases. |
show tables; |
Show all available tables in the selected database. |
show columns from <table>; |
Show all columns in the selected table. |
select * from <table>; |
Show everything in the desired table. |
select * from <table> where <column> = "<string>"; |
Search for needed string in the desired table. |
Setting up a test MySQL instance locally allows for hands-on exploration of configurations and security best practices.