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 "sockunconnected"
  58  * and set a grace period, during which we won't try connecting again (to prevent unduly
  59  * lagging the server and being impolite to a DB server that may be having other
  60  * issues).  If successful in connecting, set state to sockconnected.   - chad
  61  */
  62 static int connect_single_socket(SQLSOCK *sqlsocket, SQL_INST *inst) {
  63         if ((inst->module->sql_init_socket)(sqlsocket, inst->config) < 0) {
  64                 radlog(L_CONS | L_ERR, "rlm_sql:  Failed to connect DB handle #%d", sqlsocket->id);
  65                 inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  66                 sqlsocket->state = sockunconnected;
  67                 return(-1);
  68         } else {
  69                 radlog(L_DBG, "rlm_sql:  Connected new DB handle, #%d", sqlsocket->id);
  70                 sqlsocket->state = sockconnected;
  71                 return(0);
  72         }
  73 }
  74
  75
  76 /*************************************************************************
  77  *
  78  *      Function: sql_init_socketpool
  79  *
  80  *      Purpose: Connect to the sql server, if possible
  81  *
  82  *************************************************************************/
  83 int sql_init_socketpool(SQL_INST * inst) {
  84
  85         SQLSOCK *sqlsocket;
  86         int     i;
  87
  88         inst->connect_after = 0;
  89         inst->used = 0;
  90         inst->sqlpool = NULL;
  91
  92         for (i = 0; i < inst->config->num_sql_socks; i++) {
  93
  94                 sqlsocket = rad_malloc(sizeof(SQLSOCK));
  95                 if (sqlsocket == NULL) {
  96                         return -1;
  97                 }
  98                 sqlsocket->conn = NULL;
  99                 sqlsocket->id = i;
 100                 sqlsocket->state = sockunconnected;
 101
 102 #if HAVE_PTHREAD_H
 103                 sqlsocket->semaphore = (sem_t *) rad_malloc(sizeof(sem_t));
 104                 sem_init(sqlsocket->semaphore, 0, SQLSOCK_UNLOCKED);
 105 #else
 106                 sqlsocket->in_use = 0;
 107 #endif
 108
 109                 if (time(NULL) > inst->connect_after) {
 110                         /* this sets the sqlsocket->state, and possibly sets inst->connect_after */
 111                         connect_single_socket(sqlsocket, inst);
 112                 }
 113
 114                 /* Add this socket to the list of sockets */
 115                 sqlsocket->next = inst->sqlpool;
 116                 inst->sqlpool = sqlsocket;
 117         }
 118
 119         return 1;
 120 }
 121
 122 /*************************************************************************
 123  *
 124  *     Function: sql_poolfree
 125  *
 126  *     Purpose: Clean up and free sql pool
 127  *
 128  *************************************************************************/
 129 void sql_poolfree(SQL_INST * inst) {
 130
 131         SQLSOCK *cur;
 132
 133         for (cur = inst->sqlpool; cur; cur = cur->next) {
 134                 sql_close_socket(inst, cur);
 135         }
 136 #if HAVE_PTHREAD_H
 137         pthread_mutex_destroy(inst->lock);
 138         pthread_cond_destroy(inst->notfull);
 139 #endif
 140 }
 141
 142
 143 /*************************************************************************
 144  *
 145  *      Function: sql_close_socket
 146  *
 147  *      Purpose: Close and free a sql sqlsocket
 148  *
 149  *************************************************************************/
 150 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket) {
 151
 152         radlog(L_DBG, "rlm_sql: Closing sqlsocket %d", sqlsocket->id);
 153         (inst->module->sql_close)(sqlsocket, inst->config);
 154 #if HAVE_PTHREAD_H
 155         sem_destroy(sqlsocket->semaphore);
 156 #endif
 157         free(sqlsocket);
 158         return 1;
 159 }
 160
 161
 162 /*************************************************************************
 163  *
 164  *      Function: sql_get_socket
 165  *
 166  *      Purpose: Return a SQL sqlsocket from the connection pool
 167  *
 168  *************************************************************************/
 169 SQLSOCK * sql_get_socket(SQL_INST * inst) {
 170         SQLSOCK *cur;
 171         struct timeval timeout;
 172         int tried_to_connect = 0;
 173
 174 #if HAVE_PTHREAD_H
 175         pthread_mutex_lock(inst->lock);
 176 #endif
 177         while (inst->used == inst->config->num_sql_socks) {
 178                 radlog(L_DBG, "rlm_sql: Waiting for open sql socket");
 179 #if HAVE_PTHREAD_H
 180                 pthread_cond_wait(inst->notfull, inst->lock);
 181 #else
 182                 /* this should be portable... */
 183                 timeout.tv_sec = 0;
 184                 timeout.tv_usec = 200;
 185                 select(0, NULL, NULL, NULL, &timeout)
 186 #endif
 187         }
 188
 189         for (cur = inst->sqlpool; cur; cur = cur->next) {
 190
 191                 /* if we happen upon an unconnected socket, and this instance's grace
 192                  * period on (re)connecting has expired, then try to connect it.  This
 193                  * should be really rare.  - chad
 194                  */
 195                 if ((cur->state == sockunconnected) && (time(NULL) > inst->connect_after)) {
 196                         tried_to_connect = 1;
 197                         radlog(L_INFO, "rlm_sql: Trying to (re)connect an unconnected handle...");
 198                         connect_single_socket(cur, inst);
 199                 }
 200
 201                 /* if we still aren't connected, ignore this handle */
 202                 if (cur->state == sockunconnected) {
 203                         radlog(L_DBG, "rlm_sql: Ignoring unconnected handle");
 204                         continue;
 205                 }
 206
 207 #if HAVE_PTHREAD_H
 208                 if (sem_trywait(cur->semaphore) == 0) {
 209 #else
 210                 if (cur->in_use == SQLSOCK_UNLOCKED) {
 211 #endif
 212                         (inst->used)++;
 213 #if HAVE_PTHREAD_H
 214                         pthread_mutex_unlock(inst->lock);
 215 #else
 216                         cur->in_use = SQLSOCK_LOCKED;
 217 #endif
 218                         radlog(L_DBG, "rlm_sql: Reserving sql socket id: %d", cur->id);
 219                         return cur;
 220                 }
 221         }
 222
 223 #if HAVE_PTHREAD_H
 224         pthread_mutex_unlock(inst->lock);
 225 #endif
 226
 227         /* We get here if every DB handle is unconnected and unconnectABLE */
 228         radlog((tried_to_connect = 0) ? (L_DBG) : (L_CONS | L_ERR), "rlm_sql:  There are no DB handles to use!");
 229         return NULL;
 230 }
 231
 232 /*************************************************************************
 233  *
 234  *      Function: sql_release_socket
 235  *
 236  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 237  *
 238  *************************************************************************/
 239 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket) {
 240
 241 #if HAVE_PTHREAD_H
 242         pthread_mutex_lock(inst->lock);
 243 #endif
 244         (inst->used)--;
 245 #if HAVE_PTHREAD_H
 246         sem_post(sqlsocket->semaphore);
 247 #else
 248         sqlsocket->in_use = 0;
 249 #endif
 250
 251         radlog(L_DBG, "rlm_sql: Released sql socket id: %d", sqlsocket->id);
 252
 253 #if HAVE_PTHREAD_H
 254         pthread_mutex_unlock(inst->lock);
 255         pthread_cond_signal(inst->notfull);
 256 #endif
 257
 258         return 1;
 259 }
 260
 261
 262 /*************************************************************************
 263  *
 264  *      Function: sql_userparse
 265  *
 266  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 267  *
 268  *************************************************************************/
 269 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row, int mode) {
 270
 271         DICT_ATTR *attr;
 272         VALUE_PAIR *pair, *check;
 273
 274         if ((attr = dict_attrbyname(row[2])) == (DICT_ATTR *) NULL) {
 275                 radlog(L_ERR | L_CONS, "rlm_sql: unknown attribute %s", row[2]);
 276                 return (-1);
 277         }
 278
 279         /*
 280          * If attribute is already there, skip it because we checked usercheck first
 281          * and we want user settings to over ride group settings
 282          */
 283         if ((check = pairfind(*first_pair, attr->attr)) != NULL &&
 284 #if defined( BINARY_FILTERS )
 285                         attr->type != PW_TYPE_ABINARY &&
 286 #endif
 287                         mode == PW_VP_GROUPDATA)
 288                 return 0;
 289
 290         pair = pairmake(row[2], row[3], T_OP_CMP_EQ);
 291         pairadd(first_pair, pair);
 292
 293         vp_printlist(stderr, *first_pair);
 294
 295         return 0;
 296 }
 297
 298
 299 /*************************************************************************
 300  *
 301  *      Function: sql_getvpdata
 302  *
 303  *      Purpose: Get any group check or reply pairs
 304  *
 305  *************************************************************************/
 306 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query, int mode) {
 307
 308         SQL_ROW row;
 309         int     rows = 0;
 310
 311         if ((inst->module->sql_select_query)(sqlsocket, inst->config, query) < 0) {
 312                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 313                 return -1;
 314         }
 315         while ((row = (inst->module->sql_fetch_row)(sqlsocket, inst->config))) {
 316                 if (sql_userparse(pair, row, mode) != 0) {
 317                         radlog(L_ERR | L_CONS, "rlm_sql:  Error getting data from database");
 318                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 319                         return -1;
 320                 }
 321                 rows++;
 322         }
 323         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 324
 325         return rows;
 326 }
 327
 328
 329 static int got_alrm;
 330 static void
 331 alrm_handler() {
 332         got_alrm = 1;
 333 }
 334
 335 /*************************************************************************
 336  *
 337  *      Function: sql_check_ts
 338  *
 339  *      Purpose: Checks the terminal server for a spacific login entry
 340  *
 341  *************************************************************************/
 342 static int sql_check_ts(SQL_ROW row) {
 343
 344         int     pid, st, e;
 345         int     n;
 346         NAS    *nas;
 347         char    session_id[12];
 348         char   *s;
 349         void    (*handler) (int);
 350
 351         /*
 352          *      Find NAS type.
 353          */
 354         if ((nas = nas_find(ip_addr(row[4]))) == NULL) {
 355                 radlog(L_ERR, "rlm_sql:  unknown NAS [%s]", row[4]);
 356                 return -1;
 357         }
 358
 359         /*
 360          *      Fork.
 361          */
 362         handler = signal(SIGCHLD, SIG_DFL);
 363         if ((pid = fork()) < 0) {
 364                 radlog(L_ERR, "rlm_sql: fork: %s", strerror(errno));
 365                 signal(SIGCHLD, handler);
 366                 return -1;
 367         }
 368
 369         if (pid > 0) {
 370                 /*
 371                  *      Parent - Wait for checkrad to terminate.
 372                  *      We timeout in 10 seconds.
 373                  */
 374                 got_alrm = 0;
 375                 signal(SIGALRM, alrm_handler);
 376                 alarm(10);
 377                 while ((e = waitpid(pid, &st, 0)) != pid)
 378                         if (e < 0 && (errno != EINTR || got_alrm))
 379                                 break;
 380                 alarm(0);
 381                 signal(SIGCHLD, handler);
 382                 if (got_alrm) {
 383                         kill(pid, SIGTERM);
 384                         sleep(1);
 385                         kill(pid, SIGKILL);
 386                         radlog(L_ERR, "rlm_sql:  Check-TS: timeout waiting for checkrad");
 387                         return 2;
 388                 }
 389                 if (e < 0) {
 390                         radlog(L_ERR, "rlm_sql:  Check-TS: unknown error in waitpid()");
 391                         return 2;
 392                 }
 393                 return WEXITSTATUS(st);
 394         }
 395
 396         /*
 397          *      Child - exec checklogin with the right parameters.
 398          */
 399         for (n = 32; n >= 3; n--)
 400                 close(n);
 401
 402         sprintf(session_id, "%.8s", row[1]);
 403
 404         s = CHECKRAD2;
 405         execl(CHECKRAD2, "checkrad", nas->nastype, row[4], row[5],
 406                                 row[2], session_id, NULL);
 407         if (errno == ENOENT) {
 408                 s = CHECKRAD1;
 409                 execl(CHECKRAD1, "checklogin", nas->nastype, row[4], row[5],
 410                                         row[2], session_id, NULL);
 411         }
 412         radlog(L_ERR, "rlm_sql:  Check-TS: exec %s: %s", s, strerror(errno));
 413
 414         /*
 415          *      Exit - 2 means "some error occured".
 416          */
 417         exit(2);
 418
 419 }
 420
 421
 422 /*************************************************************************
 423  *
 424  *      Function: sql_check_multi
 425  *
 426  *      Purpose: Check radius accounting for duplicate logins
 427  *
 428  *************************************************************************/
 429 int sql_check_multi(SQL_INST * inst, SQLSOCK * sqlsocket, char *name, VALUE_PAIR * request, int maxsimul) {
 430
 431         char    querystr[MAX_QUERY_LEN];
 432         char    authstr[256];
 433         VALUE_PAIR *fra;
 434         SQL_ROW row;
 435         int     count = 0;
 436         uint32_t ipno = 0;
 437         int     mpp = 1;
 438
 439         sprintf(authstr, "UserName = '%s'", name);
 440         sprintf(querystr, "SELECT COUNT(*) FROM %s WHERE %s AND AcctStopTime = 0", inst->config->sql_acct_table, authstr);
 441         if ((inst->module->sql_select_query)(sqlsocket, inst->config, querystr) < 0) {
 442                 radlog(L_ERR, "sql_check_multi: database query error");
 443                 return -1;
 444         }
 445
 446         row = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 447         count = atoi(row[0]);
 448         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 449
 450         if (count < maxsimul)
 451                 return 0;
 452
 453         /*
 454          * *      Setup some stuff, like for MPP detection.
 455          */
 456         if ((fra = pairfind(request, PW_FRAMED_IP_ADDRESS)) != NULL)
 457                 ipno = htonl(fra->lvalue);
 458
 459         count = 0;
 460         sprintf(querystr, "SELECT * FROM %s WHERE %s AND AcctStopTime = 0", inst->config->sql_acct_table, authstr);
 461         if ((inst->module->sql_select_query)(sqlsocket, inst->config, querystr) < 0) {
 462                 radlog(L_ERR, "sql_check_multi: database query error");
 463                 return -1;
 464         }
 465         while ((row = (inst->module->sql_fetch_row)(sqlsocket, inst->config))) {
 466                 int     check = sql_check_ts(row);
 467
 468                 if (check == 1) {
 469                         count++;
 470
 471                         if (ipno && atoi(row[19]) == ipno)
 472                                 mpp = 2;
 473
 474                 } else if (check == 2)
 475                         radlog(L_ERR, "rlm_sql:  Problem with checkrad [%s] (from nas %s)", name, row[4]);
 476                 else {
 477                         /*
 478                          *      False record - zap it
 479                          */
 480
 481                         if (inst->config->deletestalesessions) {
 482                                 SQLSOCK *sqlsocket1;
 483
 484                                 radlog(L_ERR, "rlm_sql:  Deleteing stale session [%s] (from nas %s/%s)", row[2], row[4], row[5]);
 485                                 sqlsocket1 = sql_get_socket(inst);
 486                                 sprintf(querystr, "DELETE FROM %s WHERE RadAcctId = '%s'", inst->config->sql_acct_table, row[0]);
 487                                 (inst->module->sql_query)(sqlsocket1, inst->config, querystr);
 488                                 (inst->module->sql_finish_query)(sqlsocket1, inst->config);
 489                                 sql_release_socket(inst, sqlsocket1);
 490                         }
 491                 }
 492         }
 493         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 494
 495         return (count < maxsimul) ? 0 : mpp;
 496 }
 497
 498 void query_log(SQL_INST * inst, char *querystr) {
 499         FILE   *sqlfile = 0;
 500
 501         if (inst->config->sqltrace) {
 502                 if ((sqlfile = fopen(inst->config->tracefile, "a")) == (FILE *) NULL) {
 503                         radlog(L_ERR, "rlm_sql: Couldn't open file %s",
 504                                                  inst->config->tracefile);
 505                 } else {
 506 #if defined(F_LOCK) && !defined(BSD)
 507                         (void) lockf((int) sqlfile, (int) F_LOCK, (off_t) MAX_QUERY_LEN);
 508 #else
 509                         (void) flock(sqlfile, SQL_LOCK_EX);
 510 #endif
 511                         fputs(querystr, sqlfile);
 512                         fputs(";\n", sqlfile);
 513                         fclose(sqlfile);
 514                 }
 515         }
 516 }
 517
 518 int sql_set_user(SQL_INST *inst, REQUEST *request, char *sqlusername, char *username) {
 519         VALUE_PAIR *vp=NULL;
 520         char    tmpuser[MAX_STRING_LEN];
 521
 522         tmpuser[0]=0;
 523         sqlusername[0]=0;
 524
 525         /* Remove any user attr we added previously */
 526         pairdelete(&request->packet->vps, PW_SQL_USER_NAME);
 527
 528         if(username) {
 529                 strNcpy(tmpuser, username, MAX_STRING_LEN);
 530         } else if(strlen(inst->config->query_user)) {
 531                 radius_xlat(tmpuser, MAX_STRING_LEN, inst->config->query_user, request, NULL);
 532         } else {
 533                 return 0;
 534         }
 535
 536         if(strlen(tmpuser)) {
 537                 sql_escape_string(sqlusername, tmpuser, MAX_STRING_LEN);
 538                 DEBUG2("sql_set_user:  escaped user --> '%s'", sqlusername);
 539                 vp = pairmake("SQL-User-Name", sqlusername, 0);
 540                 if (!vp) {
 541                         radlog(L_ERR, "%s", librad_errstr);
 542                         return -1;
 543                 }
 544
 545                 pairadd(&request->packet->vps, vp);
 546                 return 0;
 547         }
 548         return -1;
 549 }
 550
 551 /*
 552  *      Purpose: Esacpe "'" and any other wierd charactors
 553  */
 554 int sql_escape_string(char *to, char *from, int length) {
 555         int x, y;
 556
 557         DEBUG2("sql_escape in:  '%s'", from);
 558
 559         for(x=0, y=0; (x < length) && (from[x]!='\0'); x++) {
 560     switch (from[x]) {
 561     case 0:
 562       to[y++]= '\\';
 563       to[y++]= '0';
 564       break;
 565     case '\n':
 566       to[y++]= '\\';
 567       to[y++]= 'n';
 568       break;
 569     case '\r':
 570       to[y++]= '\\';
 571       to[y++]= 'r';
 572       break;
 573     case '\\':
 574       to[y++]= '\\';
 575       to[y++]= '\\';
 576       break;
 577     case '\'':
 578       to[y++]= '\\';
 579       to[y++]= '\'';
 580       break;
 581     case '"':
 582       to[y++]= '\\';
 583       to[y++]= '"';
 584       break;
 585     case ';':
 586       to[y++]= '\\';
 587       to[y++]= ';';
 588       break;
 589                 /* Ascii file separator */
 590     case '\032':
 591       to[y++]= '\\';
 592       to[y++]= 'Z';
 593       break;
 594     default:
 595       to[y++]= from[x];
 596     }
 597   }
 598         to[y]=0;
 599
 600         DEBUG2("sql_escape out:  '%s'", to);
 601         return 1;
 602 }