PHP to MySQL SSL Connections

Question

I have successfully setup an SSL enabled install of MySQL on one server on one network and can connect to it using SSL with the Linux command line mysql client on a different server on a different network, however every time I try to connect (using PHP 5.3.3) I keep getting:

Warning: mysqli_real_connect(): (HY000/2026): SSL connection error in /var/www/html/test.php on line 18

My PHP is as follows have I done something wrong? The certs are 777 (only while testing) and the same code works for a different user's un-encrypted connection (with the SSL setting commented out i.e. mysqli can definitely connect generally to the DB)

<?php

error_reporting(E_ALL);
ini_set("display_errors", "1");

$obj = mysqli_init();

mysqli_options($obj, MYSQLI_OPT_CONNECT_TIMEOUT, 5);

mysqli_ssl_set($obj,
               '/mysql-ssl-certs/server-key.pem',
               '/mysql-ssl-certs/server-cert.pem',
               '/mysql-ssl-certs/ca-cert.pem',
               NULL,
               NULL);

$link = mysqli_real_connect($obj, 'localhost', 'ssluser', 'some_password', 'test');

if (!$link)
{
    die('<br /><br />Connect Error (' . mysqli_connect_errno() . ') '.mysqli_connect_error());
}

echo 'Success... ' . mysqli_get_host_info($obj) . "\n";

$obj->close();

?>

Note: this says localhost as I'm now also trying it on the local machine so I'm 100% sure it's not a firewall issue - I get the same errors there... — CoderChris, Sep 07 '10 at 10:26
No - I know they're in an odd location but there is a valid folder in / and it's chmodded to 777 for testing — CoderChris, Sep 07 '10 at 10:59
Is there something in the MySQL server's error log that might be related? If possible start the MySQL server with --log-warnings=2 (or 3 or 99, something > 1), see http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html — VolkerK, Sep 07 '10 at 11:28
btw: which version of MySQL do you use? Maybe some other notes about your system? E.g. OS version, how those certificates where created ...no idea if this is relevant, but it may be... — VolkerK, Sep 07 '10 at 11:32

score 8 · Accepted Answer · answered Sep 07 '10 at 12:18

Here PHP (and mysqli_real_connect) is the client not the server. You're configuring it with mysqli_ssl_set for client-certificate authentication (and using the server key and certificate).

I'm not sure how you've configured your MySQL server, but there should be something like this in the (MySQL) server section of the configuration:

ssl-key=/mysql-ssl-certs/server-key.pem
ssl-cert=/mysql-ssl-certs/server-cert.pem
ssl-ca=/mysql-ssl-certs/ca-cert.pem

These don't belong to the client side anyway (only the CA certificate does, but definitely not the server's private key).

Once you've done this, you can try to see if the server is configured properly using the command line client:

mysql --ssl-verify-server-cert --ssl-ca=/mysql-ssl-certs/ca-cert.pem --ssl -h hostname ...

or perhaps this (although verify server cert should really be enabled for SSL/TLS to be useful)

mysql --ssl-ca=/mysql-ssl-certs/ca-cert.pem --ssl -h hostname ...

This should work at least on the command line.

Then, from PHP, you get two options:

use mysqli_ssl_set like you've done, but leaving $key and $cert null, unless you want to use a client-certificate which really ought to be different from your server certificate. (I can't remember whether that works.)
possibly easier, omit mysqli_ssl_set altogether and configure this in your global MySQL client configuration file (where PHP should be able to pick it up, possibly /etc/mysql/my.cnf, but this may vary depending on your distribution):
```
[client]
ssl-ca=/mysql-ssl-certs/ca-cert.pem
```

(This is similar to the server config, but on the client side/in the client section.)

For the authorization part (GRANT):

REQUIRE SSL only requires the use of SSL/TLS
REQUIRE ISSUER, REQUIRE SUBJECT and REQUIRE X509 require the client to present a client-certificate to compare to the required values (that's the case where you'd need to use ssl-key and ssl-cert on the client side (config or within mysqli_ssl_set).

(You should also enable `ssl-verify-server-cert` in the client side config.) — Bruno, Sep 07 '10 at 12:23
I've got this working now Most of this is already setup as mentioned, but I like the [client] idea - didn't think of that. The generation of the certs was done following the instructions from the mysql man pages to the letter, but the weird thing is that the connection from PHP only seems to work if I ONLY set the ca variable in the mysqli connection and I set that to the server-cert.pem any idea why? here's the ssl bit of [mysqld] ssl=1 ssl-ca=/mysql-ssl-certs/ca-cert.pem ssl-capath=/mysql-ssl-certs ssl-cert=/mysql-ssl-certs/server-cert.pem ssl-key=/mysql-ssl-certs/server-key.pem — CoderChris, Sep 07 '10 at 16:57
Thanks for the authorization remark. I used REQUIRE SSL: an SSL connection was established, but all local certificates were being ignored. After setting REQUIRE X509 at least the client key and cert files were required. — lmeurs, Nov 13 '14 at 08:49
I've having this exact problem. I setup `mysqli_ssl_set($db, null, null, '/path/to/ca-cert.pem', null, null)` to ca-cert.pem, but getting `Warning: mysqli_real_connect(): Unable to locate peer certificate CN in /MySQLConnection.php on line 31` — Justin, Feb 28 '15 at 03:19
@Justin Your best bet would probably be to set up a server certificate with the CN you're using to establish the connection if you can. — Bruno, Feb 28 '15 at 10:30
Note that the my.cnf solution will only work in PHP <5.3.0. Newer PHP versions use mysql native driver (mysqlnd) by default to communicate with the server and it does not read my.cnf or my.ini http://php.net/manual/en/ref.pdo-mysql.php#pdo.constants.mysql-attr-read-default-file — Mr_Moneybags, Nov 17 '16 at 06:47

score 0 · Answer 2 · answered Apr 13 '15 at 21:47

You should be careful if you're using MySQL prior to 5.7.3 and the standard PHP library:

Before MySQL 5.7.3, --ssl permits but does not require the client to connect to the server using SSL. Therefore, this option is not sufficient in itself to cause an SSL connection to be used. For example, if you specify this option for a client program but the server has not been configured to permit SSL connections, an unencrypted connection is used.

http://dev.mysql.com/doc/refman/5.7/en/ssl-options.html#option_general_ssl

In the case of PHP, MySQL will silently fail if the server fails to support SSL

https://www.idontplaydarts.com/2015/03/mysql-with-ssl-does-not-protect-against-active-mitm/

score 0 · Answer 3 · answered Sep 07 '10 at 10:39

Maybe your version of php uses mysqlnd as transport driver which doesn't support ssl (yet?).
What does

<?php
error_reporting(E_ALL);
ini_set("display_errors", "1");
echo 'version: ', phpversion(), "\n";
echo function_exists('mysqli_fetch_all') ? 'mysqlnd' : '--', "\n";

print?

PHP to MySQL SSL Connections

3 Answers3

Linked