MySql by Oracle is one of the most used Database Management System in the world. Despite a lot of new DBMS, it maintains its quota between Oracle and MSSQL. For this, it is surprising to see yet that the majority of installations contain misconfiguration, especially in the security field. But whatever data are stored, if it is not a public dataset, they must be protected. This is particularly true because MySql is a system service, so a compromise could conduct not only to a data breach but also to a system compromise.
In other words, a malicious user can use your misconfigured MySql installation as an attack vector to gain privileged access to the hosting machine. Since I see every day a lot of vulnerable installations, I want to share my knowledge about a MySql secure configuration, typical vulnerabilities, and possible mitigations and best practices. Whether you are a system administrator or a database administrator, you must know how to manage your data security.
In this tutorial, I refer to a MySql installation on Linux CentOs host but most considerations are valid also if you run it on Windows or in a container.
The first step is the installation and before it is necessary to choose between a version. At the time of this tutorial writing, MySql 8 is relatively young compared to other versions. And very often there is no official support from the most used application, eg Wordpress. So I can understand that one would prefer to install the “classic” MySql 5.7. I don't disagree but I think there is a need to go deeper.
Some considerations: if you want to wait to adopt the last version, do you have a migration plan? For sure you don't want to wait forever! Having a plan means to be prepared to upgrade the version and migrate your data. Do you really need the former version? Can you adjust your application? Did you take a look at known vulnerabilities on the chosen version? Just a fast search online can lead to a lot of ready to go exploit, not to mention zero-day and other vulnerabilities caused by a poor configuration.
Generally speaking, I use the last version, at least its major, every time there aren't strict requirements that force me to use a previous version. On the other hand, keep up to date is a general rule in the cybersecurity world.
You can search for known vulnerabilities on:
- https://www.google.com/ (can't you tell it?)
You should also look at the official documentation for Supported Platforms:
- End of Life https://endoflife.software/applications/databases/mysql
Ok, you've just installed MySql on your server but it doesn't mean you're ready to start working with data. It's time to make your installation secure, or at least try. Oracle MySql comes with a tool called
mysql_secure_installation which aims to apply some basics security-oriented configuration. Remember that “default is your first enemy” let's take a step by step tour between actions that are being performed by this tool.
As reported by official documentation (https://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installation.html)
mysql_secure_installation enables the user to improve the security of MySql installation in the following ways:
- Set a password for the root user
- Remove root accounts that are accessible from outside the host
- Remove anonymous-user account
- Remove “test” databases because these are accessible by all users, even anonymous ones. The same is applied for databases the names of which start with “test_".
Before starting you should recover the MySql temporary password with:
[user@mysql]$ grep 'temporary password' /var/log/mysqld.log
Once you saved it you can run the
[user@mysql]$ mysql_secure_installation The first step asks current root password, that is the temporary password just recovered, then prompt for a password change: In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. Set root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables... ... Success!
I'm not here to preach about how much it is important to use a strong password, not the same as other services, not a dictionary one, not an already breached one and other stuff like this. Please don't be a lazy admin and use a very secure password.
You can generate a secure password on sites like https://passwordsgenerator.net/. Later in the article, I'll show how easy can be to gain access to databases when a weak password is used.
Do I need to say that a blank password is worst?
The next step is asked to remove anonymous users. Just do it. You can create later your test user according to your needs. Maybe you consider this step too trivial: if so you've never seen a worldwide anonymous MySql scan. Try doing some search on https://www.shodan.io and let me know.
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? [Y/n] Y ... Success!
The third step can literally save your work. You're prompted for removing root access from outside the server.
There should be no need to explain why disallowing root remote access is vital to make your installation safe for life. If you are obsessive like me you have often looked at logs on your server, even if one working ones. If so you've definitely seen a huge amount of brute-forcing attempts on your services.
Knowing that, why one would let the root user usable from outside the host? The answer is easy: because of laziness.
Normally, root should only be allowed to connect from the 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success!
I know that connect with the user only on localhost requires an additional effort but it's life or death. And if you can't really handle it, at least configure your firewall to accept connection only from a trusted host. Or better apply a specific grant for a specific user:
GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'192.162.%' identified by 'password';
The last step requires you to remove test databases. Do you need them? Create yours owns.
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? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!
Principle of least privilege
So now MySql is secure and it can be used? No, the game hasn't started yet. Users and databases must be created and every step has the right way and wrong way of doing it. So let's talk about the principle of least privilege. As Wikipedia says this principle states that “every module must be able to access only the information and resources that are necessary for its legitimate purpose”.
In MySql environment, this means that:
- You should create one database per application. In other words, don't make a mess between applications don't mix data when is not absolutely necessary.
- Every user must have only indispensable grants and nothing more. Why you would use a privileged user, one with SUPER rights, for all databases? Easy usage is not an excuse. So one user per database with only needed grants. For administration working there's always the root user.
- Obviously, also there must be applied a strong password policy. Strong mean good password and mostly to never use the same password twice. I've lost count how many installations use the same password and the same user for dozens and dozens of databases.
Did I mention that the host root/administrator password must be totally different than MySql root one?
You may ask why to be so obsessive about the principle of least privilege. Let's imagine this simple and common scenario. On the server are hosted one hundred databases and all make use of the same user and password. The administrator did it well because for each application there is one database, and on the other hand nothing different could be done because of one hundred different customers. No one would share its data with others.
But the administrator can't control all application and one day, sooner than later, one WordPress site is compromised and the attacker was able to gain a shell on the database server. What could happen than have all databases compromised now that the malicious user can gain access to all databases? And what if, not happy enough, the attacker tries the password to elevate its privileges as root on the host?
Let's be clear: this happens every day. And it's dramatic to see that year after year, yet a lot of database servers are so misconfigured.
Both on Linux and Windows, MySql allow creating a file called “my.cnf” or “my.ini” that contains user and password to log on MySql shell without being prompted every time for user and password on the command line. Under Linux it's enough to create the .my.cnf file under the home directory:
And then in the “client” section insert user and password.
[client] user=mysqluser password=mysqlpass
This feature is good for more than one reason. First, it's a handy way to log in to the MySql shell. Then because this method allows the administrator to avoid inserting the password every time. Yes, you can log in by using hidden password:
shell> mysql -u user -p Insert password:
But how many times by mistake the administrator paste the password from its super secure notepad in clear text in the shell instead than in the MySql prompt? For this reason, hackers consider the “history” of a shell a treasure: I know very few administrators that in such cases clean the history. In the best situations they think to do it later and then forgot it.
That said one consideration about my.cnf file is needed. Because reading online tutorials, very few mention something about rights setting on the file. Long story short it's enough to make the file readable only by the current user:
chmod 0600 ~/.my.cnf
Last but not least: it’s not mandatory that the user MySQL owns my.cnf file. So, why take unnecessary risks? MySql will work well also if you assign the ownership of this file to root.
chown root:root ~/.my.cnf
In the case of an attack where a malicious user gains access to the server with MySql user, you can still hope to stop its path here.
Users naming convention
The attackers know the system administrator’s bad habits very well. One of these is to create users with the same name as databases. Whether it's comfort or laziness this kind of configuration provides a huge help to those who want to brute force your MySql’s users credential.
Indeed when the attacker knows the database name and its user, he can easily try to guess the password. There’s no need to discover the username because it is the same as the database.
Avoiding this situation is very simple: just name your users with a different name than the database one.
Don't run as root
MySql should never be run as the system's root user. This would mean somehow to run commands with the same privileges as root.
Let’s take some examples:
- When MySql is being executed as root, any user with the FILE privilege can create or modify any files on the server as root.
- As we will see in the next chapters, in the case of SQL injection it’s possible to obtain a remote shell on the system through MySql. If the service is run as “mysql” user, the damage is limited to what this user can do. But it would be a tragedy if the attacker could run commands with the system’s root privileges. The system would be considered fully compromised.
The best practice, and usually the default, is to use a separate user, exclusively used for MySql. In Oracle MySql, this user is called “mysql”.
Logs are your friends
Especially those who work on Unix like system knows that everything is logged or it can be. MySql is no exception but rather offer to the administrator more than one log. In a MySql shell the former command show a lot of variables referred to logs:
mysql> show variables like "%log%";
A summary of the output show these log files:
log_error = /var/log/mysqld.log slow_query_log_file = /var/lib/mysql/mysql-slow.log general_log_file = /var/lib/mysql/fdnsmysql.log
- The error log contains pieces of information about every error that occurs while the server is running.
- The general log contains all the actions done by MySql such as connection, disconnection, queries.
- The slow query log contains only the queries in which execution takes more than a certain period of time.
This is not the place to talk in deep about how to manage logs, official documentation should always be the administrator reference in this matter. What I'd like to concentrate here on is about the mindset of the administrator: logs are your friends and this is true also when all seems to work well. Very often logs are screaming in front of imminent danger and you must be here to react.
What do I mean? Let's do some examples.
Let's suppose to take a look to your error log, or general log and see queries containing these strings:
/*!%55NiOn*/ /*!%53eLEct*/ %55nion(%53elect 1,2,3)-- - +union+distinct+select+ +union+distinctROW+select+ /**//*!12345UNION SELECT*//**/ concat(0x223e,@@version) concat(0x273e27,version(),0x3c212d2d) concat(0x223e3c62723e,version(),0x3c696d67207372633d22) concat(0x223e,@@version,0x3c696d67207372633d22) concat(0x223e,0x3c62723e3c62723e3c62723e,@@version,0x3c696d67207372633d22,0x3c62723e) concat(0x223e3c62723e,@@version,0x3a,”BlackRose”,0x3c696d67207372633d22)
How do you feel? I mean, even if you don't know what is running on the application side, I suppose you understand that something is going wrong. What kind of devil-developer should use these in a query?
Or take this query:
union all select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null--
How such a query can be useful in any application? It doesn't need to be a cybersecurity expert to understand that someone is trying to do some kind of enumeration.
Why a developer should hide query content by encoding it?
or 1=convert(int,(select cast(Char(114)+Char(51)+Char(100)+Char(109)+Char(48)+Char(118)+Char(51)+Char(95)+Char(104)+Char(118)+Char(106)+Char(95)+Char(105)+Char(110)+Char(106)+Char(101)+Char(99)+Char(116)+Char(105)+Char(111)+Char(110) as nvarchar(4000))))--
I think I was clear. You can't control everything, especially when a lot of applications are hosted on the server. You can't spend your time checking every line of logs. But always keep your eyes open. I'm not saying you need to replace professional tools that check every query, every application, scan for vulnerability. But do not be blind when you work on your MySql instance.
Review users periodically
During the years a lot of think change in life but in a virtual environment as a database server, changes happen month to month if not less. What it means in the system administrator work, is that some activities must become routines. One of these is the periodical user review. Here are some reasons to understand why this activity is fundamental:
- No longer needed users: this is the most frequent case when facing the administration of any kind of system. Especially in medium and big corporations, there’s a large turnover and so users on systems. At any moment you should be aware of which users are currently active and used: did you deleted that database but its user is still here? You know what to do. An employee has been fired? Its user must be removed.
- Users in privileged roles: do you really need all those users with high permissions on all databases? A very frequent case concerns users created with access from all hosts as ‘user’@’%’ or with access to all databases. Obviously this isn’t a best practice at all but, but often due to laziness or rarely for real applications constraint this kind of configuration is applied. Take in serious consideration to remove this wide privilege access at the first opportunity.
- Unknown users: one of the most important phases of attack is to maintaining access to the compromised system. For sure the best method is the one that leaves no trace. So first of all, a savvy attacker would exclude backdoor, listening services, malware because they are incline to be discovered easily by automatic tools. The quieter method is to create a new user to be used in the future without the need to repeat the attack or arousing suspect. This is the reason why you should check periodically users and grants and you must know them all, one by one.
Keep up to date
This section is self-explanatory. The current version of MySql is 8. Do you really want to use MySql 4? Yet still many companies use very old versions of Mysql.
How to manage applications
Phptherightway and the most dangerous vulnerability
It's unbelievable how many system administrators and developers are unaware of the typical vulnerabilities of applications that use MySql. I'm not talking about the typical mistakes of those who write thousands of lines of code: I refer to the complete lack of knowledge about how to manage the transfer of data from the application to the database.
The Open Web Application Security Project (OWASP) is a nonprofit foundation that works to improve the security of software. Every year OWASP publishes a report called “top ten”, which represents a broad consensus about the most critical security risks to web applications. SQL injection is a vulnerability that has been around for years.
So let's go in deep and see what is the SQL injection vulnerability. In this explanation, I will refer to PHP language and phptherightway.com. As the name says, this website “aims to introduce new PHP developers to some topics which they may not discover until it is too late, and aims to give seasoned pros some fresh ideas on those topics they’ve been doing for years without ever reconsidering”. Although it does not exist the “right method” to develop using PHP, there are techniques that can be used to avoid the most common and dangerous mistakes.
Let’s assume a PHP script receives a numeric ID as a query parameter:
$pdo->query("SELECT id FROM users WHERE id = " . $_GET['id']);
So your URL would be https://www.site.com/?id=1 and the query SELECT id FROM users WHERE id = 1;
What can go wrong? Let’s imagine that a malicious user compiles the “id” parameter with “1;DELETE FROM users”. In this case, the URL would become:
https://www.site.com/?id=11%3BDELETE FROM users
and the query will delete all of your users:
SELECT id FROM users WHERE id = 1;DELETE FROM users
The right way would be to sanitize parameters:
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id'); $id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT); $stmt->bindParam(':id', $id, PDO::PARAM_INT); $stmt->execute();
Even if you aren’t able to develop using PHP, you have to understand that parameter sanitization means escaping the foreign input ID before it is introduced to the database preventing potential SQL injection attacks. Doing so makes it impossible to exploit any SQL injection vulnerability.
Every language offer modules or libraries to sanitize parameters. It's up to you to use them.
Note that in the above example I showed you how to delete user rows, but I could use a SELECT to retrieve the full table. This would mean big data leaks.
GitHub, Google and public repositories
The internet is gorgeous but can become dangerous if not handled properly. One example is the unintentional data leakage. And this is worse when data are widespread by those who should preserve them.
If you've never heard of “google dork”, you have to know that this term indicates some kind of research that aims to find data that are intended to be secret.
Here some example you can try searching on google:
intext:my.cnf intitle:index of
filetype:cnf my.cnf -cvs -example
filetype:inc mysql_connect OR mysql_pconnect
?site:github.com master/wp_config.php password blob DB_PASSWORD
?inurl:wp-config -intext:wp-config "'DB_PASSWORD'"
Each of these searches made on Google leads to data exfiltration. As you can verify a lot of developers and system administrators, upload on GitHub, public accessible directories on the web and other places their passwords and configurations. It would be so simple to use private repositories to store that kind of data.
Also Github has its own “dorks”. The easiest method is to add “site:github.com” after the later queries on google. This option restricts the results only to the result of Github.com website.
Eventually, it’s easy to guess that a lot of tools exist to automatize the search for this kind of data. One overall is called “Github dorks” (https://github.com/techgaun/github-dorks) because it uses the Github API to search on public repositories. If you or your company have a public repository, you can rest assured that someone will try to find passwords and configurations here.
How to store passwords in the database
Password storing in databases is usually managed by developers. System administrators are neutral because data are data and it doesn't matter to them what is stored in the database. Anyway, it’s vital for both developers and system administrators to treat passwords in a safe manner.
First of all: never store passwords in plain text. It should be the most obvious thing in the world but the facts say otherwise. Every day both big corporations and small companies are attacked and data leaks spread around the world. Every time passwords are stored in plain text.
So how to manage passwords? Storing the representation of a password in the database is the proper thing to do. By representation, I mean that you have to hash the password using a salt (which should be different for every user) and a secure 1-way algorithm and store that, throwing away the original password. Then, when you want to verify a password, you hash again the value (using the same hashing algorithm and salt) and compare it to the hashed value in the database.
A small addition about the salt is useful. You may know that there’s a lot of online services that allow you to decrypt passwords that are encoded with a 1-way algorithm. In truth, these services are called “rainbow tables” because they don’t decrypt passwords, but they have a big database with an already decrypted password. So they simply encrypt your plain text password which is compared to those in their database. Using the salt mean that you don’t just encrypt passwords, but you do it with additional value. Doing so makes rainbow attacks simply infeasible to do because the attacker can’t decrypt passwords without knowing the salt.
Let’s see the following example:
hash("hello") = 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 hash("hello" + "QxLUF1bgIAdeQX") = 9e209040c863f84a31e719795b2577523954739fe5ed3b58a75cff2127075ed1 hash("hello" + "bv5PehSMfV11Cd") = d1d3ec2e6f20fd420d50e2642992841d8338a314b8ea157c9e18477aaef226ab hash("hello" + "YYLmfY6IehjZMQ") = a49670c3c18b9e079b9cfaf51634f563dc8ae3070db2c4a8544305df1b60f007
As you can see the hash of the word “hello” is different from the same word hashed with salt. Furthermore, you can simply find the “hello” only hash clear password just by googling it. But you can’t find salted passwords online.
The use of hash and salt does not replace in any way all secure password policies. Finally, remember that using the same salt for all users is like not using it because once the attacker knows it, it’s trivial to decrypt all stored passwords in your database.
The initial phase of any attack is the discovery of potential targets. This first stage includes both host discovery and information gathering. In other words, the attacker tries to find MySql host and then will gain as much as possible information about it. Some useful information could be MySql version, anonymous access, users and databases enumeration and any other public accessible detail. And given that a good start is half the job, with the collected pieces of information, the attacker could start the initial compromise and gain access to the system. In this section, we’ll take a look at some reconnaissance techniques using Nmap, Metasploit, Shodan.io and Google Dorks.
In the following examples the root user has been created with all grants on all hosts:
CREATE USER 'root'@'%' IDENTIFIED BY ‘Password!'; GRANT ALL PRIVILEGES ON *.* TO root@'%';
Even if this is an environment created for educational purposes, it’s a common scenario.
Scan for MySQL (Nmap and Metasploit)
Nmap is probably the most famous network scanner in the world. It is not a coincidence if it’s called the “Swiss Army Knife” for network security testing. Nmap plays its part also in checking MySql security and configuration through the use of scripts. Before going in deep, let’s see a simple scan for MySql hosts in a network:
nmap -sV 192.168.2.69 -p3306 This results in the following output: Nmap scan report for 192.168.2.69 Host is up (0.00032s latency). PORT STATE SERVICE VERSION 3306/tcp open mysql MySQL 5.7.29 MAC Address: 00:0C:29:28:55:8A (VMware)
Nmap script: mysql-info
As reported on Nmap documentation, mysql-info script connects to a MySQL server and prints information such as the protocol and version numbers, thread ID, status, capabilities, and the password salt. Repeating the former scan with this script results as follow:
nmap --script=mysql-info 192.168.2.69 -p3306 Nmap scan report for 192.168.2.69 Host is up (0.000056s latency). Not shown: 998 closed ports PORT STATE SERVICE 3306/tcp open mysql | mysql-info: | Protocol: 10 | Version: 5.7.29 | Thread ID: 15 | Capabilities flags: 65535 | Some Capabilities: Speaks41ProtocolOld, DontAllowDatabaseTableColumn, Support41Auth, FoundRows, LongPassword, ConnectWithDatabase, IgnoreSpaceBeforeParenthesis, SupportsLoadDataLocal, LongColumnFlag, IgnoreSigpipes, InteractiveClient, ODBCClient, SwitchToSSLAfterHandshake, Speaks41ProtocolNew, SupportsTransactions, SupportsCompression, SupportsAuthPlugins, SupportsMultipleStatments, SupportsMultipleResults | Status: Autocommit | Salt: =@l`.iWUY\x05D[hOHavs |_ Auth Plugin Name: mysql_native_password MAC Address: 00:0C:29:28:55:8A (VMware)
How this information leakage could be avoided? Nmap would not have been able to get all these pieces of information if the root user hadn’t granted for all hosts:
GRANT ALL PRIVILEGES ON *.* TO root@'%';. Here the sign
% means “all hosts”. On the other hand, this isn’t a best practice at all: why a system administrator would allow connection from any host instead of some specific hosts? Allowing the application server should be enough.
The right way is:
GRANT ALL PRIVILEGES ON *.* TO root@'192.168.x.x';”
where the IP belongs to the application. As well the administrator IP can be added but for sure not “all hosts”.
Nmap script: mysql-empty-password
Now the fun part begins. Try to remove the root password, just to see what Nmap can discover. Maybe you’re thinking this is an unreal scenario but it’s not: in the other section you’ll see how to find anonymous MySql around the web. and you’ll be amazed about how many lazy administrators leave public exposed MySql instances without any protection.
If you’re testing on a fresh installation, maybe you have to remove the validate_password plugin:
mysql > uninstall plugin validate_password;
Then you can remove the root password:
SET PASSWORD FOR root@'%'=PASSWORD('');
Now scan the host again using mysql-empty-password script:
nmap --script=mysql-empty-password 192.168.2.69 -p3306 Nmap scan report for 192.168.2.69 Host is up (0.000064s latency). Not shown: 998 closed ports PORT STATE SERVICE 3306/tcp open mysql | mysql-empty-password: |_ root account has empty password MAC Address: 00:0C:29:28:55:8A (VMware)
As you can see the root user “has empty password”. This obviously means that everyone can connect to this MySql instance.
Nmap script: mysql-databases
At this point using the mysql-databases script is trivial and the results obvious. Before performing the attack, let’s add our database on the MySql server:
CREATE DATABASE attacker;
Now launch the scan:
nmap --script=mysql-databases 192.168.2.69 -p 3306 Nmap scan report for 192.168.2.69 Host is up (0.000064s latency). Not shown: 998 closed ports PORT STATE SERVICE 3306/tcp open mysql | mysql-databases: | information_schema | attacker | mysql | performance_schema | sys MAC Address: 00:0C:29:28:55:8A (VMware)
It is useful to specify that not all scan works every time. Based on MySql configuration and version, user grants and others “unpredictable” details, a scan can return results or not. In cases where no results are obtained, you only have to go on.
Nmap script: mysql-dump-hashes
The mysql-dump-hashes Nmap script dumps the password hashes from a MySQL server in a format suitable for cracking by tools such as John the Ripper.
Usually, the username and password arguments take precedence over credentials discovered by the mysql-brute and mysql-empty-password scripts, but in our scenario, the root user doesn’t have a password, so there’s no need to add script arguments. Anyway, a scan using the script argument would look like this:
nmap -p 3306 192.168.2.69 --script mysql-dump-hashes --script-args='username=root,password=secret'
So let’s scan the MySql host:
nmap -p 3306 192.168.2.69 --script mysql-dump-hashes Nmap scan report for 192.168.2.69 Host is up (0.000064s latency). Not shown: 998 closed ports PORT STATE SERVICE 3306/tcp open mysql | mysql-dump-hashes: | root:*9B500343BC52E2911172EB52AE5CF4847……... |_ attacker:*14E65567ABDB5135D0CFD9A70B3032C……... MAC Address: 00:0C:29:28:55:8A (VMware)
Now you can decrypt these hashes using John the Ripper or one of the may online rainbow table services.
Nmap script: mysql-enum
The mysql-enum script performs valid-user enumeration against MySQL server: never forget that you may get a wide range of different results, especially based on version.
In our case the scan is straightforward:
nmap --script=mysql-enum 192.168.2.69 -p3306 Nmap scan report for 192.168.2.69 Host is up (0.00037s latency). PORT STATE SERVICE 3306/tcp open mysql | mysql-enum: | Valid usernames: | root:<empty> - Valid credentials | netadmin:<empty> - Valid credentials | web:<empty> - Valid credentials | test:<empty> - Valid credentials | user:<empty> - Valid credentials | sysadmin:<empty> - Valid credentials | administrator:<empty> - Valid credentials | webadmin:<empty> - Valid credentials | admin:<empty> - Valid credentials | guest:<empty> - Valid credentials |_ Statistics: Performed 10 guesses in 1 seconds, average tps: 10.0 MAC Address: 00:0C:29:28:55:8A (VMware)
This script can be used in conjunction with the mysql-brute: in this case, you need to provide valid user and password files as arguments.
Nmap script: mysql-query
The mysql-query script tries to execute the query on MySql instance. No need to mention that you can specify a user and its password in script arguments and that the results will be returned according to user grants.
The scan need you to specify the query:
nmap --script=mysql-query 192.168.2.69 -p3306 --script-args='query=show databases' Nmap scan report for 192.168.2.69 Host is up (0.00063s latency). PORT STATE SERVICE 3306/tcp open mysql | mysql-query: |_ information_schema, attacker, mysql, performance_schema, sys
Nmap script: mysql-users
The mysql-users script attempts to list all users on a MySQL server. As in the previous examples, just specify the script “mysql-user”, the target and eventually the port.
nmap --script=mysql-users 192.168.2.69 -p3306 Nmap scan report for 192.168.2.69 Host is up (0.00057s latency). PORT STATE SERVICE 3306/tcp open mysql | mysql-users: |_ root
If you already have credentials for the MySql server, you can use them with script arguments by adding:
--script-args='username=root,password=secret'. You can also user another user than root and results will be based upon its grants.
Nmap script: mysql-variables
The mysql-variables goal is to extract all the variables from the MySql instance. Its success is highly dependent on the MySql version and user grants.
nmap --script=mysql-databases 192.168.2.69 -p3306 Nmap scan report for 192.168.2.69 Host is up (0.00052s latency). PORT STATE SERVICE 3306/tcp open mysql | mysql-variables: | auto_increment_increment: 1 | auto_increment_offset: 1 | automatic_sp_privileges: ON | back_log: 50 | basedir: /usr/ ...
To know the variables may be a very important starting point to better understand the attacked host, its weakness and additional vulnerabilities.
Nmap script: mysql-vuln-cve2012-2122
The mysql-vuln-cve-2012-2122 is taken as an example to show the Nmap’s ability to provide deep results about known vulnerability. In this case, this script attempts to bypass authentication in MySQL servers by exploiting CVE2012-2122. If it’s vulnerable, it will also attempt to dump the MySQL usernames and password hashes. Only MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22 are vulnerable but exploitation depends on whether memcmp() returns an arbitrary integer outside of -128..127 range. Our scenario doesn’t provide such versions but the scan would look like this:
nmap --script=mysql-vuln-cve2012-2122 192.168.2.69 -p3306
If the scan is successful, the Nmap script will also attempt to dump the MySQL usernames and password hashes.
It is not necessary to go in deep about this CVE: the point is that with Nmap you can discover and exploit MySql vulnerability.
scanner/mysql/mysql_writable_dirs module enumerates writeable directories using the
MySQL SELECT INTO DUMPFILE MySql feature. To better understand how it works, you should know that MySql allows the user to write the resultset into a single unformatted row, without any separators, in a file. In other words, the output of a query is being written in DUMPFILE.
By default MySQL server is being started with –secure-file-priv option which basically limits from which directories you can load files using
LOAD DATA INFILE. Furthermore these operations are permitted only to users who have the
FILE privilege. Anyway, a lot of MySql instances allow file upload haphazard, so its worth to check if this feature is enabled.
Before proceeding with the scan, check-in your MySql host if
secure_file_priv is enabled and which is its value:
mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.01 sec)
Then we create a directories.txt file with /var/lib/mysql-files/ and others directories to test:
/var/lib/mysql-files/ /tmp/ /root/
Now in Metasploit:
msf5 > use auxiliary/scanner/mysql/mysql_writable_dirs msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > show options Module options (auxiliary/scanner/mysql/mysql_writable_dirs): Name Current Setting Required Description ---- --------------- -------- ----------- DIR_LIST yes List of directories to test FILE_NAME vkvrKdWb yes Name of file to write PASSWORD no The password for the specified username RHOSTS yes The target host(s), range CIDR identifier, or hosts file with syntax 'file:<path>' RPORT 3306 yes The target port (TCP) THREADS 1 yes The number of concurrent threads (max one per host) USERNAME root yes The username to authenticate as msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > set DIR_LIST /root/directories.txt msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > set RHOSTS 192.168.2.69 msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > set USERNAME root msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > set PASSWORD your_root_password (if any)
Finally let’s start the scan:
msf5 auxiliary(scanner/mysql/mysql_writable_dirs) > run [!] 192.168.2.69:3306 - For every writable directory found, a file called vkvrKdWb with the text test will be written to the directory. [*] 192.168.2.69:3306 - Login... [*] 192.168.2.69:3306 - Checking /tmp... [!] 192.168.2.69:3306 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement [*] 192.168.2.69:3306 - Checking /var/lib/mysql-files/... [+] 192.168.2.69:3306 - /var/lib/mysql-files/ is writeable [*] 192.168.2.69:3306 - Checking /root... [!] 192.168.2.69:3306 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement [*] 192.168.2.69:3306 - Scanned 1 of 1 hosts (100% complete) [*] Auxiliary module execution completed
As you can see the
/var/lib/mysql-files/ is writeable. For an additional check, open this directory on the MySql host:
[user@mysql]# ls /var/lib/mysql-files/ vkvrKdWb [user@mysql]# cat /var/lib/mysql-files/vkvrKdWb test
vkvrKdWb has been uploaded in the directory.
mysql_enum Metasploit module allows for simple enumeration of MySQL Database Server provided proper credentials to connect remotely. Like other Metasploit modules, you have to specify username, password and remote host IP. With these data, the modules attempt to extract as many pieces of information.
Let’s see an example:
msf5 > use auxiliary/admin/mysql/mysql_enum msf5 auxiliary(admin/mysql/mysql_enum) > set RHOST 192.168.2.69 msf5 auxiliary(admin/mysql/mysql_enum) > set USERNAME root msf5 auxiliary(admin/mysql/mysql_enum) > set PASSWORD password if any msf5 auxiliary(admin/mysql/mysql_enum) > run [*] Running module against 192.168.2.69 [*] 192.168.2.69:3306 - Running MySQL Enumerator... [*] 192.168.2.69:3306 - Enumerating Parameters [*] 192.168.2.69:3306 - MySQL Version: 5.7.29 [*] 192.168.2.69:3306 - Compiled for the following OS: Linux [*] 192.168.2.69:3306 - Architecture: x86_64 [*] 192.168.2.69:3306 - Server Hostname: mysql-test [*] 192.168.2.69:3306 - Data Directory: /var/lib/mysql/ [*] 192.168.2.69:3306 - Logging of queries and logins: ON [*] 192.168.2.69:3306 - Log Files Location: OFF [*] 192.168.2.69:3306 - Old Password Hashing Algorithm 0 [*] 192.168.2.69:3306 - Loading of local files: ON [*] 192.168.2.69:3306 - Deny logins with old Pre-4.1 Passwords: ON [*] 192.168.2.69:3306 - Allow Use of symlinks for Database Files: DISABLED [*] 192.168.2.69:3306 - Allow Table Merge: [*] 192.168.2.69:3306 - SSL Connections: Enabled [*] 192.168.2.69:3306 - SSL CA Certificate: ca.pem [*] 192.168.2.69:3306 - SSL Key: server-key.pem [*] 192.168.2.69:3306 - SSL Certificate: server-cert.pem [*] 192.168.2.69:3306 - Enumerating Accounts: [*] 192.168.2.69:3306 - The following users have GRANT Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - The following users have CREATE USER Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following users have RELOAD Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following users have SHUTDOWN Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following users have SUPER Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: mysql.session Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following users have FILE Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following users have PROCESS Privilege: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [*] 192.168.2.69:3306 - The following accounts have privileges to the mysql database: [*] 192.168.2.69:3306 - User: root Host: localhost [*] 192.168.2.69:3306 - User: root Host: % [-] 192.168.2.69:3306 - MySQL Error: RbMysql::BadFieldError Unknown column 'password' in 'field list' [*] 192.168.2.69:3306 - The following accounts are not restricted by source: [*] 192.168.2.69:3306 - User: root Host: % [*] Auxiliary module execution completed
A huge amount of data has been enumerated from the MySql server. The example uses root user, but any users and password can be provided: cleary the module will extract only data which the user can access.
As seen with the similar Nmap script, the mysql_sql module allows you to execute a query on a remote host. Besides credentials and host, you have to provide the query you want to be executed.
msf5 > use auxiliary/admin/mysql/mysql_sql msf5 auxiliary(admin/mysql/mysql_sql) > set USERNAME root msf5 auxiliary(admin/mysql/mysql_sql) > set PASSWORD password if any msf5 auxiliary(admin/mysql/mysql_sql) > set RHOST 192.168.2.69 msf5 auxiliary(admin/mysql/mysql_sql) > set SQL show databases; msf5 auxiliary(admin/mysql/mysql_sql) > run [*] Running module against 192.168.2.69 [*] 192.168.2.69:3306 - Sending statement: 'show databases;'... [*] 192.168.2.69:3306 - | information_schema | [*] 192.168.2.69:3306 - | attacker | [*] 192.168.2.69:3306 - | mysql | [*] 192.168.2.69:3306 - | performance_schema | [*] 192.168.2.69:3306 - | sys | [*] Auxiliary module execution completed
Simple and effective. This module may be a worthwhile alternative to its Nmap equivalent since it uses a different method to execute the query and get data from the MySql host.
auxiliary/scanner/mysql/mysql_file_enum module enumerates files and directories using the MySQL load_file feature. Before trying the module, you should know how load_file works. This MySql function reads the file passed as argument and returns the file contents as a string. Then it can be used to execute an insert in a table.
Let’s see an example:
mysql> UPDATE table SET blob_column=LOAD_FILE('/tmp/picture') WHERE id=1;
This query will insert in
blob_content column of table “table” the content of
That said, go on and try to abuse this function. One of the options needed by the Metasploit module is a file containing a list of directories to scan. In my case I’m using the following file:
root@kali:~# cat dir.txt /tmp /var/lib/mysql-files/ /root
So configure options:
msf5 > use auxiliary/scanner/mysql/mysql_file_enum msf5 auxiliary(scanner/mysql/mysql_file_enum) > show options Module options (auxiliary/scanner/mysql/mysql_file_enum): Name Current Setting Required Description ---- --------------- -------- ----------- DATABASE_NAME mysql yes Name of database to use FILE_LIST yes List of directories to enumerate PASSWORD no The password for the specified username RHOSTS yes The target host(s), range CIDR identifier, or hosts file with syntax 'file:<path>' RPORT 3306 yes The target port (TCP) TABLE_NAME NYoEdrHL yes Name of table to use - Warning, if the table already exists its contents will be corrupted THREADS 1 yes The number of concurrent threads (max one per host) USERNAME root yes The username to authenticate as msf5 auxiliary(scanner/mysql/mysql_file_enum) > set FILE_LIST /root/dir.txt FILE_LIST => /root/dir.txt msf5 auxiliary(scanner/mysql/mysql_file_enum) > set RHOSTS 192.168.2.69 RHOSTS => 192.168.2.69 msf5 auxiliary(scanner/mysql/mysql_file_enum) > run [+] 192.168.2.69:3306 - /var/lib/mysql-files/ is a directory and exists [*] 192.168.2.69:3306 - Scanned 1 of 1 hosts (100% complete) [*] Auxiliary module execution completed
/var/lib/mysql-files/ appears in results? It’s quite simple: because the
secure_file_priv variable in MySql instance is set like this.
mysql> show variables like "%secure_file_priv%"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec)
In this case, MySQL server has been started with
--secure-file-priv option which basically limits from which directories you can load files into, but it’s very common to see instances where
load_file works in every directory writeable by the user that run MySql on the system (usually MySql).
As the name says, the auxiliary/scanner/mysql/mysql_hashdump module extracts MySql users hash from the instance.
Before proceeding, try to change, or set, the root user password on MySql server:
[user@mysql]# mysqladmin -u root "password" New password: password Confirm new password: password
Now insert username, password, rhost in the module options:
msf5 > use scanner/mysql/mysql_hashdump msf5 auxiliary(scanner/mysql/mysql_hashdump) > show options Module options (auxiliary/scanner/mysql/mysql_hashdump): Name Current Setting Required Description ---- --------------- -------- ----------- PASSWORD no The password for the specified username RHOSTS yes The target host(s), range CIDR identifier, or hosts file with syntax 'file:<path>' RPORT 3306 yes The target port (TCP) THREADS 1 yes The number of concurrent threads (max one per host) USERNAME no The username to authenticate as msf5 auxiliary(scanner/mysql/mysql_hashdump) > set USERNAME root USERNAME => root msf5 auxiliary(scanner/mysql/mysql_hashdump) > set PASSWORD password PASSWORD => password msf5 auxiliary(scanner/mysql/mysql_hashdump) > set RHOSTS 192.168.2.69 RHOSTS => 192.168.2.69
Then run the module:
msf5 auxiliary(scanner/mysql/mysql_hashdump) > run [+] 192.168.2.69:3306 - Saving HashString as Loot: mysql.session:*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE [+] 192.168.2.69:3306 - Saving HashString as Loot: mysql.sys:*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE [+] 192.168.2.69:3306 - Saving HashString as Loot: root:*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 [*] 192.168.2.69:3306 - Scanned 1 of 1 hosts (100% complete) [*] Auxiliary module execution completed
As you can see the root hash is found and if you google
2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19” you’ll find that it corresponds to
password. Obviously this is useless in this case because we had to insert the password to run the module. But it’s not always the case because hash retrieving depends on which grants are assigned to users: so maybe you can retrieve hashes through another user, which has
SUPER privileges. In other words, it always worth a try with this module.
auxiliary/scanner/mysql/mysql_schemadump objective is clear by the name: to retrieve the MySql schema dump.
Since this is an educational example, you can remove again the root password from MySql instance:
mysql> SET PASSWORD FOR root@"%"=PASSWORD(''); Query OK, 0 rows affected, 1 warning (0.01 sec) msf5 > use scanner/mysql/mysql_schemadump msf5 auxiliary(scanner/mysql/mysql_schemadump) > show options Module options (auxiliary/scanner/mysql/mysql_schemadump): Name Current Setting Required Description ---- --------------- -------- ----------- DISPLAY_RESULTS true yes Display the Results to the Screen PASSWORD no The password for the specified username RHOSTS yes The target host(s), range CIDR identifier, or hosts file with syntax 'file:<path>' RPORT 3306 yes The target port (TCP) THREADS 1 yes The number of concurrent threads (max one per host) USERNAME no The username to authenticate as msf5 auxiliary(scanner/mysql/mysql_schemadump) > set RHOSTS 192.168.2.69 RHOSTS => 192.168.2.69 msf5 auxiliary(scanner/mysql/mysql_schemadump) > set USERNAME root USERNAME => root
Then execute the module:
msf5 auxiliary(scanner/mysql/mysql_schemadump) > run [+] 192.168.2.69:3306 - Schema stored in: /root/.msf4/loot/20200210220807_default_192.168.2.69_mysql_schema_823779.txt [+] 192.168.2.69:3306 - MySQL Server Schema Host: 192.168.2.69 Port: 3306 ==================== - DBName: attacker Tables:  - DBName: sys Tables: - TableName: host_summary Columns: - ColumnName: host ColumnType: varchar(60) - ColumnName: statements ColumnType: decimal(64,0) - ColumnName: statement_latency ColumnType: text - ColumnName: statement_avg_latency ColumnType: text [...] - TableName: x$waits_global_by_latency Columns: - ColumnName: events ColumnType: varchar(128) - ColumnName: total ColumnType: bigint(20) unsigned - ColumnName: total_latency ColumnType: bigint(20) unsigned - ColumnName: avg_latency ColumnType: bigint(20) unsigned - ColumnName: max_latency ColumnType: bigint(20) unsigned [*] 192.168.2.69:3306 - Scanned 1 of 1 hosts (100% complete) [*] Auxiliary module execution completed
The content is very long because all the schema is dumped, so it has been cut here. As said previously we’re running Metasploit modules under MySql root user but results depend on user’s grants. It’s not uncommon to find users being used in applications to which are assigned root-like grants.
auxiliary/scanner/mysql/mysql_version module enumerates the version of MySQL servers. It may seem trivial but to know the MySql version it’s a big step to exploit the instance. In fact, this information allows the attacker to find a specific exploit or attack aimed at the running version.
To use this module, you have to insert only the remote host:
msf5 > use scanner/mysql/mysql_version msf5 auxiliary(scanner/mysql/mysql_version) > show options Module options (auxiliary/scanner/mysql/mysql_version): Name Current Setting Required Description ---- --------------- -------- ----------- RHOSTS yes The target host(s), range CIDR identifier, or hosts file with syntax 'file:<path>' RPORT 3306 yes The target port (TCP) THREADS 1 yes The number of concurrent threads (max one per host) msf5 auxiliary(scanner/mysql/mysql_version) > set RHOSTS 192.168.2.69 RHOSTS => 192.168.2.69 msf5 auxiliary(scanner/mysql/mysql_version) > run [+] 192.168.2.69:3306 - 192.168.2.69:3306 is running MySQL 5.7.29 (protocol 10) [*] 192.168.2.69:3306 - Scanned 1 of 1 hosts (100% complete) [*] Auxiliary module execution completed
The running version is just MySQL 5.7.29. To avoid underestimating the impact of this module, google for “mysql 5.7.29 exploit” and see how many results you can find.
From SQL Injection to shell…
…and from shell to root
Brute forcing (Hydra)
Brute forcing (Metasploit)
Brute forcing (Nmap script mysql-brute)
Sqlmap example to gain a shell