Enable MySQL over SSL in PHP (PDO)

If you are using a MySQL server that does not live on the same server as your website or application you should strongly consider accessing your database over SSL to prevent any snooping of not only your data but also your database credentials.

Of course, doing so is highly dependent on the developers of applications you use supporting this functionality. If you write your own apps then you have full control. However, if you are using apps provided by someone else then your mileage may vary. I’ve had good and bad luck with getting developers to add support.

For the purpose of this article I’m assuming you are writing code for MySQL in PHP using the PDO library. If so, your code probably looks something like the following which is not accessing your MySQL server over SSL:

$db_info = array(
'host' => 'mysql.exampledomain.gdn',
'port' => '3306',
'name' => 'my-database',
'user' => 'dbuser',
'pass' => 'S3CuRe^PaSsW0rd!'
);

$dbh = new PDO( 'mysql:host=' . $db_info['host'] . ';port=' . $db_info['port'] . ';dbname=' . $db_info['name'], $db_info['user'], $db_info['pass'] );

To enable your connection over SSL you will need a copy of the SSL cert your database server is expecting and you will need to know if it is a self-signed certificate or issued by a commercial certificate authority. There’s nothing wrong with using self-signed, it will just determine if you need to tell PDO if it should try to validate the certificate or not.

Once you’ve saved a copy of the certificate to your website or app server you’ll need to pass 1 or 2 “options” to the PDO MySQL library. The PDO::MYSQL_ATTR_SSL_CA option will tell PDO where to find the certificate file to use to make the connection and the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT option will determine if PDO should try to validate the certificate. Assuming you are using a self-signed your new code will look like the below. Note the additions in bold:

$db_info = array(
'host' => 'mysql.exampledomain.gdn',
'port' => '3306',
'name' => 'my-database',
'user' => 'dbuser',
'pass' => 'S3CuRe^PaSsW0rd!',
'opts' => array(
PDO::MYSQL_ATTR_SSL_CA => '/path/to/ssl-cert.pem',
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
)
);

$dbh = new PDO( 'mysql:host=' . $db_info['host'] . ';port=' . $db_info['port'] . ';dbname=' . $db_info['name'], $db_info['user'], $db_info['pass'], $db_info['opts'] );

You can see this is a very simple change that will increase the security of your data in-flight between your database server and your application server.

Taking this one step further, assuming all your applications support database connections over SSL, you can enable your database user to require SSL connections. Once required, any connection not using SSL will fail. Your SQL query, which you can execute from a command-line connection to your database, or with an app like PHPMyAdmin, is:

ALTER USER 'dbuser'@'%' REQUIRE SSL

If you have some apps that support MySQL over SSL and some that don’t I would recommend creating 2 different users that have access to your database. One with REQUIRE SSL and one with REQUIRE NONE so you can maintain a secure connection with the apps that support this functionality.