a

PHP application cannot connect to MySQL over SSL

This article describes how to solve PDOException: SQLSTATE[HY000] [2002] Connection refused when connecting from any PHP application with PDO::MySQL to a SSL enabled MySQL database with mismatching CN.

Symptoms

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] [2002] 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.

Cause

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.

Solution

After a few bug reports and complaints a new PDO::MySQL attribute was added in php-7.0.18php-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

 

 

Be social and share
2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *