8

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.

André
  • 2,042
  • 1
  • 23
  • 26
  • We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) 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](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). – Jay Blanchard Oct 23 '17 at 18:21
  • @JayBlanchard I've updated my question. Anything else you consider helpful? – André Oct 23 '17 at 19:33
  • Show us the PHP code where you try to connect and give us the full text of the errors. – Jay Blanchard Oct 23 '17 at 19:39
  • I have added more information. I think that adding more examples of things that did not work (as they also obviously do not make any sense) do not improve the question; they increase the effort to read the question without adding any value. – André Oct 23 '17 at 20:06
  • While you might think that it actually provides more information on what you're attempting and why it may not work. – Jay Blanchard Oct 23 '17 at 20:09
  • I rephrased the question again. Is it not a duplicate of the question you linked as I need to login with SSL which works from the console and Sequel Pro - but not yet with PDO as I do not know how to pass options/params or whatever it takes. Can you therefore please remove the link? – André Oct 23 '17 at 20:18
  • http://php.net/manual/en/ref.pdo-mysql.php *"SSL support is enabled using the appropriate PDO_MySQL constants, which is equivalent to calling the » MySQL C API function mysql_ssl_set()."* Specifically http://php.net/manual/en/ref.pdo-mysql.php#103501 – Jay Blanchard Oct 23 '17 at 20:27
  • 1
    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é Oct 23 '17 at 20:32
  • If you have no certificates then you cannot be using SSL. The duplicate stands because that is the error you're reporting. You're not reporting a connectivity error because of SSL ro the lack thereof. – Jay Blanchard Oct 23 '17 at 20:34
  • 1
    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é Oct 23 '17 at 20:44

2 Answers2

4

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,
];
Derek
  • 1,466
  • 15
  • 24
  • 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. – David Apr 21 '20 at 20:57
  • I'd been struggling with this and this is helpful to confirm what I'm also seeing. – Demerit Jun 27 '20 at 14:29
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.
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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. – André Oct 24 '17 at 07:54
  • @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? – Bill Karwin Oct 24 '17 at 15:09
  • 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). – André Oct 24 '17 at 16:25
  • 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. – Bill Karwin Oct 24 '17 at 17:56