How to use SSL in PDO mysql

connect securely to your MySQL database from PHP PDO

If you want to connect securely to your MySQL database over SSL from PHP Data Objects (PDO), here is how.

<?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);
    // set the PDO error mode to exception
    $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();
}
?>

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

As you may know, in your Windows Server IIS PHP config, you need to set a 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' : '',

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

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!

Psstt: Curious how to boost PHP performance with WinCache and OPcache? Learn how to add SSL in WordPress.

Posted by Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.