src/modules/rlm_sql/drivers/rlm_sql_mysql/rlm_sql_mysql.c

   1 /*
   2  * sql_mysql.c          SQL Module
   3  *
   4  * Version:     $Id$
   5  *
   6  *   This program is free software; you can redistribute it and/or modify
   7  *   it under the terms of the GNU General Public License as published by
   8  *   the Free Software Foundation; either version 2 of the License, or
   9  *   (at your option) any later version.
  10  *
  11  *   This program is distributed in the hope that it will be useful,
  12  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  *   GNU General Public License for more details.
  15  *
  16  *   You should have received a copy of the GNU General Public License
  17  *   along with this program; if not, write to the Free Software
  18  *   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  19  *
  20  * Copyright 2000-2007  The FreeRADIUS server project
  21  * Copyright 2000  Mike Machado <mike@innercite.com>
  22  * Copyright 2000  Alan DeKok <aland@ox.org>
  23  */
  24
  25 #include <freeradius-devel/ident.h>
  26 RCSID("$Id$")
  27
  28 #include <freeradius-devel/radiusd.h>
  29
  30 #include <sys/stat.h>
  31
  32 #include "config.h"
  33
  34 #ifdef HAVE_MYSQL_MYSQL_H
  35 #include <mysql/mysql_version.h>
  36 #include <mysql/errmsg.h>
  37 #include <mysql/mysql.h>
  38 #else
  39 #ifdef HAVE_MYSQL_H
  40 #include <mysql_version.h>
  41 #include <errmsg.h>
  42 #include <mysql.h>
  43 #endif
  44 #endif
  45
  46 #include        "rlm_sql.h"
  47
  48 typedef struct rlm_sql_mysql_sock {
  49         MYSQL conn;
  50         MYSQL *sock;
  51         MYSQL_RES *result;
  52         rlm_sql_row_t row;
  53 } rlm_sql_mysql_sock;
  54
  55 /* Prototypes */
  56 static int sql_free_result(rlm_sql_handle_t*, rlm_sql_config_t*);
  57
  58 /*************************************************************************
  59  *
  60  *      Function: sql_create_socket
  61  *
  62  *      Purpose: Establish connection to the db
  63  *
  64  *************************************************************************/
  65 static int sql_init_socket(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
  66 {
  67         rlm_sql_mysql_sock *mysql_sock;
  68         unsigned long sql_flags;
  69
  70         if (!handle->conn) {
  71                 handle->conn = (rlm_sql_mysql_sock *)rad_malloc(sizeof(rlm_sql_mysql_sock));
  72                 if (!handle->conn) {
  73                         return -1;
  74                 }
  75         }
  76         mysql_sock = handle->conn;
  77         memset(mysql_sock, 0, sizeof(*mysql_sock));
  78
  79         DEBUG("rlm_sql_mysql: Starting connect to MySQL server");
  80
  81         mysql_init(&(mysql_sock->conn));
  82         mysql_options(&(mysql_sock->conn), MYSQL_READ_DEFAULT_GROUP, "freeradius");
  83
  84 #if (MYSQL_VERSION_ID >= 50000)
  85         if (config->query_timeout) {
  86                 unsigned int timeout = config->query_timeout;
  87
  88                 /*
  89                  *      3 retries are hard-coded into the MySQL library.
  90                  *      We ensure that the REAL timeout is what the user
  91                  *      set by accounting for that.
  92                  */
  93                 if (timeout > 3) timeout /= 3;
  94
  95                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_CONNECT_TIMEOUT,
  96                               &timeout);
  97                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_READ_TIMEOUT,
  98                               &timeout);
  99                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_WRITE_TIMEOUT,
 100                               &timeout);
 101         }
 102 #endif
 103
 104 #if (MYSQL_VERSION_ID >= 40100)
 105         sql_flags = CLIENT_MULTI_RESULTS | CLIENT_FOUND_ROWS;
 106 #else
 107         sql_flags = CLIENT_FOUND_ROWS;
 108 #endif
 109
 110 #ifdef CLIENT_MULTI_STATEMENTS
 111         sql_flags |= CLIENT_MULTI_STATEMENTS;
 112 #endif
 113         if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
 114                                                     config->sql_server,
 115                                                     config->sql_login,
 116                                                     config->sql_password,
 117                                                     config->sql_db,
 118                                                     atoi(config->sql_port),
 119                                                     NULL,
 120                                                     sql_flags))) {
 121                 radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server %s@%s:%s", config->sql_login, config->sql_server, config->sql_db);
 122                 radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn));
 123                 mysql_sock->sock = NULL;
 124                 return -1;
 125         }
 126
 127
 128         return 0;
 129 }
 130
 131
 132 /*************************************************************************
 133  *
 134  *      Function: sql_destroy_socket
 135  *
 136  *      Purpose: Free socket and any private connection data
 137  *
 138  *************************************************************************/
 139 static int sql_destroy_socket(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 140 {
 141         free(handle->conn);
 142         handle->conn = NULL;
 143
 144         return 0;
 145 }
 146
 147
 148 /*************************************************************************
 149  *
 150  *      Function: sql_check_error
 151  *
 152  *      Purpose: check the error to see if the server is down
 153  *
 154  *************************************************************************/
 155 static int sql_check_error(int error)
 156 {
 157         switch(error) {
 158         case CR_SERVER_GONE_ERROR:
 159         case CR_SERVER_LOST:
 160         case -1:
 161                 radlog(L_DBG, "rlm_sql_mysql: MYSQL check_error: %d, returning SQL_DOWN", error);
 162                 return SQL_DOWN;
 163                 break;
 164         case 0:
 165                 return 0;
 166                 break;
 167         case CR_OUT_OF_MEMORY:
 168         case CR_COMMANDS_OUT_OF_SYNC:
 169         case CR_UNKNOWN_ERROR:
 170         default:
 171                 radlog(L_DBG, "rlm_sql_mysql: MYSQL check_error: %d received", error);
 172                 return -1;
 173                 break;
 174         }
 175 }
 176
 177
 178 /*************************************************************************
 179  *
 180  *      Function: sql_query
 181  *
 182  *      Purpose: Issue a query to the database
 183  *
 184  *************************************************************************/
 185 static int sql_query(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config,
 186                      char *querystr)
 187 {
 188         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 189
 190         if (mysql_sock->sock == NULL) {
 191                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 192                 return SQL_DOWN;
 193         }
 194
 195         mysql_query(mysql_sock->sock, querystr);
 196         return sql_check_error(mysql_errno(mysql_sock->sock));
 197 }
 198
 199
 200 /*************************************************************************
 201  *
 202  *      Function: sql_store_result
 203  *
 204  *      Purpose: database specific store_result function. Returns a result
 205  *               set for the query. In case of multiple results, get the
 206  *               first non-empty one.
 207  *
 208  *************************************************************************/
 209 static int sql_store_result(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 210 {
 211         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 212         int status;
 213
 214         if (mysql_sock->sock == NULL) {
 215                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 216                 return SQL_DOWN;
 217         }
 218 retry_store_result:
 219         if (!(mysql_sock->result = mysql_store_result(mysql_sock->sock))) {
 220                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 221                 if (status != 0) {
 222                         radlog(L_ERR, "rlm_sql_mysql: Cannot store result");
 223                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 224                                mysql_error(mysql_sock->sock));
 225                         return status;
 226                 }
 227 #if (MYSQL_VERSION_ID >= 40100)
 228                 status = mysql_next_result(mysql_sock->sock);
 229                 if (status == 0) {
 230                         /* there are more results */
 231                         goto retry_store_result;
 232                 } else if (status > 0) {
 233                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 234                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 235                                mysql_error(mysql_sock->sock));
 236                         return sql_check_error(status);
 237                 }
 238 #endif
 239         }
 240         return 0;
 241 }
 242
 243
 244 /*************************************************************************
 245  *
 246  *      Function: sql_num_fields
 247  *
 248  *      Purpose: database specific num_fields function. Returns number
 249  *               of columns from query
 250  *
 251  *************************************************************************/
 252 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 253 {
 254         int     num = 0;
 255         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 256
 257 #if MYSQL_VERSION_ID >= 32224
 258         if (!(num = mysql_field_count(mysql_sock->sock))) {
 259 #else
 260         if (!(num = mysql_num_fields(mysql_sock->sock))) {
 261 #endif
 262                 radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: No Fields");
 263                 radlog(L_ERR, "rlm_sql_mysql: MYSQL error: %s",
 264                        mysql_error(mysql_sock->sock));
 265         }
 266         return num;
 267 }
 268
 269
 270 /*************************************************************************
 271  *
 272  *      Function: sql_select_query
 273  *
 274  *      Purpose: Issue a select query to the database
 275  *
 276  *************************************************************************/
 277 static int sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config,
 278                             char *querystr)
 279 {
 280         int ret;
 281
 282         ret = sql_query(handle, config, querystr);
 283         if(ret)
 284                 return ret;
 285         ret = sql_store_result(handle, config);
 286         if (ret) {
 287                 return ret;
 288         }
 289
 290         /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
 291          * this cannot return an error.  Perhaps just to complain if no
 292          * fields are found?
 293          */
 294         sql_num_fields(handle, config);
 295
 296         return ret;
 297 }
 298
 299
 300 /*************************************************************************
 301  *
 302  *      Function: sql_num_rows
 303  *
 304  *      Purpose: database specific num_rows. Returns number of rows in
 305  *               query
 306  *
 307  *************************************************************************/
 308 static int sql_num_rows(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 309 {
 310         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 311
 312         if (mysql_sock->result)
 313                 return mysql_num_rows(mysql_sock->result);
 314
 315         return 0;
 316 }
 317
 318
 319 /*************************************************************************
 320  *
 321  *      Function: sql_fetch_row
 322  *
 323  *      Purpose: database specific fetch_row. Returns a rlm_sql_row_t struct
 324  *               with all the data for the query in 'handle->row'. Returns
 325  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 326  *
 327  *************************************************************************/
 328 static int sql_fetch_row(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 329 {
 330         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 331         int status;
 332
 333         /*
 334          *  Check pointer before de-referencing it.
 335          */
 336         if (!mysql_sock->result) {
 337                 return SQL_DOWN;
 338         }
 339
 340 retry_fetch_row:
 341         handle->row = mysql_fetch_row(mysql_sock->result);
 342
 343         if (handle->row == NULL) {
 344                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 345                 if (status != 0) {
 346                         radlog(L_ERR, "rlm_sql_mysql: Cannot fetch row");
 347                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 348                                mysql_error(mysql_sock->sock));
 349                         return status;
 350                 }
 351 #if (MYSQL_VERSION_ID >= 40100)
 352                 sql_free_result(handle, config);
 353                 status = mysql_next_result(mysql_sock->sock);
 354                 if (status == 0) {
 355                         /* there are more results */
 356                         if ((sql_store_result(handle, config) == 0)
 357                          && (mysql_sock->result != NULL))
 358                                 goto retry_fetch_row;
 359                 } else if (status > 0) {
 360                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 361                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 362                                mysql_error(mysql_sock->sock));
 363                         return sql_check_error(status);
 364                 }
 365 #endif
 366         }
 367         return 0;
 368 }
 369
 370
 371 /*************************************************************************
 372  *
 373  *      Function: sql_free_result
 374  *
 375  *      Purpose: database specific free_result. Frees memory allocated
 376  *               for a result set
 377  *
 378  *************************************************************************/
 379 static int sql_free_result(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 380 {
 381         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 382
 383         if (mysql_sock->result) {
 384                 mysql_free_result(mysql_sock->result);
 385                 mysql_sock->result = NULL;
 386         }
 387
 388         return 0;
 389 }
 390
 391
 392
 393 /*************************************************************************
 394  *
 395  *      Function: sql_error
 396  *
 397  *      Purpose: database specific error. Returns error associated with
 398  *               connection
 399  *
 400  *************************************************************************/
 401 static const char *sql_error(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 402 {
 403         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 404
 405         if (mysql_sock == NULL || mysql_sock->sock == NULL) {
 406                 return "rlm_sql_mysql: no connection to db";
 407         }
 408         return mysql_error(mysql_sock->sock);
 409 }
 410
 411
 412 /*************************************************************************
 413  *
 414  *      Function: sql_close
 415  *
 416  *      Purpose: database specific close. Closes an open database
 417  *               connection
 418  *
 419  *************************************************************************/
 420 static int sql_close(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 421 {
 422         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 423
 424         if (mysql_sock && mysql_sock->sock){
 425                 mysql_close(mysql_sock->sock);
 426                 mysql_sock->sock = NULL;
 427         }
 428
 429         return 0;
 430 }
 431
 432
 433 /*************************************************************************
 434  *
 435  *      Function: sql_finish_query
 436  *
 437  *      Purpose: As a single SQL statement may return multiple results
 438  *      sets, (for example stored procedures) it is necessary to check
 439  *      whether more results exist and process them in turn if so.
 440  *
 441  *************************************************************************/
 442 static int sql_finish_query(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 443 {
 444 #if (MYSQL_VERSION_ID >= 40100)
 445         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 446         int status;
 447
 448 skip_next_result:
 449         status = sql_store_result(handle, config);
 450         if (status != 0) {
 451                 return status;
 452         } else if (mysql_sock->result != NULL) {
 453                 radlog(L_DBG, "rlm_sql_mysql: SQL statement returned unexpected result");
 454                 sql_free_result(handle, config);
 455         }
 456         status = mysql_next_result(mysql_sock->sock);
 457         if (status == 0) {
 458                 /* there are more results */
 459                 goto skip_next_result;
 460         }  else if (status > 0) {
 461                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 462                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 463                        mysql_error(mysql_sock->sock));
 464                 return sql_check_error(status);
 465         }
 466 #endif
 467         return 0;
 468 }
 469
 470
 471
 472 /*************************************************************************
 473  *
 474  *      Function: sql_finish_select_query
 475  *
 476  *      Purpose: End the select query, such as freeing memory or result
 477  *
 478  *************************************************************************/
 479 static int sql_finish_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config)
 480 {
 481 #if (MYSQL_VERSION_ID >= 40100)
 482         int status;
 483         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 484 #endif
 485         sql_free_result(handle, config);
 486 #if (MYSQL_VERSION_ID >= 40100)
 487         status = mysql_next_result(mysql_sock->sock);
 488         if (status == 0) {
 489                 /* there are more results */
 490                 sql_finish_query(handle, config);
 491         }  else if (status > 0) {
 492                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 493                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 494                        mysql_error(mysql_sock->sock));
 495                 return sql_check_error(status);
 496         }
 497 #endif
 498         return 0;
 499 }
 500
 501
 502 /*************************************************************************
 503  *
 504  *      Function: sql_affected_rows
 505  *
 506  *      Purpose: End the select query, such as freeing memory or result
 507  *
 508  *************************************************************************/
 509 static int sql_affected_rows(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 510 {
 511         rlm_sql_mysql_sock *mysql_sock = handle->conn;
 512
 513         return mysql_affected_rows(mysql_sock->sock);
 514 }
 515
 516
 517 /* Exported to rlm_sql */
 518 rlm_sql_module_t rlm_sql_mysql = {
 519         "rlm_sql_mysql",
 520         sql_init_socket,
 521         sql_destroy_socket,
 522         sql_query,
 523         sql_select_query,
 524         sql_store_result,
 525         sql_num_fields,
 526         sql_num_rows,
 527         sql_fetch_row,
 528         sql_free_result,
 529         sql_error,
 530         sql_close,
 531         sql_finish_query,
 532         sql_finish_select_query,
 533         sql_affected_rows
 534 };