MySQL
MySQL is an open-source SQL relational database management system developed and supported by Oracle. A database is a structured collection of data organized for easy use and retrieval. MySQL can quickly process large amounts of data with high performance and stores data efficiently to minimize space usage. The database is controlled using the SQL database language.
MySQL works according to the client-server principle and consists of a MySQL server and one or more MySQL clients. The MySQL server is the actual database management system that handles data storage and distribution. Data is stored in tables with different columns, rows, and data types. Databases are often stored in a single file with the .sql extension (e.g., wordpress.sql).
MySQL Clients
MySQL clients can retrieve and edit data using structured queries to the database engine. Inserting, deleting, modifying, and retrieving data is done using the SQL database language. MySQL is suitable for managing many different databases to which clients can send multiple queries simultaneously. Access is possible via an internal network or the public Internet, depending on the database configuration.
MySQL Databases
MySQL is ideally suited for applications such as dynamic websites, where efficient syntax and high response speed are essential. It is often combined with a Linux OS, PHP, and an Apache web server in the LAMP stack (Linux, Apache, MySQL, PHP), or with Nginx as LEMP. In web hosting with MySQL database, this serves as a central instance in which content required by PHP scripts is stored, including:
- Content: Headers, texts, meta tags, forms
- Users: Customers, usernames, administrators, moderators
- Credentials: Email addresses, user information, permissions, passwords
- References: External/internal links, links to files, specific contents, values
Sensitive data such as passwords can be stored in plain-text form by MySQL; however, they are generally encrypted beforehand by PHP scripts using secure methods such as one-way encryption.
MySQL Commands
A MySQL database translates commands internally into executable code and performs the requested actions. The web application informs the user if an error occurs during processing, which various SQL injections can provoke. Often, these error descriptions contain important information and confirm that the web application interacts with the database differently than developers intended.
SQL commands can display, modify, add, or delete rows in tables. In addition, SQL can also change the structure of tables, create or delete relationships and indexes, and manage users.
| 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. |
MariaDB
MariaDB is a fork of the original MySQL code. The chief developer of MySQL left MySQL AB after it was acquired by Oracle and developed another open-source SQL database management system based on the MySQL source code, calling it MariaDB.
Default Configuration
The management of SQL databases and their configurations is a vast topic. Database administration is a core competency for software developers and information security analysts. The default configuration of MySQL can be found at /etc/mysql/mysql.conf.d/mysqld.cnf:
rnemeth@htb[/htb]$ 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
Many things can be misconfigured with MySQL. The main options that are security-relevant are:
| Setting | Description |
|---|---|
user | Sets which user the MySQL service will run as. |
password | Sets the password for the MySQL user. |
admin_address | The IP address on which to listen for TCP/IP connections on the administrative network interface. |
debug | This variable indicates the current debugging settings. |
sql_warnings | This variable controls whether single-row INSERT statements produce an information string if warnings occur. |
secure_file_priv | This variable is used to limit the effect of data import and export operations. |
The settings user, password, and admin_address are security-relevant because the entries are made in plain text. Often, the rights for the configuration file of the MySQL server are not assigned correctly. If an attacker gains file read access or a shell, they can see the file and the username and password for the MySQL server. Without other security measures to prevent unauthorized access, the entire database and all existing customer information, email addresses, passwords, and personal data can be viewed and even edited.
The debug and sql_warnings settings provide verbose information output in case of errors, which are essential for the administrator but should not be seen by others. This information often contains sensitive content, which could be detected by trial and error to identify further attack possibilities. These error messages are often displayed directly on web applications. SQL injections could be manipulated to have the MySQL server execute system commands.
System Databases
MySQL includes several system databases that are important for management:
Information Schema
The information_schema database contains metadata retrieved from the system schema database. It exists to comply with the ANSI/ISO standard and provides information about databases, tables, columns, and other database objects.
System Schema (sys)
The sys database contains tables, information, and metadata necessary for management. It provides a set of objects that help interpret performance schema data more easily. The system schema contains more information than the information schema.
Example system schema tables:
mysql> use sys;
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
...SNIP...
| x$waits_global_by_latency |
+-----------------------------------------------+
Example query:
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)
Footprinting MySQL Services
There are many reasons why a MySQL server could be accessed from an external network. Nevertheless, it is far from being a best practice, and databases that can be reached externally are often found. Usually, the MySQL server runs on TCP port 3306.
Scanning MySQL Server
nmap can be used to scan and enumerate MySQL servers:
rnemeth@htb[/htb]$ sudo nmap 10.129.14.128 -sV -sC -p3306 --script mysql*
Starting Nmap 7.80 ( https://nmap.org ) at 2021-09-21 00:53 CEST
Nmap scan report for 10.129.14.128
Host is up (0.00021s latency).
PORT STATE SERVICE VERSION
3306/tcp open nagios-nsca Nagios NSCA
| mysql-brute:
| Accounts:
| root:<empty> - Valid credentials
|_ Statistics: Performed 45010 guesses in 5 seconds, average tps: 9002.0
|_mysql-databases: ERROR: Script execution failed (use -d to debug)
|_mysql-dump-hashes: ERROR: Script execution failed (use -d to debug)
| mysql-empty-password:
|_ root account has empty password
| mysql-enum:
| Valid usernames:
| root:<empty> - Valid credentials
Enumerating Databases
Once connected to a MySQL server, you can enumerate databases and tables:
mysql> show databases;
+------------------------------------------------------+
| Database |
+------------------------------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
...SNIP...
| wordpress |
+------------------------------------------------------+
37 rows in set (0.002 sec)
Security Best Practices
When setting up a MySQL server, it is important to follow security best practices:
- Secure file permissions: Ensure configuration files have proper permissions to prevent unauthorized access
- Strong passwords: Use strong passwords for MySQL users, especially the root account
- Network access: Restrict network access to MySQL servers; avoid exposing them to public networks unless necessary
- Error messages: Disable verbose error messages in production environments
- File operations: Use
secure_file_privto limit data import and export operations - User privileges: Follow the principle of least privilege when granting user permissions
- Encryption: Encrypt sensitive data before storing it in the database
The MySQL reference manual contains a widely covered security issues section that covers best practices for securing MySQL servers. This should be consulted when setting up a MySQL server to understand better why certain configurations might not work.