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