How to connect to a MySQL database using SSL ?

[qbyte]

Hi,

I need to connect to a MySQL database using SSL. I have no problem doing that using the command-line mysql client (under linux), as long as I provide the "--ssl-ca", "--ssl-cert" and "--ssl-key" parameters (so I know that my server-side is correctly configured). Unfortunately, I can't find how to do it with Qt5 (5.2.1). There doesn't seem to be an API in QSqlDatabase to specify those parameters. I tried the following code, but the connection still fails :
@
db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
QVariant v = db.driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "MYSQL*")==0)
{
MYSQL *handle = static_cast<MYSQL *>(v.data());
if (handle != NULL)
{
mysql_ssl_set(handle, "/home/.../client-key.pem", "/home/.../client-cert.pem", "/home/.../cacert.pem", NULL, NULL);
}
}
@
(BTW, I had to add "LIBS+=-lmysqlclient" in my .pro to get the symbol "mysql_ssl_set")

Anything wrong there ? What can I do to make it work (under Linux & Windows) ?

Thank you,

PS: I know that there is another ticket on this forum called "[SOLVED] Need a HowTo for setting SSL certificates for a MySQL Connection - QT5"; unfortunately, the "solution" is useless as it's an expired link.

[qbyte]

Hi,

Some more info : from the same Linux machine, I am able to connect to my MySQL/SSL database using

• mysql CLI
• PHP
• C-code using libmysqlclient (mysql_init(NULL) + mysql_ssl_set(...) + mysql_options(...) + mysql_real_connect(...) )

I'm running out of ideas... Next one is to find the source code of my libmysqlclient, recompile it with with printf()'s and see what the other ones do that Qt doesn't to make it work.

Thank you.

[qbyte]

Hi,

I downloaded the source code of Qt5.3.2 and noticed that mysql_init() and mysql_real_connec() were called right after each other in the same method. Trying to call mysql_ssl_set() from my application didn't make a chance (because it has to be called between the two other ones). So, I patched the QMYSQL driver with an extension of the options ... AND IT WORKS !

Unfortunately, there is no possibility to add an attachment in this forum. So, I put the patch here:
http://www.kuboku.com/Qt/qt5.3.1_qmysql_ssl.patch

Usage example:
@
db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1;SSL_key=client-key.pem;SSL_cert=client-cert.pem;SSL_ca=cacert.pem");
@

Qt-guys, IMHO, MySQL over SSL is an important feature and should be supported in one of the next releases.

[SGaist]

Hi and welcome to devnet,

If you would like to see it in the next release, you could submit your patch for inclusion in Qt.

[qbyte]

Thanks for your advice, SGaist.
I struggled a bit with git/gerrit, but I finally got it submitted :
https://codereview.qt-project.org/#/c/96849/
Keeping my fingers crossed.

[SGaist]

Gerrit can indeed be a bit difficult to get used to at first but.

Don't worry, you'll make it ;)

[qbyte]

Some additional (good) news : I got a Windows PC from work for the week-end, managed to compile Qt 5.3.2 (with the patch) on it, and tested it.

Test result: MySQL/SSL connection OK (Qt 5.3.2 + patch + MSVC12 32 bits).

[SGaist]

Nice !

Did you also check the cases where not all parameters are given ?

[qbyte]

Working configurations :

• given : SSL_KEY, SSL_CERT
• given : SSL_KEY, SSL_CERT, SSL_CA
• given : SSL_KEY, SSL_CERT, SSL_CA, SSL_CAPATH
• given : SSL_KEY, SSL_CERT, SSL_CA, SSL_CAPATH, SSL_CIPHER
• given : SSL_KEY, SSL_CERT, SSL_CA, SSL_CIPHER
• given : SSL_KEY, SSL_CERT, SSL_CIPHER

These are the only possibilities I can test with the server I use (but the coverage is already pretty good).
When any given value is wrong, the connection fails (which is fine).
Giving a SSL_CAPATH without SSL_CA does not work, but I get the same result when I use the libmysqlclient from a c program. I probably just misunderstand how to use it.

According to the documentation of mysql, "CLIENT_SSL=1" should not be set (it will be set automatically by mysql_ssl_set() (http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html)
Removing it from the driver would cause a warning message for anyone using it, so I'm not sure what to do.

[SGaist]

You should add that in the comments of the code review for the other reviewers to know