PHP Mysql connection with SSL is not working PDO

user_3067928 0
Viewed 5819

Mysql server is running php5.3. New webserver is running php7.1 (migrated from php5.3). When I try to connect Mysql server with ssl its not working.

try {
$dbh = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_SSL_KEY  => '/etc/mysql/client-key.pem',
                                             PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/client-cert.pem',
                                             PDO::MYSQL_ATTR_SSL_CA   => '/etc/mysql/ca-cert.pem')
              );
    echo "Connestion established";
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Connection failed: SQLSTATE[HY000] [2002]

PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed

But, When I remove SSL block from connection, its working fine. I don't know what's going on. May be version mismatch of server and client. Becasue I am using old public key and private key.

Is it because of mysql client and server version mismatch?

PS: I have upgraded php7 in webserver only.

php-7 ssl mysql php
5 Answers

With some trial and error I was able to fix this issue without disabling peer verification, thanks in part to MySQL documentation:

Important

Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. A typical error in this case is:

ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)

MySQL documentation

However, this only got me part of the way. By default PHP has VERIFY_IDENTITY enabled, which requires a hostname match for the Common Name.

This satisfies everything:

CA: assign a unique name. Can be anything. I just prepend root. to my FQDN.

client and server: Assign the FQDN of the MySQL server. These two values must match.

If the FQDN does not match between client and server, then VERIFY_IDENTITY will fail.

If the FQDN does match between ca, client and server, then OpenSSL in PHP will fail as promised in MySQL documentation.

user_19353380
Related