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