Writing Clients That Include SSL Support

MySQL 4 includes SSL support, which you can use in your own programs to access the server over secure connections. To show how this is done, this section describes the process of modifying client4 to produce a similar client named sslclient that outwardly is much the same but allows encrypted connections to be established. For sslclient to work properly, MySQL must have been built with SSL support, and the server must be started with the proper options that identify its certificate and key files. You'll also need certificate and key files on the client end. For more information, see the "Setting Up Secure Connections" section in Chapter 12, "Security." In addition, you should use MySQL 4.0.5 or later. The SSL and option-handling routines for earlier 4.0.x releases will not behave quite as described here.

The sampdb distribution contains a source file, sslclient.c, from which the client program sslclient can be built. The following procedure describes how sslclient.c is created, beginning with client4.c:

  1. Copy client4.c to sslclient.c. The remaining steps apply to sslclient.c.

  2. To allow the compiler to detect whether SSL support is available, the MySQL header file my_config.h defines the symbol HAVE_OPENSSL appropriately. This means that when writing SSL-related code, you use the following construct so that the code will be ignored if SSL cannot be used:

    #ifdef HAVE_OPENSSL 
        ...SSL-related code here...
    #endif
    

    my_config.h is included by my_global.h.sslclient.c already includes the latter file, so you need not include my_config.h explicitly.

  3. Modify the my_opts array that contains option information structures to include entries for the standard SSL-related options as well (--ssl-ca, --ssl-key, and so on). The easiest way to do this is to include the contents of the sslopt-longopts.h file into the my_opts array with an #include directive. After making the change, my_opts looks like this:

    static struct my_option my_opts[] =     /* option information structures */ 
    {
        {"help", '?', "Display this help and exit",
        NULL, NULL, NULL,
        GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
        {"host", 'h', "Host to connect to",
        (gptr *) &opt_host_name, NULL, NULL,
        GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"password", 'p', "Password",
        (gptr *) &opt_password, NULL, NULL,
        GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0},
        {"port", 'P', "Port number",
        (gptr *) &opt_port_num, NULL, NULL,
        GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"socket", 'S', "Socket path",
        (gptr *) &opt_socket_name, NULL, NULL,
        GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"user", 'u', "User name",
        (gptr *) &opt_user_name, NULL, NULL,
        GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    
    #include <sslopt-longopts.h>
        { NULL, 0, NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 }
    };
    

    sslopt-longopts.h is a public MySQL header file. Its contents look like this (reformatted slightly):

    #ifdef HAVE_OPENSSL 
        {"ssl", OPT_SSL_SSL,
        "Enable SSL for connection. Disable with --skip-ssl",
        (gptr*) &opt_use_ssl, NULL, 0,
        GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
        {"ssl-key", OPT_SSL_KEY, "X509 key in PEM format (implies --ssl)",
        (gptr*) &opt_ssl_key, NULL, 0,
        GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"ssl-cert", OPT_SSL_CERT, "X509 cert in PEM format (implies --ssl)",
        (gptr*) &opt_ssl_cert, NULL, 0,
        GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"ssl-ca", OPT_SSL_CA,
        "CA file in PEM format (check OpenSSL docs, implies --ssl)",
        (gptr*) &opt_ssl_ca, NULL, 0,
        GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"ssl-capath", OPT_SSL_CAPATH,
        "CA directory (check OpenSSL docs, implies --ssl)",
        (gptr*) &opt_ssl_capath, NULL, 0,
        GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
        {"ssl-cipher", OPT_SSL_CIPHER, "SSL cipher to use (implies --ssl)",
        (gptr*) &opt_ssl_cipher, NULL, 0,
        GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    #endif /* HAVE_OPENSSL */
    
  4. The option structures defined by sslopt-longopts.h refer to the values OPT_SSL_SSL, OPT_SSL_KEY, and so on. These are used for the short option codes and must be defined by your program, which can be done by adding the following lines preceding the definition of the my_opts array:

    #ifdef HAVE_OPENSSL 
    enum options
    {
        OPT_SSL_SSL=256,
        OPT_SSL_KEY,
        OPT_SSL_CERT,
        OPT_SSL_CA,
        OPT_SSL_CAPATH,
        OPT_SSL_CIPHER
    };
    #endif
    

    When writing your own applications, if a given program also defines codes for other options, make sure these OPT_SSL_XXX symbols have different values than those codes.

  5. The SSL-related option structures in sslopt-longopts.h refer to a set of variables that are used to hold the option values. To declare these, use an #include directive to include the contents of the sslopt-vars.h file into your program preceding the definition of the my_opts array. sslopt-vars.h looks like this:

    #ifdef HAVE_OPENSSL 
    static my_bool opt_use_ssl  = 0;
    static char *opt_ssl_key    = 0;
    static char *opt_ssl_cert   = 0;
    static char *opt_ssl_ca     = 0;
    static char *opt_ssl_capath = 0;
    static char *opt_ssl_cipher = 0;
    #endif
    
  6. In the get_one_option() routine, add a line that includes the sslopt-case.h file:

    my_bool 
    get_one_option (int optid, const struct my_option *opt, char *argument)
    {
        switch (optid)
        {
        case '?':
            my_print_help (my_opts);    /* print help message */
            exit (0);
        case 'p':                       /* password */
            if (!argument)              /* no value given, so solicit it later */
                ask_password = 1;
            else                        /* copy password, wipe out original */
            {
                opt_password = strdup (argument);
                if (opt_password == NULL)
                {
                    print_error (NULL, "could not allocate password buffer");
                    exit (1);
                }
                while (*argument)
                    *argument++ = 'x';
            }
            break;
    #include <sslopt-case.h>
        }
        return (0);
    }
    

    sslopt-case.h includes cases for the switch() statement that detect when any of the SSL options were given and sets the opt_use_ssl variable if so. It looks like this:

    #ifdef HAVE_OPENSSL 
        case OPT_SSL_KEY:
        case OPT_SSL_CERT:
        case OPT_SSL_CA:
        case OPT_SSL_CAPATH:
        case OPT_SSL_CIPHER:
        /*
          Enable use of SSL if we are using any ssl option
          One can disable SSL later by using --skip-ssl or --ssl=0
        */
          opt_use_ssl= 1;
          break;
    #endif
    

    The effect of this is that after option processing has been done, it is possible to determine whether the user wants a secure connection by checking the value of opt_use_ssl.

If you follow the preceding procedure, the usual load_defaults() and handle_options() routines will take care of parsing the SSL-related options and setting their values for you automatically. The only other thing you need to do is pass SSL option information to the client library before connecting to the server if the options indicate that the user wants an SSL connection. Do this by invoking mysql_ssl_set() after calling mysql_init() and before calling mysql_real_connect(). The sequence looks like this:

    /* initialize connection handler */ 
    conn = mysql_init (NULL);
    if (conn == NULL)
    {
        print_error (NULL, "mysql_init() failed (probably out of memory)");
        exit (1);
    }

#ifdef HAVE_OPENSSL
    /* pass SSL information to client library */
    if (opt_use_ssl)
        mysql_ssl_set (conn, opt_ssl_key, opt_ssl_cert, opt_ssl_ca,
                        opt_ssl_capath, opt_ssl_cipher);
#endif

    /* connect to server */
    if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
            opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)
    {
        print_error (conn, "mysql_real_connect() failed");
        mysql_close (conn);
        exit (1);
    }

Note that you don't test mysql_ssl_set() to see if it returns an error. Any problems with the information you supply to that function will result in an error when you call mysql_real_connect().

Produce sslclient by compiling sslclient.c and then run it. Assuming that the mysql_real_connect() call succeeds, you can proceed to issue queries. If you invoke sslclient with the appropriate SSL options, communication with the server should occur over an encrypted connection. To determine whether or not that is so, issue the following query:

SHOW STATUS LIKE 'Ssl_cipher' 

The value of Ssl_cipher will be non-blank if an encryption cipher is in use. (To make this easier, the version of sslclient included in the sampdb distribution actually issues the query for you and reports the result.)