Photo by Clem Onojeghuo on Unsplash
To connect to a MySQL Server that requires SSL from PHP with PDO, you can use this piece of code:
try {
$db = new PDO('mysql:host=DB_HOST;dbname=DB_NAME', $user, $pass, [
PDO::MYSQL_ATTR_SSL_KEY => 'path/to/client_private_key',
PDO::MYSQL_ATTR_SSL_CERT => 'path/to/client_cert',
PDO::MYSQL_ATTR_SSL_CA => 'path/to/server_ca_cert',
]);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
To my surprise I was greeted with an abort signal (not an Exception
!) when running this piece of code when trying to connect to a MySQL 5.7 Server on Google Cloud SQL:
Terminated due to signal: ABORT TRAP (6)
Thinking it was a problem with the used certificates I turned to the mysql
binary to verify their contents:
$ mysql \
--ssl-cert='path/to/client_cert' \
--ssl-key='path/to/client_private_key' \
--ssl-ca='path/to/server_ca_cert' \
--host=DB_HOST \
--user=DB_USERNAME \
--password
And guess what: that command, of course, worked just fine … but what is the problem then? Is it Google Cloud SQL? Is it PHP acting up?
At the root of the problem is the fact that I’m connecting to the host using an IPv4 address and that the Common Name in the certificate – which one needs to choose manually when generating an SSL Certificate for a Cloud SQL instance – does not match (ref).
To work around this issue you have two options:
- (recommended) Connect to your Cloud SQL instance using the Cloud SQL proxy
- (not recommended, but needed when solution 1 is not an option) Disable the verification of the server SSL certificate
For the first solution follow Google’s instructions on the Cloud SQL Proxy.
For the second solution, set the PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
option to false
:
try {
$db = new PDO('mysql:host=DB_HOST;dbname=DB_NAME', $user, $pass, [
PDO::MYSQL_ATTR_SSL_KEY => 'path/to/client_private_key',
PDO::MYSQL_ATTR_SSL_CERT => 'path/to/client_cert',
PDO::MYSQL_ATTR_SSL_CA => 'path/to/server_ca_cert',
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
]);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
💁♂️ Do note that in this case your connection is encrypted, but you’re not validation the server SSL certificate, and therefore cannot be guaranteed that you’re connecting to the correct server …
With all these set, your PHP code should be able to connect to the MySQL instance without being terminated prematurely …
Thank me with a coffee.
I don\'t do this for profit but a small one-time donation would surely put a smile on my face. Thanks!
To stay in the loop you can follow @bramus or follow @bramusblog on Twitter.