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