add MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT to MYSQL SSL connnection

[rebelde]

Found the answer, so I am turning this question into a suggestion.

In short, SSL to MySQL on a remote server won't work unless you add MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT to the connection in src/XF/Db/Mysqli/Adapter.php, as I have here:

$isConnected = @$connection->real_connect(
$config['host'], $config['username'], $config['password'],
$config['dbname'], $config['port'] ?: 3306, $config['socket']
, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
);

My original question:

I am transitioning to a new server. As an intermediate step, I want to have the xenforo files on the new server and mysql still on the old server. I want to enable SSL, but it isn't working.

It works with:

$config['db']['ssl']['enabled'] = false;

I can connect to the server with mysql client from the new server:

mysql
--ssl-ca=/xenforo/src/ca.pem
--ssl-cert=/xenforo/src/client-cert.pem
--ssl-key=/xenforo/src/client-key.pem
-h forum.mysite.com
-u user34 -p

I can connect with a basic mysqli php script.

But it doesn't work when enabled:

$config['db']['ssl']['enabled'] = true; // Enable/disable SSL support
$config['db']['ssl']['key'] = 'src/client-key.pem'; // The path name to the key file.
$config['db']['ssl']['cert'] = 'src/client-cert.pem'; // The path name to the certificate file.
$config['db']['ssl']['ca'] = 'src/ca.pem'; // The path name to the certificate authority file.

Mysql error on the old server:

[Note] Got an error reading communication packets
[Note] Got an error reading communication packets
[Note] Got an error reading communication packets

Xenforo 2.2
MySQL5.7
PHP 8.3

Cheer! Hope this helps someone.

 

[Kirby]

In short, SSL to MySQL on a remote server won't work unless you add MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT to the connection in src/XF/Db/Mysqli/Adapter.php, as I have here

This is nonsense and a dangerous advice!

If you can't establish a secure connection without skipping certification validation then there is an issue with ... certificate validation.
Either the certificate is not valid at all or I can't be validated for some other reason (wrong CA cert, incompatible ciphers, etc.).
Certification validation needs to be fixed, skipping certifiication validation is a bad idea!

mysql --ssl-ca=/xenforo/src/ca.pem --ssl-cert=/xenforo/src/client-cert.pem --ssl-key=/xenforo/src/client-key.pem -h forum.mysite.com -u user34 -p

Depending on MySQL client config this will not verify the server cert!

You should probably add --ssl-verify-server-cert[/code] to be sure and check the connection status via [icode]SHOW STATUS after the connection has been established .

That being said, unless you use MySQL TLS client authentication (somewhat unlikely) you don't need to set key and cert - setting ca is sufficient.

The following test script is verified to work with

Let's Encrypt Certificate for the MariaDB Server
Ubuntu 22.04 / PHP 8.3
MariaDB 10.6

$config['db']['host'] = '<host>';
$config['db']['port'] = 3306;
$config['db']['username'] = '<username>';
$config['db']['password'] = '<password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['key'] = null;
$config['db']['ssl']['cert'] = null;
$config['db']['ssl']['ca'] = null;
$config['db']['ssl']['capath'] = '/etc/ssl/certs';
$config['db']['ssl']['cipher'] = null;

$mysqli = new MySqli();
$mysqli->ssl_set($config['db']['ssl']['key'], $config['db']['ssl']['cert'], $config['db']['ssl']['ca'], $config['db']['ssl']['capath'], $config['db']['ssl']['ca']);
$mysqli->real_connect($config['db']['host'], $config['db']['username'], $config['db']['password'], null, $config['db']['port']);
$result = $mysqli->query("SHOW STATUS LIKE 'ssl%'");
while ($row = $result->fetch_assoc())
{
    echo("$row[Variable_name] = $row[Value]\n");
}

 

[rebelde]

Kirby, thank you for your advice and perspective. I am not an expert at security, but I don't see it as that dangerous.

Starting with:
Working connection with username and password to MySQL on another server. The other server has 'CREATE USER user@new_server's IP IDENTIFIED BY 'strong_password'. Security is there. but I want to add encryption (SSL/TLS), right?
Doing what I did adds encryption without reducing security. You can't deny that!
BTW, MySQL replication with the same certs works great. MySQL replication has no problem with those certs.

What certs did I use? Well, the ones generated by MySQL for situations like this! I have been using them for replication for years without any problems. Nothing dangerous there. Just the setup recommended by MySQL.

Now, trying to set xenforo up on this new server, it wouldn't work. I spent hours trying to figure it out. Finally, I tried a PHP-MySQL connection outside of the forums and got a good error message that the certs (that mysql generated) did not have the subdomain (forum.mysite.com) and were rejected for that reason. MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT is the way to get around that 'requirement'.

I would be happy to know how to get better certs for MySQL. So, how can I get MySQL or OpenSSL to generate the three files with my correct subdomain, so I can use it here?
Can I just use my Letsencrypt files? Or some thing based on them? That would be ideal.

Again, thank you for responding, though I still think that the connection is more secure with SSL as I have it than it was without it.

PS. Tried your "capath only" config and it didn't work, probably for the same reason as everything else not working. The certs don't have the correct subdomain.