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 #if HAVE_PTHREAD_H
  48 #include        <pthread.h>
  49 #endif
  50
  51 #include        "radiusd.h"
  52 #include        "conffile.h"
  53 #include        "rlm_sql.h"
  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         radlog(L_DBG, "rlm_sql (%s): Attempting to connect %s #%d",
  67                inst->config->xlat_name, inst->module->name, sqlsocket->id);
  68
  69         if ((inst->module->sql_init_socket)(sqlsocket, inst->config) < 0) {
  70                 radlog(L_CONS | L_ERR, "rlm_sql (%s): Failed to connect DB handle #%d", inst->config->xlat_name, sqlsocket->id);
  71                 inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  72                 sqlsocket->state = sockunconnected;
  73                 return(-1);
  74         } else {
  75                 radlog(L_DBG, "rlm_sql (%s): Connected new DB handle, #%d",
  76                        inst->config->xlat_name, sqlsocket->id);
  77                 sqlsocket->state = sockconnected;
  78                 return(0);
  79         }
  80 }
  81
  82
  83 /*************************************************************************
  84  *
  85  *      Function: sql_init_socketpool
  86  *
  87  *      Purpose: Connect to the sql server, if possible
  88  *
  89  *************************************************************************/
  90 int sql_init_socketpool(SQL_INST * inst)
  91 {
  92         int i, rcode;
  93         SQLSOCK *sqlsocket;
  94
  95         inst->connect_after = 0;
  96         inst->sqlpool = NULL;
  97
  98         for (i = 0; i < inst->config->num_sql_socks; i++) {
  99                 radlog(L_DBG, "rlm_sql (%s): starting %d",
 100                        inst->config->xlat_name, i);
 101
 102                 sqlsocket = rad_malloc(sizeof(SQLSOCK));
 103                 if (sqlsocket == NULL) {
 104                         return -1;
 105                 }
 106                 sqlsocket->conn = NULL;
 107                 sqlsocket->id = i;
 108                 sqlsocket->state = sockunconnected;
 109
 110 #if HAVE_PTHREAD_H
 111                 rcode = pthread_mutex_init(&sqlsocket->mutex,NULL);
 112                 if (rcode != 0) {
 113                         radlog(L_ERR, "rlm_sql: Failed to init lock: %s",
 114                                strerror(errno));
 115                         return 0;
 116                 }
 117 #endif
 118
 119                 if (time(NULL) > inst->connect_after) {
 120                         /* this sets the sqlsocket->state, and possibly sets inst->connect_after */
 121                         /* FIXME! check return code */
 122                         connect_single_socket(sqlsocket, inst);
 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         return 1;
 132 }
 133
 134 /*************************************************************************
 135  *
 136  *     Function: sql_poolfree
 137  *
 138  *     Purpose: Clean up and free sql pool
 139  *
 140  *************************************************************************/
 141 void sql_poolfree(SQL_INST * inst)
 142 {
 143         SQLSOCK *cur;
 144
 145         for (cur = inst->sqlpool; cur; cur = cur->next) {
 146                 sql_close_socket(inst, cur);
 147         }
 148 }
 149
 150
 151 /*************************************************************************
 152  *
 153  *      Function: sql_close_socket
 154  *
 155  *      Purpose: Close and free a sql sqlsocket
 156  *
 157  *************************************************************************/
 158 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket)
 159 {
 160         radlog(L_DBG, "rlm_sql (%s): Closing sqlsocket %d",
 161                inst->config->xlat_name, sqlsocket->id);
 162         (inst->module->sql_close)(sqlsocket, inst->config);
 163 #if HAVE_PTHREAD_H
 164         pthread_mutex_destroy(&sqlsocket->mutex);
 165 #endif
 166         free(sqlsocket);
 167         return 1;
 168 }
 169
 170
 171 /*************************************************************************
 172  *
 173  *      Function: sql_get_socket
 174  *
 175  *      Purpose: Return a SQL sqlsocket from the connection pool
 176  *
 177  *************************************************************************/
 178 SQLSOCK * sql_get_socket(SQL_INST * inst)
 179 {
 180         SQLSOCK *cur, *start;
 181         int tried_to_connect = 0;
 182         int unconnected = 0;
 183
 184         /*
 185          *      Start at the last place we left off.
 186          */
 187         start = inst->last_used;
 188         if (!start) start = inst->sqlpool;
 189
 190         cur = start;
 191
 192         while (cur) {
 193 #if HAVE_PTHREAD_H
 194                 /*
 195                  *      If this socket is in use by another thread,
 196                  *      skip it, and try another socket.
 197                  *
 198                  *      If it isn't used, then grab it ourselves.
 199                  */
 200                 if (pthread_mutex_trylock(&cur->mutex) != 0) {
 201                         goto next;
 202                 } /* else we now have the lock */
 203 #endif
 204
 205                 /*
 206                  *      If we happen upon an unconnected socket, and
 207                  *      this instance's grace period on
 208                  *      (re)connecting has expired, then try to
 209                  *      connect it.  This should be really rare.
 210                  */
 211                 if ((cur->state == sockunconnected) && (time(NULL) > inst->connect_after)) {
 212                         radlog(L_INFO, "rlm_sql (%s): Trying to (re)connect unconnected handle %d..", inst->config->xlat_name, cur->id);
 213                         tried_to_connect++;
 214                         connect_single_socket(cur, inst);
 215                 }
 216
 217                 /* if we still aren't connected, ignore this handle */
 218                 if (cur->state == sockunconnected) {
 219                         radlog(L_DBG, "rlm_sql (%s): Ignoring unconnected handle %d..", inst->config->xlat_name, cur->id);
 220                         unconnected++;
 221 #if HAVE_PTHREAD_H
 222                         pthread_mutex_unlock(&cur->mutex);
 223 #endif
 224                         goto next;
 225                 }
 226
 227                 /* should be connected, grab it */
 228                 radlog(L_DBG, "rlm_sql (%s): Reserving sql socket id: %d", inst->config->xlat_name, cur->id);
 229
 230                 if (unconnected != 0 || tried_to_connect != 0) {
 231                         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);
 232                 }
 233
 234                 /*
 235                  *      The socket is returned in the locked
 236                  *      state.
 237                  *
 238                  *      We also remember where we left off,
 239                  *      so that the next search can start from
 240                  *      here.
 241                  *
 242                  *      Note that multiple threads MAY over-write
 243                  *      the 'inst->last_used' variable.  This is OK,
 244                  *      as it's a pointer only used for reading.
 245                  */
 246                 inst->last_used = cur->next;
 247                 return cur;
 248
 249                 /* move along the list */
 250         next:
 251                 cur = cur->next;
 252
 253                 /*
 254                  *      Because we didnt start at the start, once we
 255                  *      hit the end of the linklist, we should go
 256                  *      back to the beginning and work toward the
 257                  *      middle!
 258                  */
 259                 if (!cur) {
 260                         cur = inst->sqlpool;
 261                 }
 262
 263                 /*
 264                  *      If we're at the socket we started
 265                  */
 266                 if (cur == start) {
 267                         break;
 268                 }
 269         }
 270
 271         /* We get here if every DB handle is unconnected and unconnectABLE */
 272         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);
 273         return NULL;
 274 }
 275
 276 /*************************************************************************
 277  *
 278  *      Function: sql_release_socket
 279  *
 280  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 281  *
 282  *************************************************************************/
 283 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket)
 284 {
 285 #if HAVE_PTHREAD_H
 286         pthread_mutex_unlock(&sqlsocket->mutex);
 287 #endif
 288
 289         radlog(L_DBG, "rlm_sql (%s): Released sql socket id: %d",
 290                inst->config->xlat_name, sqlsocket->id);
 291
 292         return 0;
 293 }
 294
 295
 296 /*************************************************************************
 297  *
 298  *      Function: sql_userparse
 299  *
 300  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 301  *
 302  *************************************************************************/
 303 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row, int querymode)
 304 {
 305         DICT_ATTR *attr;
 306         VALUE_PAIR *pair, *check;
 307         char *ptr;
 308         char buf[128];
 309         int pairmode = T_EOL;
 310
 311         if ((attr = dict_attrbyname(row[2])) == (DICT_ATTR *) NULL) {
 312                 radlog(L_ERR | L_CONS, "rlm_sql: unknown attribute %s",
 313                        row[2]);
 314                 return (-1);
 315         }
 316
 317         if (row[4] != NULL && strlen(row[4]) > 0) {
 318                 ptr = row[4];
 319                 pairmode = gettoken(&ptr, buf, sizeof(buf));
 320         } else {
 321                 /*
 322                  *  'op' fields of NULL are a plague, and a bane on the
 323                  *  existence of mankind.
 324                  */
 325                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 326                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 327         }
 328         if (pairmode <= T_EOL) pairmode = T_OP_CMP_EQ;
 329
 330         /*
 331          * If attribute is already there, skip it because we checked usercheck first
 332          * and we want user settings to over ride group settings
 333          */
 334         if (pairmode != T_OP_ADD && (check = pairfind(*first_pair, attr->attr)) != NULL &&
 335 #ifdef ASCEND_BINARY
 336                         attr->type != PW_TYPE_ABINARY &&
 337 #endif
 338                         querymode == PW_VP_GROUPDATA)
 339                 return 0;
 340
 341         pair = pairmake(row[2], row[3], pairmode);
 342         pairadd(first_pair, pair);
 343
 344         return 0;
 345 }
 346
 347
 348 /*************************************************************************
 349  *
 350  *      Function: rlm_sql_fetch_row
 351  *
 352  *      Purpose: call the module's sql_fetch_row and implement re-connect
 353  *
 354  *************************************************************************/
 355 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 356 {
 357         int ret;
 358
 359         ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 360
 361         if (ret == SQL_DOWN) {
 362                 /* close the socket that failed */
 363                 (inst->module->sql_close)(sqlsocket, inst->config);
 364
 365                 /* reconnect the socket */
 366                 if (connect_single_socket(sqlsocket, inst) < 0) {
 367                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 368                         return -1;
 369                 }
 370
 371                 /* retry the query on the newly connected socket */
 372                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 373
 374                 if (ret) {
 375                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 376                                inst->config->xlat_name);
 377                         return -1;
 378                 }
 379         }
 380
 381         return ret;
 382 }
 383
 384 /*************************************************************************
 385  *
 386  *      Function: rlm_sql_query
 387  *
 388  *      Purpose: call the module's sql_query and implement re-connect
 389  *
 390  *************************************************************************/
 391 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 392 {
 393         int ret;
 394
 395         /*
 396          *      If there's no query, return an error.
 397          */
 398         if (!query || !*query) {
 399                 return -1;
 400         }
 401
 402         ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 403
 404         if (ret == SQL_DOWN) {
 405                 /* close the socket that failed */
 406                 (inst->module->sql_close)(sqlsocket, inst->config);
 407
 408                 /* reconnect the socket */
 409                 if (connect_single_socket(sqlsocket, inst) < 0) {
 410                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 411                         return -1;
 412                 }
 413
 414                 /* retry the query on the newly connected socket */
 415                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 416
 417                 if (ret) {
 418                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 419                                inst->config->xlat_name);
 420                         return -1;
 421                 }
 422         }
 423
 424         return ret;
 425 }
 426
 427 /*************************************************************************
 428  *
 429  *      Function: rlm_sql_select_query
 430  *
 431  *      Purpose: call the module's sql_select_query and implement re-connect
 432  *
 433  *************************************************************************/
 434 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 435 {
 436         int ret;
 437
 438         /*
 439          *      If there's no query, return an error.
 440          */
 441         if (!query || !*query) {
 442                 return -1;
 443         }
 444
 445         ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 446
 447         if (ret == SQL_DOWN) {
 448                 /* close the socket that failed */
 449                 (inst->module->sql_close)(sqlsocket, inst->config);
 450
 451                 /* reconnect the socket */
 452                 if (connect_single_socket(sqlsocket, inst) < 0) {
 453                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 454                         return -1;
 455                 }
 456
 457                 /* retry the query on the newly connected socket */
 458                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 459
 460                 if (ret) {
 461                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 462                                inst->config->xlat_name);
 463                         return -1;
 464                 }
 465         }
 466
 467         return ret;
 468 }
 469
 470
 471 /*************************************************************************
 472  *
 473  *      Function: sql_getvpdata
 474  *
 475  *      Purpose: Get any group check or reply pairs
 476  *
 477  *************************************************************************/
 478 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query, int mode)
 479 {
 480         SQL_ROW row;
 481         int     rows = 0;
 482
 483         /*
 484          *      If there's no query, return an error.
 485          */
 486         if (!query || !*query) {
 487                 return -1;
 488         }
 489
 490         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 491                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 492                 return -1;
 493         }
 494         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 495                 row = sqlsocket->row;
 496                 if (!row)
 497                         break;
 498                 if (sql_userparse(pair, row, mode) != 0) {
 499                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 500                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 501                         return -1;
 502                 }
 503                 rows++;
 504         }
 505         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 506
 507         return rows;
 508 }
 509
 510 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 511 {
 512         FILE   *sqlfile = NULL;
 513
 514         if (inst->config->sqltrace) {
 515                 char buffer[8192];
 516
 517                 if (!radius_xlat(buffer, sizeof(buffer),
 518                                  inst->config->tracefile, request, NULL)) {
 519                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 520                          inst->config->xlat_name);
 521                   return;
 522                 }
 523
 524                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 525                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 526                                inst->config->xlat_name,
 527                                buffer);
 528                 } else {
 529                         int fd = fileno(sqlfile);
 530
 531                         rad_lockfd(fd, MAX_QUERY_LEN);
 532                         fputs(querystr, sqlfile);
 533                         fputs(";\n", sqlfile);
 534                         fclose(sqlfile); /* and release the lock */
 535                 }
 536         }
 537 }