How to set x509 on a QSqlDatabase Connection?

[m3rlin]

@wysota,

That would be a realistic option if I couldn't login with a terminal. Using the following command works fine however with the same certificates:

mysql --ssl-ca ca-cert.pem --ssl-cert p2pro-application-cert.pem --ssl-key p2pro-application-key.pem -u test -h 192.xxx.xxx.xxx -p

mysql --ssl-ca ca-cert.pem --ssl-cert p2pro-application-cert.pem --ssl-key p2pro-application-key.pem -u test -h 192.xxx.xxx.xxx -p

To copy to clipboard, switch view to plain text mode 

I am checking right now if the C-API mysql_real_connect() works with the same certs. If so then the problem lies with Qt. If that also doesn't work - then I will check the MySql C++ connector API. Maybe there is something else that I am overseeing...
Again thx. for your patience, help, and suggestions..

[wysota]

Qt doesn't do anything special, it just calls mysql_real_connect() You should really check whether your app tries to connect over ssl and fails or if it totally ignores ssl.

[m3rlin]

Here is what I tried and this DOES work with the same certificates:

#include <QtCore/QCoreApplication>
#include <cstdio>
#include "mysql.h"
 
int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    MYSQL mysql;
    if (mysql_init(&mysql)==NULL)
          {
                  printf("Failed to initate MySQL connection");
          }
    mysql_ssl_set(&mysql, "../../p2pro/src/newcerts/p2pro-application-key.pem", "../../p2pro/src/newcerts/p2pro-application-cert.pem", "../../p2pro/src/newcerts/ca-cert.pem", NULL, "DHE-RSA-AES256-SHA");
    if (!mysql_real_connect( &mysql, "192.xxx.xxx.xxx", "test", "xxxxxxxx", "mysql", 3306, NULL, 0) )
    {
        printf("Failed to connect to MySQL: Error: %s", mysql_error(&mysql) );
    }
 
      printf("Logged on to database sucessfully");
      mysql_close(&mysql);
 
    return a.exec();
}

#include <QtCore/QCoreApplication>
#include <cstdio>
#include "mysql.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    MYSQL mysql;
    if (mysql_init(&mysql)==NULL)
          {
                  printf("Failed to initate MySQL connection");
          }
    mysql_ssl_set(&mysql, "../../p2pro/src/newcerts/p2pro-application-key.pem", "../../p2pro/src/newcerts/p2pro-application-cert.pem", "../../p2pro/src/newcerts/ca-cert.pem", NULL, "DHE-RSA-AES256-SHA");
    if (!mysql_real_connect( &mysql, "192.xxx.xxx.xxx", "test", "xxxxxxxx", "mysql", 3306, NULL, 0) )
    {
        printf("Failed to connect to MySQL: Error: %s", mysql_error(&mysql) );
    }

      printf("Logged on to database sucessfully");
      mysql_close(&mysql);

    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

So in summary - I will try to use a reference (this is the only difference) - instead of mysql_ssl_set(handle.... I will use mysql_ssl_set(&handle
If that doesn't work... then I need to find out why in a gui application it doesn't work - but it does in a console app...
Oh djeee.. well tomorrow I will post my results..

[wysota]

Sorry to be a spoil sport but I think you example proves exactly nothing. You don't have any reference anywhere, you are dereferening an object, effectively turning it into a pointer, which is what you already have in your Qt app. I really advise you to take my hint and check if SSL is used or not and whether your paths are correct or not. Those are the only two points of failure here.

[m3rlin]

Indeed, the referencing doesn't matter. But I did prove the following:
1. The path to the certificates is correct.
2. The way they are used in mysql_ssl_set() is correct.
3. That the program is actually using an SSL connection:
a. because MySQL Server accepted the connection, while it was configured to ONLY accept user "test", if he could supply a valid x509.
b. because the SHOW STATUS LIKE 'Ssl_cipher' replies with the cipher used, and it ONLY does that when a SSL connection is established.
c. becuase WireShark reports SSL trafic on port 3306
4. That somehow setting the QSqlDriver or QMySQlDriver handle with my code doesn't work via mysql_ssl_set().

Whom should I contact for an explanation? Where can I find documentation about the underlying structures? Maybe anyone reading this has a similar problem and found a solution? Or is it a Qt shortcoming in arguments that can be specified for QSqlDriver?

[wysota]

1. The path to the certificates is correct.

Not necessarily but that's a minor problem.

4. That somehow setting the QSqlDriver or QMySQlDriver handle with my code doesn't work via mysql_ssl_set().

There is no such handle. There is the MYSQL structure which is a MySql handle which is exactly the same what you are using in your test program.

Whom should I contact for an explanation?

What kind of explanation?

Where can I find documentation about the underlying structures?

Qt's SQL drivers are documented in Qt's docs.

Or is it a Qt shortcoming in arguments that can be specified for QSqlDriver?

Yes, and you need to work around that by calling mysql_ssl_set at the right moment (like directly after calling addDatabase())

[m3rlin]

In my latest test, I now for sure know that QSqlDatabase doesn't adapt its connection to any mysql_ssl_set definitions.

I created another user test2.
I set for test2 user the following GRANT on the test database:

GRANT ALL PRIVILEGES ON test.* FOR 'test'@'192.xxx.xxx.xxx' IDENTIFIED BY 'xxxxxxxxx' REQUIRE SSL;

GRANT ALL PRIVILEGES ON test.* FOR 'test'@'192.xxx.xxx.xxx' IDENTIFIED BY 'xxxxxxxxx' REQUIRE SSL;

To copy to clipboard, switch view to plain text mode 

In my application I used:

mysql_ssl_set(handle, NULL, NULL, NULL, NULL, NULL);

mysql_ssl_set(handle, NULL, NULL, NULL, NULL, NULL);

To copy to clipboard, switch view to plain text mode 

and

db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1;CLIENT_COMPRESS");

db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1;CLIENT_COMPRESS");

To copy to clipboard, switch view to plain text mode 

User test2 can connect with SSL (checked by WireShark)
User test2 can also connect when CLIENT_SSL=0 but then no SSL is used (checked by WireShark)

If I however demand that test2 identifies himself by specific x509 (by REQUIRE SUBJECT or ISSUER or a combination of them) then the connection is refused by MySQL Server.
In summary, I must conclude that - QSqlDatabase doesn't allow you to specify WHICH certificate, issed by what Authority must be used. If this conclusion is true then using SSL is very limited with Qt?!?

[wysota]

Can we see complete code of yours used for establishing the connection using mysql_ssl_set?

Ok, I have analyzed Qt's code regarding connecting to MySql. With current implementation you won't be able to call mysql_ssl_set between mysql_init and mysql_real_connect. You should probably report it as a bug. mysql_init() should be called earlier (probably as a result to QSqlDatabase::addDatabase()) so that one can manipulate the structure before opening the connection.

[m3rlin]

Wow beyond expectation, I like you will-never-give-up mentality! Of course I will post my code. Be it tomorrow - having guests now...
Thanks so much!

[wysota]

Before I forget... What I think you should do is that you should either subclass the MySql driver Qt has and reimplement the method for opening the database (which is a bit of work) or you should copy the source code for the plugin, rename the classes and modify the open() routine so that you have the ability to call mysql_ssl_set between mysql_init and mysql_real_connect. Then you can build the new plugin --- either as a plugin (and then deploy it in the proper directory) or as part of your application (and then call QSqlDatabase::registerSqlDriver() to register it). Of course these are only workarounds, the real solution is to modify Qt's MySql driver directly.

[m3rlin]

I have dug deeper into plugins and "all" there is to do to make it work... seems to me like a nice project for Nokia, or for me in a somewhat quite week. You are right, QSqlDriver should be programmed for more flexibility. Is Nokia reading this?
Thanks for all the help.

[aleyer]

Hi!
I looked into the code of mysql driver and QSqlDatabase to make sure that mysql_real_connect() is executed only in the driver->open() function and not before.
So, I used this code to connect to the DB:

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    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, "client-key.pem",
                          "client-cert.pem", "ca-cert.pem",
                          NULL, "DHE-RSA-AES256-SHA");
        }
    }
 
    db.setHostName(settings.value("database/host").toString());
    db.setDatabaseName(settings.value("database/databaseName").toString());
    db.setUserName(settings.value("database/userName").toString());
    db.setPassword(crypto.decryptToString(settings.value("database/password").toString()));
    db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
    db.open();

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    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, "client-key.pem",
                          "client-cert.pem", "ca-cert.pem",
                          NULL, "DHE-RSA-AES256-SHA");
        }
    }
    
    db.setHostName(settings.value("database/host").toString());
    db.setDatabaseName(settings.value("database/databaseName").toString());
    db.setUserName(settings.value("database/userName").toString());
    db.setPassword(crypto.decryptToString(settings.value("database/password").toString()));
    db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
    db.open();

To copy to clipboard, switch view to plain text mode 

Well, it works somehow. First, I had problems with certs on the side of the server (at first, apparmor blocked the access to the cert files, then this problem occured. So, the last time I generated the new certs by tinyca, exported it to zip (key+cert), then removed the pass from the key, and only then it finally works.
The user is set up with REQUIRE SSL option, for some reason the server doesn't accept the user when REQUIRE X509 is selected. The strange thing - it connects even when i change all the parameters to NULL. If I call "mysql --ssl -u test -h 192.168.1.8 -p" it does not authorize.
Server runs on Ubuntu Server 11.10, client - on Mint 12, Qt version 4.7.4, MySQL - 5.1.58-1ubuntu1, OpenSSL 1.0.0e.