Chapter 17: MySQL Architecture | The Database Hackers Handbook: Defending Database Servers

Examining the Physical Database Architecture

MySQL claims to be "The world's most popular open source database," and with good reason. It's free, and runs on a wide variety of platforms. It's relatively simple, easy to configure, and performs well even under significant load. By comparison to some of the other databases discussed in this volume, it is quite simple, but still has a sufficiently wide variety of security-relevant configuration issues to make securing it a challenge.

MySQL is a somewhat unusual open source project in that the source code for the database server is owned by a company (MySQL AB, based in Sweden) and released under both the GPL and a commercial license. The commercial license comes with a support package, but more importantly, it enables other companies to incorporate the MySQL engine into their product without making their product open source.

MySQL AB recommends that the database server be installed from a binary package rather than by building the source code. Binary packages are available for the following:

Linux x86

Linux IA64

Linux AMD64

Windows

Solaris

FreeBSD

Mac OS X

HP-UX

IBM AIX

QNX

Novell Netware

OpenBSD

SGI IRIX

DEC OSF

and the source code itself will build on an even wider variety of platforms.

Most of the discussions in this chapter refer to the GPL version of MySQL version 4.0 and 4.1 ”which is the latest production version and contains a number of important security fixes, notably significant changes to the authentication protocol and password hashing mechanism.

Deployment

Because it's so popular, and free, you find MySQL servers in all manner of places on a network. Many open source projects integrate with it so it is not uncommon to find users running MySQL on their desktop machines, rather than dedicated servers.

In a typical configuration, a client will connect to MySQL over TCP port 3306. On the Windows platforms it is possible to configure MySQL to run over named pipes (with the -enable-named-pipe option) but this is not a recommended configuration. By default, MySQL running in named pipe mode will listen on both TCP port 3306 and a named pipe called MySQL. The network protocol that MySQL uses is relatively simple (when compared with other database systems such as Oracle) and is plaintext by default, though an SSL-enabled version is available in more recent versions (4.0.0 and higher). The SSL-enabled versions still run over TCP port 3306, and negotiate SSL in-stream.

You can easily check which version of MySQL a host is running because it returns the major and minor version in a banner when you connect. Some versions also return a clue to the operating system, for example 4.0.18-nt is returned by version 4.0.18 of the Windows build of MySQL. At the time of writing this feature cannot be changed by the administrator other than by altering the source code or editing the binary, so it is likely that any MySQL version numbers you see in a network are correct. Any banner- grabbing TCP portscanner should return the MySQL version.

Perhaps the most common use for MySQL is to provide a backend to dynamic web applications. It is normally found as a backend to Apache/PHP applications and (depending on the hardware budget of the network in question) may even be running on the same host as the web server. In larger environments it may be used as a logging server, as the destination for Intrusion Detection System logs, web logs, or other audit tasks . In an internal network you might find it being used in a more traditional, ODBC-oriented client-server mode, perhaps as the backend to a helpdesk system. And then there are a number of reasons why a user would run MySQL on their own desktop machine, so it is not unusual to find MySQL instances on workstations, especially in development environments.

Because the MySQL communications protocol has historically been plaintext, one fairly popular configuration is to deploy an SSH server on the same host as the MySQL server, and use port forwarding to connect to port 3306 over the encrypted tunnel. There are several advantages to this approach; it means that the data is encrypted in transit, it enforces an additional authentication step, and it also provides an additional audit record of connections to the database. For details of how to deploy this configuration, see

 http://dev.mysql.com/doc/mysql/en/Security_against_attack.html

and

 http://dev.mysql.com/doc/mysql/en/Secure_connections.html

One dangerous piece of advice that is seen fairly often in MySQL secure configuration guides is that the MySQL server should be run on the same host as the web server, so that remote connections to the MySQL server can be prohibited . This configuration leads to dangers of its own, however. Because the MySQL tables are stored in files that are not normally locked, a file disclosure bug in the web application may well lead to an attacker being able to download the entire contents of the database. From another perspective, a SQL injection bug in the web application may well lead to the attacker being able to modify the contents of scripts on the web server. Correct file permissions will prevent these problems, but it is worth bearing in mind that placing the web server and database server on the same host opens up many other avenues to the attacker.

WinMySQLAdmin Autostart

When MySQL is installed on a Windows platform, the WinMySQLAdmin tool is supplied with it. When this tool is run for the first time, it will add itself to the startup group for the user that runs it. When it runs, WinMySQLAdmin will automatically start MySQL, which can result in instances of MySQL running on Windows hosts inadvertently.

Also, when WinMySQLAdmin is run on a host that has no default MySQL user account, it prompts for a username and password pair to create. It stores these credentials in plaintext password in the my.ini file in the system root directory (for example, c:\winnt). This file is normally readable to any user of that host.

Default Usernames and Passwords

The default configuration of MySQL varies depending on the platform, mode of deployment, distribution (source or binary), and initial configuration, but in some cases it is possible for a remote attacker to compromise a MySQL server immediately after installation.

For example, in some default configurations of MySQL 4.0.20, there are four default entries in the mysql.user table: two entries for root and two entries for the anonymous account. There is a remote entry with root privileges for the account root on the host build. The precise semantics of entries in these tables are discussed in detail later in this chapter, but for now, here's what they mean in simple terms:

If you are on the local host, you can authenticate as "root" with a blank password and have total control of the database.
If you are on the local host, you can authenticate using any username and have guest access to the database.
If you are on a remote host, but can control the server's name resolution in order to make your apparent hostname "build," you can authenticate as root with a blank password and have total control of the database.
If you are on a remote host called build (as above) you can authenticate using any username and have guest access to the database.

On a Windows host, the presence of the root account results in any local user being able to upgrade themselves to local system-level access (MySQL runs as SYSTEM by default). Worse, if the attacker simply names his machine build, he will have remote SYSTEM-level access to the machine as soon as the MySQL service starts. Obviously the attacker would have to be in the same NetBIOS name domain as the target, or have the ability to spoof a DNS response.

There are several ways of doing this, but one obvious path to root is as follows :

Create a User Defined Function dll on the remote host via select . . . into dumpfile. The function should allow the upload and execution of an arbitrary .exe. User-defined functions are covered in depth in a later chapter.
Use "create function" to configure MySQL to run the malicious function as a UDF.
Upload and run the malicious code. Because it's running as SYSTEM it can do anything on the machine, including installing Trojans and adding accounts.

This is precisely the mechanism used by the W32.Spybot.IVQ worm that infected thousands of Internet- facing Windows MySQL servers in January 2005.

The best protection against this problem is to do the following:

Disable network access while installing MySQL (either pull the network cable out or apply a block all firewall ruleset).
Immediately after installation, remove all accounts from the mysql.user table except the localhost root account.
Apply a complex password to the localhost root account.

Protocol

MySQL uses a proprietary protocol for authentication and for sending and receiving data. This protocol is relatively simple, and writing a custom client for MySQL is fairly straightforward. That said, several serious bugs in the various versions of the MySQL authentication protocol can lead to an almost immediate compromise of the server. The following section is a brief pr cis of known flaws in the various versions of the authentication protocol, along with an overview of other attacks on it.

Before describing the attacks, we will describe the rough packet format and cryptographic mechanisms involved in the authentication protocol.

When a client connects, the server sends a greeting packet, which contains the following fields:

Packet Length (3 bytes)

Packet Number (1 byte)

Protocol Version (1 byte)

Server Version String (null- terminated )

Server Thread ID (4 bytes)

Challenge String (null-terminated)

Server Capabilities Flags (2 bytes)

Server Character Set (1 byte)

Server Status (2 bytes)

Padding (remainder of packet)

In terms of the authentication protocol, the relevant items here are the Protocol Version and the Challenge, though the Server Version String is very helpful in determining which authentication bugs the server is vulnerable to.

The client then sends an authentication packet to the server:

Packet Length (3 bytes)

Packet Number (1 byte)

Client Capabilities (2 bytes)

Max packet size (3 bytes)

Username (null terminated)

Password (null terminated challenge response)

Bugs in the Authentication Protocol

There have been a fairly significant number of bugs in the MySQL authentication protocol. We document these here for reference, in chronological order.

Basic Cryptographic Weakness in the Authentication Protocol Prior to 4.1

In versions of MySQL prior to version 4.1, knowledge of the password hash (contained in the mysql.user table) was sufficient to authenticate, rather than knowledge of the password . This means that there is almost no point in writing a password cracker for the password hashes in MySQL versions prior to 4.1, because it is fairly straightforward to patch the standard MySQL client to accept a password hash rather than a password. Of course, users tend to re-use passwords (especially root passwords) so cracking any password hash is of some value when the security of the network as a whole is taken into account.

Authentication Algorithm Prior to 3.23.11

In MySQL versions prior to 3.23.11, there was a serious bug in the authentication mechanism that meant that an attacker could authenticate using only a single character of the scrambled password. It turns out that the scrambled string consists of characters from a set of 32, so the attacker needed only a small number of guesses to log in.

CHANGE_USER Prior to 3.23.54

In MySQL versions prior to 3.23.54, if the user could authenticate, he could then issue a CHANGE_USER command with either an overly long string (to trigger a buffer overflow) or a single byte string, to allow easy privilege elevation.

Authentication Algorithm in 4.1.1, 4.1.2, and 5.0.0

By submitting a carefully crafted authentication packet, it is possible for an attacker to bypass password authentication in MySQL 4.1.0 to 4.1.2, and early builds of 5.0.

 From check_connection (sql_parse.cpp), line ~837:   /*      Old clients send null-terminated string as password; new clients send     the size (1 byte) + string (not null-terminated). Hence in case of empty     password both send '  From check_connection (sql_parse.cpp), line ~837: /* Old clients send null-terminated string as password; new clients send the size (1 byte) + string (not null-terminated). Hence in case of empty password both send '\0'. */ uint passwd_len= thd->client_capabilities & CLIENT_SECURE_CONNECTION ? *passwd++ : strlen(passwd); 
 '.   */   uint passwd_len= thd->client_capabilities & CLIENT_SECURE_CONNECTION ?      *passwd++ : strlen(passwd);

Provided 0x8000 is specified in the client capabilities flags, users can specify the passwd_len field of their choice. For this attack, we will choose 0x14 (20), which is the expected SHA1 hash length.

Several checks are now carried out to ensure that the user is authenticating from a host that is permitted to connect. Provided these checks are passed, we reach:

 /* check password: it should be empty or valid */         if (passwd_len == acl_user_tmp->salt_len)         {           if (acl_user_tmp->salt_len == 0                acl_user_tmp->salt_len == SCRAMBLE_LENGTH &&               check_scramble(passwd, thd->scramble, acl_user_tmp->salt) == 0                check_scramble_323(passwd, thd->scramble,                                  (ulong *) acl_user_tmp->salt) == 0)           {             acl_user= acl_user_tmp;             res= 0;           }         }

The check_scramble function fails, but within the check_scramble_323 function we see:

 my_bool check_scramble_323(const char *scrambled, const char *message,                    ulong *hash_pass) {   struct rand_struct rand_st;   ulong hash_message[2];   char buff[16],*to,extra;                      /* Big enough for check */   const char *pos;      hash_password(hash_message, message, SCRAMBLE_LENGTH_323);   randominit(&rand_st,hash_pass[0] ^ hash_message[0],              hash_pass[1] ^ hash_message[1]);   to=buff;   for (pos=scrambled ; *pos ; pos++)     *to++=(char) (floor(my_rnd(&rand_st)*31)+64);   extra=(char) (floor(my_rnd(&rand_st)*31));   to=buff;   while (*scrambled)   {     if (*scrambled++ != (char) (*to++ ^ extra))       return 1;                                 /* Wrong password */   }   return 0; }

At this point, the user has specified a scrambled string that is as long as he wants. In the case of the straightforward authentication bypass, this is a zero-length string. The final loop compares each character in the scrambled string against the string that MySQL knows is the correct response, until there are no more characters in scrambled. Because there are no characters at all in scrambled, the function returns 0 immediately, allowing the user to authenticate with a zero-length string.

This bug is relatively easy to exploit, although it is necessary to write a custom MySQL client in order to do so.

In addition to the zero-length string authentication bypass, a long scramble string can overflow the stack-based buffer. The buffer is overflowed with characters output from my_rnd(), a pseudo random number generator. The characters are in the range 0x40..0x5f. On some platforms, arbitrary code execution is possible, though the exploit is complex and requires either brute force, or knowledge of at least one password hash.

The attacker must know or be able to guess the name of a user in order for either of these attacks to work, so renaming the default MySQL root account is a reasonable precaution. Also, the account in question must be accessible from the attacker's host, so applying IP-address “based login restrictions will also mitigate this bug.