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