CODEWITHSUNDEEP
phpmysqlsslmysqli
Tuxie
Tuxie

Reputation: 575

Mysql connection over SSL with PHP mysqli

I'm trying to setup PHP mysqli connection to MariaDB database for two VPS servers and need to encrypt the communications due to it being over public network.

Currently I can connect from the client server to database server via commandline mysql client normally and I have checked via tcpdump that the connection is encrypted. However for some reason I can't figure out the PHP part. It's relatively basic nginx + php5-fpm + mariadb setup but mysql is working on non default port. Debian Jessie, Php5 5.6.7, Mariadb 10.0.16, nginx 1.6.2

Here's the test script:

   <?php
        $DB_NAME = '';
        $DB_HOST = '111.111.111.111';
        $DB_USER = 'username';
        $DB_PASS = 'password';

$mysqli = mysqli_init();
if (!$mysqli) {
    die('mysqli_init failed');
}
//have tried witha and without the following with multiple variations
$mysqli->ssl_set(NULL, NULL, NULL,'/etc/mysql/ssl/',NULL);
if (!$mysqli->real_connect($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME, 11111, NULL,MYSQLI_CLIENT_SSL )) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}
        $query = "SHOW STATUS LIKE 'ssl_cipher'";
        $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
        if($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                        print_r($row);
                }
        }
        else {
                echo 'NO RESULTS';
        }
        mysqli_close($mysqli);
?>

Main error I'm getting without the ssl_set:

2015/07/11 15:58:34 [error] 2857#0: *374 FastCGI sent in stderr: "PHP message: PHP Warning:  mysqli::real_connect(): SSL operation failed with code 1. OpenSSL Error messages:
error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed in /srv/www/test.php on line 15
PHP message: PHP Warning:  mysqli::real_connect(): Cannot connect to MySQL by using SSL in /srv/www/test.php on line 15
PHP message: PHP Warning:  mysqli::real_connect(): [2002]  (trying to connect via tcp://192.168.130.123:42139) in /srv/www/test.php on line 15
PHP message: PHP Warning:  mysqli::real_connect(): (HY000/2002):  in /srv/www/test.php on line 15".....

Any ideas would be appreciated. This is really killing me.

Upvotes: 2

Views: 5973

Answers (2)

Mel_T
Mel_T

Reputation: 451

Maybe this problem occurs due to the changes made in PHP 5.6. I guess you are using self-signed certificates? If your DB enables peer_name validation by DEFAULT, there is no way to disable this in PHP. So when generating you certificates you have to use the right "Common Name" for each one:

CA: hostname Server: FQND, e.g. hostname.example.com Client: somename

The important part is the server certificate where the Common Name has to be the same as the host you are connecting to.

Upvotes: 3

Machavity
Machavity

Reputation: 31614

What it looks like is this

SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

My guess is that you don't have the correct CA set up. Some DB systems (like Amazon Web Services RDS) have their own CA file. You're using the capath argument so make sure the PEM files are in that path. If they are, the next thing I would do is switch to the third argument of ssl_set and specify the PEM file directly

$mysqli->ssl_set(NULL, NULL, '/path/to/ca.pem', NULL, NULL);

Upvotes: 1

Related Questions