连接到MySql时，PHP会忽略ssl证书 [英] PHP ignores ssl certificates when connecting to MySql

问题描述

使用此处和此处

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

我无法连接到MySql.它给出了以下错误:

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

我已将问题缩小到$mysqli->ssl_set(...)行.

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.

我通过如下配置ssl连接，在数据库管理员的帮助下使用Navicat与服务器建立了连接:

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:

注意:

1. 我的PHP版本是 5.3.15
远程数据库服务器上使用的
2. MySql版本为 5.5.13-55
3. Navicat和PHP代码都在同一台PC上运行.
4. 数据库管理员创建了一个专门用于测试的 ssl_user .拒绝该用户正常访问，必须使用ssl进行连接.
5. 不使用代理.
6. 我尝试了PDO，它也遇到了同样的问题.
7. 我尝试了mysql->connect()，但是它引发了严重的握手错误，并且根据手册无效.
8. 从phpinfo()启用了打开ssl

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

所以问题是为什么PHP不会抱怨证书路径错误?看来PHP忽略了我设置证书路径的那一行！

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!!

更新: 我尝试更改进行连接的行以显式设置端口并使用MYSQLI_CLIENT_SSL:

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 ...

更新2:

这是从数据库服务器进行tcpdump-ing的结果:

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是数据库服务器ip.
xx.xx.xx.130是我的PC IP.

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

更新3:

我使用以下代码检查了密码长度:

I checked for the password length using the following code:

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

并且密码长度为 41 .

更新4:

当我通过命令行通过远程服务器上的 ssh 使用命令行连接时，返回的错误消息中可能会有提示(我将整个项目上传到了远程服务器):

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

我收到以下错误:

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)

当我使用PHP使用证书的正确或无效路径进行连接时，会遇到相同的错误！

也许这意味着什么?

推荐答案

好了，我们能够解决此问题，但是我不确定这是否是可行的解决方案.

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

首先，我们能够找到问题的根源，即MySql客户端库，PHP用来使用SSL连接这些库.必须使PHP与这些库兼容，才能使用SSL进行连接.

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.

此外，MySql客户端库和OpenSSL库之间存在冲突.

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

解决我们问题的唯一方法是从头开始重新安装所有内容.我们使用了新服务器，全新的Mysql(最新版​​本)安装以及所有库，最新的PHP版本和最新的CentOS发行版.

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.

这篇关于连接到MySql时，PHP会忽略ssl证书的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！