php脚本无法使用SSL连接到MySQL [英] php script fails to connect to MySQL with SSL
问题描述
下面的脚本在Centos服务器上运行，并试图连接到另一台需要SSL参数的服务器上的MySQL数据库.脚本中使用的凭据可以通过Microsoft Access DSN连接正常使用.
The script below runs on a Centos server and is trying to connect to a MySQL database on another server which requires SSL parameters. The credentials used in the script work fine using and Microsoft Access DSN connection.
<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);
$pdo = new PDO('mysql:host=99.99.199.199;dbname=dummy1', 'user1', 'pwd1',
array(
PDO::MYSQL_ATTR_SSL_KEY =>'/etc/mysql/ssl/ck.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/cc.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/etc/mysql/ssl/c1.pem'
));
$statement = $pdo->query("SHOW TABLES;");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);
?>
上面的代码给出 SSL操作失败，代码为1 -这是完整的消息:
The code above gives SSL operation failed with code 1 - here is the full message:
严重错误:未捕获PDOException:PDO :: __ construct():SSL操作 失败，代码1.OpenSSL错误消息:error:14090086:SSL 例程:ssl3_get_server_certificate:证书验证失败 /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php:10 堆栈跟踪:#0/var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php(10):PDO-> __ construct('mysql:host = 99.9 ...'，'odbc_guil ...'， 'pwd1'， 数组)#1 {main}下一个PDOException:SQLSTATE [HY000] [2002] in /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php:10堆栈跟踪: #0/var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php(10):PDO-> __ construct('mysql:host = 99.9 ...'，'odbc_guil ...'，'pwd1' ， 数组)#1 {main}被抛出 /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php，第10行
Fatal error: Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed in /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php:10 Stack trace: #0 /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php(10): PDO->__construct('mysql:host=99.9...', 'odbc_guil...', 'pwd1', Array) #1 {main} Next PDOException: SQLSTATE[HY000] [2002] in /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php:10 Stack trace: #0 /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php(10): PDO->__construct('mysql:host=99.9...', 'odbc_guil...', 'pwd1', Array) #1 {main} thrown in /var/www/vhosts/zzzzz.org/httpdocs/zzodbc/dgodbc1.php on line 10
我已验证凭据(包括带有DSN连接的SSL参数).我检查了SSL密钥是否正确位于/etc/mysql/ssl 目录中.
I have verified that the credentials, including the SSL parameters with a DSN connection. I have checked that the SSL Keys are correctly located in the /etc/mysql/ssl directory.
任何建议我做错事的帮助都会很好.谢谢.
Any help to suggest what I'm doing wrong would be good. Thanks.
我可能一直以错误的方式进行此操作.... 由于这些键可与ODBC一起使用，因此我认为我应该使用odbc_connect并发送与MS访问(例如
I may have been going at this in the wrong way.... Since these keys work with ODBC then I think I should be using using odbc_connect and sending the same string as I use with MS access such as
$user = "user";
$pass = "pwd";
$connection = "Driver={MySQL ODBC 5.1 Driver};Server=46.51.178.163;Database=db1;sslca=/etc/mysql/ssl/c1.pem;sslkey=/etc/mysql/ssl/ck.pem;sslcapath=/etc/mysql/ssl/;sslcert=/etc/mysql/ssl/cc.pem";
$con = odbc_connect($connection, $user, $pass);
但是要使其正常工作，我需要在目前正在使用的服务器上安装MySQL连接器.
But to get this to work I need to install a MySQL connector on the server which I'm grappling with at the moment.
推荐答案
我已经解决了这个问题-感谢所有提供帮助的人.这是我所学到的:
I have solved this problem -thanks for all who have helped. This is what I have learned:
- SSL密钥是特定于连接类型的-所以我有与ODBC一起使用的密钥，期望它们与PDO一起使用是错误的
- ODBC驱动程序(php扩展)需要安装在服务器上-它们不会自动出现. 此处是一段出色的视频，展示了如何执行此操作.
- 您需要通过命令行访问服务器以安装驱动程序(并将SSL密钥上传到安全位置)-它们位于/etc/mysql/ssl中.
- 我将驱动程序安装在/usr/lib/odbc2/lib中，而不是在视频中的长文件夹名称中.我还在/usr树中安装了，因为当我尝试视频中的位置时，找不到文件错误.这两个驱动程序文件是 libmyodbc5a.so 和 libodbc5w.so .似乎只需要... 5w.so文件.
-
这些文件到位后，您需要在/etc文件夹中的 odbcinst.ini 中添加一个条目.我使用了nano，因此命令行 nano odbcinst.ini 会打开该文件，其中包含用于PostgresSQL的模型条目.如果服务器是64位的，那么这些是我在odbcinst.ini中输入的内容: [mysql537] 驱动程序64 =/usr/lib/odbc2/lib/libmyodbc5w.so Setup64 =/usr/lib/odbc2/lib/libmyodbc5w.so UsageCount = 1
- SSL keys are connection type specific - so I had keys that worked with ODBC and it was wrong to expect them to work with PDO
- ODBC drivers ( php extensions ) need to be installed on the server - they aren't automatically present. Here is an excellent video showing how to do this.
- You need command line access to the server to install the driver ( and also to upload the SSL keys to a secure location ) - they are in /etc/mysql/ssl.
- I installed the driver in /usr/lib/odbc2/lib rather than in the long folder name in the video. I also installed the in the /usr tree because when I tried the locations in the video I got file not found errors. The two driver files are libmyodbc5a.so and libodbc5w.so. Only the ...5w.so file seems to be required.
Once these files are in place then you need to add an entry to odbcinst.ini in the /etc folder. I used nano so the command line nano odbcinst.ini brings up the file which had a model entry for PostgresSQL. If the server is 64 bit then these are the entries I made in odbcinst.ini: [mysql537] Driver64 = /usr/lib/odbc2/lib/libmyodbc5w.so Setup64 = /usr/lib/odbc2/lib/libmyodbc5w.so UsageCount = 1
您必须具有... 64路径，否则找不到驱动程序(即Driver64 = NOT Driver =).我首先犯了这个错误.
You must have the ...64 paths otherwise the driver isn't found ( i.e Driver64 = NOT Driver= ). I made this mistake first off.
以下是有效的代码(连接字符串与Microsoft Access连接中使用的字符串完全相同):
Here is the code that works ( the connection string is exactly the same as the string used in a Microsoft Access connection ):
<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);
$user = "odbcmmm";
$pass = "999999999";
$connection = "Driver={mysql537};Server=99.99.199.199;Database=db_name;UID=odbc_db_name;PWD=password;sslca=/etc/mysql/ssl/c1.pem;sslkey=/etc/mysql/ssl/ck.pem;sslcapath=/etc/mysql/ssl/;sslcert=/etc/mysql/ssl/cc.pem";
$con = odbc_connect($connection, $user, $pass);
$sql="SELECT Id from stk_item";
$rs=odbc_exec($con,$sql);
if (!$rs) {
exit("Error in SQL");
}
我希望这是有用的.
这篇关于php脚本无法使用SSL连接到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
