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.

We are always glad to help and support new coders but you need to help yourself first. :-) After doing more research if you have a problem post what you've tried with a clear explanation of what isn't working and provide a Minimal, Complete, and Verifiable example. Read How to Ask a good question. Be sure to take the tour and read this. — Jay Blanchard
@JayBlanchard I've updated my question. Anything else you consider helpful? — André
Show us the PHP code where you try to connect and give us the full text of the errors. — Jay Blanchard
As mentioned in the question, this is what I tried with the constants like MYSQL_ATTR_SSL_KEY but it makes no sense as I have no certificates and I obviously also do not need them. So there must be something besides these constants. Can you please remove the "duplicate" link? — André
The question is about what Sequel Pro and mysql console are doing with the --ssl option and how it can be possible that I can connect there but not from PDO. That I cannot access without SSL when SSL is required to login for the given user is obvious. But again: I have no certificates/keys/etc. provided (same as in the linked Sequel Pro question). — André

Derek Derek · Accepted Answer · 2019-05-15T16:14:18

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,
];

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

2 Answers