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                 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_DBG, "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                         (inst->module->sql_close)(cur, inst->config);
 230                         cur->state = sockunconnected;
 231                         goto reconnect;
 232                 }
 233
 234                 /*
 235                  *      If we have performed too many queries over this
 236                  *      socket, then close it.
 237                  */
 238                 if (inst->config->max_queries && (cur->state == sockconnected) &&
 239                     (cur->queries >= inst->config->max_queries)) {
 240                         (inst->module->sql_close)(cur, inst->config);
 241                         cur->state = sockunconnected;
 242                         goto reconnect;
 243                 }
 244
 245                 /*
 246                  *      If we happen upon an unconnected socket, and
 247                  *      this instance's grace period on
 248                  *      (re)connecting has expired, then try to
 249                  *      connect it.  This should be really rare.
 250                  */
 251                 if ((cur->state == sockunconnected) && (now > inst->connect_after)) {
 252                 reconnect:
 253                         radlog(L_INFO, "rlm_sql (%s): Trying to (re)connect unconnected handle %d..", inst->config->xlat_name, cur->id);
 254                         tried_to_connect++;
 255                         connect_single_socket(cur, inst);
 256                 }
 257
 258                 /* if we still aren't connected, ignore this handle */
 259                 if (cur->state == sockunconnected) {
 260                         radlog(L_DBG, "rlm_sql (%s): Ignoring unconnected handle %d..", inst->config->xlat_name, cur->id);
 261                         unconnected++;
 262 #ifdef HAVE_PTHREAD_H
 263                         pthread_mutex_unlock(&cur->mutex);
 264 #endif
 265                         goto next;
 266                 }
 267
 268                 /* should be connected, grab it */
 269                 radlog(L_DBG, "rlm_sql (%s): Reserving sql socket id: %d", inst->config->xlat_name, cur->id);
 270
 271                 if (unconnected != 0 || tried_to_connect != 0) {
 272                         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);
 273                 }
 274
 275                 /*
 276                  *      The socket is returned in the locked
 277                  *      state.
 278                  *
 279                  *      We also remember where we left off,
 280                  *      so that the next search can start from
 281                  *      here.
 282                  *
 283                  *      Note that multiple threads MAY over-write
 284                  *      the 'inst->last_used' variable.  This is OK,
 285                  *      as it's a pointer only used for reading.
 286                  */
 287                 inst->last_used = cur->next;
 288                 cur->queries++;
 289                 return cur;
 290
 291                 /* move along the list */
 292         next:
 293                 cur = cur->next;
 294
 295                 /*
 296                  *      Because we didnt start at the start, once we
 297                  *      hit the end of the linklist, we should go
 298                  *      back to the beginning and work toward the
 299                  *      middle!
 300                  */
 301                 if (!cur) {
 302                         cur = inst->sqlpool;
 303                 }
 304
 305                 /*
 306                  *      If we're at the socket we started
 307                  */
 308                 if (cur == start) {
 309                         break;
 310                 }
 311         }
 312
 313         /*
 314          *      Suppress most of the log messages.  We don't want to
 315          *      flood the log with this message for EVERY packet.
 316          *      Instead, write to the log only once a second or so.
 317          *
 318          *      This code has race conditions when threaded, but the
 319          *      only result is that a few more messages are logged.
 320          */
 321         if (now <= last_logged_failure) return NULL;
 322         last_logged_failure = now;
 323
 324         /* We get here if every DB handle is unconnected and unconnectABLE */
 325         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);
 326         return NULL;
 327 }
 328
 329 /*************************************************************************
 330  *
 331  *      Function: sql_release_socket
 332  *
 333  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 334  *
 335  *************************************************************************/
 336 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket)
 337 {
 338 #ifdef HAVE_PTHREAD_H
 339         pthread_mutex_unlock(&sqlsocket->mutex);
 340 #endif
 341
 342         radlog(L_DBG, "rlm_sql (%s): Released sql socket id: %d",
 343                inst->config->xlat_name, sqlsocket->id);
 344
 345         return 0;
 346 }
 347
 348
 349 /*************************************************************************
 350  *
 351  *      Function: sql_userparse
 352  *
 353  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 354  *
 355  *************************************************************************/
 356 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row)
 357 {
 358         VALUE_PAIR *pair;
 359         const char *ptr, *value;
 360         char buf[MAX_STRING_LEN];
 361         char do_xlat = 0;
 362         FR_TOKEN token, operator = T_EOL;
 363
 364         /*
 365          *      Verify the 'Attribute' field
 366          */
 367         if (row[2] == NULL || row[2][0] == '\0') {
 368                 radlog(L_ERR, "rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row.");
 369                 return -1;
 370         }
 371
 372         /*
 373          *      Verify the 'op' field
 374          */
 375         if (row[4] != NULL && row[4][0] != '\0') {
 376                 ptr = row[4];
 377                 operator = gettoken(&ptr, buf, sizeof(buf));
 378         }
 379         if (operator <= T_EOL) {
 380                 /*
 381                  *  Complain about empty or invalid 'op' field
 382                  */
 383                 operator = T_OP_CMP_EQ;
 384                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 385                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 386         }
 387
 388         /*
 389          *      The 'Value' field may be empty or NULL
 390          */
 391         value = row[3];
 392         /*
 393          *      If we have a new-style quoted string, where the
 394          *      *entire* string is quoted, do xlat's.
 395          */
 396         if (row[3] != NULL &&
 397            ((row[3][0] == '\'') || (row[3][0] == '`') || (row[3][0] == '"')) &&
 398            (row[3][0] == row[3][strlen(row[3])-1])) {
 399
 400                 token = gettoken(&value, buf, sizeof(buf));
 401                 switch (token) {
 402                         /*
 403                          *      Take the unquoted string.
 404                          */
 405                 case T_SINGLE_QUOTED_STRING:
 406                 case T_DOUBLE_QUOTED_STRING:
 407                         value = buf;
 408                         break;
 409
 410                         /*
 411                          *      Mark the pair to be allocated later.
 412                          */
 413                 case T_BACK_QUOTED_STRING:
 414                         value = NULL;
 415                         do_xlat = 1;
 416                         break;
 417
 418                         /*
 419                          *      Keep the original string.
 420                          */
 421                 default:
 422                         value = row[3];
 423                         break;
 424                 }
 425         }
 426
 427         /*
 428          *      Create the pair
 429          */
 430         pair = pairmake(row[2], value, operator);
 431         if (pair == NULL) {
 432                 radlog(L_ERR, "rlm_sql: Failed to create the pair: %s", fr_strerror());
 433                 return -1;
 434         }
 435         if (do_xlat) {
 436                 pair->flags.do_xlat = 1;
 437                 strlcpy(pair->vp_strvalue, buf, sizeof(pair->vp_strvalue));
 438                 pair->length = 0;
 439         }
 440
 441         /*
 442          *      Add the pair into the packet
 443          */
 444         pairadd(first_pair, pair);
 445         return 0;
 446 }
 447
 448
 449 /*************************************************************************
 450  *
 451  *      Function: rlm_sql_fetch_row
 452  *
 453  *      Purpose: call the module's sql_fetch_row and implement re-connect
 454  *
 455  *************************************************************************/
 456 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 457 {
 458         int ret;
 459
 460         if (sqlsocket->conn) {
 461                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 462         } else {
 463                 ret = SQL_DOWN;
 464         }
 465
 466         if (ret == SQL_DOWN) {
 467                 /* close the socket that failed, but only if it was open */
 468                 if (sqlsocket->conn) {
 469                         (inst->module->sql_close)(sqlsocket, inst->config);
 470                 }
 471
 472                 /* reconnect the socket */
 473                 if (connect_single_socket(sqlsocket, inst) < 0) {
 474                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 475                         return -1;
 476                 }
 477
 478                 /* retry the query on the newly connected socket */
 479                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 480
 481                 if (ret) {
 482                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 483                                inst->config->xlat_name);
 484                         return -1;
 485                 }
 486         }
 487
 488         return ret;
 489 }
 490
 491 /*************************************************************************
 492  *
 493  *      Function: rlm_sql_query
 494  *
 495  *      Purpose: call the module's sql_query and implement re-connect
 496  *
 497  *************************************************************************/
 498 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 499 {
 500         int ret;
 501
 502         /*
 503          *      If there's no query, return an error.
 504          */
 505         if (!query || !*query) {
 506                 return -1;
 507         }
 508
 509         ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 510
 511         if (ret == SQL_DOWN) {
 512                 /* close the socket that failed */
 513                 if (sqlsocket->state == sockconnected) {
 514                         (inst->module->sql_close)(sqlsocket, inst->config);
 515                 }
 516
 517                 /* reconnect the socket */
 518                 if (connect_single_socket(sqlsocket, inst) < 0) {
 519                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 520                         return -1;
 521                 }
 522
 523                 /* retry the query on the newly connected socket */
 524                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 525
 526                 if (ret) {
 527                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 528                                inst->config->xlat_name);
 529                         return -1;
 530                 }
 531         }
 532
 533         return ret;
 534 }
 535
 536 /*************************************************************************
 537  *
 538  *      Function: rlm_sql_select_query
 539  *
 540  *      Purpose: call the module's sql_select_query and implement re-connect
 541  *
 542  *************************************************************************/
 543 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 544 {
 545         int ret;
 546
 547         /*
 548          *      If there's no query, return an error.
 549          */
 550         if (!query || !*query) {
 551                 return -1;
 552         }
 553
 554         ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 555
 556         if (ret == SQL_DOWN) {
 557                 /* close the socket that failed */
 558                 if (sqlsocket->state == sockconnected) {
 559                         (inst->module->sql_close)(sqlsocket, inst->config);
 560                 }
 561
 562                 /* reconnect the socket */
 563                 if (connect_single_socket(sqlsocket, inst) < 0) {
 564                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 565                         return -1;
 566                 }
 567
 568                 /* retry the query on the newly connected socket */
 569                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 570
 571                 if (ret) {
 572                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 573                                inst->config->xlat_name);
 574                         return -1;
 575                 }
 576         }
 577
 578         return ret;
 579 }
 580
 581
 582 /*************************************************************************
 583  *
 584  *      Function: sql_getvpdata
 585  *
 586  *      Purpose: Get any group check or reply pairs
 587  *
 588  *************************************************************************/
 589 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query)
 590 {
 591         SQL_ROW row;
 592         int     rows = 0;
 593
 594         /*
 595          *      If there's no query, return an error.
 596          */
 597         if (!query || !*query) {
 598                 return -1;
 599         }
 600
 601         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 602                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 603                 return -1;
 604         }
 605         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 606                 row = sqlsocket->row;
 607                 if (!row)
 608                         break;
 609                 if (sql_userparse(pair, row) != 0) {
 610                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 611                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 612                         return -1;
 613                 }
 614                 rows++;
 615         }
 616         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 617
 618         return rows;
 619 }
 620
 621 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 622 {
 623         FILE   *sqlfile = NULL;
 624
 625         if (inst->config->sqltrace) {
 626                 char buffer[8192];
 627
 628                 if (!radius_xlat(buffer, sizeof(buffer),
 629                                  inst->config->tracefile, request, NULL)) {
 630                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 631                          inst->config->xlat_name);
 632                   return;
 633                 }
 634
 635                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 636                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 637                                inst->config->xlat_name,
 638                                buffer);
 639                 } else {
 640                         int fd = fileno(sqlfile);
 641
 642                         rad_lockfd(fd, MAX_QUERY_LEN);
 643                         fputs(querystr, sqlfile);
 644                         fputs(";\n", sqlfile);
 645                         fclose(sqlfile); /* and release the lock */
 646                 }
 647         }
 648 }