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
  70         if (!sqlsocket->conn) {
  71                 sqlsocket->conn = (rlm_sql_mysql_sock *)rad_malloc(sizeof(rlm_sql_mysql_sock));
  72                 if (!sqlsocket->conn) {
  73                         return -1;
  74                 }
  75         }
  76         mysql_sock = sqlsocket->conn;
  77         memset(mysql_sock, 0, sizeof(*mysql_sock));
  78
  79         radlog(L_INFO, "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(SQLSOCK *sqlsocket, UNUSED SQL_CONFIG *config)
 140 {
 141         free(sqlsocket->conn);
 142         sqlsocket->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(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr)
 186 {
 187         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 188
 189         if (config->sqltrace)
 190                 radlog(L_DBG,"rlm_sql_mysql: query:  %s", querystr);
 191         if (mysql_sock->sock == NULL) {
 192                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 193                 return SQL_DOWN;
 194         }
 195
 196         mysql_query(mysql_sock->sock, querystr);
 197         return sql_check_error(mysql_errno(mysql_sock->sock));
 198 }
 199
 200
 201 /*************************************************************************
 202  *
 203  *      Function: sql_store_result
 204  *
 205  *      Purpose: database specific store_result function. Returns a result
 206  *               set for the query. In case of multiple results, get the
 207  *               first non-empty one.
 208  *
 209  *************************************************************************/
 210 static int sql_store_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 211 {
 212         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 213         int status;
 214
 215         if (mysql_sock->sock == NULL) {
 216                 radlog(L_ERR, "rlm_sql_mysql: Socket not connected");
 217                 return SQL_DOWN;
 218         }
 219 retry_store_result:
 220         if (!(mysql_sock->result = mysql_store_result(mysql_sock->sock))) {
 221                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 222                 if (status != 0) {
 223                         radlog(L_ERR, "rlm_sql_mysql: Cannot store result");
 224                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 225                                mysql_error(mysql_sock->sock));
 226                         return status;
 227                 }
 228 #if (MYSQL_VERSION_ID >= 40100)
 229                 status = mysql_next_result(mysql_sock->sock);
 230                 if (status == 0) {
 231                         /* there are more results */
 232                         goto retry_store_result;
 233                 } else if (status > 0) {
 234                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 235                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 236                                mysql_error(mysql_sock->sock));
 237                         return sql_check_error(status);
 238                 }
 239 #endif
 240         }
 241         return 0;
 242 }
 243
 244
 245 /*************************************************************************
 246  *
 247  *      Function: sql_num_fields
 248  *
 249  *      Purpose: database specific num_fields function. Returns number
 250  *               of columns from query
 251  *
 252  *************************************************************************/
 253 static int sql_num_fields(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 254 {
 255         int     num = 0;
 256         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 257
 258 #if MYSQL_VERSION_ID >= 32224
 259         if (!(num = mysql_field_count(mysql_sock->sock))) {
 260 #else
 261         if (!(num = mysql_num_fields(mysql_sock->sock))) {
 262 #endif
 263                 radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: No Fields");
 264                 radlog(L_ERR, "rlm_sql_mysql: MYSQL error: %s",
 265                        mysql_error(mysql_sock->sock));
 266         }
 267         return num;
 268 }
 269
 270
 271 /*************************************************************************
 272  *
 273  *      Function: sql_select_query
 274  *
 275  *      Purpose: Issue a select query to the database
 276  *
 277  *************************************************************************/
 278 static int sql_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config,
 279                             char *querystr)
 280 {
 281         int ret;
 282
 283         ret = sql_query(sqlsocket, config, querystr);
 284         if(ret)
 285                 return ret;
 286         ret = sql_store_result(sqlsocket, config);
 287         if (ret) {
 288                 return ret;
 289         }
 290
 291         /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
 292          * this cannot return an error.  Perhaps just to complain if no
 293          * fields are found?
 294          */
 295         sql_num_fields(sqlsocket, config);
 296
 297         return ret;
 298 }
 299
 300
 301 /*************************************************************************
 302  *
 303  *      Function: sql_num_rows
 304  *
 305  *      Purpose: database specific num_rows. Returns number of rows in
 306  *               query
 307  *
 308  *************************************************************************/
 309 static int sql_num_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 310 {
 311         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 312
 313         if (mysql_sock->result)
 314                 return mysql_num_rows(mysql_sock->result);
 315
 316         return 0;
 317 }
 318
 319
 320 /*************************************************************************
 321  *
 322  *      Function: sql_fetch_row
 323  *
 324  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 325  *               with all the data for the query in 'sqlsocket->row'. Returns
 326  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 327  *
 328  *************************************************************************/
 329 static int sql_fetch_row(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 330 {
 331         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 332         int status;
 333
 334         /*
 335          *  Check pointer before de-referencing it.
 336          */
 337         if (!mysql_sock->result) {
 338                 return SQL_DOWN;
 339         }
 340
 341 retry_fetch_row:
 342         sqlsocket->row = mysql_fetch_row(mysql_sock->result);
 343
 344         if (sqlsocket->row == NULL) {
 345                 status = sql_check_error(mysql_errno(mysql_sock->sock));
 346                 if (status != 0) {
 347                         radlog(L_ERR, "rlm_sql_mysql: Cannot fetch row");
 348                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 349                                mysql_error(mysql_sock->sock));
 350                         return status;
 351                 }
 352 #if (MYSQL_VERSION_ID >= 40100)
 353                 sql_free_result(sqlsocket, config);
 354                 status = mysql_next_result(mysql_sock->sock);
 355                 if (status == 0) {
 356                         /* there are more results */
 357                         if ((sql_store_result(sqlsocket, config) == 0)
 358                          && (mysql_sock->result != NULL))
 359                                 goto retry_fetch_row;
 360                 } else if (status > 0) {
 361                         radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 362                         radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 363                                mysql_error(mysql_sock->sock));
 364                         return sql_check_error(status);
 365                 }
 366 #endif
 367         }
 368         return 0;
 369 }
 370
 371
 372 /*************************************************************************
 373  *
 374  *      Function: sql_free_result
 375  *
 376  *      Purpose: database specific free_result. Frees memory allocated
 377  *               for a result set
 378  *
 379  *************************************************************************/
 380 static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 381 {
 382         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 383
 384         if (mysql_sock->result) {
 385                 mysql_free_result(mysql_sock->result);
 386                 mysql_sock->result = NULL;
 387         }
 388
 389         return 0;
 390 }
 391
 392
 393
 394 /*************************************************************************
 395  *
 396  *      Function: sql_error
 397  *
 398  *      Purpose: database specific error. Returns error associated with
 399  *               connection
 400  *
 401  *************************************************************************/
 402 static const char *sql_error(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 403 {
 404         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 405
 406         if (mysql_sock == NULL || mysql_sock->sock == NULL) {
 407                 return "rlm_sql_mysql: no connection to db";
 408         }
 409         return mysql_error(mysql_sock->sock);
 410 }
 411
 412
 413 /*************************************************************************
 414  *
 415  *      Function: sql_close
 416  *
 417  *      Purpose: database specific close. Closes an open database
 418  *               connection
 419  *
 420  *************************************************************************/
 421 static int sql_close(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 422 {
 423         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 424
 425         if (mysql_sock && mysql_sock->sock){
 426                 mysql_close(mysql_sock->sock);
 427                 mysql_sock->sock = NULL;
 428         }
 429
 430         return 0;
 431 }
 432
 433
 434 /*************************************************************************
 435  *
 436  *      Function: sql_finish_query
 437  *
 438  *      Purpose: As a single SQL statement may return multiple results
 439  *      sets, (for example stored procedures) it is necessary to check
 440  *      whether more results exist and process them in turn if so.
 441  *
 442  *************************************************************************/
 443 static int sql_finish_query(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 444 {
 445 #if (MYSQL_VERSION_ID >= 40100)
 446         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 447         int status;
 448
 449 skip_next_result:
 450         status = sql_store_result(sqlsocket, config);
 451         if (status != 0) {
 452                 return status;
 453         } else if (mysql_sock->result != NULL) {
 454                 radlog(L_DBG, "rlm_sql_mysql: SQL statement returned unexpected result");
 455                 sql_free_result(sqlsocket, config);
 456         }
 457         status = mysql_next_result(mysql_sock->sock);
 458         if (status == 0) {
 459                 /* there are more results */
 460                 goto skip_next_result;
 461         }  else if (status > 0) {
 462                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 463                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 464                        mysql_error(mysql_sock->sock));
 465                 return sql_check_error(status);
 466         }
 467 #endif
 468         return 0;
 469 }
 470
 471
 472
 473 /*************************************************************************
 474  *
 475  *      Function: sql_finish_select_query
 476  *
 477  *      Purpose: End the select query, such as freeing memory or result
 478  *
 479  *************************************************************************/
 480 static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 481 {
 482 #if (MYSQL_VERSION_ID >= 40100)
 483         int status;
 484         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 485 #endif
 486         sql_free_result(sqlsocket, config);
 487 #if (MYSQL_VERSION_ID >= 40100)
 488         status = mysql_next_result(mysql_sock->sock);
 489         if (status == 0) {
 490                 /* there are more results */
 491                 sql_finish_query(sqlsocket, config);
 492         }  else if (status > 0) {
 493                 radlog(L_ERR, "rlm_sql_mysql: Cannot get next result");
 494                 radlog(L_ERR, "rlm_sql_mysql: MySQL error '%s'",
 495                        mysql_error(mysql_sock->sock));
 496                 return sql_check_error(status);
 497         }
 498 #endif
 499         return 0;
 500 }
 501
 502
 503 /*************************************************************************
 504  *
 505  *      Function: sql_affected_rows
 506  *
 507  *      Purpose: End the select query, such as freeing memory or result
 508  *
 509  *************************************************************************/
 510 static int sql_affected_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 511 {
 512         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
 513
 514         return mysql_affected_rows(mysql_sock->sock);
 515 }
 516
 517
 518 /* Exported to rlm_sql */
 519 rlm_sql_module_t rlm_sql_mysql = {
 520         "rlm_sql_mysql",
 521         sql_init_socket,
 522         sql_destroy_socket,
 523         sql_query,
 524         sql_select_query,
 525         sql_store_result,
 526         sql_num_fields,
 527         sql_num_rows,
 528         sql_fetch_row,
 529         sql_free_result,
 530         sql_error,
 531         sql_close,
 532         sql_finish_query,
 533         sql_finish_select_query,
 534         sql_affected_rows
 535 };