How to use SSL in PHP Data Objects (PDO) mysql

Photo of author
Written By Jan Reilink

Windows Server systems administrator & enthusiast.

If you want to connect securely to your MySQL database using PHP Data Objects (PDO), here is how to connect to MySQL with PHP PDO using an SSL encrypted connection.

<?php $servername = "db_hostname"; $username = "db_username"; $password = "db_password"; $options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::MYSQL_ATTR_SSL_CA => '/path/to/cacert.pem', PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, ); try { $conn = new PDO("mysql:host=$servername;port=3306;dbname=db_name", $username, $password, $options); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; var_dump($conn->query("SHOW STATUS LIKE 'Ssl_cipher';")->fetchAll()); $conn = null; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } ?>
Code language: PHP (php)

Please note that the above code should only work correctly on Windows Server, due to path to cacert.pem. See below. The PDO option MYSQL_ATTR_SSL_VERIFY_SERVER_CERT is important to disable checking of the server certificate. If omitted, you’ll receive an error: `SQLSTATE[HY000] [2002]`.

Cross-platform MYSQL_ATTR_SSL_CA usage in PHP

As you may know, in your Windows Server IIS PHP config, you need to set an openssl.cafile directive, providing the path to a Certificate Authority (CA) file. This is not necessary on Linux, where the system default is used. MYSQL_ATTR_SSL_CA needs this path on Windows, and may be omitted on Linux.

To add this logic into your PDO test script, add an extra variable with your OS information and change the MYSQL_ATTR_SSL_CA line:

$OS = ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ); PDO::MYSQL_ATTR_SSL_CA => $OS ? 'c:/path/to/cacert.pem' : '',
Code language: PHP (php)

Your $options array becomes:

$OS = ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ); $options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::MYSQL_ATTR_SSL_CA => $OS ? 'c:/path/to/cacert.pem' : '', PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, );
Code language: PHP (php)

If $OS === true (e.g $OS === 'WIN') use c:/path/to/cacert.pem as PDO::MYSQL_ATTR_SSL_CA file path, else we’re on Linux and omit the path. This way you are sure you can use this PHP code cross-platform on both Windows Server and Linux!

Secure MySQL connections in WordPress with SSL/TLS

In WordPress you can define a MYSQL_CLIENT_FLAGS constant with MYSQLI_CLIENT_SSL as its value. This makes WordPress make SSL/TLS secured connections to your MySQL database.

Add to your wp-config.php file:

define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );
Code language: PHP (php)

Did you like: How to use SSL in PHP Data Objects (PDO) mysql

Then please, take a second to support Sysadmins of the North and donate!

Your generosity helps pay for the ongoing costs associated with running this website like coffee, hosting services, library mirrors, domain renewals, time for article research, and coffee, just to name a few.



Hi! Join the discussion, leave a reply!