使用带有--ssl的PHP PDO连接MySQL / MariaDB而不使用证书

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

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.