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