PHP application cannot connect to MySQL over SSL
This article describes how to solve
PDOException: SQLSTATE[HY000]  Connection refused when connecting from any PHP application with PDO::MySQL to a SSL enabled MySQL database with mismatching CN.
Connecting to the database without SSL works, but when it is enabled, then you cannot connect although all certificates and keys are provided.
Database connection properties (in Drupal8) looks like:
array ( 'database' => 'database', 'username' => 'username', 'password' => 'password', 'prefix' => '', 'host' => 'hostname', 'port' => '3306', 'driver' => 'mysql', 'pdo' => [ \PDO::MYSQL_ATTR_SSL_KEY =>'/var/www/site/keys/client-key.pem', \PDO::MYSQL_ATTR_SSL_CERT =>'/var/www/site/keys/client-cert.pem', \PDO::MYSQL_ATTR_SSL_CA =>'/var/www/site/keys/server-ca.pem' ] )
When trying to connect older versions of PHP does not report the real error but just
PDOException: SQLSTATE[HY000]  Connection refused
while newer ones report an additional
PHP Fatal error: Uncaught PDOException: PDO::__construct(): Peer certificate CN=`*********:stage-database' did not match expected CN=`***.***.***.***'
and is reproducible with SSL enabled Google Cloud SQL instance because the issued certificates CN is not a resolvable FQDN which can be used to be connect to from outside.
The root cause is PHP by default verifies if the canonical name of the certificate matches the hostname you are trying to connect to. If that fails then connection is denied but this is not reported as an error or warning.
After a few bug reports and complaints a new PDO::MySQL attribute was added in php-7.0.18, php-7.1.4 with commit 247ce052cd0fc7d0d8ea1a0e7ea2075e9601766a but not documented in the official documentation. That parameter disables hostname verification and applications connect without any issue. I’ve commented on php.net about that here.
So add to your pdo properties array the following key – PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
'pdo' => [ \PDO::MYSQL_ATTR_SSL_KEY =>'/var/www/site/keys/client-key.pem', \PDO::MYSQL_ATTR_SSL_CERT =>'/var/www/site/keys/client-cert.pem', \PDO::MYSQL_ATTR_SSL_CA =>'/var/www/site/keys/server-ca.pem', \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false ]
If after adding this parameter you get an error message
PHP message: Error: Undefined class constant 'MYSQL_ATTR_SSL_VERIFY_SERVER_CERT'
then your PHP version does not support that parameter and you need to upgrade.
The solutions is applicable to any PHP application that uses PDO::MySQL. So far verified on Symfony, Laravel, Drupal
Thank you! run into the same problem and now everything works perfectly fine. Could you maybe further explain the solution and cause? I don’t want to run into security issues due to this fix. I’m using this for my Laravel app so that I can connect to a database that sits on another server.
I’m trying to implement this but still getting “Undefined Class” for ‘MYSQL_ATTR_SSL_VERIFY_SERVER_CERT’.
I’m on php7.2… So no clue about what it’s happening. Could you please help me?
Thanks a lot in advance.