Putting DBI to Work

Writing Clients That Include SSL Support

MySQL includes SSL support as of version 4.0, and you can use it to write your own programs that access the server over secure connections. To show how this is done, this section describes the process of modifying stmt_exec to produce a similar client named stmt_exec_ssl that outwardly is much the same but allows encrypted connections to be established. For stmt_exec_ssl 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 "Setting Up Secure Connections," in Chapter 12, "MySQL and Security."

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

1.	Copy stmt_exec.c to stmt_exec_ssl.c. The remaining steps apply to stmt_exec_ssl.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 You need not include my_config.h explicitly because it is included by my_global.h, and stmt_exec_ssl.c already includes the latter file.
3.	Modify the my_opts array that contains option information structures so that it includes entries for the standard SSL-related options (--ssl-ca, --ssl-key, and so forth). 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. It contents look like this (reformatted slightly): #ifdef HAVE_OPENSSL {"ssl", OPT_SSL_SSL, "Enable SSL for connection (automatically enabled with other flags). Disable with --skip-ssl.", (gptr*) &opt_use_ssl, (gptr*) &opt_use_ssl, 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, (gptr*) &opt_ssl_key, 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, (gptr*) &opt_ssl_cert, 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, (gptr*) &opt_ssl_ca, 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, (gptr*) &opt_ssl_capath, 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, (gptr*) &opt_ssl_cipher, 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 forth. 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_client { 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 that 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 near the end that includes the sslopt-case.h file: static 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; 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'; ask_password = 0; } break; #include <sslopt-case.h> } return (0); } sslopt-case.h includes additional 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);     } 

This code doesn'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().

Compile stmt_exec_ssl.c to produce the stmt_exec_ssl program and then run it. Assuming that the mysql_real_connect() call succeeds, you can proceed to issue statements. If you invoke stmt_exec_ssl with the appropriate SSL options, communication with the server should occur over an encrypted connection. To determine whether that is so, issue the following statement:

 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 stmt_exec_ssl included in the sampdb distribution actually issues the statement for you and reports the result.)