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