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         char value[256];
 334         LRAD_TOKEN xlat, pairmode = T_EOL;
 335
 336         if ((attr = dict_attrbyname(row[2])) == (DICT_ATTR *) NULL) {
 337                 radlog(L_ERR | L_CONS, "rlm_sql: unknown attribute %s",
 338                        row[2]);
 339                 return (-1);
 340         }
 341
 342         if (row[4] != NULL && strlen(row[4]) > 0) {
 343                 ptr = row[4];
 344                 pairmode = gettoken(&ptr, buf, sizeof(buf));
 345         } else {
 346                 /*
 347                  *  'op' fields of NULL are a plague, and a bane on the
 348                  *  existence of mankind.
 349                  */
 350                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 351                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 352         }
 353         if (pairmode <= T_EOL) pairmode = T_OP_CMP_EQ;
 354
 355         /*
 356          * If attribute is already there, skip it because we checked usercheck first
 357          * and we want user settings to over ride group settings
 358          */
 359         if (pairmode != T_OP_ADD && (check = pairfind(*first_pair, attr->attr)) != NULL &&
 360 #ifdef ASCEND_BINARY
 361                         attr->type != PW_TYPE_ABINARY &&
 362 #endif
 363                         querymode == PW_VP_GROUPDATA)
 364                 return 0;
 365
 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][0] == '\'') ||
 371              (row[3][0] == '`') ||
 372              (row[3][0] == '"')) &&
 373             (row[3][0] == row[3][strlen(row[3])-1])) {
 374
 375                 ptr = row[3];
 376                 xlat = gettoken(&ptr, value, sizeof(value));
 377                 switch (xlat) {
 378                         /*
 379                          *      Make the full pair now.
 380                          */
 381                 default:
 382                         pair = pairmake(row[2], row[3], pairmode);
 383                         break;
 384
 385                 case T_SINGLE_QUOTED_STRING:
 386                 case T_DOUBLE_QUOTED_STRING:
 387                         pair = pairmake(row[2], value, pairmode);
 388                         break;
 389
 390                         /*
 391                          *      Mark the pair to be allocated later.
 392                          */
 393                 case T_BACK_QUOTED_STRING:
 394                         pair = pairmake(row[2], NULL, pairmode);
 395                         if (pair) {
 396                                 pair->flags.do_xlat = 1;
 397                                 strNcpy(pair->strvalue, value, sizeof(pair->strvalue));
 398                                 pair->length = 0;
 399                         }
 400                 }
 401         } else {
 402                 /*
 403                  * String starts and ends differently. Take it literally
 404                  * */
 405                 pair = pairmake(row[2], row[3], pairmode);
 406         }
 407         pairadd(first_pair, pair);
 408
 409         return 0;
 410 }
 411
 412
 413 /*************************************************************************
 414  *
 415  *      Function: rlm_sql_fetch_row
 416  *
 417  *      Purpose: call the module's sql_fetch_row and implement re-connect
 418  *
 419  *************************************************************************/
 420 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 421 {
 422         int ret;
 423
 424         if (sqlsocket->conn) {
 425                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 426         } else {
 427                 ret = SQL_DOWN;
 428         }
 429
 430         if (ret == SQL_DOWN) {
 431                 /* close the socket that failed, but only if it was open */
 432                 if (sqlsocket->conn) {
 433                         (inst->module->sql_close)(sqlsocket, inst->config);
 434                 }
 435
 436                 /* reconnect the socket */
 437                 if (connect_single_socket(sqlsocket, inst) < 0) {
 438                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 439                         return -1;
 440                 }
 441
 442                 /* retry the query on the newly connected socket */
 443                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 444
 445                 if (ret) {
 446                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 447                                inst->config->xlat_name);
 448                         return -1;
 449                 }
 450         }
 451
 452         return ret;
 453 }
 454
 455 /*************************************************************************
 456  *
 457  *      Function: rlm_sql_query
 458  *
 459  *      Purpose: call the module's sql_query and implement re-connect
 460  *
 461  *************************************************************************/
 462 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 463 {
 464         int ret;
 465
 466         /*
 467          *      If there's no query, return an error.
 468          */
 469         if (!query || !*query) {
 470                 return -1;
 471         }
 472
 473         ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 474
 475         if (ret == SQL_DOWN) {
 476                 /* close the socket that failed */
 477                 (inst->module->sql_close)(sqlsocket, inst->config);
 478
 479                 /* reconnect the socket */
 480                 if (connect_single_socket(sqlsocket, inst) < 0) {
 481                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 482                         return -1;
 483                 }
 484
 485                 /* retry the query on the newly connected socket */
 486                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 487
 488                 if (ret) {
 489                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 490                                inst->config->xlat_name);
 491                         return -1;
 492                 }
 493         }
 494
 495         return ret;
 496 }
 497
 498 /*************************************************************************
 499  *
 500  *      Function: rlm_sql_select_query
 501  *
 502  *      Purpose: call the module's sql_select_query and implement re-connect
 503  *
 504  *************************************************************************/
 505 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 506 {
 507         int ret;
 508
 509         /*
 510          *      If there's no query, return an error.
 511          */
 512         if (!query || !*query) {
 513                 return -1;
 514         }
 515
 516         ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 517
 518         if (ret == SQL_DOWN) {
 519                 /* close the socket that failed */
 520                 (inst->module->sql_close)(sqlsocket, inst->config);
 521
 522                 /* reconnect the socket */
 523                 if (connect_single_socket(sqlsocket, inst) < 0) {
 524                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 525                         return -1;
 526                 }
 527
 528                 /* retry the query on the newly connected socket */
 529                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 530
 531                 if (ret) {
 532                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 533                                inst->config->xlat_name);
 534                         return -1;
 535                 }
 536         }
 537
 538         return ret;
 539 }
 540
 541
 542 /*************************************************************************
 543  *
 544  *      Function: sql_getvpdata
 545  *
 546  *      Purpose: Get any group check or reply pairs
 547  *
 548  *************************************************************************/
 549 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query, int mode)
 550 {
 551         SQL_ROW row;
 552         int     rows = 0;
 553
 554         /*
 555          *      If there's no query, return an error.
 556          */
 557         if (!query || !*query) {
 558                 return -1;
 559         }
 560
 561         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 562                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 563                 return -1;
 564         }
 565         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 566                 row = sqlsocket->row;
 567                 if (!row)
 568                         break;
 569                 if (sql_userparse(pair, row, mode) != 0) {
 570                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 571                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 572                         return -1;
 573                 }
 574                 rows++;
 575         }
 576         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 577
 578         return rows;
 579 }
 580
 581 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 582 {
 583         FILE   *sqlfile = NULL;
 584
 585         if (inst->config->sqltrace) {
 586                 char buffer[8192];
 587
 588                 if (!radius_xlat(buffer, sizeof(buffer),
 589                                  inst->config->tracefile, request, NULL)) {
 590                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 591                          inst->config->xlat_name);
 592                   return;
 593                 }
 594
 595                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 596                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 597                                inst->config->xlat_name,
 598                                buffer);
 599                 } else {
 600                         int fd = fileno(sqlfile);
 601
 602                         rad_lockfd(fd, MAX_QUERY_LEN);
 603                         fputs(querystr, sqlfile);
 604                         fputs(";\n", sqlfile);
 605                         fclose(sqlfile); /* and release the lock */
 606                 }
 607         }
 608 }