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
– Jay Blanchard, Commented Oct 23, 2017 at 18:21
@JayBlanchard I've updated my question. Anything else you consider helpful? — André
– André, Commented Oct 23, 2017 at 19:33
Show us the PHP code where you try to connect and give us the full text of the errors. — Jay Blanchard
– Jay Blanchard, Commented Oct 23, 2017 at 19:39
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é
– André, Commented Oct 23, 2017 at 20:32
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é
– André, Commented Oct 23, 2017 at 20:44

Derek · Accepted Answer · 2019-05-23 18:16:23Z

5

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

edited May 23, 2019 at 18:16

answered May 15, 2019 at 16:14

Derek

1,61618 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

I tried it with php 7.1 and got an error having to do with the TLS protocol version (sorry I don't have the exact wording of the error message). I bumped php up to 7.3 and the issue went away.

Demerit Over a year ago

I'd been struggling with this and this is helpful to confirm what I'm also seeing.

Bill Karwin · Accepted Answer · 2017-10-24 00:22:59Z

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.

edited Oct 24, 2017 at 0:22

answered Oct 24, 2017 at 0:16

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

4 Comments

André Over a year ago

Thank you very much, this is helpful. I found that "--ssl-mode=PREFERRED" is not (yet) supported on my machine so I went with "--ssl". Entering "status" in the active connection outputs besides other information "SSL: Cipher in use is DHE-RSA-AES256-SHA". So it seems an SSL connection is in place. This leaves me with the question on how to use this new knowledge for opening the connection with PHP's PDO.

Bill Karwin Over a year ago

@André do you have SSL cert options configured for your client, either in your $HOME/.my.cnf or in /etc/my.cnf under the [client] or [mysql] section?

André Over a year ago

The only my.cnf I have is in /etc/mysql/my.cnf. Under [client] there is only port and socket and under [mysql] there is not a single entry. The only SSL related lines are commented out (e.g.: # ssl-ca=/etc/mysql/cacert.pem).

Bill Karwin Over a year ago

Okay I have nothing to suggest. As far as I know, an SSL connection requires a valid cert on both client and server side. I found some references that while the mysql client can skip CA verification, PHP does not.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related