通过SSL的PHP​​ MySQL.对等证书不匹配 [英] PHP MySQL over SSL. Peer certificate did not match

问题描述

我正在尝试通过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

这个问题似乎与我的情况有关，但尚无答案. 用于通过PHP与Mysql连接的SSL自签名证书

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无法通过对等证书验证

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

我的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 选项.

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

裁判(日语)

http://blog.hrendoh.com /connecting-to-google-cloud-sql-using-mysql-client/ http://blog.hrendoh.com/google-appengine-php- using-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

这篇关于通过SSL的PHP​​ MySQL.对等证书不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！