[odb-users] ssl support for mysql

Boris Kolpackov boris at codesynthesis.com
Fri Jan 13 04:31:25 EST 2012


Hi Ettore,

Caprella Ettore Elio <ettoreelio.caprella at telecomitalia.it> writes:

> I find odb perfect for my goal but I need the ssl support for mysql
> in order to use it and I think it lacks, is it correct?
> I read the code and it seems easy to add it (thanks to the good design).
> Are you planning to introduce this feature?

ODB does not support setting up SSL connections for MySQL directly but
it allows you to add this support quite easily. If you look at the
connection class in Section 13.3, "MySQL Connection and Connection
Factory" in the ODB Manual, you will see that it has a constructor
which allows you to pass an already connected MYSQL connection handle
(MYSQL*). This generic feature allows for all kinds of customization
of the connections to the databases (note that this mechanism is also
available for other databases).

As an example, let's say we want to create an ssl_connection_pool_factory
class which will implement a pool of encrypted MySQL connections. Here
is how we can do this:

#include <new>   // std::bad_alloc
#include <string>

#include <odb/mysql/database.hxx>
#include <odb/mysql/exceptions.hxx>
#include <odb/mysql/connection-factory.hxx>

class ssl_connection_pool_factory: public odb::mysql::connection_pool_factory
{
  ssl_connection_pool_factory (const std::string& key_file,
                               const std::string& cert_file,
                               const std::string& cacert_file,
                               std::size_t max_connections = 0,
      			       std::size_t min_connections = 0,
                               bool ping = true)
      : odb::mysql::connection_pool_factory (max_connections,
                                             min_connections,
                                             ping),
        key_file_ (key_file),
        cert_file_ (cert_file),
        cacert_file_ (cacert_file)
  {
  }

  virtual pooled_connection_ptr
  create ()
  {
    MYSQL* h (mysql_init (0));

    if (h == 0)
      throw std::bad_alloc ();

    // Set character set.
    //
    if (*db_->charset () != '\0')
      mysql_options (h, MYSQL_SET_CHARSET_NAME, db_->charset ());

    // Set SSL parameters.
    //
    mysql_ssl_set (h,
                   key_file_.c_str (),
                   cert_file_.c_str (),
                   cacert_file_.c_str (),
                   0,
                   0);

    // Connect.
    //
    if (mysql_real_connect (h,
                            db_->host (),
                            db_->user (),
                            db_->password (),
                            db_->db (),
                            db_->port (),
                            db_->socket (),
                            db_->client_flags () | CLIENT_FOUND_ROWS) == 0)
    {
      unsigned int e (mysql_errno (h));
      std::string sqlstate (mysql_sqlstate (h));
      std::string message (mysql_error (h));

      mysql_close (h);

      if (e == CR_OUT_OF_MEMORY)
        throw std::bad_alloc ();
      else
        throw odb::mysql::database_exception (e, sqlstate, message);
    }

    return pooled_connection_ptr (
      new (odb::details::shared) pooled_connection (*db_, h));
  }

private:
  std::string key_file_;
  std::string cert_file_;
  std::string cacert_file_;
};

Then you will need to pass an instance of ssl_connection_pool_factory
to the odb::mysql::database constructor (Section 13.3 shows how to do
this) and you are all set.

As you can see above, we pass SSL parameters (key, cert, etc) to the
pool_factory. You can go a step further and derive your own class
(e.g., ssl_database) from odb::mysql::database and add these parameters
to it, so that ssl_connection_pool_factory gets them from the database,
just like the standard ones. You can also instantiate and pass 
ssl_connection_pool_factory by default if no factory was specified.
This way your ssl_database class will "look & feel" just like 
odb::mysql::database, for example:

ssl_database db ("key.pem",
                 "cert.pem",
                 "ca.pem",
                 "john",
                 "secret",
                 "mydb",
                 "database.example.com");

Boris



More information about the odb-users mailing list