PHP MySQL over SSL. Peer certificate did not match

Question

I'm trying to use Google Cloud SQL over SSL from GCE(Google Compute Engine) instance. My problem is that I cannot connect to Cloud SQL instance over SSL.

mysql command works normally. I can connect to Cloud SQL instance with certification files.

mysql -uroot -p -h [IP Address] --ssl-ca=/home/user/.cert/server-ca.pem --ssl-cert=/home/user/.cert/client-cert.pem  --ssl-key=/home/user/.cert/client-key.pem

However, I got warning and fatal error as followings when I access from PHP program.

<?php
$pdo = new PDO('mysql:host=[IP Address];dbname=testdb', 'root', 'test', array(
    PDO::MYSQL_ATTR_SSL_KEY    =>'/home/user/.cert/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/home/user/.cert/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/home/user/.cert/server-ca.pem'
    )
);
$stmt = $pdo->query("SHOW TABLES;");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
?>

PHP Warning:  PDO::__construct(): Peer certificate CN=`[GCP project name]:[Cloud SQL instance name]' did not match expected CN=`[IP Address]' in /tmp/mysql.php on line 7
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] ' in /tmp/mysql.php on line 7

I got same error when I used mysqli.

$mysqli = mysqli_init();
mysqli_options($mysqli, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$mysqli->ssl_set('/home/user/.cert/client-key.pem',
                 '/home/user/.cert/client-cert.pem',
                 '/home/user/.cert/server-ca.pem',
                  NULL,NULL);

$mysqli->real_connect('[IP Address]', 'root', 'test', 'testdb', 3306, NULL, MYSQLI_CLIENT_SSL);

PHP Warning:  mysqli::real_connect(): Peer certificate CN=`[GCP project name]:[Cloud SQL instance name]' did not match expected CN=`[IP Address]' in /tmp/mysql3.php on line 30
Warning: mysqli::real_connect(): (HY000/2002):  in /tmp/mysql3.php on line 30

This question looks be relevant to my case but there is no answer yet. SSL self-signed certifications to connect with Mysql with PHP

Does anyone know about solutions?

Update 1

The bug is reported. https://bugs.php.net/bug.php?id=71003

Very similar question here. Google Cloud SQL SSL fails peer certificate validation

My PHP version is 5.6.14. I will update to 5.6.16 to use MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT.

Update 2

Fixed it when I use mysqli

What I did are as followings:

1 I updated my PHP to 5.6.20

sudo apt-get install php5

2 I put the MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT option like this.

$mysqli->real_connect('[IP Address]', 'root', 'test', 'testdb', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);

My application uses both mysqli and PDO for some reasons. I'm now looking for PDO's solution.

Update 3

This bug report shows about PDO's case. Sounds not fixed yet.

https://bugs.php.net/bug.php?id=71845

This is also related. https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/google-cloud-sql-discuss/4HNvmq7MpU4/kuSjhkS2AwAJ

As far as I understand, there is no way to resolve for PDO.

Update 4

Some people blame google's design of CN names (I agree with them actually..)

What's worst is you guys use impossible CN names (:) . If the CN was without colon maybe I can map the ip and the CN in my hosts file so that when peer validation is done it can pass. With the colon, php thinks is host and is port

and Google's staff? understand the problem.

I understand the current situation when connecting by IP is not ideal.

But it seems they provide a solution called 'proxy'.

https://groups.google.com/forum/#!topic/google-cloud-sql-discuss/gAzsuCzPlaU

I'm using Cloud SQL second generation and my applications are hosted GCE. So I think I can use the proxy way. I will try it now.

Update 5

Setup the proxy access. Solved both PDO and mysqli access.

Install the proxy on Ubuntu

$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
$ mv cloud_sql_proxy.linux.amd64 cloud_sql_proxy
$ chmod +x cloud_sql_proxy
$ sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
$ ./cloud_sql_proxy -dir=/cloudsql -instances=<project name>:us-central1:mydb

PDO

<?php
$pdo = new pdo('mysql:unix_socket=/cloudsql/<project name>:us-central1:mydb;dbname=testdb','root','test');
$stmt = $pdo->query("SHOW TABLES;");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
?>

mysqli

$mysqli = mysqli_connect('localhost', 'root', 'test', 'testdb', 3306, '/cloudsql/<project name>:us-central1');
$sql = "SELECT id FROM users";
if ($result = $mysqli->query($sql)) {
    while ($row = $result->fetch_assoc()) {
        echo $row["id"] . "\n";
    }
    $result->close();
}
$mysqli->close();

refs (Japanese)

http://blog.hrendoh.com/connecting-to-google-cloud-sql-using-mysql-client/ http://blog.hrendoh.com/google-appengine-php-using-cloud-sql/

you are amazing. update #5 totally worked for me. thank you!!! — ingernet, Sep 14 '17 at 21:09
I was banging my head for hours. Maybe this will help someone else. Step 5 part 1 was my issue - I was logged in as root. Re-ran as normal user, all worked as expected! — webaholik, Sep 30 '17 at 07:20

score 15 · Answer 1 · edited Dec 27 '19 at 21:35

15

For PDO connections that don't use Google cloud or cannot benefit from the proxy solution, they have fixed the bug and it is merged now. There is now a constant for this (starting April 2017):

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,

http://git.php.net/?p=php-src.git;a=commit;h=247ce052cd0fc7d0d8ea1a0e7ea2075e9601766a

edited Dec 27 '19 at 21:35

Dharman

30,962
25
85
135

answered May 19 '17 at 10:14

Javi

353
2
11

1

Does this mean the connection is secured? – Yetispapa Dec 28 '17 at 09:34
Well, yes, it is an encrypted connection, but take into account that you are not verifying the cert itself. – Javi Jan 07 '18 at 21:26
I know but i could not find any solution without using the code of line above. The certificates works fine when using the terminal or an app like sequel. But in terms of php I couldn’t find any solution – Yetispapa Jan 07 '18 at 23:59
This only works if you are using PHP 7.1.x. If using azure, a good way to deploy for ubuntu is to ensure using Ubuntu 18LTS. This provides a default for the correct version of PHP that has PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT – CarComp Feb 01 '18 at 13:03
@javi: do you know of any way this is possible in PHP 5.6? – Max Apr 11 '18 at 13:09
@Max: according to this, it is avalable since 5.6.22: https://secure.php.net/manual/en/ref.pdo-mysql.php#122326 – Javi Apr 30 '18 at 14:10
I'm using php 5.6.40, but does not support PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT Any other solution for PDO? Thanks – ytdm Mar 10 '22 at 08:51

score 4 · Answer 2 · edited Dec 20 '17 at 00:29

4

In short, use Cloud SQL Proxy if you need to access from both PDO and mysqli. You have no choice.

https://cloud.google.com/sql/docs/sql-proxy

edited Dec 20 '17 at 00:29

Sammitch

30,782
7
50
77

answered Apr 08 '16 at 17:33

zono

8,366
21
75
113

3

Note to GCP employees: PR is not a valid reason to edit answers. – Sammitch Dec 20 '17 at 00:30

score 4 · Answer 3 · answered Apr 23 '18 at 23:35

For mysqli, adding:

MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT

to real_connect() solved my instance of this issue.

Example (replace host, user, password and database as needed):

$db_connection->real_connect('ip address or host', 'user', 'password', 'database', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);

Full php mysqli SSL connection script:

// check if openssl is enabled on server
if(!extension_loaded('openssl')) {
    throw new Exception('This app needs the Open SSL PHP extension and it is missing.');
}

// start connection
$db_connection = mysqli_init(); 

// set ssl config (update with your certs location)
$db_connection->ssl_set('/etc/my.cnf.d/certs/client-key.pem','/etc/my.cnf.d/certs/client-cert.pem', '/etc/my.cnf.d/certs/ca-cert.pem', NULL, NULL); 

// connect (update with your host, db and credentials)
$db_connection->real_connect('ip address or host', 'user', 'password', 'database', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);

score 1 · Answer 4 · answered Jan 25 '18 at 04:00

Example in CakePHP 3.5.x + PHP 7.1.x + SQL Google Cloud + SSL

Change file in: config/app.php

    ...
    ...

    /**
     * The test connection is used during the test suite.
     */
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'xx.xxx.xxx.xxx',
        //'port' => 'non_standard_port_number',
        'username' => 'user_name_x',
        'password' => 'pass_x',
        'database' => 'bd_name_x',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'quoteIdentifiers' => false,
        'log' => false,

        'flags' => [
           PDO::MYSQL_ATTR_SSL_KEY  => CONFIG.'client-key.pem',
           PDO::MYSQL_ATTR_SSL_CERT => CONFIG.'client-cert.pem',
           PDO::MYSQL_ATTR_SSL_CA   => CONFIG.'server-ca.pem',
           PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
        ],

        //'ssl_key' => CONFIG.'client-key.pem',
        //'ssl_cert' => CONFIG.'client-cert.pem',
        //'ssl_ca' => CONFIG.'server-ca.pem',

        //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],
    ],
 ],
 ...
 ...

Example PHP Pure + PDO + SSL:

$ssl_key = CONFIG.'client-key.pem';
$ssl_cert = CONFIG.'client-cert.pem';
$ssl_ca = CONFIG.'server-ca.pem';

$pdo = new PDO('mysql:host=xxx.xxx.xxx.xxx;dbname=db_name_x', 'user_name_x', 'pass_x', array(
    PDO::MYSQL_ATTR_SSL_KEY   => '/path/full/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT  => '/path/full/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    => '/path/full/server-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
    )
);
$statement = $pdo->query("SHOW TABLES;");
var_dump($statement);
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo json_encode($row);
exit();

score 0 · Answer 5 · edited Jul 03 '19 at 15:26

I had the same problem with a MySQL database hosted at compose cluster.

After searching and testing for hours, I found out that the best solution is to disable testing the certificate provided by the server by using the MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag.

I haven't tested it with PDO but with the mysqli interface it is working for me. In general this problem would appear with a database hosted at a cluster server.

PHP MySQL over SSL. Peer certificate did not match

5 Answers5

Linked