74.笔记 MySQL学习——C编写MySQL程序七支持SSL
MYSQL支持对SSL的支持，可以用它来编写自己的程序，让它通过安全连接来访问服务器。
将上节笔记中的exec_stmt改成ssl加密方式连接的。
Sslopt-longopts.h是一个公共的MySQL头文件，定义的选项结构引用了OPT_SSL_SSL,OPT_SSL_KEY等多个值。
在头文件my_config.h中定义了一个HAVE_OPENSSL的符号
修改选项信息结构的my_opts数组，让它包含一些标准的与SSL相关的选项。
引用sslopt-vars.h头文件
代码如下#include <my_global.h>
#include <my_sys.h>
#include <m_string.h> /* for strdup() */
#include <mysql.h>
#include <my_getopt.h>
/* #@ _OPTION_SSL_ENUM_ */
#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,
OPT_SSL_VERIFY_SERVER_CERT
};
#endif
/* #@ _OPTION_SSL_ENUM_ */
static char *opt_host_name = NULL; /* server host (default=localhost) */
static char *opt_user_name = NULL; /* username (default=login name) */
static char *opt_password = NULL; /* password (default=none) */
static unsigned int opt_port_num = 0; /* port number(use built-in value) */
static char *opt_socket_name = NULL; /* socket name (use built-in value) */
static char *opt_db_name = NULL; /* database name (default=none) */
static unsigned int opt_flags = 0; /* connection flags (none) */
#include <sslopt-vars.h>
static int ask_password = 0; /* whether to solicit password */
static MYSQL *conn; /* pointer to connectionhandler */
static const char *client_groups[] = {"client", NULL };
/* #@ _MY_OPTS_ */
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",
(uchar **)&opt_host_name, NULL, NULL,
GET_STR,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"password", 'p', "Password",
(uchar **)&opt_password, NULL, NULL,
GET_STR,OPT_ARG, 0, 0, 0, 0, 0, 0},
{"port", 'P', "Port number",
(uchar **)&opt_port_num, NULL, NULL,
GET_UINT,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"socket", 'S', "Socket path",
(uchar **)&opt_socket_name, NULL, NULL,
GET_STR,REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"user", 'u', "User name",
(uchar **)&opt_user_name, NULL, NULL,
GET_STR,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 }
};
/* #@ _MY_OPTS_ */
/*
* Printdiagnostic message. If conn is non-NULL, print error information
* returned byserver.
*/
static void
print_error (MYSQL *conn, char *message)
{
fprintf(stderr, "%s\n", message);
if (conn !=NULL)
{
fprintf(stderr, "Error %u (%s): %s\n",
mysql_errno (conn), mysql_sqlstate (conn), mysql_error (conn));
}
}
/* #@ _GET_ONE_OPTION_ */
static my_bool
get_one_option (int optid, const struct my_option*opt, char *argument)
{
switch(optid)
{
case '?':
my_print_help (my_opts); /* printhelp message */
exit (0);
case'p': /* password */
if(!argument) /* no value given;solicit it later */
ask_password= 1;
else /* copy password,overwrite 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);
}
/* #@ _GET_ONE_OPTION_ */
#include "process_result_set.c"
#include "process_statement.c"
int
main (int argc, char *argv[])
{
int opt_err;
MY_INIT(argv[0]);
load_defaults("my", client_groups, &argc, &argv);
if ((opt_err= handle_options (&argc, &argv, my_opts, get_one_option)))
exit(opt_err);
/* solicitpassword if necessary */
if(ask_password)
opt_password= get_tty_password (NULL);
/* getdatabase name if present on command line */
if (argc >0)
{
opt_db_name= argv[0];
--argc;++argv;
}
/* initializeclient library */
if(mysql_library_init (0, NULL, NULL))
{
print_error(NULL, "mysql_library_init() failed");
exit (1);
}
/* #@ _SET_UP_SSL_ */
/* initializeconnection handler */
conn =mysql_init (NULL);
if (conn ==NULL)
{
print_error(NULL, "mysql_init() failed (probably out of memory)");
exit (1);
}
#ifdef HAVE_OPENSSL
/* pass SSLinformation 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);
mysql_options(conn,MYSQL_OPT_SSL_VERIFY_SERVER_CERT,
(char*)&opt_ssl_verify_server_cert);
#endif
/* connect toserver */
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);
}
/* #@ _SET_UP_SSL_ */
printf("Testing connection...\n");
process_statement (conn, "SHOW STATUS LIKE 'Ssl_cipher'");
printf("If Ssl_cipher is non-blank, the connection is secure.\n");
while (1)
{
char buf[10000];
fprintf(stderr, "query> "); /* print prompt */
if (fgets(buf, sizeof (buf), stdin) == NULL) /* read statement */
break;
if (strcmp(buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0)
break;
process_statement (conn, buf); /* execute it */
}
/* disconnectfrom server, terminate client library */
mysql_close(conn);
mysql_library_end ();
exit (0);
}
代码解释和exec_stmt.c基本一致，加入了ssl相关的部分。
加入了sslopt-longopts.h，sslopt-vars.h
测试执行先查看数据库是否支持SSL
> show variables like 'have_ssl'
如果没有启用，则启用如下：
mysqld --user=mysql –ssl
> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
