PHP ignores ssl certificates when connecting to MySql

Question

Using the code from the PHP manual found here and here

<?php

$mysqli = mysqli_init();
if (!$mysqli) {
    die('mysqli_init failed');
}

$mysqli->ssl_set('/path/to/client-key.pem',   
                 '/path/to/client-cert.pem',
                 '/path/to/ca-cert.pem',
                  NULL,NULL); //<-- Doesn't matter if the paths are right or wrong

if (!$mysqli->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}

if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
    die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

if (!$mysqli->real_connect('xx.xx.xx.xx', 'my_user', 'my_password', 'my_db')) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

echo 'Success... ' . $mysqli->host_info . "\n";

$mysqli->close();
?>

I'm unable to connect to MySql. It gives the following error:

Message: SQLSTATE[28000] [1045] Access denied for user 'my_user'@'xx.xx.xx.xx' (using password: YES) <-- the client IP

I have narrowed down the problem to the line $mysqli->ssl_set(...).

When I pass wrong paths to the certificates it should throw a SSL certificate error according to this post where the user suffered from the same problem instead it throws the same previous Access denied error.

I managed to make a connection using Navicat to the server with the help of the database admin by configuring the ssl connection as follows:

Notes:

1. My PHP version is 5.3.15
2. MySql version used on the remote database server is 5.5.13-55
3. Both Navicat and the PHP code are run from the same PC.
4. The database admin created a ssl_user specially for testing. This user is denied normal access and must connect using ssl.
5. No proxies are used.
6. I tried PDO and it suffers from the same problem.
7. I tried mysql->connect(), but it throws a bad handshake error and according to some comments in the manual it doesn't work.
8. Open ssl is enabled from phpinfo()

So the question is why PHP isn't complaining about the wrong paths to the certificates? It seems PHP is ignoring the line where I set the certificate paths!!

---

UPDATE: I tried changing the line where I make the connection to explicitly set the port and use MYSQLI_CLIENT_SSL:

$mysqli->real_connect('xx.xx.xx.xx', 'my_user', 'my_password', 'my_db','3306',NULL,MYSQLI_CLIENT_SSL)

However, I got this error:

Warning: mysqli::real_connect(): (08S01/1043): Bad handshake in ...

UPDATE 2:

This is the result of tcpdump-ing from the database server:

0x0040:  3142 6164 2068 616e 6473 6861 6b65       1Bad.handshake
13:16:18.133282 IP (tos 0x8, ttl  64, id 40823, offset 0, flags [DF], proto: TCP (6), length: 52) xx.xx.xx.202.mysql > xx.xx.xx.130.42766: F, cksum 0xf15f (correct), 112:112(0) ack 79 win 46 <nop,nop,timestamp 19516376 3465907>
        0x0000:  4508 0034 9f77 4000 4006 c8a7 c0a8 28ca  E..4.w@.@.....(.
        0x0010:  c0a8 2882 0cea a70e b36c 7e8b 7b4d a169  ..(......l~.{M.i
        0x0020:  8011 002e f15f 0000 0101 080a 0129 cbd8  ....._.......)..
        0x0030:  0034 e2b3                                .4..
13:16:18.133433 IP (tos 0x8, ttl  64, id 17337, offset 0, flags [DF], proto: TCP (6), length: 52) xx.xx.xx.130.42766 > xx.xx.xx.202.mysql: F, cksum 0xb695 (correct), 79:79(0) ack 113 win 92 <nop,nop,timestamp 3480910 19516376>
        0x0000:  4508 0034 43b9 4000 4006 2466 c0a8 2882  E..4C.@.@.$f..(.
        0x0010:  c0a8 28ca a70e 0cea 7b4d a169 b36c 7e8c  ..(.....{M.i.l~.
        0x0020:  8011 005c b695 0000 0101 080a 0035 1d4e  ...\.........5.N
        0x0030:  0129 cbd8                                .)..
13:16:18.133452 IP (tos 0x8, ttl  64, id 40824, offset 0, flags [DF], proto: TCP (6), length: 52) xx.xx.xx.202.mysql > xx.xx.xx.130.42766: ., cksum 0xb6c3 (correct), 113:113(0) ack 80 win 46 <nop,nop,timestamp 19516376 3480910>
        0x0000:  4508 0034 9f78 4000 4006 c8a6 c0a8 28ca  E..4.x@.@.....(.
        0x0010:  c0a8 2882 0cea a70e b36c 7e8c 7b4d a16a  ..(......l~.{M.j
        0x0020:  8010 002e b6c3 0000 0101 080a 0129 cbd8  .............)..
        0x0030:  0035 1d4e                                .5.N

xx.xx.xx.202 is the database server ip.
xx.xx.xx.130 is my PC ip.

UPDATE 3:

I checked for the password length using the following code:

mysql> select length(Password) from mysql.user where User='youruser'

And the password length was 41.

UPDATE 4:

There might be a hint in the error message returned when I connect using the command line via ssh on the remote server (I uploaded the whole project to the remote server):

mysql -u test_ssl -password --ssl-key=/usr/local/apache2/htdocs/certs/client-key.pem --ssl-cert=/usr/local/apache2/htdocs/certs/WRONG-CERTIFICATE.pem

I get the following error:

SSL error: Unable to get certificate from '/usr/local/apache2/htdocs/certs/WRONG-CERTIFICATE.pem'
ERROR 2026 (HY000): SSL connection error ---> notice the error here

If I don't supply any certificates at all I get the following message:

-bash-3.2$ mysql -u test_ssl -password
ERROR 1045 (28000): Access denied for user 'test_ssl'@'localhost' (using password: YES)

Which is the same error I get when I connect using PHP using correct or invalid paths to the certificates!!

Maybe this means something?

Answer 1

Well we were able to solve the problem, but I'm not sure that this is a practical solution.

First of all we were able to locate the source of the problem which was the MySql client libraries, the ones PHP uses to connect using SSL. PHP had to be complied with those libraries to be able to connect using SSL.

Moreover there was a conflict between the MySql client libraries and OpenSSL libraries.

The only solution to our problem was to re-install everything from scratch. We used a new server, fresh Mysql (latest build) install with all libraries, latest PHP build and latest CentOS distribution.

Answer 2

At a first glance, you did not set MYSQLI_CLIENT_SSL option to connection.

if (!$mysqli->real_connect('localhost', 'my_user', 'my_password', 'my_db', null, MYSQLI_CLIENT_SSL)) { ... }

Update:
You could try adding $mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true); before $mysqli->ssl_set().

Also, this works for me:

<?php
$pdo = new PDO('mysql:host=ip;dbname=dbname', 'username', 'password', array(
    PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql/ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/etc/mysql/ssl/ca-cert.pem'
    )
);
$stmt = $pdo->query("SHOW TABLES;");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
?>

Update 2: You may receive a bad handshake if you use old style mysql password (16 bytes long). To check, run the following query:

mysql> select length(Password) from mysql.user where User='youruser'

If you receive 16:

// disable old_passwords:
mysql> SET @@session.old_passwords = 0;

// update password for user used in PHP script:
mysql> SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');

// check if we have a 41 bytes long password:
mysql> select length(Password) from mysql.user where User='youruser'

And check the connection from PHP script again.