3.9 Securing Connections with SSL

You can now encrypt the connections between PHP and MySQL. Normally, MySQL does all communication in plain text because it's the fastest way to send data. However, MySQL 4.0 lets you use SSL encryption to prevent people from spying on traffic between PHP and MySQL, and MySQL 4.1 extends this to include replication over SSL.

An SSL-enabled version of MySQL doesn't require you to use SSL for all your connections. You can set different permission levels on your accounts, so you can place varying restrictions as you see fit. For some accounts, you may not want the hassle of dealing with SSL and authentication.

Once all your systems are configured, it's quite easy to use SSL with MySQLi. Getting everything up and running can be a bit of a struggle because you need to add OpenSSL support to both MySQL and PHP, create SSL certificates for MySQL, and also properly configure your MySQL user accounts and configuration files.

Here's a list of what you need to do:

1. Install OpenSSL if your system doesn't already have it.

2. Add SSL support to MySQL and reinstall.

3. Add SSL support to PHP, link against the new MySQL client, and reinstall.

4. Create SSL certificates for the MySQL server.

5. Add SSL certificate information to your my.cnf files.

6. Restart MySQL.

7. Edit the MySQL GRANT table to require SSL.

8. Connect to MySQL from PHP using SSL.

Before you can do anything, you need to have OpenSSL on your machine. Most systems come with OpenSSL preinstalled , but you can download a copy from http://www.openssl.org/.

The second step is to check whether your MySQL server already supports SSL. Issue the following command:

 SHOW VARIABLES LIKE 'have_openssl'; 

If it returns YES , you're in luck; otherwise , you must reconfigure MySQL. Modify your previous configuration to add --with-openssl and --with-vio . (If you downloaded a prebuilt MySQL package, you will need to download the MySQL source and follow the installation instructions.) After you've rebuilt MySQL, install it.

Now add OpenSSL to PHP and get mysqli to enable its SSL support. This is done by adding the --with-openssl configuration option to PHP. Even if your copy of PHP already has SSL support, you still must remake PHP if your MySQL server didn't support SSL when you originally built PHP. When you're done, go ahead and reinstall PHP, restarting your web server if necessary.

Even though both PHP and MySQL now have SSL support, this doesn't mean they'll automatically use it. Before this can happen, you need to create SSL certificates for MySQL. See http://dev.mysql.com/doc/mysql/en/Secure_create_certs.html for a step-by-step guide if you're unfamiliar with OpenSSL and certificates.

Once the certificates are created, add the SSL certificate information to your my.cnf files. This allows MySQL to load in the configuration settings during startup. Here's an example:

 [client] ssl-ca=/usr/local/mysql/openssl/cacert.pem ssl-cert=/usr/local/mysql/openssl/client-cert.pem ssl-key=/usr/local/mysql/openssl/client-key.pem [mysqld] ssl-ca=/usr/local/mysql/openssl/cacert.pem ssl-cert=/usr/local/mysql/openssl/server-cert.pem ssl-key=/usr/local/mysql/openssl/server-key.pem 

This adds the key, certificate, and certificate authority (CA) files to MySQL. You can also set a pathname to trusted SSL CA certificates using the ssl-capath configuration option and provide a list of ciphers using ssl-ciphers .

The next step is to restart the MySQL server. The have_openssl variable should now be YES .

Now the entire foundation is complete and you're ready to force SSL connections on your users. MySQL lets you handle this on a per-user basis with its GRANT tables, as you may want to allow some local users to make unencrypted connections. To require a basic SSL connection, add REQUIRE SSL to the end of your GRANT statement. For example:

 GRANT ALL PRIVILEGES ON users.* TO 'ssl-user'@'localhost'    IDENTIFIED BY 'password' REQUIRE SSL; 

The web page at http://dev.mysql.com/doc/mysql/en/Secure_GRANT.html covers all the different ways you can make MySQL enforce an SSL connection and gives a few additional sample GRANT commands.

Everything is now complete and you're ready to make an SSL connection from PHP using mysqli . This can be done in two different ways, but both methods require you to use mysqli_real_connect( ) .

The easiest way is to read the SSL settings from a my.cnf file:

 $db = mysqli_init( ); mysqli_options($db, MYSQLI_READ_DEFAULT_FILE, '/etc/my.cnf'); mysqli_real_connect($db, 'localhost', 'ssl-user', 'password', 'database'); 

The MYSQLI_READ_DEFAULT_FILE option forces MySQL to read in the contents of the client group in /etc/my.cnf . This allows you to set all your SSL options in a single place and use them from PHP, the mysql command-line tool, or other programs.

You can also specify the options using mysqli_ssl_set( ) :

 $db = mysqli_init( ); mysqli_ssl_set($db, '/usr/local/mysql/openssl/server-key.pem',                      '/usr/local/mysql/openssl/server-cert.pem',                      '/usr/local/mysql/openssl/cacert.pem',                      NULL,                      NULL); mysqli_real_connect($db, 'localhost', 'ssl-user', 'password', 'database'); 

The mysqli_ssl_set( ) function takes six arguments. The first is the database link and the next five are the SSL key, certificate, CA certificate, CA path , and cipher. If your encryption method doesn't require one or more of these parameters, pass NULL in their place.

This function always returns true . SSL errors are reported when you make the actual connection to MySQL.

Using mysqli_ssl_set( ) overrides any variables set with the MYSQLI_READ_DEFAULT_FILE option, so you can still read in a configuration file for general settings, but alter your SSL information if necessary.

Both techniques automatically set the MYSQLI_SSL_CLIENT flag, so you don't need to add this to your call to mysqli_real_connect( ) .