How to Securely Connect to a Remote MySQL Server via SSL in PHP?

Connecting to Remote MySQL Server with SSL from PHP

When attempting to establish a connection to a remote MySQL server with SSL using the old MySQL extension (mysql_connect), users may encounter an error indicating "SSL connection error." Despite configuring appropriate SSL parameters in my.cnf to enable secure terminal connections, the PHP connection remains unsuccessful.

PHP Version Considerations

The provided code utilizes the outdated MySQL extension, which is no longer actively developed. Instead, it is recommended to use the MySQLi extension (MySQL Improved Extension) which offers an enhanced feature set, including:

• Object-oriented interface
• Support for prepared and multiple statements
• Improved debugging capabilities

Improved Connectivity with MySQLi

The following sample code showcases how to use MySQLi for a secure SSL connection:

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

PDO_MYSQL Alternative

While not ideal due to limited SSL support in older PHP versions, PDO_MYSQL can be utilized for SSL connections:

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

It is important to note that SSL support in PDO for PHP versions prior to 5.3.8 may not function as expected.

Should you encounter further issues, please consult the following resources for additional guidance:

• [PHP MySQLi SSL Connection Example](https://www.digitalocean.com/community/tutorials/how-to-use-ssl-to-secure-mysql-connections-in-php)
• [PDO MySQL SSL Configuration](https://stackoverflow.com/questions/30075513/pdo-ssl-connection-fails)

The above is the detailed content of How to Securely Connect to a Remote MySQL Server via SSL in PHP?. For more information, please follow other related articles on the PHP Chinese website!