PHP mysqli and SSL

I am trying to configure Galera Cluster over a WAN (VPN is not an option). For obvious reasons I am setting up the connections to require SSL keys, and am having difficulties getting the application to connect. I can successfully connect using PDO, but cannot get mysqli to work. The setup will be:

USA location:

• mainWebServer: openSUSE Leap 15.2, Apache 2.4.33 (named virtual host myserver.mycompany.com)
• myServer1: Ubuntu 20.04, MySQL Galera Cluster 8.0.19
• myDevBox: openSUSE Tumbleweed, running PHP CLI to debug connection issues

China location:

• myServer2: Ubuntu 20.04, MySQL Galera Cluster 8.0.19, Apache 2.4 current
• myServer3: clone of myServer2

The Galera cluster name is myServer, and the node names are myServerX corresponding to their actual hostname.

PHP version info:

me@mydevbox:~> php --version
PHP 7.4.9 (cli) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.9, Copyright (c), by Zend Technologies

MySQL server version info:

me@myServer1:~$ mysql --version
mysql  Ver 8.0.19-26.3 for Linux on x86_64 (MySQL Wsrep Server - GPL)

Code:

<?php

$conn = mysqli_init() or print("init failedn");

mysqli_ssl_set(
    $conn,
    '/srv/www/vhosts/myProject/mysql_ssl_certs/client-key.pem',
    '/srv/www/vhosts/myProject/mysql_ssl_certs/client-cert.pem',
    '/srv/www/vhosts/myProject/mysql_ssl_certs/ca-cert.pem',
    NULL,
    NULL
);

mysqli_real_connect(
    $conn,
    'myServer1',
    'myUser',
    'myPassword',
    'myDB',
    3306,
    MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
) or print("connect failed to myServer1 [{$conn->connect_errno}: {$conn->connect_error}]n");

print_r($conn);

Output:

me@mydevbox:~> php sqlssltest.php
PHP Warning:  mysqli_real_connect(): Peer certificate CN=`myServer' did not match expected CN=`myServer1' in /home/me/sqlssltest.php on line 21
PHP Warning:  mysqli_real_connect(): Cannot connect to MySQL by using SSL in /home/me/sqlssltest.php on line 21
PHP Warning:  mysqli_real_connect(): [2002]  (trying to connect via (null)) in /home/me/sqlssltest.php on line 21
PHP Warning:  mysqli_real_connect(): (HY000/2002):  in /home/me/sqlssltest.php on line 21
connect failed to myServer1 [2002: ]
mysqli Object
(
    [client_info] => mysqlnd 7.4.9
    [client_version] => 70409
    [connect_errno] => 2002
    [connect_error] => 
    [errno] => 2002
    [error] => 
)

By the looks of it mysqli is ignoring my MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag. I was able to successfully connect with PDO using PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false – is there a way to make mysqli work? Unfortunately this is to support legacy application code that still uses mysqli and there is no way to refactor by the time this needs to go live. I believe creating separate CA certs would break the replication connection as they all have different hostnames.

Answer

You are passing MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT as the 7th parameter, however that is the socket parameter, flags is the 8th. Try passing a null for the 7th and bumping MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT to the 8th.