src/modules/rlm_sql/drivers/rlm_sql_mysql/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         SQL_ROW row;
  53 } rlm_sql_mysql_sock;
  54
  55 /* Prototypes */
  56 static int sql_free_result(SQLSOCK*, SQL_CONFIG*);
  57
  58 /*************************************************************************
  59  *
  60  *      Function: sql_create_socket
  61  *
  62  *      Purpose: Establish connection to the db
  63  *
  64  *************************************************************************/
  65 static int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config)
  66 {
  67         rlm_sql_mysql_sock *mysql_sock;
  68         unsigned long sql_flags;
  69         unsigned int timeout = config->query_timeout;
  70
  71         if (!sqlsocket->conn) {
  72                 sqlsocket->conn = (rlm_sql_mysql_sock *)rad_malloc(sizeof(rlm_sql_mysql_sock));
  73                 if (!sqlsocket->conn) {
  74                         return -1;
  75                 }
  76         }
  77         mysql_sock = sqlsocket->conn;
  78         memset(mysql_sock, 0, sizeof(*mysql_sock));
  79
  80         radlog(L_INFO, "rlm_sql_mysql: Starting connect to MySQL server");
  81
  82         mysql_init(&(mysql_sock->conn));
  83         mysql_options(&(mysql_sock->conn), MYSQL_READ_DEFAULT_GROUP, "freeradius");
  84
  85 #if (MYSQL_VERSION_ID >= 50000)
  86         if(timeout) {
  87                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
  88                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_READ_TIMEOUT, &timeout);
  89                 mysql_options(&(mysql_sock->conn), MYSQL_OPT_WRITE_TIMEOUT, &timeout);
  90         }
  91 #endif
  92
  93 #if (MYSQL_VERSION_ID >= 40100)
  94         sql_flags = CLIENT_MULTI_RESULTS | CLIENT_FOUND_ROWS;
  95 #else
  96         sql_flags = CLIENT_FOUND_ROWS;
  97 #endif
  98
  99 #ifdef CLIENT_MULTI_STATEMENTS
 100         sql_flags |= CLIENT_MULTI_STATEMENTS;
 101 #endif
 102         if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
 103                                                     config->sql_server,
 104                                                     config->sql_login,
 105                                                     config->sql_password,
 106                                                     config->sql_db,
 107                                                     atoi(config->sql_port),
 108                                                     NULL,
 109                                                     sql_flags))) {
 110                 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);
 111                 radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn));
 112                 mysql_sock->sock = NULL;
 113                 return -1;
 114         }
 115
 116
 117         return 0;
 118 }
 119
 120
 121 /*************************************************************************
 122  *
 123  *      Function: sql_destroy_socket
 124  *
 125  *      Purpose: Free socket and any private connection data
 126  *
 127  *************************************************************************/
 128 static int sql_destroy_socket(SQLSOCK *sqlsocket, UNUSED SQL_CONFIG *config)
 129 {
 130         free(sqlsocket->conn);
 131         sqlsocket->conn = NULL;
 132
 133         return 0;
 134 }
 135
 136
 137 /*************************************************************************
 138  *
 139  *      Function: sql_check_error
 140  *
 141  *      Purpose: check the error to see if the server is down
 142  *
 143  *************************************************************************/
 144 static int sql_check_error(int error)
 145 {
 146         switch(error) {
 147         case CR_SERVER_GONE_ERROR:
 148         case CR_SERVER_LOST:
 149         case -1:
 150                 radlog(L_DBG, "rlm_sql_mysql: MYSQL check_error: %d, returning SQL_DOWN", error);
 151                 return SQL_DOWN;
 152                 break;
 153         case 0:
 154                 return 0;
 155                 break;
 156         case CR_OUT_OF_MEMORY:
 157         case CR_COMMANDS_OUT_OF_SYNC:
 158         case CR_UNKNOWN_ERROR:
 159         default:
 160                 radlog(L_DBG, "rlm_sql_mysql: MYSQL check_error: %d received", error);
 161                 return -1;
 162                 break;
 163         }
 164 }
 165
 166
 167 /*************************************************************************
 168  *
 169  *      Function: sql_query
 170  *
 171  *      Purpose: Issue a query to the database
 172  *
 173  *************************************************************************/
 174 static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr)
 175 {
 176         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 177
 178         if (config->sqltrace)
 179                 radlog(L_DBG,"rlm_sql_mysql: query:  %s", querystr);
 180         if (mysql_sock->sock == NULL) {
 181                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 182                 return SQL_DOWN;
 183         }
 184
 185         mysql_query(mysql_sock->sock, querystr);
 186         return sql_check_error(mysql_errno(mysql_sock->sock));
 187 }
 188
 189
 190 /*************************************************************************
 191  *
 192  *      Function: sql_store_result
 193  *
 194  *      Purpose: database specific store_result function. Returns a result
 195  *               set for the query. In case of multiple results, get the
 196  *               first non-empty one.
 197  *
 198  *************************************************************************/
 199 static int sql_store_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 200 {
 201         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 202         int status;
 203
 204         if (mysql_sock->sock == NULL) {
 205                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 206                 return SQL_DOWN;
 207         }
 208 retry_store_result:
 209         if (!(mysql_sock->result = mysql_store_result(mysql_sock->sock))) {
 210                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 211                 if (status != 0) {
 212                         radlog(L_ERR, "rlm_sql_mysql: Cannot store result");
 213                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 214                                mysql_error(mysql_sock->sock));
 215                         return status;
 216                 }
 217 #if (MYSQL_VERSION_ID >= 40100)
 218                 status = mysql_next_result(mysql_sock->sock);
 219                 if (status == 0) {
 220                         /* there are more results */
 221                         goto retry_store_result;
 222                 } else if (status > 0) {
 223                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 224                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 225                                mysql_error(mysql_sock->sock));
 226                         return sql_check_error(status);
 227                 }
 228 #endif
 229         }
 230         return 0;
 231 }
 232
 233
 234 /*************************************************************************
 235  *
 236  *      Function: sql_num_fields
 237  *
 238  *      Purpose: database specific num_fields function. Returns number
 239  *               of columns from query
 240  *
 241  *************************************************************************/
 242 static int sql_num_fields(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 243 {
 244         int     num = 0;
 245         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 246
 247 #if MYSQL_VERSION_ID >= 32224
 248         if (!(num = mysql_field_count(mysql_sock->sock))) {
 249 #else
 250         if (!(num = mysql_num_fields(mysql_sock->sock))) {
 251 #endif
 252                 radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: No Fields");
 253                 radlog(L_ERR, "rlm_sql_mysql: MYSQL error: %s",
 254                        mysql_error(mysql_sock->sock));
 255         }
 256         return num;
 257 }
 258
 259
 260 /*************************************************************************
 261  *
 262  *      Function: sql_select_query
 263  *
 264  *      Purpose: Issue a select query to the database
 265  *
 266  *************************************************************************/
 267 static int sql_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config,
 268                             char *querystr)
 269 {
 270         int ret;
 271
 272         ret = sql_query(sqlsocket, config, querystr);
 273         if(ret)
 274                 return ret;
 275         ret = sql_store_result(sqlsocket, config);
 276         if (ret) {
 277                 return ret;
 278         }
 279
 280         /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
 281          * this cannot return an error.  Perhaps just to complain if no
 282          * fields are found?
 283          */
 284         sql_num_fields(sqlsocket, config);
 285
 286         return ret;
 287 }
 288
 289
 290 /*************************************************************************
 291  *
 292  *      Function: sql_num_rows
 293  *
 294  *      Purpose: database specific num_rows. Returns number of rows in
 295  *               query
 296  *
 297  *************************************************************************/
 298 static int sql_num_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 299 {
 300         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 301
 302         if (mysql_sock->result)
 303                 return mysql_num_rows(mysql_sock->result);
 304
 305         return 0;
 306 }
 307
 308
 309 /*************************************************************************
 310  *
 311  *      Function: sql_fetch_row
 312  *
 313  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 314  *               with all the data for the query in 'sqlsocket->row'. Returns
 315  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 316  *
 317  *************************************************************************/
 318 static int sql_fetch_row(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 319 {
 320         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 321         int status;
 322
 323         /*
 324          *  Check pointer before de-referencing it.
 325          */
 326         if (!mysql_sock->result) {
 327                 return SQL_DOWN;
 328         }
 329
 330 retry_fetch_row:
 331         sqlsocket->row = mysql_fetch_row(mysql_sock->result);
 332
 333         if (sqlsocket->row == NULL) {
 334                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 335                 if (status != 0) {
 336                         radlog(L_ERR, "rlm_sql_mysql: Cannot fetch row");
 337                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 338                                mysql_error(mysql_sock->sock));
 339                         return status;
 340                 }
 341 #if (MYSQL_VERSION_ID >= 40100)
 342                 sql_free_result(sqlsocket, config);
 343                 status = mysql_next_result(mysql_sock->sock);
 344                 if (status == 0) {
 345                         /* there are more results */
 346                         if ((sql_store_result(sqlsocket, config) == 0)
 347                          && (mysql_sock->result != NULL))
 348                                 goto retry_fetch_row;
 349                 } else if (status > 0) {
 350                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 351                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 352                                mysql_error(mysql_sock->sock));
 353                         return sql_check_error(status);
 354                 }
 355 #endif
 356         }
 357         return 0;
 358 }
 359
 360
 361 /*************************************************************************
 362  *
 363  *      Function: sql_free_result
 364  *
 365  *      Purpose: database specific free_result. Frees memory allocated
 366  *               for a result set
 367  *
 368  *************************************************************************/
 369 static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 370 {
 371         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 372
 373         if (mysql_sock->result) {
 374                 mysql_free_result(mysql_sock->result);
 375                 mysql_sock->result = NULL;
 376         }
 377
 378         return 0;
 379 }
 380
 381
 382
 383 /*************************************************************************
 384  *
 385  *      Function: sql_error
 386  *
 387  *      Purpose: database specific error. Returns error associated with
 388  *               connection
 389  *
 390  *************************************************************************/
 391 static const char *sql_error(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 392 {
 393         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 394
 395         if (mysql_sock == NULL || mysql_sock->sock == NULL) {
 396                 return "rlm_sql_mysql: no connection to db";
 397         }
 398         return mysql_error(mysql_sock->sock);
 399 }
 400
 401
 402 /*************************************************************************
 403  *
 404  *      Function: sql_close
 405  *
 406  *      Purpose: database specific close. Closes an open database
 407  *               connection
 408  *
 409  *************************************************************************/
 410 static int sql_close(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 411 {
 412         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 413
 414         if (mysql_sock && mysql_sock->sock){
 415                 mysql_close(mysql_sock->sock);
 416                 mysql_sock->sock = NULL;
 417         }
 418
 419         return 0;
 420 }
 421
 422
 423 /*************************************************************************
 424  *
 425  *      Function: sql_finish_query
 426  *
 427  *      Purpose: As a single SQL statement may return multiple results
 428  *      sets, (for example stored procedures) it is necessary to check
 429  *      whether more results exist and process them in turn if so.
 430  *
 431  *************************************************************************/
 432 static int sql_finish_query(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 433 {
 434 #if (MYSQL_VERSION_ID >= 40100)
 435         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 436         int status;
 437
 438 skip_next_result:
 439         status = sql_store_result(sqlsocket, config);
 440         if (status != 0) {
 441                 return status;
 442         } else if (mysql_sock->result != NULL) {
 443                 radlog(L_DBG, "rlm_sql_mysql: SQL statement returned unexpected result");
 444                 sql_free_result(sqlsocket, config);
 445         }
 446         status = mysql_next_result(mysql_sock->sock);
 447         if (status == 0) {
 448                 /* there are more results */
 449                 goto skip_next_result;
 450         }  else if (status > 0) {
 451                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 452                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 453                        mysql_error(mysql_sock->sock));
 454                 return sql_check_error(status);
 455         }
 456 #endif
 457         return 0;
 458 }
 459
 460
 461
 462 /*************************************************************************
 463  *
 464  *      Function: sql_finish_select_query
 465  *
 466  *      Purpose: End the select query, such as freeing memory or result
 467  *
 468  *************************************************************************/
 469 static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 470 {
 471 #if (MYSQL_VERSION_ID >= 40100)
 472         int status;
 473         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 474 #endif
 475         sql_free_result(sqlsocket, config);
 476 #if (MYSQL_VERSION_ID >= 40100)
 477         status = mysql_next_result(mysql_sock->sock);
 478         if (status == 0) {
 479                 /* there are more results */
 480                 sql_finish_query(sqlsocket, config);
 481         }  else if (status > 0) {
 482                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 483                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 484                        mysql_error(mysql_sock->sock));
 485                 return sql_check_error(status);
 486         }
 487 #endif
 488         return 0;
 489 }
 490
 491
 492 /*************************************************************************
 493  *
 494  *      Function: sql_affected_rows
 495  *
 496  *      Purpose: End the select query, such as freeing memory or result
 497  *
 498  *************************************************************************/
 499 static int sql_affected_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 500 {
 501         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 502
 503         return mysql_affected_rows(mysql_sock->sock);
 504 }
 505
 506
 507 /* Exported to rlm_sql */
 508 rlm_sql_module_t rlm_sql_mysql = {
 509         "rlm_sql_mysql",
 510         sql_init_socket,
 511         sql_destroy_socket,
 512         sql_query,
 513         sql_select_query,
 514         sql_store_result,
 515         sql_num_fields,
 516         sql_num_rows,
 517         sql_fetch_row,
 518         sql_free_result,
 519         sql_error,
 520         sql_close,
 521         sql_finish_query,
 522         sql_finish_select_query,
 523         sql_affected_rows
 524 };