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