Tag Archives: mysql

Turning On MySQL TLS for Sites I Don’t Control

I run a shared hosting platform with a lot of customer WordPress sites on it (thousands), and every one of them talks to a MySQL cluster over the network. I wanted that traffic encrypted and the server’s certificate verified, without editing a single customer site. PHP has no way to do that, so I wrote php-mysqlnd-tls, an extension that turns it on from php.ini:

mysqlnd_tls.enabled = On
mysqlnd_tls.hosts   = "db.example.com"

Only the Client Can Ask

In the MySQL protocol the server speaks first, and the client then decides whether to request TLS before it logs in. The server can accept a plaintext login or refuse it, but it can’t upgrade one.

The mysql command line client asks by default, but PHP doesn’t. On a stock PHP build, both mysqli and PDO connect through mysqlnd (for mysqli it’s been the only driver since PHP 8.2), and mysqlnd uses TLS only when the application requests it: MYSQLI_CLIENT_SSL, mysqli::ssl_set(), or the PDO::MYSQL_ATTR_SSL_* options. There’s no php.ini setting for it.

One Line per Site Is Still Too Many

WordPress can ask, with one line in wp-config.php:

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

That’s one edit per site, in a file the customer owns. Customers restore old backups, migrate sites in from other hosts, and reinstall from scratch, and each of those quietly puts the site back on plaintext. The line also does nothing for sites that aren’t WordPress.

Even where the line is set, WordPress passes the flag and nothing else, and with the flag alone mysqlnd doesn’t verify the server’s certificate. The connection is encrypted, but nothing checks who’s on the other end.

I could have added that line myself. PHP’s auto_prepend_file setting runs a file ahead of every script, so one prepended file could define the constant for every site. That means running my code inside every request to every customer’s site, which is a security issue. It’s a permissions issue too: the file has to be readable by each site’s own user and sit inside each site’s open_basedir, or PHP refuses to open it. And it’s still WordPress only.

The server side is no help. Set require_secure_transport = ON and MySQL refuses every plaintext login, which on day one means every site that hasn’t been edited. Requiring it user by user, with ALTER USER ... REQUIRE SSL, helps with a slow rollout, but it doesn’t solve the other issues: each of those sites still has to ask for TLS.

The Tunnel I Used Before

My old answer was a TLS tunnel that’s invisible to the sites: a proxy on each web server accepts plaintext MySQL connections and carries them inside TLS to the MySQL cluster, where a second proxy unwraps them.

The problem is that every database connection from every site goes through the tunnel, which makes it a single point of failure between two systems that were built to stay up. When it goes down, the web servers and MySQL are both still up, and every site shows a database error anyway.

MySQL also never sees TLS. The tunnel hands mysqld an ordinary plaintext TCP connection, so require_secure_transport and REQUIRE SSL can never be turned on.

Native TLS has neither problem: there’s nothing in between to fail, and both ends know the connection is encrypted, so the server can enforce it. I just needed a way to turn it on.

Moving the Decision to php.ini

mysqlnd has a plugin API that lets an extension replace the methods of its connection object. php-mysqlnd-tls replaces connect with a wrapper: for a host on the list it adds the TLS flag, sets the verification flags and the trust store, and calls the original. That’s about 250 lines of C. It never reads mysqlnd’s internal structures, which is why one source tree builds on every PHP version from 7.4 to 8.6.

For a host on the list, the setting decides what every site gets:

The site What it gets
Asks for nothing TLS, with the certificate and host name verified
Already asks for TLS (the WordPress define) The same; nothing conflicts
Sets its own CA, or asks for no verification The administrator’s trust store and verify setting
Sends a client certificate Kept, so accounts that require one keep working
Connects to a server that doesn’t offer TLS Refused. There’s no fallback to plaintext
Connects to any other host, or over a Unix socket Left exactly as it was

Only the Hosts You List

The policy applies to the hosts named in mysqlnd_tls.hosts and to nothing else. I list my own cluster, so every connection to it is encrypted and verified, whatever the site asked for.

A customer who connects to an external MySQL server is left alone. If their site asks for TLS, it gets TLS with its own settings, and if it connects in plaintext, that still works. It’s their server and their decision. The extension lets me enforce encryption on the servers I control without breaking a site that points somewhere else.

Rolling It Out

Every setting is PHP_INI_SYSTEM, so ini_set(), .htaccess, and .user.ini can’t change it. The administrator can still enable it per site, which makes it easy to roll out a few sites at a time:

<VirtualHost *:80>
    ServerName www.example.com
    php_admin_flag mysqlnd_tls.enabled on
</VirtualHost>

It installs with PIE. Each release carries pre-built binaries for PHP 7.4 through 8.6 on x86_64 and arm64, so most Linux servers don’t need a compiler:

pie install mikepultz/php-mysqlnd-tls

Then Enforce It on the Server

The extension makes PHP ask for TLS, but it can’t stop a client that doesn’t load it: anyone who can run php -n connects however they like, and so does anything that isn’t PHP. The session status shows what a connection actually got:

$row = $mysqli->query("show session status like 'Ssl_cipher'")->fetch_row();

echo ($row[1] === '') ? 'plaintext' : 'encrypted with ' . $row[1];

Once no client is still connecting in plaintext, set require_secure_transport = ON. That’s the setting I couldn’t touch at the start, and it’s on now.

The extension has been running in production for weeks now, and it works great.