Connect to MySQL w/SSL no verify


#1

Hi there,

I was able to troubleshoot my issue, but I wanted to share my findings here in case someone else runs into the same problem. I was not able to connect to my database using SSL with no certificate / validation during the InvoicePlane setup in the below scenario:

Scenario

I run a separate application and database servers which communicate via SSL over intranet. I use a self-signed certificate for MySQL and create my database users with REQUIRE SSL, and everything is encrypted and normally works fine without supplying any certificate or key info. I can connect from the application server via mysql client command and have several WordPress sites that work fine without supplying this info (only the MYSQLI_CLIENT_SSL flag needs to be supplied to mysqli_real_connect)

As an example, I followed this guide when setting up the MySQL server: https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-16-04

After much trial and error and research, I discovered what is probably a bug in the CodeIgniter mysqli driver, which is used by InvoicePlane to connect to MySQL. It is necessary to supply some valid SSL-related value (I chose to use the cipher value, since I knew what it was) to the database driver, even though that value should strictly not be required, along with the verify_ssl = false option. If the former is not set, the MYSQLI_CLIENT_SSL flag is never set, and if the latter isn’t set, mysqi will attempt to validate a certificate, which isn’t strictly necessary in this case.

Here’s my solution:

Edit the application/config/database.php file to supply a valid (but should be unnecessary) SSL configuration parameter along with the ssl_verify option set to false. Example:

$sslConfig = array(
    'ssl_verify' => false,
    'ssl_cipher' => 'DHE-RSA-AES256-SHA'
);

$db['default'] = array(
   // ...
   'encrypt' => $sslConfig,
   // ...
);

Again, this issue is resolved for me, so please flag it as such, but I wanted to pass the information along in case someone else comes here looking for an answer.

Regards,
Spencer