Monday, 13 June 2016


When you have PHP running on same server as webserver like Apache or Nginx, there is no need to worry if somebody can monitor data sent between PHP and MySQL. All the data are being sent through the local socket and are not leaving your server.

But with cloud hosting, docker containers, there is good chance that data will flow through the network, which you can't control and you can't bee sure that somebody is not listening. Even if you use solutions like AWS EC2 with VPC (virtual private cloud) still there is no harm to encrypt traffic between webserver and database.

Following post describes quick and painless way how to do it and things to watch for.

MySQL

Database server configuration is very easy. With configuration can help a lot MySQL Workbench. This tool helps you with generating self signed certificates which you install on server and you use on client, in our case php.
When you look at Workbench SSL tab on existing connection:

You will see SSL Wizard button. When you click on button and you go through the process, it generates self signed client and server certificates and configuration file changes which need to be applied on database server.

Then I would suggest to create extra user where SSL connection is required.
See MySQL documentation or use something like:

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'password' REQUIRE SSL;

PHP

In PHP you have three supported ways how to connect to the database:
  • MySQL
  • MySQLi
  • PDO MySQL
In theory all of the should support SSL connection with usage of underlying OpenSSL library.

MySQL extension

MySQL is deprecated extension and was removed from PHP7. PHP documentation   says that you can pass client flag MYSQL_CLIENT_SSL and use SSL encryption. This is didn't work for me. If you use this deprecated extension you realy should upgrade to MySQLi, this is not difficult, it has almost identical API only with "i" on the end (mysql_query => mysqli_query). For some methods you might need to shift parameters but it's nothing difficult.

MySQLi extension

MySQLi does fully support SSL, before connecting to database you need to use mysqli::ssl_set function. 

Sample code could look like this:
<?php
$connection = mysqli_init();

mysqli_ssl_set(
    $connection,
    $db_client_key,
    $db_client_cert,
    $db_ca_cert,
    null,
    null
);

mysqli_real_connect(
    $connection,
    $host,
    $user,
    $pass,
    $name,
);


PDO MySQL extension

 Usage is very similar to MySQLi extension.

$connection = new PDO('mysql:host=ip;dbname=db', 'user', 'pass', array(
    PDO::MYSQL_ATTR_SSL_KEY    => $db_client_key,
    PDO::MYSQL_ATTR_SSL_CERT=> $db_client_cert,
    PDO::MYSQL_ATTR_SSL_CA    => $db_ca_cert,
    )
);

Things to watch for 

If you will see error similar to:

mysqli::real_connect(): Peer certificate CN=XX did not match expected CN=YY 

It's related to peer verification issue  - enabled by default since PHP 5.6. Because in this context peer verification doesn't make much sense you can disable it by :

MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT

 In code:

mysqli_real_connect(
    $connection,
    $host,
    $user,
    $pass,
    $name,
    3306,
    '',
    MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
);

  
Another annoying problem could be: 
mysqli_real_connect() : this stream does not support SSL/crypto
PDO::__construct() : this stream does not support SSL/crypto

It's related to this problem. It happens when is used socket. MySQL documentation says:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file

Solution is simple, don't use socket with SSL, if you use something like Vagrant, change server address to IP address.

Otherwise SSL on MySQL and together with PHP is not difficult to use so give it a try.

PHP with MySQL and SSL


When you have PHP running on same server as webserver like Apache or Nginx, there is no need to worry if somebody can monitor data sent between PHP and MySQL. All the data are being sent through the local socket and are not leaving your server.

But with cloud hosting, docker containers, there is good chance that data will flow through the network, which you can't control and you can't bee sure that somebody is not listening. Even if you use solutions like AWS EC2 with VPC (virtual private cloud) still there is no harm to encrypt traffic between webserver and database.

Following post describes quick and painless way how to do it and things to watch for.

MySQL

Database server configuration is very easy. With configuration can help a lot MySQL Workbench. This tool helps you with generating self signed certificates which you install on server and you use on client, in our case php.
When you look at Workbench SSL tab on existing connection:

You will see SSL Wizard button. When you click on button and you go through the process, it generates self signed client and server certificates and configuration file changes which need to be applied on database server.

Then I would suggest to create extra user where SSL connection is required.
See MySQL documentation or use something like:

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'password' REQUIRE SSL;

PHP

In PHP you have three supported ways how to connect to the database:
  • MySQL
  • MySQLi
  • PDO MySQL
In theory all of the should support SSL connection with usage of underlying OpenSSL library.

MySQL extension

MySQL is deprecated extension and was removed from PHP7. PHP documentation   says that you can pass client flag MYSQL_CLIENT_SSL and use SSL encryption. This is didn't work for me. If you use this deprecated extension you realy should upgrade to MySQLi, this is not difficult, it has almost identical API only with "i" on the end (mysql_query => mysqli_query). For some methods you might need to shift parameters but it's nothing difficult.

MySQLi extension

MySQLi does fully support SSL, before connecting to database you need to use mysqli::ssl_set function. 

Sample code could look like this:
<?php
$connection = mysqli_init();

mysqli_ssl_set(
    $connection,
    $db_client_key,
    $db_client_cert,
    $db_ca_cert,
    null,
    null
);

mysqli_real_connect(
    $connection,
    $host,
    $user,
    $pass,
    $name,
);


PDO MySQL extension

 Usage is very similar to MySQLi extension.

$connection = new PDO('mysql:host=ip;dbname=db', 'user', 'pass', array(
    PDO::MYSQL_ATTR_SSL_KEY    => $db_client_key,
    PDO::MYSQL_ATTR_SSL_CERT=> $db_client_cert,
    PDO::MYSQL_ATTR_SSL_CA    => $db_ca_cert,
    )
);

Things to watch for 

If you will see error similar to:

mysqli::real_connect(): Peer certificate CN=XX did not match expected CN=YY 

It's related to peer verification issue  - enabled by default since PHP 5.6. Because in this context peer verification doesn't make much sense you can disable it by :

MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT

 In code:

mysqli_real_connect(
    $connection,
    $host,
    $user,
    $pass,
    $name,
    3306,
    '',
    MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
);

  
Another annoying problem could be: 
mysqli_real_connect() : this stream does not support SSL/crypto
PDO::__construct() : this stream does not support SSL/crypto

It's related to this problem. It happens when is used socket. MySQL documentation says:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file

Solution is simple, don't use socket with SSL, if you use something like Vagrant, change server address to IP address.

Otherwise SSL on MySQL and together with PHP is not difficult to use so give it a try.