Installing MySQL

Databases can be found in everything from desktop applications, web applications, corporate servers to smartphones and other devices. Almost every software program relies on some sort of database to store its data. As applications continue to grow, so is the amount of data that is being stored into their databases, which is the main reason why they are the number one target of attackers–databases frequently contain information that is useful or desirable to an attacker. Data breaches, commonly expose data containing personally identifiable information such as names, usernames, passwords, email addresses, credit card numbers, health records; as well as other confidential documents such as trade secrets or commercially sensitive information. Legislation around the world is very strict when it comes to data privacy, so much so, that substantial fines may be incurred by organizations suffering a data breach. To such an extent, SQL security is crucial since securing databases and their content is key.

For the past 15 years, SQL injection has been among the most common and dangerous attacks on web applications. In SQL injection, the database server is not directly exposed to the attacker since an attacker would exploit vulnerabilities that arise as a result of poor coding practices in the frontend or backend of web applications. However, misconfiguration of the database server itself can also lead to unauthorized access or escalation of privilege which is granted via other vulnerabilities like the SQL Injection.

In this series, we will focus on SQL Security, showing how to create a more secure environment for MySQL server, which is currently the second most popular database management system (DBMS), in order to prevent common attacks, as well as to mitigate the attack vector of other vulnerabilities.

For the purposes of this article we have setup a machine running Ubuntu 16.04 LTS (Xenial Xerus) and MySQL 5.7. We have also edited our hosts file to point ‘example.com’ to the IP address of our test machine.

Installing MySQL server

MySQL server can be installed in two ways. We can either do a version-specific installation or automatically install the latest version that is available for our distribution. We will proceed with the second option, since it is always better to run the latest version of a software.

Before installing any software, we need to make sure that our system has the latest package lists from its repositories, otherwise, this might result in installing outdated software.

secuser@secureserver:/# sudo apt-get update

Now that our package lists are updated, we can proceed with the installation of the latest version of MySQL server by running the following command.

secuser@secureserver:/# sudo apt-get install mysql-server

The installer will search for the packages and dependencies that need to be installed and then we will be prompted to confirm that we want to proceed with the installation of those packages.

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
tinyca
The following NEW packages will be installed
mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7 mysql-server-core-5.7
0 to upgrade, 9 to newly install, 0 to remove and 253 not to upgrade.
Need to get 0 B/17.9 MB of archives.
After this operation, 160 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

MySQL Setup – Root account

During the initial setup, we are being prompted to set a password for the MySQL’s root account.

Using a strong password is critical. A strong password should be longer than 12-15 characters and should be a combination of alphanumeric and special characters and ideally, it should not contain words you can find in a dictionary (e.g. – LjoS^5hwF$9Uyn*0pBEe). Many administrators, choose very easily guessable passwords for their own convenience, or forget to change passwords when the server moves into a production environment. This means that the database user account can be brute-forced either remotely or locally (requires to already have access to the server). Furthermore, simple passwords are much easier for an attacker to crack offline should they manage to steal MySQL users’ password hashes.

The following is an example of a MySQL server which is exposed over a network combined with an insecure root account password.

[*] 192.168.2.108:3444 MYSQL - Found remote MySQL version 5.7.12
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:admin (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:root (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: root:r00t (Incorrect: Access denied for user 'root'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:r00t (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:toor (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[-] 192.168.2.108:3444 MYSQL - LOGIN FAILED: admin:l3tme1n (Incorrect: Access denied for user 'admin'@'192.168.2.112' (using password: YES))
[+] 192.168.2.108:3444 MYSQL - Success: root:l3tme1n'

The root account should under no circumstances be used in any of our web applications. If we use the root account to connect our web application to the database, then if an attacker is able to execute commands on the SQL server, they will be able to enumerate and possibly modify and even delete data inside all the databases and tables that the database server manages.

Not only will an attacker be able to compromise data in the database server, but in the case of a server misconfiguration, as we will see later on in this article, this could result in a complete server compromise.

Testing installation

Now that the installation is finished, we can test whether the MySQL has been successfully installed and running. We can verify if the mysql daemon is running in numerous ways.

Services

We can list the services that are currently running on the system and filter to show mysql related ones

secuser@secureserver:/# ps aux | grep mysql

--> mysql 7891 0.2 7.1 1784520 145100 ? Ssl 14:32 0:00 /usr/sbin/mysqld
secuser 8093 0.0 0.0 14232 1084 pts/5 S+ 14:35 0:00 grep mysql

mysqladmin

We can use mysqladmin, which is a client for performing administrative operations on MySQL Server.

secuser@secureserver:/# mysqladmin -u root -p status

--> Uptime: 318 Threads: 1 Questions: 2 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.006

service

We can use the service command which is mostly used to start/stop services. Using the status parameter, we can see the current status of a given service.

secuser@secureserver:/# service mysql status

--> Active: active (running) since Thu 2016-07-14 11:13:03 BST; 6min ago
Main PID: 7891 (mysqld)

Should the mysql daemon not be running, we’d get the following output instead.

secuser@secureserver:/# service mysql status

--> Active: inactive (dead) since Thu 2016-07-14 11:19:50 BST; 1s ago
Main PID: 7891 (code=exited, status=0/SUCCESS)

We can also login to the MySQL server. A successful login means that the server is properly functioning.

secuser@secureserver:/# mysql -u root -p

--> Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

SQL Security: Configuring MySQL Securely

Configuration

We will start off with the most common settings by opening the default MySQL configuration file using the nano text editor.

secuser@secureserver:/# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

User

MySQL server should never be run as the root user. To understand why this is so important, let’s take as an example MySQL user jason who has the FILE privilege. This means that jason can read/write files on the server. If MySQL server is running under the root account, it can read and write files as root, which on Linux systems is the super-user.

To such an extent, MySQL should only be run as an unprivileged user which minimizes the risk of users having unauthorized access to sensitive files. By default, MySQL creates the mysql user and the MySQL server should run under that account.

user = mysql

Server Binding

The bind-address option, which is currently set by default to 127.0.0.1 (localhost), tells MySQL server on which address its network socket will listen to. By using the 127.0.0.1 address, the MySQL server can only be accessed by applications which are installed on the same local environment as the MySQL server.

A web application on the same machine can access the backend database, while another computer on the local or external network will not have access to it. This means that we restrict any direct remote access to the MySQL server.

We make sure that MySQL has the following bind-address entry in mysqld.cnf.

bind-address = 127.0.0.1

Port

By default, the MySQL server listens on port 3306. By changing the default port, we can make the MySQL service harder to identify.

Port = 3444 #sample port number

We can verify that MySQL is running on the newly configured port by running netstat and using the grep utility to filter for the mysql service.

secuser@secureserver:/# netstat –ntulp | grep mysql

--> Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:3444 0.0.0.0:* LISTEN 865/mysqld

local_infile

This option, which is enabled by default, allows a user to use LOCAL in LOAD_DATA statements, which means that it allows that user to read files on the local filesystem. Having said this, for this to work, the user must have the FILE privilege granted and can only read files they have access to.

Nevertheless, it is still very dangerous to allow MySQL users to read files as they could potentially have access sensitive data.

The following is an example of saving the content of /etc/passwd into a table with the name of test.

mysql> LOAD DATA LOCAL INFILE "/etc/passwd" REPLACE INTO TABLE test FIELDS TERMINATED BY '\t' lines terminated by '\n';

We can then query the table to see the file content.

mysql> SELECT * FROM test;

-->
+----------------------------------------------------------------------+
| text |
+----------------------------------------------------------------------+
| root:x:0:0:root:/root:/bin/bash |
| daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin |
| bin:x:2:2:bin:/bin:/usr/sbin/nologin |
| sys:x:3:3:sys:/dev:/usr/sbin/nologin |
| sync:x:4:65534:sync:/bin:/bin/sync |
| games:x:5:60:games:/usr/games:/usr/sbin/nologin |
+----------------------------------------------------------------------+

We can disable this feature as follows in mysqld.cnf.

local_infile = 0

secure_file_priv

Users who have the FILE privilege can use the LOAD_FILE() or LOAD_DATA() and SELECT INTO statements to load or write data to a file.

What secure_file_priv does, is limit the usage of these operations within a specified directory which means that the user can only work with files from within that directory.

secure_file_priv = '/path/'

Symbolic links

Symbolic links, commonly referred to as symlinks, are files that link to other files (similar to shortcuts in Microsoft Windows). They are commonly used to move the data directory (the directory containing databases) to another folder or path.

Symbolic links to tables shouldn’t be allowed, especially if MySQL server is running as root (which, as we already discussed should never be the case) because under certain circumstances, users can access files outside the data directory which may lead to unauthorized access to sensitive data.

We can disable this feature as follows in mysqld.cnf.

symbolic-links = 0

default_password_lifetime

As from MySQL 5.7.4, automatic password expiration is available in MySQL. This means that we can define the number of days a password is valid for. The default global value of default_password_lifetime is 360, which means that a password must be changed approximately once a year.

If we want passwords to expire after 90 days (3 months) we would define the following in mysqld.cnf.

default_password_lifetime = 90

Otherwise, if we want passwords to never expire, we can set the following in mysqld.cnf.

default_password_lifetime = 0

Alternatively, we can specify per user expiry by using ALTER_USER in a MySQL shell as follows.

mysql> ALTER USER jason'@'localhost' PASSWORD EXPIRE INTERVAL 35 DAY;

If our database user’s password has expired, we will get the following message (for example’s sake, errors are being displayed to the user, however, in reality applications should properly handle and log database errors as opposed to printing them).

In order to set a new password, we must login to the MySQL server using the credentials of the user whose password has expired. We will use ‘jason’ as an example.

secuser@secureserver:/# mysql -u jason -p

mysql> SET PASSWORD = PASSWORD('gsu8L54eV6UBpS5A8ZbZ');
--> Query OK, 0 rows affected, 1 warning (0.00 sec)

We then edit our web application’s configuration file and set the new password.

secuser@secureserver:/# sudo nano /var/www/html/sql.php
...

$con = mysqli_connect("localhost", "jason", "gsu8L54eV6UBpS5A8ZbZ") or
die("Could not connect: " . mysqli_error());
...

Once we save the changes and refresh the page, our web application is now back online.

AppArmor

AppArmor in Ubuntu is a kernel-integrated security module. Its primary job is permission control, which means that it checks if a program is running within its allowed environment.

Each program has its own profile which AppArmor loads once started. MySQL has its own profile that gets installed during MySQL Server setup.

To check whether MySQL’s profile is loaded we can run the following command.

secuser@secureserver:/# sudo apparmor_status

--> apparmor module is loaded.
28 profiles are loaded.
28 profiles are in enforce mode.
# We can make sure this profile is loaded
/usr/sbin/mysqld

If the profile is not loaded, we can enable it by running the following commands.

secuser@secureserver:/# sudo rm /etc/apparmor.d/disable/usr.sbin.mysqld
secuser@secureserver:/# sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

User accounts

In order for an application to access a database, it must first authenticate against the database server using a valid user account. As part of our MySQL server audit process, we need to review the user accounts and identify redundant accounts, or accounts that do not have a password specified. The latest versions of MySQL initially only create one account–root. However, older versions create also an Anonymous account which has no password.

In order to get a list of users, we must first login to the MySQL server.

secuser@secureserver:/# mysql -u root -p
Enter password:
# mysql server message
mysql>

After logging in, we can query the mysql.user table to get the user list.

mysql> SELECT User, Host, Authentication_String FROM mysql.user;

-->
+------------------+-----------+----------------------------------------+
| User | Host | Authentication_String
+------------------+-----------+----------------------------------------+
| root | localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615... |
| mysql.sys | localhost | *0CC75F492CB9CCCB138B7C76F73C0B5D17... |
| debian-sys-maint | localhost | *FDD4ACF2CE3B980940298A1FD2D6A5A7ED... |
| test | localhost |
+------------------+-----------+----------------------------------------+
4 rows in set (0.00 sec)

As we can see there is the root account, and the test account which has no password. We can specify a secure password for that account as follows.

mysql> SET PASSWORD FOR 'test'@'localhost' = "uYI3cU_FreFgR*UsS2B";

--> Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, Authentication_String FROM mysql.user WHERE User='test';

-->
+------------------+-----------+----------------------------------------+
| User | Host | Authentication_String
+------------------+-----------+----------------------------------------+
| test | localhost | *73C084DAC378FF328AD3C131ED10F6E309... |
+------------------+-----------+----------------------------------------+
1 row in set (0.00 sec)

Alternatively, we can remove the account if we don’t need it.

mysql> DROP USER 'test'@'localhost';
--> Query OK, 0 rows affected (0.00 sec)

Having a very strong password for the root user is fundamental. Additionally, we could also rename the root account username to make it harder for an attacker to guess.

mysql> RENAME USER 'root'@'localhost' TO new_username;
--> Query OK, 0 rows affected (0.00 sec)

User Privileges

The privileges of a database user are very often overlooked. Either the administrator does not know which permissions to grant to a user and thus grants all, or, the administrator does not carefully assign the correct permissions. Permissions are key to the overall website and server security, and could help mitigate the effects of a successful attack.

In the event of our web application getting compromised, we should have the correct mechanisms in place to minimize the damage or the chance of further privilege escalation by an attacker.

Since there are too many permissions to list, we will take a look at which permissions a common website, in our case a WordPress site, needs to have in order to function normally.

During the installation, the application may use all of the permissions in order to correctly setup the tables and insert the required data. However, after the setup is finished, a typical installation will need only the following permissions for it to function, which are only related to the DATA of the database and not STRUCTURE or ADMINISTRATION.

For example, we can grant the above permissions on our user ‘jason’ on the ‘test’ database using the following SQL statement.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON Test.* TO 'jason'@'localhost';

--> Query OK, 0 rows affected (0.01 sec)

mysql> show grants FOR 'jason'@'localhost';

-->
+-----------------------------------------------------------------------+
| Grants for jason@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jason'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `Test`.* TO 'jason'@'localhost'
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

Some users prefer the graphical interface of some administration tools like phpMyAdmin and MySQL Workbench, which make it easier to manage databases as well as the privileges of a user. As we can see the privileges are grouped in categories.

Databases

After reviewing the users, we must now review the databases to identify unneeded databases.

mysql> show databases;

-->
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)

Older versions of MySQL server initially create a test database which is accessible by any user and should be removed along with any other database that is not being used. In the case that we need to keep the test database, we should remove access rights for all users.

We can remove access to the test database by executing the following SQL commands.

mysql> DELETE FROM mysql.db WHERE Db = 'test';
--> Query OK, 0 rows affected (0.02 sec)

mysql> FLUSH PRIVILEGES;
--> Query OK, 0 rows affected (0.00 sec)

Now, no users should be able to access that database. If we want to completely remove the database, we can do so by executing the following SQL statement.

mysql> DROP DATABASE test;
--> Query OK, 0 rows affected (0.00 sec)

Command History

Like Bash, MySQL has its own history log stored in the ~/.mysql_history file. Any commands send to the MySQL server via shell will be logged into this file. As you may have already figured out, it will likely contain information about permissions, database names, usernames and passwords which we may have used in queries.

secuser@secureserver:~# cat ~/.mysql_history

--> use\040wp;
select\040*\040FROM\040wp_users;
select\040*\040FROM\040wp_users\040WHERE\040user_pass='1hF71jf91f7919Hf271HHFbA24';
exit

We can clear this file using the following Bash command.

secuser@secureserver:~# cat /dev/null > ~/.mysql_history

Secure Communications

In some cases, we might have the MySQL database server setup on a dedicated machine independent from the web server. Therefore, a remote connection would be required. Using an unencrypted connection between our web application and the MySQL Server means that all traffic is sent in cleartext (unencrypted). An attacker within the network could sniff all our traffic and exfiltrate sensitive information.

The following is a sample of sniffed network packets containing unencrypted traffic containing sensitive data to MySQL.
"........SELECT * FROM wp_users....,....def.wp.wp_users.wp_users.ID.ID.?......#B...<....def.wp.wp_users.wp_users
user_loginuser_login.!...... @...:....def.wp.wp_users.wp_users user_pass ser_pass.!...........B....def.wp.wp_users.wp_users user_nicename user_nicename.!...... @...<....def.wp……....?...........@....def.wp.wp_users.wp_users.display_name.display_name.!...........|....1.admin"$P$Bvsl4MV4/JFJp89aplP8wBHsIQSmbl0.admin.admin@admin.com. 

In order to avoid this, we must enable TLS on the database server. We need a valid TLS certificate prior enabling secure connection. For the purposes of this article we will create a self-signed certificate (we’ll use OpenSSL for this). For production-use, it’s recommended to use a certificate issued by a certificate authority (CA) you trust.

We first need to create a directory where the TLS certificates will be stored.

secuser@secureserver:/# mkdir sqlcert && cd sqlcert

Now we will create the CA certificate. The CA certificate will be used later on to create-sign the Server-Client certificates. We use the genrsa option to create a 2048-bit RSA private key.

secuser@secureserver:~/sqlcert# openssl genrsa 2048 > ca-key.pem

--> Generating RSA private key, 2048 bit long modulus
.................................................+++...+++
e is 65537 (0x10001)

Instead of creating a CSR (Certificate Signing Request) which would then be needed to be sent to a third party Certificate Authority for signing, we use the -x509 option to self-sign our root certificate (or Certificate of Authority) using the private key we generated.

secuser@secureserver:~/sqlcert# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

You are about to be asked to enter information that will be incorporated
into your certificate request.

# You will be asked some questions

Server certificate

We are now ready to create the Server certificate. First we initiate a private key request.

secuser@secureserver:~/sqlcert# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

Generating a 2048 bit RSA private key
..........+++.......................................................+++
writing new private key to 'server-key.pem'
You are about to be asked to enter information that will be incorporated
into your certificate request.

# You will be asked some questions

The private key is protected by a passphrase which will be needed every time the server starts. To avoid having to enter the passphrase, we can remove it using the following command.

secuser@secureserver:~/sqlcert# openssl rsa -in server-key.pem -out server-key.pem

--> writing RSA key

The root certificate we initially created (ca.pem) as well as its private key (ca-key.pem) will now be used to sign the server’s certificate. The result will be saved in server-cert.pem file

secuser@secureserver:~/sqlcert# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/OU=Security/CN=example.com/emailAddress=ssl@example.com
Getting CA Private Key

Client certificate

The user-client who will be connecting to the MySQL server must have a certificate as well. We will follow the same procedure that we followed for the server certificate.

secuser@secureserver:~/sqlcert# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

--> Generating a 2048 bit RSA private key
....................................................+++...+++
writing new private key to 'client-key.pem'
You are about to be asked to enter information that will be incorporated into your certificate request.

# You will be asked some questions

We sign the certificate using our root certificate (CA).

secuser@secureserver:~/sqlcert# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

--> Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=example.com/emailAddress=ssl@example.com
Getting CA Private Key

After generating the server-client certificates, we need to verify that they are not corrupted and that they were created using our root certificate (CA).

secuser@secureserver:~/sqlcert# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

--> server-cert.pem: OK
client-cert.pem: OK

The sslcert directory should now resemble the following.

secuser@secureserver:~/sqlcert# ls -la

-->
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 ca-key.pem
-rw-r--r-- 1 mysql mysql 1367 May 29 19:49 ca.pem
-rw-r--r-- 1 mysql mysql 1241 May 29 19:49 client-cert.pem
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 client-key.pem
-rw-r--r-- 1 secuser secuser 1062 May 29 19:49 client-req.pem
-rw-r--r-- 1 mysql mysql 1241 May 29 19:49 server-cert.pem
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 server-key.pem
-rw-r--r-- 1 secuser secuser 1062 May 29 19:49 server-req.pem

We should now copy the following certificate files into a new directory and assign the correct permissions.

secuser@secureserver:/# sudo cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql-tls

secuser@secureserver:/# sudo chown mysql:mysql /etc/mysql-tls/*

Now it’s time to configure MySQL server to use our TLS certificates. To do so, we’ll need to modify the mysqld.cnf file using a text editor.

secuser@secureserver:/# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Under [mysqld] we find the following entries and uncomment them. We then specify the correct path.

ssl-ca=/etc/mysql-tls/ca.pem
ssl-cert=/etc/mysql-tls/server-cert.pem
ssl-key=/etc/mysql-tls/server-key.pem

Finally, we must restart the MySQL Server for our changes to take effect.

secuser@secureserver:/# sudo service mysql restart

We will login to the MySQL server to verify that TLS is now enabled.

secuser@secureserver:/# mysql -u root -p

mysql> SHOW GLOBAL VARIABLES LIKE 'have_%ssl';

-->
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
+---------------+-------+
2 rows in set (0.00 sec)

In order to test it, we must copy ca.pem, client-cert.pem and client-key.pem to our client. After copying the files, we are now ready to securely connect to our MySQL Server.

secuser@secureserver2:~# mysql -h 192.168.2.114 -u jason -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Enter password:
# MySQL Server message

mysql> status;

-->
mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapper
Connection id: 20
Current user: jason@192.168.2.108
SSL: Cipher in use is DHE-RSA-AES256-SHA
Server version: 5.7.12-0ubuntu1 (Ubuntu)
Protocol version: 10
Connection: 192.168.2.114 via TCP/IP
TCP port: 3306
Uptime: 4 min 57 sec

The connection to our MySQL server is now encrypted. The following is a sample of sniffed network packets containing encrypted traffic after the TLS setup.

..........!...........................K...G......#.3..u+.&..j..[..hx...f.W.i... .9.3.}.|.y.x.w.5...... ...../.~......J...F....E:..0..OC.Kt.F..G.....lU...... ......s......j.......E...j..We..9.....v...r..o..l0..h0..P...0. *.H.......0y1.0 ..U....AU1.0...U...Some-State1!0...U...Internet Widgits Pty Ltd1.0...U...cacert.com1.0.. *.H... ...ssl@cacert.com0..160529184557Z.2604071 84557Z0{1.0 ..U....AU1.0...U...Some-State1 !0...U...Internet Widgits Pty Ltd1.0...U....example.com1.0.. *.H... ...ssl@example.com0.."0. *.H............0...........P.....t.q..rw...@..+N3....e...y..0...,..sb.D.wv .x....~...l...q...w...)R.!..kl.aU.u.L Qv...{.T.. .4 ...[.!;.=.G .T..b.9V.k.K*.r./}>.../...I..b^<D...xg!.i..l.R .rGO....8.*..Q.. ...w.[.....|.{...fz..'. ..2ZI...WW..0.....V....>.'...l.q.6.....kuiWL....a........0. *.H............ ...d.5..o..'..]/)...?.~X.

mysql_secure_installation

MySQL server has an out-of-the-box security configuration script which helps us improve the security of our MySQL installation by doing the following.

• Setting a password for root accounts
• Disable remote access
• Remove Anonymous user accounts
• Remove the test database and revoke access to users with privileges that permit anyone to access databases with names that start with test_

As of MySQL 5.7.2 there is the option to install validate_password plugin which can be used to test passwords against a password policy, and reject a password if it is weak.

After the installation of MySQL Server finishes, we can run the following command

secuser@secureserver:~$ mysql_secure_installation
--> Securing the MySQL server deployment.

If we have already specified a password for root during the MySQL server setup, we will be asked to enter it.

Enter password for user root:

Otherwise, if there is no password set for root, we’ll connect to MySQL straight away and receive a prompt to set a root password later on.

Connecting to MySQL using a blank password.

We will be asked to install Validate Password Plugin. Entering the letter y to install it.

VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

We then select the level of validation policy. We will set it to 2 (strong).

There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

If there is a password set for root, it will show its strength and then ask if we want to change it.

Estimated strength of the password: 100
Change the root password? (Press y|Y for Yes, any other key for No) :

The rest of the process is straightforward. Press y for all prompts.

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Logs

When it comes to troubleshooting or debugging an application, as well as investigating a security incident, one of the most important source of information we have is log files. MySQL server has the many log files, but we will focus only on the main three.

MySQL’s logging can be configured from the mysqld.cnf file using a text editor.

secuser@secureserver:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

General log

The general log contains information regarding client connection/disconnection as well as all SQL queries received from clients. It is useful for debugging and auditing user actions but it may affect the overall performance of the server.

general_log_file = /var/log/mysql/mysql.log
general_log = 1

Error Log

The error log contains information regarding the mysql daemon operation, and will contain information such as when the mysql daemon was started and stopped, as well as any critical errors that may have occurred during the daemon’s runtime.

log_error = /var/log/mysql/error.log

Long/Slow queries log

It contains information regarding queries that took more time than the value set for long_query_time to execute. This is mostly useful for monitoring the performance of the server.

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

Updates

Apart from all the configuration analyzed in this article, another important area we should focus on is MySQL server updates.

Keeping MySQL server up to date is critical because not only do newer versions include patches to known (or unknown) vulnerabilities, but they can also include security enhancements and features which can make MySQL server more secure. Sometimes, all an attacker needs is one vulnerability or one misconfiguration to cause havoc, we need to ensure MySQL is hardened at all levels to make it as resistant to attack as possible.

We can upgrade MySQL as part of system updates using APT package manager on Ubuntu.

secuser@secureserver:~$ sudo apt-get update && sudo apt-get upgrade

If we only want to upgrade MySQL server, we can run the following command.

secuser@secureserver:~$ sudo apt-get update && sudo apt-get install mysql-server

Backups

No system can ever be 100% secure. Whilst we can’t change that fact, we can, and should be proactive by having the correct mechanisms in place that will make an attacker’s job harder, as well as to be well prepared in the event of a disaster.

If an attacker gains access to our server and drops our database, the only way to restore business continuity is to restoring it from a backup. Not only we need to keep backups, but backups need it to be stored in a secure location. Even if an attacker manages to get access to our secure storage, by encrypting them we make his job really hard, if not impossible.

SQL Injection + Server misconfiguration = Recipe for disaster

Below we will demonstrate how a typical SQL Injection vulnerability can lead to potential complete server compromise. Below we have a script written in PHP which accepts an id parameter via HTTP GET method. It queries the database and if the given id is valid, it displays the email of that user.

A single quote (‘) next to the id (1) is enough to verify that the script is vulnerable to SQL Injection.

After counting the table columns, we know that the “injectable” column is the number 5

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,5,6,7,8,9,10--

We can now test the server’s defenses by using the load_file() function to load a file on the remote server.

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/passwd'),6,7,8,9,10--

It seems that the MySQL user that the script uses to access the database has FILE privileges and is able to read files.

Let’s try to go beyond the usual SQL Injection route. Instead of enumerating databases and table names, we will access files using the load_file() function.

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/hosts'),6,7,8,9,10--

It seems that secret.example.com is hosted on this server. Navigating to that URL does not reveal anything useful.

Let’s go back and try to discover more information via the SQL injection vulnerability we identified.

We know that this webserver is running Apache HTTP Server on Ubuntu Server, therefore, unless the administrator uses a different setup, by default, the Apache HTTP Server configuration file of example.com should be found under /etc/apache2/sites-available/DOMAIN_NAME.conf

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/apache2/sites-available/example.com.conf'),6,7,8,9,10--

By analyzing the configuration file, we can spot the VirtualHost setup for secret.example.com, and that the home directory of that subdomain is /var/www/secret/.

Since navigating to the URL yielded nothing, let’s see if we can find anything from the inside.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/index.html'),6,7,8,9,10--

We get the same message. That means index.html contains only that text.

Assuming we are confident that there is more to secret.example.com other than a simple HTML index file, let’s check for the existence of an .htaccess in the directory.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/.htaccess'),6,7,8,9,10--

Sure enough, we get some data back, but the rule seems to be missing. Since we are viewing this in a browser, and the server is returning a content-type of text/html, because the .htaccess rule is wrapped in <>, the browser parses it as tag thus not displaying it. Viewing the page source reveals the code.

It seems that there is a file named latest_customer_db.csv which is accessible only from a specific IP. We confirm that by trying to access the file.

Using the SQL injection and the load_file() function, we can access the file internally.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/latest_customer_db.csv'),6,7,8,9,10--

It seems that the file contains Names, Email Addresses and Credit Card Numbers with their CCV code. That is definitely something we shouldn’t be able to access.

After some additional very tests, we have discovered that the main site, www.example.com has phpMyAdmin installed and it is publicly accessible, however, we need valid credentials to log in.

We know that www.example.com/sql.php connects to the database since it’s the script on which we exploited the SQL injection. Maybe its source file can reveal something.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/html/sql.php'),6,7,8,9,10--

The file indeed contains the MySQL user credentials. Let’s use them to login to phpMyAdmin.

Once authentication was successful. We can now do anything on the MySQL databases since the user has been granted all privileges.

Instead of querying the database and using normally, let’s use phpMyAdmin for something different. We will use INTO OUTFILE to upload a small PHP shell script on the remote server so that we can easily execute commands using the following SQL statement.

SELECT "" INTO OUTFILE '/var/www/secret/shell.php’;

Once our web shell has been successfully created, we can run arbitrary commands on the server.

If we wanted to, we could escalate the attack even further by downloading other, more sophisticated web shells or local exploits for privilege escalation attacks. However, that would be outside the scope of this article.

To read more about web-shells, how they are created and best practices to defend against them, take a look at our web-shell series.

Throughout this series, we have analyzed how various configuration options can affect our MySQL installation security, and how, as demonstrated in the sample attack scenario, a simple SQL injection vulnerability in combination with a misconfigured server can have devastating results–everything from database enumeration, unauthorized access, secret files to potential server compromise.

Had the MySQL server been hardened, the SQL injection attack would have been limited only to basic database enumeration. The misconfiguration however gave the attacker the option to evolve into something completely different and much more dangerous.