I created a user via MySQL Workbench.
我通过MySQL Workbench创建了一个用户。
CREATE USER 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word';
CREATE USER 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
FLUSH PRIVILEGES;
I configured the MySQL Server (5.5.34-0ubuntu0.13.04.1) to use SSL by generating the needed certificates and keys and configured them via /etc/mysql/my.cnf
我通过生成所需的证书和密钥并通过/etc/mysql/my.cnf配置它来配置MySQL服务器（5.5.34-0ubuntu0.13.04.1）以使用SSL
If I try to connect via CLI on a client machine everything just works great.
如果我尝试通过客户端计算机上的CLI连接，一切都很好。
user@ClientPC:~$ mysql -h mysql.example.com --port=3306 -v --ssl-ca=/home/user/demo/ca-cert.pem --ssl-cert=/home/user/demo/client-cert.pem --ssl-key=/home/user/demo/client-key.pem -u fba_user -p
Enter password: my_Pa$$word
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 5.5.34-0ubuntu0.13.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Reading history-file /home/user/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW STATUS LIKE "%SSL%";
--------------
SHOW STATUS LIKE "%SSL%"
--------------
+--------------------------------+------------------------+
| Variable_name | Value |
+--------------------------------+------------------------+
| Com_show_processlist | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | DHE-RSA-AES256-SHA |
| ... | ... |
+--------------------------------+------------------------+
But if I use PHP (PHP/5.4.9-4ubuntu2.3) with PDO to connect to the database, the connection is not working. I tried php5-mysql driver and I tried the php5-mysqlnd (Native Driver). Both do not change a thing.
但是，如果我使用PHP（PHP / 5.4.9-4ubuntu2.3）与PDO连接到数据库，连接将无法正常工作。我试过php5-mysql驱动程序，我尝试了php5-mysqlnd（Native Driver）。两者都不会改变一件事。
<?php
error_reporting(E_ALL);
ini_set("display_errors", "1");
if (!defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO unavailable';
}
elseif (defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO available';
}
$pdo = new PDO('mysql:host=mysql.example.com;dbname=fallbackauthdb;charset=utf8', 'fba_user', 'my_Pa$$word', array(
PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ca-cert.pem'
));
foreach ($pdo->query('SHOW STATUS LIKE "%Ssl%"') as $row) {
print_r($row[0] ."=". $row[1] . PHP_EOL);
}
?>
Thats the error message I got:
这是我收到的错误消息：
PDO available
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1045] Access denied for user 'fba_user'@'mysql.example.com' (using password: YES)' in /var/www/gfa/sql.php:17 Stack trace: #0 /var/www/gfa/sql.php(17): PDO->__construct('mysql:host=mysq...', 'fba_user', 'my_Pa$$...', Array) #1 {main} thrown in /var/www/gfa/sql.php on line 17
If I disable SSL by "not requireing" it on the fba_user, the connection with PDO just works fine. I think its a bug, but maybe someone else can help please?
如果我通过fba_user上的“不需要”禁用SSL，则与PDO的连接工作正常。我认为这是一个错误，但也许别人可以帮忙吗？
EDIT 1: If I use the database-root user instead, the connection is working, but I'm not sure if ssl is used, because the query:
编辑1：如果我使用数据库root用户，连接正在工作，但我不确定是否使用ssl，因为查询：
SHOW STATUS LIKE "%SSL%";
does return
确实回来了
DHE-RSA-AES256-SHA
but I think the system is lying and is not using SSL secured mysql-connection at all.
但我认为系统处于说谎状态，并且根本没有使用SSL安全的mysql连接。
EDIT 2: I sniffed the packets and yes, for the root-User the connection is secured via SSL, but for the fba_user this isn't the case.
编辑2：我嗅探了数据包，是的，对于root用户，连接是通过SSL保护的，但是对于fba_user，情况并非如此。
Why I thought the system is lying?: I can change the path in the PHP script for the client certificates and I can even delete them from the system and reboot the server. The connection is still SSL encrypted, WTF?
为什么我认为系统在说谎？：我可以在PHP脚本中更改客户端证书的路径，我甚至可以从系统中删除它们并重新启动服务器。连接仍然是SSL加密的，WTF？
2 个解决方案
#1
0
I have the same issue at post Can't connect with PDO using ssl but mysqli with ssl works
我在帖子上有相同的问题无法使用ssl与PDO连接，但mysqli与ssl工作
I would suggest not using root user because I don't think you can force SSL and PDO will fail silently making you think it's working or packets could be encrypted if you're already using SSL (https) I think. An easy test is to just make another user, force ssl, try command line remote connection than use PDO ssl and connection will fail/be blocked and tell you SSL is not working via PDO.
我建议不要使用root用户，因为我认为你不能强制使用SSL，PDO会无声地失败，让你认为它正在工作，或者如果你已经使用SSL（https）我可以加密数据包。一个简单的测试就是让另一个用户，强制ssl，尝试命令行远程连接而不是使用PDO ssl，连接将失败/被阻止并告诉你SSL无法通过PDO工作。
Anyways we had to switch to MySQLI to get DB SSL properly working and using PDO did no work at all. I am still looking for ways to diagnose/debug and figure out why we can't connect using PDO ssl because it's a problem and some third party code ships with PDO as well meaning we can't use their code if we can't figure this out and we do have cause to use the multiple drivers PDO supports so I am trying to figure this out.
无论如何，我们不得不切换到MySQLI以使DB SSL正常工作并且使用PDO根本不起作用。我仍然在寻找诊断/调试的方法，并找出为什么我们无法使用PDO ssl进行连接，因为这是一个问题，而某些第三方代码附带PDO也意味着如果我们无法想象我们就无法使用他们的代码这个，我们确实有理由使用PDO支持的多个驱动程序，所以我试图解决这个问题。
If you've made any progress let me know :).
如果你有任何进展，请告诉我:)。
#2
0
Following code will help you
以下代码将帮助您
PDO('mysql:host='.HOST.';dbname='.DBNAME.';charset=utf8', USER, PASSWORD,
array( PDO::MYSQL_ATTR_SSL_KEY =>'/mysqlsslcertificate1/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/mysqlsslcertificate1/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/mysqlsslcertificate1/ca-cert.pem' )
);
#1
0
I have the same issue at post Can't connect with PDO using ssl but mysqli with ssl works
我在帖子上有相同的问题无法使用ssl与PDO连接，但mysqli与ssl工作
I would suggest not using root user because I don't think you can force SSL and PDO will fail silently making you think it's working or packets could be encrypted if you're already using SSL (https) I think. An easy test is to just make another user, force ssl, try command line remote connection than use PDO ssl and connection will fail/be blocked and tell you SSL is not working via PDO.
我建议不要使用root用户，因为我认为你不能强制使用SSL，PDO会无声地失败，让你认为它正在工作，或者如果你已经使用SSL（https）我可以加密数据包。一个简单的测试就是让另一个用户，强制ssl，尝试命令行远程连接而不是使用PDO ssl，连接将失败/被阻止并告诉你SSL无法通过PDO工作。
Anyways we had to switch to MySQLI to get DB SSL properly working and using PDO did no work at all. I am still looking for ways to diagnose/debug and figure out why we can't connect using PDO ssl because it's a problem and some third party code ships with PDO as well meaning we can't use their code if we can't figure this out and we do have cause to use the multiple drivers PDO supports so I am trying to figure this out.
无论如何，我们不得不切换到MySQLI以使DB SSL正常工作并且使用PDO根本不起作用。我仍然在寻找诊断/调试的方法，并找出为什么我们无法使用PDO ssl进行连接，因为这是一个问题，而某些第三方代码附带PDO也意味着如果我们无法想象我们就无法使用他们的代码这个，我们确实有理由使用PDO支持的多个驱动程序，所以我试图解决这个问题。
If you've made any progress let me know :).
如果你有任何进展，请告诉我:)。
#2
0
Following code will help you
以下代码将帮助您
PDO('mysql:host='.HOST.';dbname='.DBNAME.';charset=utf8', USER, PASSWORD,
array( PDO::MYSQL_ATTR_SSL_KEY =>'/mysqlsslcertificate1/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/mysqlsslcertificate1/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/mysqlsslcertificate1/ca-cert.pem' )
);