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