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                 if ((operator < T_OP_ADD) ||
 379                     (operator > T_OP_CMP_EQ)) {
 380                         radlog(L_ERR, "rlm_sql: Invalid operator \"%s\" for attribute %s", row[4], row[2]);
 381                         return -1;
 382                 }
 383
 384         } else {
 385                 /*
 386                  *  Complain about empty or invalid 'op' field
 387                  */
 388                 operator = T_OP_CMP_EQ;
 389                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 390                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 391         }
 392
 393         /*
 394          *      The 'Value' field may be empty or NULL
 395          */
 396         value = row[3];
 397         /*
 398          *      If we have a new-style quoted string, where the
 399          *      *entire* string is quoted, do xlat's.
 400          */
 401         if (row[3] != NULL &&
 402            ((row[3][0] == '\'') || (row[3][0] == '`') || (row[3][0] == '"')) &&
 403            (row[3][0] == row[3][strlen(row[3])-1])) {
 404
 405                 token = gettoken(&value, buf, sizeof(buf));
 406                 switch (token) {
 407                         /*
 408                          *      Take the unquoted string.
 409                          */
 410                 case T_SINGLE_QUOTED_STRING:
 411                 case T_DOUBLE_QUOTED_STRING:
 412                         value = buf;
 413                         break;
 414
 415                         /*
 416                          *      Mark the pair to be allocated later.
 417                          */
 418                 case T_BACK_QUOTED_STRING:
 419                         value = NULL;
 420                         do_xlat = 1;
 421                         break;
 422
 423                         /*
 424                          *      Keep the original string.
 425                          */
 426                 default:
 427                         value = row[3];
 428                         break;
 429                 }
 430         }
 431
 432         /*
 433          *      Create the pair
 434          */
 435         pair = pairmake(row[2], value, operator);
 436         if (pair == NULL) {
 437                 radlog(L_ERR, "rlm_sql: Failed to create the pair: %s", fr_strerror());
 438                 return -1;
 439         }
 440         if (do_xlat) {
 441                 pair->flags.do_xlat = 1;
 442                 strlcpy(pair->vp_strvalue, buf, sizeof(pair->vp_strvalue));
 443                 pair->length = 0;
 444         }
 445
 446         /*
 447          *      Add the pair into the packet
 448          */
 449         pairadd(first_pair, pair);
 450         return 0;
 451 }
 452
 453
 454 /*************************************************************************
 455  *
 456  *      Function: rlm_sql_fetch_row
 457  *
 458  *      Purpose: call the module's sql_fetch_row and implement re-connect
 459  *
 460  *************************************************************************/
 461 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 462 {
 463         int ret;
 464
 465         if (sqlsocket->conn) {
 466                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 467         } else {
 468                 ret = SQL_DOWN;
 469         }
 470
 471         if (ret == SQL_DOWN) {
 472                 /* close the socket that failed, but only if it was open */
 473                 if (sqlsocket->conn) {
 474                         (inst->module->sql_close)(sqlsocket, inst->config);
 475                 }
 476
 477                 /* reconnect the socket */
 478                 if (connect_single_socket(sqlsocket, inst) < 0) {
 479                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 480                         return -1;
 481                 }
 482
 483                 /* retry the query on the newly connected socket */
 484                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 485
 486                 if (ret) {
 487                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 488                                inst->config->xlat_name);
 489                         return -1;
 490                 }
 491         }
 492
 493         return ret;
 494 }
 495
 496 /*************************************************************************
 497  *
 498  *      Function: rlm_sql_query
 499  *
 500  *      Purpose: call the module's sql_query and implement re-connect
 501  *
 502  *************************************************************************/
 503 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 504 {
 505         int ret;
 506
 507         /*
 508          *      If there's no query, return an error.
 509          */
 510         if (!query || !*query) {
 511                 return -1;
 512         }
 513
 514         if (sqlsocket->conn) {
 515                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 516         } else {
 517                 ret = SQL_DOWN;
 518         }
 519
 520         if (ret == SQL_DOWN) {
 521                 /* close the socket that failed */
 522                 if (sqlsocket->state == sockconnected) {
 523                         (inst->module->sql_close)(sqlsocket, inst->config);
 524                 }
 525
 526                 /* reconnect the socket */
 527                 if (connect_single_socket(sqlsocket, inst) < 0) {
 528                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 529                         return -1;
 530                 }
 531
 532                 /* retry the query on the newly connected socket */
 533                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 534
 535                 if (ret) {
 536                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 537                                inst->config->xlat_name);
 538                         return -1;
 539                 }
 540         }
 541
 542         return ret;
 543 }
 544
 545 /*************************************************************************
 546  *
 547  *      Function: rlm_sql_select_query
 548  *
 549  *      Purpose: call the module's sql_select_query and implement re-connect
 550  *
 551  *************************************************************************/
 552 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 553 {
 554         int ret;
 555
 556         /*
 557          *      If there's no query, return an error.
 558          */
 559         if (!query || !*query) {
 560                 return -1;
 561         }
 562
 563         if (sqlsocket->conn) {
 564                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config,
 565                                                        query);
 566         } else {
 567                 ret = SQL_DOWN;
 568         }
 569
 570         if (ret == SQL_DOWN) {
 571                 /* close the socket that failed */
 572                 if (sqlsocket->state == sockconnected) {
 573                         (inst->module->sql_close)(sqlsocket, inst->config);
 574                 }
 575
 576                 /* reconnect the socket */
 577                 if (connect_single_socket(sqlsocket, inst) < 0) {
 578                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 579                         return -1;
 580                 }
 581
 582                 /* retry the query on the newly connected socket */
 583                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 584
 585                 if (ret) {
 586                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 587                                inst->config->xlat_name);
 588                         return -1;
 589                 }
 590         }
 591
 592         return ret;
 593 }
 594
 595
 596 /*************************************************************************
 597  *
 598  *      Function: sql_getvpdata
 599  *
 600  *      Purpose: Get any group check or reply pairs
 601  *
 602  *************************************************************************/
 603 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query)
 604 {
 605         SQL_ROW row;
 606         int     rows = 0;
 607
 608         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 609                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 610                 return -1;
 611         }
 612         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 613                 row = sqlsocket->row;
 614                 if (!row)
 615                         break;
 616                 if (sql_userparse(pair, row) != 0) {
 617                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 618                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 619                         return -1;
 620                 }
 621                 rows++;
 622         }
 623         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 624
 625         return rows;
 626 }
 627
 628 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 629 {
 630         FILE   *sqlfile = NULL;
 631
 632         if (inst->config->sqltrace) {
 633                 char buffer[8192];
 634
 635                 if (!radius_xlat(buffer, sizeof(buffer),
 636                                  inst->config->tracefile, request, NULL)) {
 637                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 638                          inst->config->xlat_name);
 639                   return;
 640                 }
 641
 642                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 643                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 644                                inst->config->xlat_name,
 645                                buffer);
 646                 } else {
 647                         int fd = fileno(sqlfile);
 648
 649                         rad_lockfd(fd, MAX_QUERY_LEN);
 650                         fputs(querystr, sqlfile);
 651                         fputs(";\n", sqlfile);
 652                         fclose(sqlfile); /* and release the lock */
 653                 }
 654         }
 655 }