php script fails to connect to MySQL with SSL

Question

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']);
?>

The code above gives SSL operation failed with code 1 - here is the full message:

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

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.

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

But to get this to work I need to install a MySQL connector on the server which I'm grappling with at the moment.

Answer 1

I have solved this problem -thanks for all who have helped. This is what I have learned:

• 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

• You must have the ...64 paths otherwise the driver isn't found ( i.e Driver64 = NOT Driver= ). I made this mistake first off.

• Provided the driver files are found at the paths in odbcinst.ini then things should work. (I thought I needed entries in odbc.ini but I now believe you only need something here if you are using a DSN).
• the folder odbc2 was one I created inside /etc/lib which already exists. I did that to avoid any permission issues by creating a new folder.

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

I hope this is useful.