PHP PDO server's certificate verification

Known are problems about verifying server certificate by PHP PDO during using SSL for connection to a proxy. To help with resolving a problem, below suggestions can be helpful.

Applications suggested to use during work on a problem:

Reminder before work

  • All keys used by Heimdall are saved in a keystore named keystore.p12.
  • Password to the keystore is heimdall.
  • All key pair have to have empty password.
  • Default global key pair used by each proxy is named global_use_certificate.
  • Specific key pair for a proxy can be specified by adding key pair with alias matching lowercase name of a proxy, i.e. proxy named MySQLVirtualDatabase will use key pair aliased mysqlvirtualdatabase. That key pair have higher priority than global key pair.

1. Check certificate

There are known two issues with certificates, which can lead to problems during using PHP PDO. Issue can be:

  • an incorrect algorithm used during generating certificate,
  • an incorrect value of CN, which can be translated to an IP address.

1.1. Check used algorithm

First, algorithm used to generate key pair should be checked. There are known issues, when certificate from a key pair generated via DSA algorithm was causing issues. By default, self-signed certificate generated by Heimdall is generated by using RSA algorithm, but if key pair was generated or imported by an user, then that can't be assured.

Below can be seen marked section were is written what algorithm was used to generate key pair. Window below is visible after opening and unlocking keystore file.

If key pair was generated with using DSA, then suggested is generating or importing key pair with used RSA algorithm to generate them.

1.2. Check CN value

After checking algorithm, CN value used to sign certificate should be checked. There is a known requirement on PHP PDO using SSL that CN value should match or be possible to translate to an IP address.

First step to check CN value is checking Certificate Chain Details. To get access to that window, below steps have to be done:

  1. Right click chosen key pair.
  2. Choose option View Details(area 1 marked on the image below).
  3. Click option Certificate Chain Details (area 2 marked on the image below).

After completing above steps, the window below should pop up.In the marked area is given information about certificate details of a key pair. By default, certificate generated by Heimdall is self-generated and CN value is matching value Heimdall. The CN value should be a proper IP address or be possible to translate to an IP address.

If key pair's CN value isn't a proper IP address and can't be translated to an IP address, then suggested is generating or importing key pair with CN value which meets these requirements.

2. Export certificate to PEM format

After ensuring that certificate meets the requirements of PHP PDO's SSL certificate verification, the certificate can be finally exported to .pem file. To do that, first should be opened Certificate Chain Details window. To get access to that window, below steps have to be done:

  1. Right click chosen key pair.
  2. Choose option View Details(area 1 marked on the image below).
  3. Click option Certificate Chain Details (area 2 marked on the image below).

After completing above steps, below window should pop up. Next step is opening certificate view in PEM format. To do that, click marked on the below image button PEM.

After clicking PEM button, below window should pop up. To begin exporting certificate in PEM format, button Export should be clicked (as marked on the image below).

After clicking Export button, below window should pop up. The last step is to save exported PEM certificate to the file. To do that:

  1. Choose in what directory should the certificate be saved (by using controls in marked are 1).
  2. Name a file with exported PEM file by writing name in area 2 marked on the image below. Remember that suffix .pem won't be added automatically, so can be added, if needed, during naming file.
  3. Click button Choose in the marked area 3 on the image below.

After following above steps, the certificate should be exported in the PEM format to the chosen file.

Example configuration in PHP PDO application

After exporting certificate to a PEM format, it is ready to use in PGP PDO application. Below can be seen an example configuration in PHP code to set connection via PDO to a MySQL database, with defined SSL parameters.

$myPDO = new PDO('mysql:host=example.database.com;dbname=mysql', 'root', 'password', array(
    PDO::MYSQL_ATTR_SSL_KEY    => '/example/keys/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT   => 'certificates/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA     => 'certificates/server-cert.pem'
));

SSL parameters used above are used to declare:

  • PDO::MYSQL_ATTR_SSL_KEY - declares a file with a key, to be used during SSL connection
  • PDO::MYSQL_ATTR_SSL_CERT - declares a file with a certificate, to be used during SSL connection
  • PDO::MYSQL_ATTR_SSL_CA - declares a file with a certificate, to be verified if matches server's certificate. A certificate exported in previous steps should be set here.

Above parameters declare file path. The file path can be written as absolute file path (as can be seen for parameter PDO::MYSQL_ATTR_SSL_KEY) or as relative file path (as can be seen for parameters PDO::MYSQL_ATTR_SSL_CERT and PDO::MYSQL_ATTR_SSL_CA).