Connect to MySQL/MariaDB with PHP PDO with --ssl without certificate

Question

I can connect to MySQL/MariaDB from Sequel Pro (see this post) and from the console by providing the --ssl option without any certificate:

$ mysql -u myusername -p -h 10.123.45.67 --ssl
Enter password:
Welcome to the MariaDB monitor.

Connecting without --ssl is not possible, as the user requires SSL:

$ mysql -u myusername -p -h 10.123.45.67
Enter password:
ERROR 1045 (28000): Access denied for user 'myusername'@'10.123.45.67' (using password: YES)

How can I achieve the same in PHP with PDO?

I've researched for the past hours and did not find a solution. I tried with modifying the dsn and options parameters which are provided to the PDO constructor without success. I either end up with error code 1045 (access denied) or 2002 (can't connect through socket):

$pdo = new PDO('mysql:dbname=...;host=10.123.45.67;port=3306', 'myusername', '...');

PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user 'myusername'@'10.123.45.67' (using password: YES)

Adding PDO::MYSQL_ATTR_SSL_KEY(and others) as options to the PDO constructor, lead to 2002 (these options make no sense, as I don't have a key etc.). Though I do not know how to set up the connection in the same way as it is done from the console or internally in Sequel Pro.

Answer 1

I was able to enable an SSL connection without enabling client certificate authentication by setting the options passed to the PDO constructor. I set the CA to true and turned off server certificate verification:

$options = [
    PDO::MYSQL_ATTR_SSL_CA => '/dev/null',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
];

If you want to verify the server cert, then you need to set the CA to a file containing a real CA cert chain (though this isn't currently possible if you're using an Azure MySQL instance - you always need to disable server cert verification because PDO doesn't follow the CNAME records, but that's another issue entirely).

This works as of PHP 7.3. Not sure about earlier versions of PHP.

EDIT It turns out that the PDO::MYSQL_ATTR_SSL_CA is completely ignored, as long as you disable server verification. You still must set it to SOMETHING non-empty:

$options = [
    PDO::MYSQL_ATTR_SSL_CA => true,
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
];

Answer 2

https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-options.html says:

--ssl-mode=mode

This option is available only for client programs, not the server. It specifies the security state of the connection to the server. These option values are permitted:

• PREFERRED: Establish an encrypted connection if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established. This is the default if --ssl-mode is not specified.

• REQUIRED: Establish an encrypted connection if the server supports encrypted connections. The connection attempt fails if an encrypted connection cannot be established.

What does this mean? When you use --ssl-mode=PREFERRED and the server has an SSL cert, then the client will use it to authenticate and then you get an encrypted connection.

But if the server does not have an SSL cert, in spite of your request by using the --ssl client option, you don't get an encrypted connection.

(Using the deprecated --ssl client option is equivalent to using --ssl-mode=PREFERRED. It allows connections to be opened without encryption.)

How can you know if you have an SSL connection? In the mysql client, run:

mysql> status

It'll tell you if SSL is in use or not. See https://dba.stackexchange.com/questions/36776/how-can-i-verify-im-using-ssl-to-connect-to-mysql

Bottom line:

• You must have an SSL cert enabled on your MySQL server.
• If you don't have a cert, then you can't get an encrypted connection.