SSL Broken from 2.2.2 on

[mquillin]

Describe the bug

When building a new docker image today - we did not have mysqlclient pinned and our SSL connection to mysql broke with the following error

  File "/usr/local/lib/python3.11/site-packages/MySQLdb/connections.py", line 193, in __init__
    super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (9002, 'SSL connection is required. Please specify SSL options and retry.')

relevant requirements.txt

SQLAlchemy==2.0.25
mysqlclient==2.2.3
mysql==0.0.3

relevant code

def create_db_connection(conn_str: str, num_workers: int, query_timeout: int = 15, ssl_mode: bool = False,
                         ssl_path: str = "") -> Engine:
    connect_args = {'connect_timeout': query_timeout}

    if ssl_mode:
        ssl_context = ssl.create_default_context(cafile=ssl_path)
        ssl_context.verify_mode = ssl.CERT_REQUIRED
        ssl_context.verify_flags = ssl.VERIFY_X509_STRICT
        ssl_context.check_hostname = True
        connect_args['ssl'] = ssl_context

    return create_engine(
        conn_str,
        poolclass=QueuePool,
        pool_size=num_workers,
        max_overflow=8,
        pool_pre_ping=True,
        connect_args=connect_args
    )

This works on 2.2.1 but is broken on 2.2.2+/

This commit looks to be the culprit 720b804

Environment

No response

How did you install libmysqlclient libraries?

No response

What version of mysqlclient do you use?

No response

Docker command to start MySQL server

No response

Minimum but complete code to reproduce

# Write Python code here.
import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root')
...

Schema and initial data required to reproduce.

-- Write SQL here.
-- e.g. CREATE TABLE ...

Commands, and any other step required to reproduce your issue.

No response

[methane]

connect_args['ssl'] = ssl_context

This is not supported by mysqlclient. PyMySQL does.

This commit looks to be the culprit 720b804

Although ssl=sslcontext is not supported by mysqlclient, it called mysql_ssl_set() with all NULL parameters. After the commit, it calls mysql_options() 5 times. It is exactly what mysql_ssl_set() did. There is no behavior changes.

mysql/mysql-server@4788470#diff-5a38ed61b700819a507d12aa9c204d8c5b0d99a894217e1e2bd5ce5ac7af25cb

[methane]

Nice. That is what issue report must have from first. Now I know and be able to reproduce your environment.

In your environment, MariaDB 10.11.4 is used.

root@6a1f7e3c4dd4:/usr/local/lib/python3.11/site-packages/MySQLdb# ldd _mysql.cpython-311-x86_64-linux-gnu.so
	linux-vdso.so.1 (0x00007ffc576df000)
	libmariadb.so.3 => /lib/x86_64-linux-gnu/libmariadb.so.3 (0x00007fba15476000)
...

root@6a1f7e3c4dd4:/usr/local/lib/python3.11/site-packages/MySQLdb# apt search mariadb
default-libmysqlclient-dev/now 1.1.0 amd64 [installed,local]
  MySQL database development files (metapackage)

libmariadb-dev/now 1:10.11.4-1~deb12u1 amd64 [installed,local]
  MariaDB database development files

libmariadb-dev-compat/now 1:10.11.4-1~deb12u1 amd64 [installed,local]
  MariaDB Connector/C, compatibility symlinks

[methane]

mysql_ssl_set() in MariaDB Connector/C also set MYSQL_OPT_SSL_ENFORCE.

https://github.com/mariadb-corporation/mariadb-connector-c/blob/8dffd56936df3d03eeccf47904773860a0cdeb57/libmariadb/mariadb_lib.c#L1323-L1324

  char enable= 1;
  return (mysql_optionsv(mysql, MYSQL_OPT_SSL_ENFORCE, &enable) |

It seems MariaDB developers inetntionally chose behavior different from MySQL for security reason.

• SSL is off by default. (vs "preferred" in MySQL)
• When SSL is enabled, use SSL (vs silent plaintext fallback in MySQL)

You can set MYSQL_OPT_SSL_ENFORCE by passing sslmode="REQUIRED".

#475

[methane]

I released 2.2.4 for better compatibility to mysqlclient==2.2.1 with libmariadb.

[Jonathan06891]

Thank you. The fix you made in 2.2.4 also resolves the issue I was having.

[mquillin]

thank you! closing.

[Jonathan06891]

I noticed something similar with my Django project. Version 2.2.2 and above seems to disable the SSL functionality which in turn prevents my application from connecting to the database. I'm using the below options in my database connection, and if I use Version 2.2.1 of mysqlclient and remove the "'ssl':True" setting, I get the same error as if I'm using Version 2.2.2 (and above) with "'ssl':True" included in the options.

'OPTIONS': {'charset': 'utf8mb4', 'init_command':"SET sql_mode='STRICT_TRANS_TABLES'; SET foreign_key_checks = 0;", 'ssl':True}

The bottom line of the error log being given is as follows (with some details edited for confidentiality):

django.db.utils.OperationalError: (1045, "Access denied for user 'xxx000000'@'00.00.0.00' (using password: YES)")

[methane]

More complete information is needed.
At least about what connector library do you linked to mysqlclient.

[Jonathan06891]

Sure thing. I'm using "import MySQLdb" at the top of my settings.py file. Then, the full connection string is as follows:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'CONN_MAX_AGE': 3600,
'NAME': os.environ['MYSQLNAME'],
'USER': os.environ['MYSQLUSER'],
'PASSWORD': os.environ['MYSQLPASSWORD'],
'HOST': os.environ['MYSQLHOST'],
'PORT': os.environ['MYSQLPORT'],
'OPTIONS': {
'charset': 'utf8mb4',
'init_command':"SET sql_mode='STRICT_TRANS_TABLES'; SET foreign_key_checks = 0;", 'ssl':True},
},
}

[methane]

You don't report what C library you linked.
This library is wrapper of C connector library.
And using ssl or not is C library behavior.
I can not say anything without C library information.

Anyway, ssl:True is wrong setting too.
SSL option must be dictionary containing ca, capath, etc...