Page 1 of 2 12 LastLast
Results 1 to 20 of 25

Thread: How to set x509 on a QSqlDatabase Connection?

  1. #1
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Question How to set x509 on a QSqlDatabase Connection?

    Hi,

    I have broken several braincells, trying to find out how QSqlDatabase establishes a TCP/IP connection with another computer/server.

    What I want to do is the following:
    I want to force the socket that QSqlDatabase is using to establish a TCP/IP connection with another computer to use openssl with a predefined client-key.pem, client-x509-cert.pem, and ca-cert.pem.

    I am using a MySQL v5.5 database hosted on a different computer e.g. 192.168.1.25. It is configured properly to accept (open)SSL connections.
    My QT4 application runs on, and is programmed for Linux (I am using Ubuntu 11.04).
    My application can establish a connection with the MySQL server without any problems - though without using SSL.

    I have read the MySQL C API and understand that prior to the db.open(), you need to set the mysql_ssl_set() (see also http://developer.qt.nokia.com/forums/viewthread/415)
    I have played around with the code but cannot force the connection to use my specific ca, keys and certificates.
    I have also read that specifying connection options like I do (db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_ SPACE=1") is limited to forcing the client to use SSL, but that there is no way to define the SSL specifics?

    I hope that anyone has an idea how I can get to the socket - called by QSqlDatabase, when it establishes a connection... or can tell me how to define the connection so that it uses the pems...
    Thank you in advance!

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    What exactly did you try regarding mysql_ssl_set()?
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  3. #3
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    wysota,

    I followed the example given for the 'sqlite3' driver (see link that I posted).

    I linked to the library (libmysql) in the .pro file, and I added the QSqldriver/QMySqlDriver
    I declared a QVariant obj and can read the driver handle with qdebug (which returns "MYSQL*").

    I tried to play with the code below, after looking into mysql.h but MYSQL stays undeclared whatever I do...?!?
    In mysql.h the MYSQL refers to a connection??

    This code below allows me to connect to MySQL - but without SSL.
    Qt Code:
    1. bool p2Sql::createConnection()
    2. {
    3. QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    4. db.setHostName(sMySqlIpAddress);
    5. db.setDatabaseName(sMySqlDbName);
    6. db.setUserName(sMySqlAdminName);
    7. db.setPassword(sMySqlPwd);
    8. db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
    9.  
    10. if (!db.open())
    11. {
    12. QMessageBox::critical(0, qApp->tr("Cannot open database"),
    13. qApp->tr("Unable to establish a database connection.\n\n"
    14. "Click Cancel to exit."), QMessageBox::Cancel);
    15. return false;
    16. }
    17. return true;
    18. }
    To copy to clipboard, switch view to plain text mode 

    I tried the code (below) as I mentioned, I can't get it to work...

    Qt Code:
    1. QVariant v = db.driver()->handle();
    2. qdebug() << v.typename(); // returns "MYSQL*
    3.  
    4. MYSQL *handle = static_cast<MYSQL *>(v.data()); //what is MYSQL?, or what is the pointer to handle?
    5. if (handle != NULL) // handle stays undeclared even after including QSqlDriver or QMySqlDriver (which errors on the mysql.h - not found)
    6. {
    7. mysql_ssl_set(handle, p2pro-application-key, p2pro-application-cert, p2pro-ca-cert, NULL, NULL); // QStrings p2pro-* are set in functions in my p2Sql class.
    8. }
    To copy to clipboard, switch view to plain text mode 

    I assume that somehow I can add specifc arguments to QSqlDatabase as mentioned in the MySQL C API - needless to mention is not documented in Qt Documentation.
    Thanks for any hints or help...

  4. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    Did you #include <mysql.h> ? Probably not. You need to do that, it's part of mysqlclient development package. And you need to explicitly link to mysqlclient.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  5. #5
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    wysota,

    I erroneously included libmysqlclient.a. I changed that to be libmysqlclient.so.16.0.0, and I did indeed not include mysql.h from the include directory of the c-api. So the program compiles, but still no SSL.
    I checked that using the code below:
    const char *cipher_name;
    Qt Code:
    1. MYSQL *handle = static_cast<MYSQL *>(v.data());
    2. cipher_name = mysql_get_ssl_cipher(handle);
    3. qDebug() << "The cipher name is " << cipher_name;
    To copy to clipboard, switch view to plain text mode 

    I will have to re-visit the GRANT definition for the account and see if REQUIRE X509 is set..

  6. #6
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    First check if your mysql client library is compiled with SSL support, then check if the server advertises SSL support and finally show your code that sets the key, certificate and ca data.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  7. #7
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    The client library was compiled with ssl support from the get-go, also the server advertises SSL support. I can only establish an SSL connection from the terminal, using either GRANT... REQUIRE x509, or GRANT... REQUIRE SSL. The terminal command: SHOW STATUS LIKE 'Ssl_cipher' replies with the cipher used.

    When I attempt to establish a connection through my application, and using SSL, the program reports no SSL connection and no cipher used. The connection is refused. The MySQL Log file is not very clear about the error, it says connection rejected without further specifying a reason.
    Do I have to continue to use the C API mysql_real_connect(...) or can I use QSqlDatabase with a command like db.open()?
    I assumed that the connection was established by calling the QSqlDatabase object..

  8. #8
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,456
    Thanked 271 Times in 266 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to set x509 on a QSqlDatabase Connection?

    Did You read about QSqlDatabase::setConnectOptions method ?

  9. #9
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    You can use Qt API once you properly initialize the keys with mysql_ssl_set.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  10. #10
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    @wysota, that's what I thought.
    I might be experiencing problems with how the .pem files are presented in QT. I used an absolute path for key, cert, and ca-cert in mysql_ssl_set(handle, "path to key", "path to cert", "path to ca-cert", NULL, NULL);

    I also tried adding the keys to QtResources and called them with ":/path/...key.pem or ...cert.pem or ca...pem". I also tried adding the cipher. MySQL still keeps refusing the connection when demanding SLL/x509.

    Qt Code:
    1. QVariant v = db.driver()->handle();
    2. MYSQL *handle = static_cast<MYSQL *>(v.data());
    3. if (handle != NULL) // handle = mysql*
    4. {
    5. mysql_ssl_set(handle, ".ssl/p2pro-application-key.pem", ".sll/p2pro-application-cert.pem", ".ssl/p2pro-ca-cert.pem", NULL, "DHE-RSA-AES256-SHA");
    6. }
    To copy to clipboard, switch view to plain text mode 

    I might need to try the mysql_real_connect() to see if the problem lies with Qt?!? Any other suggestions are welcome. Thx so far for your help.

    @Lesiok:
    See my code belowm especially this line:
    Qt Code:
    1. db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
    To copy to clipboard, switch view to plain text mode 

  11. #11
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    I don't think mysql_ssl_set() expects paths. I would assume that it expects the data itself.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  12. #12
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    @wysota
    I hope not MySQL reference on C API says:

    20.9.3.67. mysql_ssl_set()
    Qt Code:
    1. my_bool mysql_ssl_set(MYSQL *mysql, const char *key, const char *cert, const char *ca, const char *capath, const char *cipher)
    To copy to clipboard, switch view to plain text mode 

    Description

    mysql_ssl_set() is used for establishing secure connections using SSL. It must be called before mysql_real_connect().
    mysql_ssl_set() does nothing unless SSL support is enabled in the client library.
    mysql is the connection handler returned from mysql_init(). The other parameters are specified as follows:

    key is the path name to the key file.
    cert is the path name to the certificate file.
    ca is the path name to the certificate authority file.
    capath is the path name to a directory that contains trusted SSL CA certificates in pem format.
    cipher is a list of permissible ciphers to use for SSL encryption.

    Any unused SSL parameters may be given as NULL.

    Return Values
    This function always returns 0. If SSL setup is incorrect, mysql_real_connect() returns an error when you attempt to connect.

  13. #13
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    Maybe the problem is that the server doesn't trust the client's certificate? Check whether the client even tries to connect to the server using SSL (use a network sniffer or check in server logs or using a firewall). And use absolute paths instead of relative ones as in your code above.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  14. #14
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    @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:
    Qt Code:
    1. 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..

  15. #15
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    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.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  16. #16
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

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

    Qt Code:
    1. #include <QtCore/QCoreApplication>
    2. #include <cstdio>
    3. #include "mysql.h"
    4.  
    5. int main(int argc, char *argv[])
    6. {
    7. QCoreApplication a(argc, argv);
    8. MYSQL mysql;
    9. if (mysql_init(&mysql)==NULL)
    10. {
    11. printf("Failed to initate MySQL connection");
    12. }
    13. 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");
    14. if (!mysql_real_connect( &mysql, "192.xxx.xxx.xxx", "test", "xxxxxxxx", "mysql", 3306, NULL, 0) )
    15. {
    16. printf("Failed to connect to MySQL: Error: %s", mysql_error(&mysql) );
    17. }
    18.  
    19. printf("Logged on to database sucessfully");
    20. mysql_close(&mysql);
    21.  
    22. return a.exec();
    23. }
    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..

  17. #17
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    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.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  18. #18
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    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?

  19. #19
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: How to set x509 on a QSqlDatabase Connection?

    Quote Originally Posted by m3rlin View Post
    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())
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  20. #20
    Join Date
    Dec 2011
    Posts
    36
    Thanks
    14
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: How to set x509 on a QSqlDatabase Connection?

    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:
    Qt Code:
    1. 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:
    Qt Code:
    1. mysql_ssl_set(handle, NULL, NULL, NULL, NULL, NULL);
    To copy to clipboard, switch view to plain text mode 
    and
    Qt Code:
    1. 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?!?

Similar Threads

  1. Replies: 0
    Last Post: 18th September 2011, 08:58
  2. QSqlDatabase Connection Close on Destruction
    By Sanuden in forum Qt Programming
    Replies: 1
    Last Post: 1st September 2011, 15:32
  3. QSqlDatabase connection timeout?
    By joseprl89 in forum Qt Programming
    Replies: 6
    Last Post: 27th March 2011, 01:43
  4. QSqlDatabase PSQL connection options
    By leknarf in forum Qt Programming
    Replies: 0
    Last Post: 17th March 2010, 16:06
  5. Replies: 3
    Last Post: 22nd June 2006, 16:27

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.