Secure PHP Connections to MySQL with SSL
Separating the elements of your LAMP stack makes a lot of security sense. If you host your web server on a different machine than your database server you get a couple of wins. A compromise in your database won't allow an attacker to alter web pages or write files to the web server, and compromise of your web server wouldn't allow attackers to read the text based MySQL data files. By segregating the functionality you effectively sandbox each tier of your web application to prevent the escalation of compromises. The potential downside of separating these elements, however, is that you introduce a new attack vector, namely snooping traffic on the wire as it travels between the database and the web server.
To mitigate this threat it is useful to implement encrypted channels of communication. Although you could use a tool like stunnel to set up a tunneled connection, there are much more direct methods. As of version 5 MySQL has supported SSL enabled connectivity. This allows your clients (the web applications) to communicate over a secure SSL connection with the database back end. Unfortunately setting up this connectivity is poorly documented and quite a hassle. The purpose of this article is to show you how to set up a SSL connection from PHP to a remote MySQL database. The examples used in this article were drawn from a CentOS 5.3 installation, but they should translate to almost any Linux distribution.
The first step to setting up your encrypted connection is to create and sign SSL certificates on the server. Using the OpenSSL program, included by default in most Linux distributions, this is relatively straightforward. First we'll create a directory to store our certificates, keys and requests, if one does not already exist. For this, and the remaining operations in this tutorial, it will be necessary to use the root account. Log into your database server and 'su' to the root user. To create the directory for storing our files use:
# mkdir /etc/ssl; mkdir /etc/ssl/mysql; cd /etc/ssl/mysql
Now that we have our directory let's create a master key. This is the server key that is used to self sign the certificates used in our operations. Although self-signed certificates should not generally be trusted, we're using them exclusively to encrypt traffic in our own LAN. To create our RSA key, with 2048 bit encryption using the triple DES algorithm use the following command:
# openssl genrsa -des3 -out server.key 2048
You should see the following output, and be prompted to enter a password for the key:
Generating RSA private key, 2048 bit long modulus ....+++ ............................+++ e is 65537 (0x10001) Enter pass phrase for server.key: Verifying - Enter pass phrase for server.key:
Now that we've generated our key we have to generate a 'certificate signing request'. Normally this file would be sent to a CA, or Certificate Authority, which is a third party that verifies and signs your request and returns a certificate to you. To generate the request use:
# openssl req -new -key server.key -out server.csr
Because this is a commercial service and we're not interested in trust, but only confidentiality and authentication in our encrypted communication we'll sign the request ourselves (using our previously generated key). To do this use:
# openssl x509 -req -days 730 -in server.csr -signkey server.key -out server.cert
Now that we have the cert and the key we want to take a final step. This is to remove the passphrase associated with the key. While this reduces the security of the key (since anyone who has the file can use it as it no longer requires a password), it is vital for our uses. Because we're going to use the key to encrypt all communications to the MySQL server we don't want any of the automated scripts that might start the server to have to require an interactive password entry. Without this step, whenever your server starts it will hang until someone enters the proper key password. This can be extremely problematic if you have to restart the server remotely. To remove the key use the following command and enter the password when prompted:
# openssl rsa -in server.key -out server.key
Now that our server key and certificate is all set up all we have to modify our MySQL configuration file, usually /etc/my.cnf, to add the SSL parameters so when the server is started it enables SSL. To do this add the following to the my.cnf file in the [mysqld] section:
ssl-ca=/etc/ssl/mysql/server.csr ssl-cert=/etc/ssl/mysql/server.cert ssl-key=/etc/ssl/mysql/server.key
After this is added go ahead and restart the MySQL server using the initscripts:
# /etc/rc.d/init.d/mysqld restart
After the service restarts let's go ahead and log into MySQL and ensure that the SSL started properly:
# mysql -u root -p mysql Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show variables like 'have_ssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+ 1 row in set (0.00 sec) mysql> quit Bye
If the 'have_ssl' variable is listed as 'Disabled' then the server didn't start with the proper SSL settings. Check the mysql log file (usually in /var/log/mysqld.log) for any errors that might be instructive.
Once you have SSL working properly you'll need to create a new user to test the settings. MySQL users can have SSL connection requirements specified in much the same way that they can have hosts defined from which they can connect to the server. For our testing purposes we'll create a new testing database, then create a test user with privileges to this database. Finally we'll create a single table in this database so we can test querying capabilities. In order to do this log into the MySQL database and use the following:
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create database test_ssl; Query OK, 1 row affected (0.00 sec) mysql> grant all privileges on test_ssl.* to 'test_user'@'192.168.0.10' identified by 'testpass' require ssl; Query OK, 0 rows affected (0.10 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select user, ssl_type from user; +-----------+----------+ | user | ssl_type | +-----------+----------+ | root | | | test_user | ANY | +-----------+----------+ 6 rows in set (0.00 sec) mysql> use test_ssl; Database changed mysql> create table test (test_id int auto_increment primary key, test_data varchar(255)); Query OK, 0 rows affected (0.09 sec) mysql> insert into test set test_data='foo'; Query OK, 1 row affected (0.02 sec)
Now that we have our test user it's time to turn our attention to the web server, the MySQL client that will connect over SSL. The first step in configuration of the client is to actually create a client certificate that we can use to initiate the connection. To do this we'll utilize OpenSSL on our MySQL database server to generate the certificates then transfer them to the client machine. To generate the certificates use the same commands as above, but with different target filenames:
# openssl req -new -key server.key -out client.csr
Fill in the requested values then sign the csr using:
# openssl x509 -req -days 730 -in client.csr -signkey server.key -out client.cert
Now you have to actually copy server.key, client.csr, and client.cert to the client machine (using something like SFTP or SCP). Once they're on the client machine (the web server) go ahead and log into that machine and 'su' to root. Create a directory /etc/ssl/myslq and move server.key, client.csr, and client.cert to this directory. You can do all this with SFTP. In the following example 192.168.0.4 is the client machine:
# sftp root@192.168.0.4 Connecting to 192.168.0.4... root@192.168.0.4's password: sftp> mkdir /etc/ssl sftp> mkdir /etc/ssl/mysql sftp> cd /etc/ssl/mysql sftp> put client.cert Uploading client.cert to /etc/ssl/mysql/client.cert client.cert 100% 1367 1.3KB/s 00:00 sftp> put client.csr Uploading client.csr to /etc/ssl/mysql/client.csr client.csr 100% 1086 1.1KB/s 00:00 sftp> put server.key Uploading server.key to /etc/ssl/mysql/server.key server.key 100% 1675 1.6KB/s 00:00 sftp> quit
Once the files are on the client you have to alter the client's my.cnf. Go ahead and edit that file and add a section called '[client]' if one doesn't exist and add the following values:
[client] ssl-ca=/etc/ssl/mysql/cleint.csr ssl-cert=/etc/ssl/mysql/client.cert ssl-key=/etc/ssl/mysql/server.key
Now that we have all of these values filled in we can go ahead and try (from the client) to log into the server (in this example 192.168.0.5) and attempt to query the database:
# mysql -u test_user -h 192.168.0.5 -p test_ssl Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables; +--------------------+ | Tables_in_test_ssl | +--------------------+ | test | +--------------------+ 1 row in set (0.00 sec) mysql> select * from test; +---------+-----------+ | test_id | test_data | +---------+-----------+ | 1 | foo | +---------+-----------+ 1 row in set (0.00 sec)
As you can see the connection is successful. Because the account is restricted to SSL logins we know that the SSL is set up and working properly. We can sniff the connection from the server machine just to confirm this is the case using the tcpdump program using:
# /usr/sbin/tcpdump -vvv -x -X -i eth0 port 3306
Observing the output will show that the connection is successfully encrypted. Now, on the client, create a new PHP file to test the connection. Make sure that PHP and the PHP CLI is installed for this operation. Change directories to the web root and create a new file called ssl_test.php with the following contents. Be sure to change the MySQL connection address to the appropriate database host location:
<?php
$link = mysql_connect("192.168.0.11","test","testpass",false,MYSQL_CLIENT_SSL)
or die(mysql_error());
$res = mysql_query("SHOW STATUS LIKE 'ssl_cipher';",$link);
print_r(mysql_fetch_row($res));
echo "Finished.";
?>
Once the file is created you can run it at the command line using:
# php ssl_test.php
Array
(
[0] => Ssl_cipher
[1] => DHE-RSA-AES256-SHA
)
Finished.
And you're done! Your connection is now proceeding over an SSL encrypted tunnel. I have run into some problems getting this setup to work on PHP 5.3 with MySQLi, however, so if you're finding problems you may want to switch to the traditional PHP MySQL interface to test and make sure everything is set up properly before you tinker with it to much.