Connect to remote MySQL server with SSL from PHP

Question

I'm trying to connect to remote MySQL server with SSL from PHP using mysql_connect:

$link = mysql_connect(     "ip",     "user",     "pass",     true,     MYSQL_CLIENT_SSL ) 

And get worst error ever:

SSL connection error 

I've added following params into my.cnf:

[client] ssl-ca      =/etc/mysql/ssl/ca-cert.pm ssl-cert    =/etc/mysql/ssl/client-cert.pem ssl-key     =/etc/mysql/ssl/client-key.pem 

So I can connect to remote mysql successfully from terminal just using

#mysql -h ip -u user -p 

So connection to mysql server do work and as far as I understand problem is in php/mysql cooperation. Probably I'm missing some params.

Unfortunately I can't use mysqli lib because have too many working adapters for pdo_mysql.

My PHP Version is 5.3.6-13ubuntu3.6 MySQL version is 5.1.61

Also I've added

mssql.secure_connection = On 

to my php.ini

Help will be appreciated!

Answer 1

"Unfortunately I can't use mysqli lib because have too many working adapters for pdo_mysql."

You're using the old MySQL extension ("mysql_connect"), which is no longer under development (maintenance only). Since you're using PHP 5, you may want to use MySQLi, the MySQL Improved Extension. Among other things, it has an object-oriented interface, support for prepared/multiple statements and has enhanced debugging capabilities. You can read more about converting to MySQLi here; more about the mysqli class itself here.

Here is some sample code that may help you get started:

<?php ini_set ('error_reporting', E_ALL); ini_set ('display_errors', '1'); error_reporting (E_ALL|E_STRICT);  $db = mysqli_init(); mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);  $db->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL); $link = mysqli_real_connect ($db, 'ip', 'user', 'pass', 'db', 3306, NULL, MYSQLI_CLIENT_SSL); if (!$link) {     die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n"); } else {     $res = $db->query('SHOW TABLES;');     print_r ($res);     $db->close(); } ?> 

If PDO_MYSQL is really what you want, then you need to do something like this:

<?php $pdo = new PDO('mysql:host=ip;dbname=db', 'user', 'pass', array(     PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql/ssl/client-key.pem',     PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/client-cert.pem',     PDO::MYSQL_ATTR_SSL_CA    =>'/etc/mysql/ssl/ca-cert.pem'     ) ); $statement = $pdo->query("SHOW TABLES;"); $row = $statement->fetch(PDO::FETCH_ASSOC); echo htmlentities($row['_message']); ?> 

However, only recent versions of PHP have SSL support for PDO, and SSL options are silently ignored in (at least) version 5.3.8: see the bug report.

Good luck!

Answer 2

if your using PHP 7.3 and AWS PEM file , Use below code to connect DB with ssl

UR AWS PEM FILE PATH =/home/cert/rds2019.pem $mysqli = mysqli_init(); $mysqli->ssl_set(NULL,NULL,'UR AWS PEM FILE PATH',NULL,'DHE-RSA-AES256-SHA'); $mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true); $mysqli->real_connect($dbHostName, $dbUserName, $dbPassword, $dataBaseName,PORT,NULL, MYSQLI_CLIENT_SSL); 

return $mysqli;

For PDO Connection

$pdo = new PDO('mysql:host='.$dbHostName.';dbname='.$dataBaseName.'', $dbUserName, $dbPassword, array(             //PDO::MYSQL_ATTR_SSL_KEY    =>NULL,              //PDO::MYSQL_ATTR_SSL_CERT=>NULL,              PDO::MYSQL_ATTR_SSL_CA    =>'UR AWS PEM FILE PATH',             PDO::MYSQL_ATTR_SSL_CAPATH    =>NULL,             PDO::MYSQL_ATTR_SSL_CIPHER    =>'DHE-RSA-AES256-SHA',             PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false             )         ); return $pdo;