3

I have an issue using MySQL on PHP 5.6, and after three days of debugging PHP, OpenSSL, MySQLnd Drivers on PHP and trying out the mysql_client on an Ubuntu 14.04 machine, I have come to a conclusion: Google Cloud SQL SSL certificates will not work on PHP 5.6 and above.

For a start, the Google Cloud is a great service, and modern cryptography is enforced throughout the Google ecosystem. However, I couldn't use it because of one little problem: Google Cloud SQL SSL Server certificates have impossible common names.

Google Cloud SQL Server (or peer) certificates have a Common Name (CN) that looks something like:

  CN=project-name:instance-id

To make matters worse, starting from PHP 5.6, all encrypted client streams will go through mandatory peer certificate validation. (Link: OpenSSL changes in PHP 5.6.x). I use the PHP Data Objects (PDO) extension, which uses the native driver MySQLnd to handle all the MySQL business. This uses the native PHP streams to handle those connections.

I have been looking through the MySQLnd source code on PHP (Link: MYSQLnd Driver Code on GitHub), MySQLnd configuration options to try and disable the SSL peer verification code on this particular MySQLND. To no avail.

Therefore, what should I do if I need to use SSL for MySQL connections on PHP 5.6?

Your response is greatly appreciated!

watonlyme
  • 43
  • 1
  • 6
  • In http://php.net/manual/en/migration56.openssl.php you may find "All encrypted client streams now enable peer verification by default" and "While not recommended in general, it is possible to disable peer certificate verification for a request by setting the verify_peer context option to FALSE, and to disable peer name validation by setting the verify_peer_name context option to FALSE", could you try it that to see if it works for you? – Mario Mar 26 '15 at 13:40
  • Hi Mario, thanks for your reply. I use PHP's PDO to handle database transactions. According to the [PHP PDO_MYSQL driver documentation](http://php.net/manual/en/ref.pdo-mysql.php), there are options for CA, Client cert and key. There isn't an option for SSL verify peer. PDO_MYSQL uses mysqlnd, and [mysqlnd uses native php streams](http://php.net/manual/en/mysqlnd.notes.php). This means that according to the above source code link (MySQLnd Driver code...), they handle it internally. I couldn't find a way to edit the verify_peer_name context option without messing with the PHP source afaik... – watonlyme Mar 26 '15 at 16:38
  • Related: http://stackoverflow.com/q/28777416/3645370 – David Apr 17 '15 at 00:09
  • 1
    This seems to be fixed in php 5.6.16 https://bugs.php.net/bug.php?id=68344 using: MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT – iewebguy Mar 09 '16 at 16:44
  • 2
    @iewebguy That bug refers to mysqli, not PDO that this question covers. As you can also see in the end of that bug the issue still exists for PDO, see https://bugs.php.net/bug.php?id=71003 – Nils Apr 04 '16 at 08:58

2 Answers2

1

Try connecting through the proxy if you have second generation sql, you might be able to connect pdo via the proxy with 127.0.0.1: https://cloud.google.com/sql/docs/compute-engine-access#gce-connect-proxy

Helgi Andri Jónsson
  • 11
  • 1
  • Indeed, Cloud SQL proxy is provided as a solution for these scenarios where direct authentication to Cloud SQL isn't feasible. The docs 'Connecting MySQL Client Using the Cloud SQL Proxy' also give a good general overview of how it works. It's essentially the same as connecting to a local MySQL instance without auth. https://cloud.google.com/sql/docs/mysql-connect-proxy – Adam Oct 14 '16 at 21:43
  • Yep, I had the same problem with php 7.0 and I used the google sql proxy. I also believe it correctly handles load balancing on the master sql when you have a read or failover replicas. I have been using the proxy in production for one week and it has been proving itself as much wiser approach. You may find that there are no instructions on how to start the sql proxy daemon process in the background but it only takes about an hour to figure that one out. – Helgi Andri Jónsson Oct 20 '16 at 19:08
0

Assuming you can convince MySQL that the hostname is should verify is in fact project-name:instance-id then I would have thought the hostname validation ought to succeed (though I agree that cert doesn't look great).

I tried the following from my Mac OS X workstation and it appeared to succeed, at least when using the mysql binary (I didn't try via PHP).

First I added a hosts entry on my machine included the colon as part of the name:

1.2.3.4   project-name:instance-id

After doing so I was able to connect successfully with the mysql (5.6.32) installed on my machine:

mysql -uroot -h "project-name:instance-id" --password \
    --ssl \
    --ssl-ca ~/Downloads/server-ca.pem \
    --ssl-cert ~/Downloads/client-cert.pem \
    --ssl-key ~/Downloads/client-key.pem \
    --ssl-verify-server-cert

(When I ran that same command with the IP address instead, I received ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure)

aeijdenberg
  • 2,427
  • 1
  • 16
  • 13