How to connect to mysql server with SSL from a flask app

Question

I want to connect to a mysql server via flask and

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://your-username:your-password@localhost/schema'

How can I add ca-cert, client-key and client-cert to the connection?

Answer 1

You can add theses informations in your URI like this :

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://your-username:your-password@localhost/schema?ssl_key=MyCertFolder/client-key.pem&ssl_cert=MyCertFolder/client-cert.pem'

Or using the SQLAlchemy create_engine, take a look to this post

Answer 2

In case you need to use a Cloud-based MySQL service, this configuration will be slightly different. For example,

In Azure, it will be,

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://your-username:your-password@localhost/your-schema?ssl_ca=BaltimoreCyberTrustRoot.crt.pem'

wherein, the file BaltimoreCyberTrustRoot.crt.pem can be downloaded from here.

Similarly, the solution for AWS is discussed here.

Answer 3

Another solution is to use sqlalchemy.engine.url.URL to define the URL.

sqlUrl = sqlalchemy.engine.url.URL(
    drivername="mysql+pymysql",
    username=db_user,
    password=db_pass,
    host=db_host,
    port=3306,
    database=db_name,
    query={"ssl_ca": "main_app/certs/BaltimoreCyberTrustRoot.crt.pem"},
)
create_engine(sqlUrl)

You can include SSL parameters as a dictionary in the query argument.

This approach is useful if you are using Flask to initialize the SqlAlchemy engine with a config parameter like SQLALCHEMY_DATABASE_URI rather than directly using create_engine.