src/modules/rlm_sql/sql.c

   1 /*
   2  *  sql.c               rlm_sql - FreeRADIUS SQL Module
   3  *              Main code directly taken from ICRADIUS
   4  *
   5  * Version:     $Id$
   6  *
   7  *   This program is free software; you can redistribute it and/or modify
   8  *   it under the terms of the GNU General Public License as published by
   9  *   the Free Software Foundation; either version 2 of the License, or
  10  *   (at your option) any later version.
  11  *
  12  *   This program is distributed in the hope that it will be useful,
  13  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  *   GNU General Public License for more details.
  16  *
  17  *   You should have received a copy of the GNU General Public License
  18  *   along with this program; if not, write to the Free Software
  19  *   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  20  *
  21  * Copyright 2001,2006  The FreeRADIUS server project
  22  * Copyright 2000  Mike Machado <mike@innercite.com>
  23  * Copyright 2000  Alan DeKok <aland@ox.org>
  24  * Copyright 2001  Chad Miller <cmiller@surfsouth.com>
  25  */
  26
  27 #include <freeradius-devel/ident.h>
  28 RCSID("$Id$")
  29
  30 #include        <freeradius-devel/radiusd.h>
  31
  32 #include        <sys/file.h>
  33 #include        <sys/stat.h>
  34
  35 #include        <ctype.h>
  36
  37 #include        "rlm_sql.h"
  38
  39 #ifdef HAVE_PTHREAD_H
  40 #endif
  41
  42
  43 /*
  44  * Connect to a server.  If error, set this socket's state to be
  45  * "sockunconnected" and set a grace period, during which we won't try
  46  * connecting again (to prevent unduly lagging the server and being
  47  * impolite to a DB server that may be having other issues).  If
  48  * successful in connecting, set state to sockconnected.
  49  * - chad
  50  */
  51 static int connect_single_socket(SQLSOCK *sqlsocket, SQL_INST *inst)
  52 {
  53         int rcode;
  54         radlog(L_INFO, "rlm_sql (%s): Attempting to connect %s #%d",
  55                inst->config->xlat_name, inst->module->name, sqlsocket->id);
  56         rcode = (inst->module->sql_init_socket)(sqlsocket, inst->config);
  57         if (rcode == 0) {
  58                 radlog(L_INFO, "rlm_sql (%s): Connected new DB handle, #%d",
  59                        inst->config->xlat_name, sqlsocket->id);
  60                 sqlsocket->state = sockconnected;
  61                 if (inst->config->lifetime) time(&sqlsocket->connected);
  62                 sqlsocket->queries = 0;
  63                 return(0);
  64         }
  65
  66         /*
  67          *  Error, or SQL_DOWN.
  68          */
  69         radlog(L_CONS | L_ERR, "rlm_sql (%s): Failed to connect DB handle #%d", inst->config->xlat_name, sqlsocket->id);
  70         inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  71         sqlsocket->state = sockunconnected;
  72         return(-1);
  73 }
  74
  75
  76 /*************************************************************************
  77  *
  78  *      Function: sql_init_socketpool
  79  *
  80  *      Purpose: Connect to the sql server, if possible
  81  *
  82  *************************************************************************/
  83 int sql_init_socketpool(SQL_INST * inst)
  84 {
  85         int i, rcode;
  86         int success = 0;
  87         SQLSOCK *sqlsocket;
  88
  89         inst->connect_after = 0;
  90         inst->sqlpool = NULL;
  91
  92         for (i = 0; i < inst->config->num_sql_socks; i++) {
  93                 radlog(L_DBG, "rlm_sql (%s): starting %d",
  94                        inst->config->xlat_name, i);
  95
  96                 sqlsocket = rad_malloc(sizeof(*sqlsocket));
  97                 if (sqlsocket == NULL) {
  98                         return -1;
  99                 }
 100                 memset(sqlsocket, 0, sizeof(*sqlsocket));
 101                 sqlsocket->conn = NULL;
 102                 sqlsocket->id = i;
 103                 sqlsocket->state = sockunconnected;
 104
 105 #ifdef HAVE_PTHREAD_H
 106                 rcode = pthread_mutex_init(&sqlsocket->mutex,NULL);
 107                 if (rcode != 0) {
 108                         free(sqlsocket);
 109                         radlog(L_ERR, "rlm_sql: Failed to init lock: %s",
 110                                strerror(errno));
 111                         return 0;
 112                 }
 113 #endif
 114
 115                 if (time(NULL) > inst->connect_after) {
 116                         /*
 117                          *      This sets the sqlsocket->state, and
 118                          *      possibly also inst->connect_after
 119                          */
 120                         if (connect_single_socket(sqlsocket, inst) == 0) {
 121                                 success = 1;
 122                         }
 123                 }
 124
 125                 /* Add this socket to the list of sockets */
 126                 sqlsocket->next = inst->sqlpool;
 127                 inst->sqlpool = sqlsocket;
 128         }
 129         inst->last_used = NULL;
 130
 131         if (!success) {
 132                 radlog(L_DBG, "rlm_sql (%s): Failed to connect to any SQL server.",
 133                        inst->config->xlat_name);
 134         }
 135
 136         return 1;
 137 }
 138
 139 /*************************************************************************
 140  *
 141  *     Function: sql_poolfree
 142  *
 143  *     Purpose: Clean up and free sql pool
 144  *
 145  *************************************************************************/
 146 void sql_poolfree(SQL_INST * inst)
 147 {
 148         SQLSOCK *cur;
 149         SQLSOCK *next;
 150
 151         for (cur = inst->sqlpool; cur; cur = next) {
 152                 next = cur->next;
 153                 sql_close_socket(inst, cur);
 154         }
 155
 156         inst->sqlpool = NULL;
 157 }
 158
 159
 160 /*************************************************************************
 161  *
 162  *      Function: sql_close_socket
 163  *
 164  *      Purpose: Close and free a sql sqlsocket
 165  *
 166  *************************************************************************/
 167 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket)
 168 {
 169         radlog(L_INFO, "rlm_sql (%s): Closing sqlsocket %d",
 170                inst->config->xlat_name, sqlsocket->id);
 171         if (sqlsocket->state == sockconnected) {
 172                 (inst->module->sql_close)(sqlsocket, inst->config);
 173         }
 174         if (inst->module->sql_destroy_socket) {
 175                 (inst->module->sql_destroy_socket)(sqlsocket, inst->config);
 176         }
 177 #ifdef HAVE_PTHREAD_H
 178         pthread_mutex_destroy(&sqlsocket->mutex);
 179 #endif
 180         free(sqlsocket);
 181         return 1;
 182 }
 183
 184 static time_t last_logged_failure = 0;
 185
 186
 187 /*************************************************************************
 188  *
 189  *      Function: sql_get_socket
 190  *
 191  *      Purpose: Return a SQL sqlsocket from the connection pool
 192  *
 193  *************************************************************************/
 194 SQLSOCK * sql_get_socket(SQL_INST * inst)
 195 {
 196         SQLSOCK *cur, *start;
 197         int tried_to_connect = 0;
 198         int unconnected = 0;
 199         time_t now = time(NULL);
 200
 201         /*
 202          *      Start at the last place we left off.
 203          */
 204         start = inst->last_used;
 205         if (!start) start = inst->sqlpool;
 206
 207         cur = start;
 208
 209         while (cur) {
 210 #ifdef HAVE_PTHREAD_H
 211                 /*
 212                  *      If this socket is in use by another thread,
 213                  *      skip it, and try another socket.
 214                  *
 215                  *      If it isn't used, then grab it ourselves.
 216                  */
 217                 if (pthread_mutex_trylock(&cur->mutex) != 0) {
 218                         goto next;
 219                 } /* else we now have the lock */
 220 #endif
 221
 222                 /*
 223                  *      If the socket has outlived its lifetime, and
 224                  *      is connected, close it, and mark it as open for
 225                  *      reconnections.
 226                  */
 227                 if (inst->config->lifetime && (cur->state == sockconnected) &&
 228                     ((cur->connected + inst->config->lifetime) < now)) {
 229                         DEBUG2("Closing socket %d as its lifetime has been exceeded", cur->id);
 230                         (inst->module->sql_close)(cur, inst->config);
 231                         cur->state = sockunconnected;
 232                         goto reconnect;
 233                 }
 234
 235                 /*
 236                  *      If we have performed too many queries over this
 237                  *      socket, then close it.
 238                  */
 239                 if (inst->config->max_queries && (cur->state == sockconnected) &&
 240                     (cur->queries >= inst->config->max_queries)) {
 241                         DEBUG2("Closing socket %d as its max_queries has been exceeded", cur->id);
 242                         (inst->module->sql_close)(cur, inst->config);
 243                         cur->state = sockunconnected;
 244                         goto reconnect;
 245                 }
 246
 247                 /*
 248                  *      If we happen upon an unconnected socket, and
 249                  *      this instance's grace period on
 250                  *      (re)connecting has expired, then try to
 251                  *      connect it.  This should be really rare.
 252                  */
 253                 if ((cur->state == sockunconnected) && (now > inst->connect_after)) {
 254                 reconnect:
 255                         radlog(L_INFO, "rlm_sql (%s): Trying to (re)connect unconnected handle %d..", inst->config->xlat_name, cur->id);
 256                         tried_to_connect++;
 257                         connect_single_socket(cur, inst);
 258                 }
 259
 260                 /* if we still aren't connected, ignore this handle */
 261                 if (cur->state == sockunconnected) {
 262                         DEBUG("rlm_sql (%s): Ignoring unconnected handle %d..", inst->config->xlat_name, cur->id);
 263                         unconnected++;
 264 #ifdef HAVE_PTHREAD_H
 265                         pthread_mutex_unlock(&cur->mutex);
 266 #endif
 267                         goto next;
 268                 }
 269
 270                 /* should be connected, grab it */
 271                 DEBUG("rlm_sql (%s): Reserving sql socket id: %d", inst->config->xlat_name, cur->id);
 272
 273                 if (unconnected != 0 || tried_to_connect != 0) {
 274                         DEBUG("rlm_sql (%s): got socket %d after skipping %d unconnected handles, tried to reconnect %d though", inst->config->xlat_name, cur->id, unconnected, tried_to_connect);
 275                 }
 276
 277                 /*
 278                  *      The socket is returned in the locked
 279                  *      state.
 280                  *
 281                  *      We also remember where we left off,
 282                  *      so that the next search can start from
 283                  *      here.
 284                  *
 285                  *      Note that multiple threads MAY over-write
 286                  *      the 'inst->last_used' variable.  This is OK,
 287                  *      as it's a pointer only used for reading.
 288                  */
 289                 inst->last_used = cur->next;
 290                 cur->queries++;
 291                 return cur;
 292
 293                 /* move along the list */
 294         next:
 295                 cur = cur->next;
 296
 297                 /*
 298                  *      Because we didnt start at the start, once we
 299                  *      hit the end of the linklist, we should go
 300                  *      back to the beginning and work toward the
 301                  *      middle!
 302                  */
 303                 if (!cur) {
 304                         cur = inst->sqlpool;
 305                 }
 306
 307                 /*
 308                  *      If we're at the socket we started
 309                  */
 310                 if (cur == start) {
 311                         break;
 312                 }
 313         }
 314
 315         /*
 316          *      Suppress most of the log messages.  We don't want to
 317          *      flood the log with this message for EVERY packet.
 318          *      Instead, write to the log only once a second or so.
 319          *
 320          *      This code has race conditions when threaded, but the
 321          *      only result is that a few more messages are logged.
 322          */
 323         if (now <= last_logged_failure) return NULL;
 324         last_logged_failure = now;
 325
 326         /* We get here if every DB handle is unconnected and unconnectABLE */
 327         radlog(L_INFO, "rlm_sql (%s): There are no DB handles to use! skipped %d, tried to connect %d", inst->config->xlat_name, unconnected, tried_to_connect);
 328         return NULL;
 329 }
 330
 331 /*************************************************************************
 332  *
 333  *      Function: sql_release_socket
 334  *
 335  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 336  *
 337  *************************************************************************/
 338 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket)
 339 {
 340 #ifdef HAVE_PTHREAD_H
 341         pthread_mutex_unlock(&sqlsocket->mutex);
 342 #endif
 343
 344         radlog(L_DBG, "rlm_sql (%s): Released sql socket id: %d",
 345                inst->config->xlat_name, sqlsocket->id);
 346
 347         return 0;
 348 }
 349
 350
 351 /*************************************************************************
 352  *
 353  *      Function: sql_userparse
 354  *
 355  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 356  *
 357  *************************************************************************/
 358 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row)
 359 {
 360         VALUE_PAIR *pair;
 361         const char *ptr, *value;
 362         char buf[MAX_STRING_LEN];
 363         char do_xlat = 0;
 364         FR_TOKEN token, operator = T_EOL;
 365
 366         /*
 367          *      Verify the 'Attribute' field
 368          */
 369         if (row[2] == NULL || row[2][0] == '\0') {
 370                 radlog(L_ERR, "rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row.");
 371                 return -1;
 372         }
 373
 374         /*
 375          *      Verify the 'op' field
 376          */
 377         if (row[4] != NULL && row[4][0] != '\0') {
 378                 ptr = row[4];
 379                 operator = gettoken(&ptr, buf, sizeof(buf));
 380                 if ((operator < T_OP_ADD) ||
 381                     (operator > T_OP_CMP_EQ)) {
 382                         radlog(L_ERR, "rlm_sql: Invalid operator \"%s\" for attribute %s", row[4], row[2]);
 383                         return -1;
 384                 }
 385
 386         } else {
 387                 /*
 388                  *  Complain about empty or invalid 'op' field
 389                  */
 390                 operator = T_OP_CMP_EQ;
 391                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 392                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 393         }
 394
 395         /*
 396          *      The 'Value' field may be empty or NULL
 397          */
 398         value = row[3];
 399         /*
 400          *      If we have a new-style quoted string, where the
 401          *      *entire* string is quoted, do xlat's.
 402          */
 403         if (row[3] != NULL &&
 404            ((row[3][0] == '\'') || (row[3][0] == '`') || (row[3][0] == '"')) &&
 405            (row[3][0] == row[3][strlen(row[3])-1])) {
 406
 407                 token = gettoken(&value, buf, sizeof(buf));
 408                 switch (token) {
 409                         /*
 410                          *      Take the unquoted string.
 411                          */
 412                 case T_SINGLE_QUOTED_STRING:
 413                 case T_DOUBLE_QUOTED_STRING:
 414                         value = buf;
 415                         break;
 416
 417                         /*
 418                          *      Mark the pair to be allocated later.
 419                          */
 420                 case T_BACK_QUOTED_STRING:
 421                         value = NULL;
 422                         do_xlat = 1;
 423                         break;
 424
 425                         /*
 426                          *      Keep the original string.
 427                          */
 428                 default:
 429                         value = row[3];
 430                         break;
 431                 }
 432         }
 433
 434         /*
 435          *      Create the pair
 436          */
 437         pair = pairmake(row[2], value, operator);
 438         if (pair == NULL) {
 439                 radlog(L_ERR, "rlm_sql: Failed to create the pair: %s", fr_strerror());
 440                 return -1;
 441         }
 442         if (do_xlat) {
 443                 pair->flags.do_xlat = 1;
 444                 strlcpy(pair->vp_strvalue, buf, sizeof(pair->vp_strvalue));
 445                 pair->length = 0;
 446         }
 447
 448         /*
 449          *      Add the pair into the packet
 450          */
 451         pairadd(first_pair, pair);
 452         return 0;
 453 }
 454
 455
 456 /*************************************************************************
 457  *
 458  *      Function: rlm_sql_fetch_row
 459  *
 460  *      Purpose: call the module's sql_fetch_row and implement re-connect
 461  *
 462  *************************************************************************/
 463 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 464 {
 465         int ret;
 466
 467         if (sqlsocket->conn) {
 468                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 469         } else {
 470                 ret = SQL_DOWN;
 471         }
 472
 473         if (ret == SQL_DOWN) {
 474                 /* close the socket that failed, but only if it was open */
 475                 if (sqlsocket->conn) {
 476                         (inst->module->sql_close)(sqlsocket, inst->config);
 477                 }
 478
 479                 /* reconnect the socket */
 480                 if (connect_single_socket(sqlsocket, inst) < 0) {
 481                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 482                         return -1;
 483                 }
 484
 485                 /* retry the query on the newly connected socket */
 486                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 487
 488                 if (ret) {
 489                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 490                                inst->config->xlat_name);
 491                         return -1;
 492                 }
 493         }
 494
 495         return ret;
 496 }
 497
 498 /*************************************************************************
 499  *
 500  *      Function: rlm_sql_query
 501  *
 502  *      Purpose: call the module's sql_query and implement re-connect
 503  *
 504  *************************************************************************/
 505 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 506 {
 507         int ret;
 508
 509         /*
 510          *      If there's no query, return an error.
 511          */
 512         if (!query || !*query) {
 513                 return -1;
 514         }
 515
 516         if (sqlsocket->conn) {
 517                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 518         } else {
 519                 ret = SQL_DOWN;
 520         }
 521
 522         if (ret == SQL_DOWN) {
 523                 /* close the socket that failed */
 524                 if (sqlsocket->state == sockconnected) {
 525                         (inst->module->sql_close)(sqlsocket, inst->config);
 526                 }
 527
 528                 /* reconnect the socket */
 529                 if (connect_single_socket(sqlsocket, inst) < 0) {
 530                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 531                         return -1;
 532                 }
 533
 534                 /* retry the query on the newly connected socket */
 535                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 536
 537                 if (ret) {
 538                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 539                                inst->config->xlat_name);
 540                         return -1;
 541                 }
 542         }
 543
 544         return ret;
 545 }
 546
 547 /*************************************************************************
 548  *
 549  *      Function: rlm_sql_select_query
 550  *
 551  *      Purpose: call the module's sql_select_query and implement re-connect
 552  *
 553  *************************************************************************/
 554 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 555 {
 556         int ret;
 557
 558         /*
 559          *      If there's no query, return an error.
 560          */
 561         if (!query || !*query) {
 562                 return -1;
 563         }
 564
 565         if (sqlsocket->conn) {
 566                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config,
 567                                                        query);
 568         } else {
 569                 ret = SQL_DOWN;
 570         }
 571
 572         if (ret == SQL_DOWN) {
 573                 /* close the socket that failed */
 574                 if (sqlsocket->state == sockconnected) {
 575                         (inst->module->sql_close)(sqlsocket, inst->config);
 576                 }
 577
 578                 /* reconnect the socket */
 579                 if (connect_single_socket(sqlsocket, inst) < 0) {
 580                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 581                         return -1;
 582                 }
 583
 584                 /* retry the query on the newly connected socket */
 585                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 586
 587                 if (ret) {
 588                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 589                                inst->config->xlat_name);
 590                         return -1;
 591                 }
 592         }
 593
 594         return ret;
 595 }
 596
 597
 598 /*************************************************************************
 599  *
 600  *      Function: sql_getvpdata
 601  *
 602  *      Purpose: Get any group check or reply pairs
 603  *
 604  *************************************************************************/
 605 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query)
 606 {
 607         SQL_ROW row;
 608         int     rows = 0;
 609
 610         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 611                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 612                 return -1;
 613         }
 614         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 615                 row = sqlsocket->row;
 616                 if (!row)
 617                         break;
 618                 if (sql_userparse(pair, row) != 0) {
 619                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 620                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 621                         return -1;
 622                 }
 623                 rows++;
 624         }
 625         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 626
 627         return rows;
 628 }
 629
 630 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 631 {
 632         FILE   *sqlfile = NULL;
 633
 634         if (inst->config->sqltrace) {
 635                 char buffer[8192];
 636
 637                 if (!radius_xlat(buffer, sizeof(buffer),
 638                                  inst->config->tracefile, request, NULL)) {
 639                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 640                          inst->config->xlat_name);
 641                   return;
 642                 }
 643
 644                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 645                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 646                                inst->config->xlat_name,
 647                                buffer);
 648                 } else {
 649                         int fd = fileno(sqlfile);
 650
 651                         rad_lockfd(fd, MAX_QUERY_LEN);
 652                         fputs(querystr, sqlfile);
 653                         fputs(";\n", sqlfile);
 654                         fclose(sqlfile); /* and release the lock */
 655                 }
 656         }
 657 }