Bug #85213 ssl_mode=PREFERRED doesn't prefer SSL
Submitted: 27 Feb 2017 18:55 Modified: 22 Oct 2018 16:50
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: C API (client library) Severity:S1 (Critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 2017 18:55] Domas Mituzas
if I specify ssl_mode=PREFERRED SSL is not preferred and plaintext connection is used
if I specify ssl_mode=REQUIRED or stricter then SSL is used

this can be reproduced both directly with libmysqlclient and bin/mysql

How to repeat:
$ mysql -ussltest --ssl-mode=PREFERRED -h localhost 
WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.
ERROR 1045 (28000): Access denied for user 'ssltest'@'localhost' (using password: NO)

$ mysql -ussltest --ssl-mode=REQUIRED
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7478
Server version: 5.7.17 Homebrew

$ mysql -ussltest --ssl-mode=VERIFY_IDENTITY
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7487
Server version: 5.7.17 Homebrew

Suggested fix:
use PREFERRED as documentation suggests, to prefer SSL
[28 Feb 2017 7:15] Chiranjeevi Battula
Hello Domas Mituzas,

Thank you for the bug report.
Verified based on internal discussion with dev's.

[9 Mar 2017 7:34] MySQL Verification Team
are you sure that local socket connection isn't considered a security with/without ssl?   i see this in the function cli_calculate_client_flag (haven't checked in debugger).

 if(mysql->options.protocol == MYSQL_PROTOCOL_SOCKET &&
     mysql->options.extension &&
     mysql->options.extension->ssl_mode <= SSL_MODE_PREFERRED)
    mysql->client_flag&= ~CLIENT_SSL;
    mysql->options.extension->ssl_mode= SSL_MODE_DISABLED;
[22 Oct 2018 16:33] Paul DuBois
Posted by developer:

Added to --ssl-mode description:

Encrypted connections over Unix sockets are disabled by default, so PREFERRED does not establish an encrypted connection. To enforce encryption for Unix socket connections, use REQUIRED or above.
[22 Oct 2018 16:50] Domas Mituzas
I don't even. 

"MySQL - ignoring your preferences since 1995!"

But sure, there's no better way to fix a weird behavior than to document it.