74.笔记 MySQL学习——C编写MySQL程序七支持SSL

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)