基于 SSL 的 PHP MySQL.对等证书不匹配

2021-12-25 00:00:00 google-compute-engine mysql pdo mysqli google-cloud-sql

我正在尝试从 GCE(Google Compute Engine)实例通过 SSL 使用 Google Cloud SQL.我的问题是我无法通过 SSL 连接到 Cloud SQL 实例.

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 命令正常工作.我可以使用认证文件连接到 Cloud SQL 实例.

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

但是，当我从 PHP 程序访问时，我收到了如下警告和致命错误.

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

我在使用 mysqli 时遇到了同样的错误.

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

这个问题看起来与我的情况有关，但目前还没有答案.SSL自签名认证，用PHP连接Mysql

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

有人知道解决方案吗?

更新 1

已报告错误.https://bugs.php.net/bug.php?id=71003

这里的问题非常相似.Google Cloud SQL SSL 未通过对等证书验证

我的 PHP 版本是 5.6.14.我将更新到 5.6.16 以使用 MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT.

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

更新 2

在我使用 mysqli 时修复了它

Fixed it when I use mysqli

我所做的如下:

1 我将 PHP 更新到 5.6.20

1 I updated my PHP to 5.6.20

sudo apt-get install php5

2 我将 MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT 选项设置为这样.

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);

由于某些原因，我的应用程序同时使用了 mysqli 和 PDO.我现在正在寻找 PDO 的解决方案.

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

更新 3

此错误报告显示了 PDO 的情况.听起来还没有修复.

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

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

这也有关系.https:///groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/google-cloud-sql-discuss/4HNvmq7MpU4/kuSjhkS2AwAJ

据我所知，没有办法解决 PDO.

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

更新 4

有些人指责谷歌对 CN 名称的设计(我实际上同意他们..)

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

最糟糕的是你们使用了不可能的 CN 名称 (:) .如果 CN 没有冒号，也许我可以在我的主机文件中映射 ip 和 CN，以便在完成对等验证后它可以通过.用冒号，php 认为是主机，是端口

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

还有 Google 的员工?了解问题.

and Google's staff? understand the problem.

我了解目前IP连接不理想的情况.

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/论坛/#!topic/google-cloud-sql-discuss/gAzsuCzPlaU

我使用的是第二代 Cloud SQL，我的应用程序由 GCE 托管.所以我想我可以使用代理方式.我现在就试试.

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.

更新 5

设置代理访问.解决了PDO和mysqli访问.

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

在 Ubuntu 上安装代理

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"] . "
";
    }
    $result->close();
}
$mysqli->close();

refs(日语)

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

推荐答案

对于不使用 Google 云或无法从代理解决方案中受益的 PDO 连接，他们已修复该错误，现在已合并.现在有一个常量(从 2017 年 4 月开始):

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

相关文章