src/modules/rlm_sql/sql.c

   1
   2 #include        <sys/types.h>
   3 #include        <sys/socket.h>
   4 #include        <sys/time.h>
   5 #include        <sys/file.h>
   6 #include        <sys/stat.h>
   7 #include        <netinet/in.h>
   8
   9 #include        <stdio.h>
  10 #include        <stdlib.h>
  11 #include        <netdb.h>
  12 #include        <pwd.h>
  13 #include        <time.h>
  14 #include        <ctype.h>
  15 #include        <unistd.h>
  16 #include        <signal.h>
  17 #include        <errno.h>
  18 #include        <sys/wait.h>
  19
  20 #include        "radiusd.h"
  21 #include        "rlm_sql.h"
  22
  23
  24
  25
  26 /*************************************************************************
  27  *
  28  *      Function: sql_save_acct
  29  *
  30  *      Purpose: Write data from the sqlrecord structure to the database
  31  *
  32  *************************************************************************/
  33
  34 int sql_save_acct(SQLREC *sqlrecord) {
  35
  36         char            querystr[2048];
  37         FILE            *sqlfile;
  38         FILE            *backupfile;
  39         int             num = 0;
  40         SQL_RES         *result;
  41 #ifdef NT_DOMAIN_HACK
  42         char            *ptr;
  43         char            newname[AUTH_STRING_LEN];
  44 #endif
  45
  46
  47
  48      if((sqlfile = fopen(QUERYLOG, "a")) == (FILE *)NULL) {
  49             log(L_ERR, "Acct: Couldn't open file %s", QUERYLOG);
  50      } else {
  51         #if defined(F_LOCK) && !defined(BSD)
  52               (void)lockf((int)sqlfile, (int)F_LOCK, (off_t)SQL_LOCK_LEN);
  53         #else
  54               (void)flock(sqlfile, SQL_LOCK_EX);
  55         #endif
  56      }
  57
  58 #ifdef NT_DOMAIN_HACK
  59         /*
  60          *      Windows NT machines often authenticate themselves as
  61          *      NT_DOMAIN\username. Try to be smart about this.
  62          *
  63          *      FIXME: should we handle this as a REALM ?
  64          */
  65         if ((ptr = strchr(sqlrecord->UserName, '\\')) != NULL) {
  66                 strncpy(newname, ptr + 1, sizeof(newname));
  67                 newname[sizeof(newname) - 1] = 0;
  68                 strcpy(sqlrecord->UserName, newname);
  69         }
  70 #endif /* NT_DOMAIN_HACK */
  71
  72  if (sql_checksocket("Acct")) {
  73
  74      if (sqlrecord->AcctStatusTypeId == PW_STATUS_ACCOUNTING_ON || sqlrecord->AcctStatusTypeId == PW_STATUS_ACCOUNTING_OFF) {
  75         log(L_INFO, "Portmaster %s rebooted at %s", sqlrecord->NASIPAddress, sqlrecord->AcctTimeStamp);
  76
  77          /* The Terminal server informed us that it was rebooted
  78          * STOP all records from this NAS */
  79
  80          sprintf(querystr, "UPDATE %s SET AcctStopTime='%s', AcctSessionTime=unix_timestamp('%s') - unix_timestamp(AcctStartTime), AcctTerminateCause='%s' WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%s' AND AcctStartTime <= '%s'", sql->config.sql_acct_table, sqlrecord->AcctTimeStamp, sqlrecord->AcctTimeStamp, sqlrecord->AcctTerminateCause, sqlrecord->NASIPAddress, sqlrecord->AcctTimeStamp);
  81
  82          if (sql_query(sql->AcctSock, querystr) < 0)
  83               log(L_ERR, "Acct: Couldn't update SQL accounting after NAS reboot - %s", sql_error(sql->AcctSock));
  84          sql_finish_query(sql->AcctSock);
  85
  86          if (sqlfile) {
  87               fputs(querystr, sqlfile);
  88               fputs(";\n", sqlfile);
  89               fclose(sqlfile);
  90           }
  91           return 0;
  92       }
  93
  94         if (sqlrecord->AcctStatusTypeId == PW_STATUS_ALIVE) {
  95                 sprintf(querystr, "UPDATE %s SET Framed-IP-Address = '%s' WHERE AcctSessionId = '%s' AND UserName = '%s' AND NASIPAddress= '%s'", sql->config.sql_acct_table, sqlrecord->FramedIPAddress, sqlrecord->AcctSessionId, sqlrecord->UserName, sqlrecord->NASIPAddress);
  96                 if (sql_query(sql->AcctSock, querystr) < 0)
  97                         log(L_ERR, "Acct: Couldn't update SQL accounting after NAS reboot - %s", sql_error(sql->AcctSock));
  98                 sql_finish_query(sql->AcctSock);
  99
 100                 if (sqlfile) {
 101                         fputs(querystr, sqlfile);
 102                         fputs(";\n", sqlfile);
 103                         fclose(sqlfile);
 104                 }
 105                 return 0;
 106         }
 107
 108
 109           /* Got start record */
 110           if(sqlrecord->AcctStatusTypeId == PW_STATUS_START) {
 111
 112              /* Set start time on record with only a stop record */
 113              snprintf(querystr, 2048, "UPDATE %s SET AcctStartTime = '%s' WHERE AcctSessionId = '%s' AND UserName = '%s' AND NASIPAddress = '%s'",
 114              sql->config.sql_acct_table,
 115              sqlrecord->AcctTimeStamp,
 116              sqlrecord->AcctSessionId,
 117              sqlrecord->UserName,
 118              sqlrecord->NASIPAddress
 119              );
 120              if (sql_query(sql->AcctSock, querystr) < 0)
 121                 log(L_ERR, "Acct: Couldn't update SQL accounting START record - %s", sql_error(sql->AcctSock));
 122              sql_finish_query(sql->AcctSock);
 123
 124              num = sql_affected_rows(sql->AcctSock);
 125              if (num == 0) {
 126
 127                 /* Insert new record with blank stop time until stop record is got */
 128                 snprintf(querystr, 2048, "INSERT INTO %s VALUES (0, '%s', '%s', '%s', '%s', %ld, '%s', '%s', 0, 0, '%s', '%s', 0, 0, '%s', '%s', '', '%s', '%s', '%s', %ld)",
 129                 sql->config.sql_acct_table,
 130                 sqlrecord->AcctSessionId,
 131                 sqlrecord->UserName,
 132                 sqlrecord->Realm,
 133                 sqlrecord->NASIPAddress,
 134                 sqlrecord->NASPortId,
 135                 sqlrecord->NASPortType,
 136                 sqlrecord->AcctTimeStamp,
 137                 sqlrecord->AcctAuthentic,
 138                 sqlrecord->ConnectInfo,
 139                 sqlrecord->CalledStationId,
 140                 sqlrecord->CallingStationId,
 141                 sqlrecord->ServiceType,
 142                 sqlrecord->FramedProtocol,
 143                 sqlrecord->FramedIPAddress,
 144                 sqlrecord->AcctDelayTime
 145                 );
 146
 147                 if (sql_query(sql->AcctSock, querystr) < 0)
 148                   log(L_ERR, "Acct: Couldn't insert SQL accounting START record - %s", sql_error(sql->AcctSock));
 149                 sql_finish_query(sql->AcctSock);
 150              }
 151
 152            /* Got stop record */
 153            } else {
 154
 155                 sprintf(querystr, "SELECT RadAcctId FROM %s WHERE AcctSessionId='%s' AND NASIPAddress='%s' AND UserName='%s'", sql->config.sql_acct_table, sqlrecord->AcctSessionId, sqlrecord->NASIPAddress, sqlrecord->UserName);
 156                 sql_select_query(sql->AcctSock, querystr);
 157                 num = sql_num_rows(sql->AcctSock);
 158                 sql_finish_select_query(sql->AcctSock);
 159
 160                 if (num > 0) {
 161
 162                         /* Set stop time on matching record with start time */
 163                         snprintf(querystr, 2048, "UPDATE %s SET AcctStopTime = '%s', AcctSessionTime = '%lu', AcctInputOctets = '%u', AcctOutputOctets = '%u', AcctTerminateCause = '%s' WHERE AcctSessionId = '%s' AND UserName = '%s' AND NASIPAddress = '%s'",
 164                         sql->config.sql_acct_table,
 165                         sqlrecord->AcctTimeStamp,
 166                         sqlrecord->AcctSessionTime,
 167                         sqlrecord->AcctInputOctets,
 168                         sqlrecord->AcctOutputOctets,
 169                         sqlrecord->AcctTerminateCause,
 170                         sqlrecord->AcctSessionId,
 171                         sqlrecord->UserName,
 172                         sqlrecord->NASIPAddress);
 173
 174
 175                         if (sql_query(sql->AcctSock, querystr) < 0)
 176                                 log(L_ERR, "Acct: Couldn't update SQL accounting STOP record - %s", sql_error(sql->AcctSock));
 177                         sql_finish_query(sql->AcctSock);
 178
 179                 } else if (num == 0) {
 180
 181
 182                         /* Insert record with no start time until matching start record comes */
 183                         snprintf(querystr, 2048, "INSERT INTO %s VALUES (0, '%s', '%s', '%s', '%s', %ld, '%s', 0, '%s', '%lu', '%s', '%s', '%u', '%u', '%s', '%s', '%s', '%s', '%s', '%s', %ld)",
 184                         sql->config.sql_acct_table,
 185                         sqlrecord->AcctSessionId,
 186                         sqlrecord->UserName,
 187                         sqlrecord->Realm,
 188                         sqlrecord->NASIPAddress,
 189                         sqlrecord->NASPortId,
 190                         sqlrecord->NASPortType,
 191                         sqlrecord->AcctTimeStamp,
 192                         sqlrecord->AcctSessionTime,
 193                         sqlrecord->AcctAuthentic,
 194                         sqlrecord->ConnectInfo,
 195                         sqlrecord->AcctInputOctets,
 196                         sqlrecord->AcctOutputOctets,
 197                         sqlrecord->CalledStationId,
 198                         sqlrecord->CallingStationId,
 199                         sqlrecord->AcctTerminateCause,
 200                         sqlrecord->ServiceType,
 201                         sqlrecord->FramedProtocol,
 202                         sqlrecord->FramedIPAddress,
 203                         sqlrecord->AcctDelayTime);
 204
 205                         if (sql_query(sql->AcctSock, querystr) < 0)
 206                                 log(L_ERR, "Acct: Couldn't insert SQL accounting STOP record - %s", sql_error(sql->AcctSock));
 207                         sql_finish_query(sql->AcctSock);
 208                 }
 209
 210           }
 211           if (sqlfile) {
 212                 fputs(querystr, sqlfile);
 213                 fputs(";\n", sqlfile);
 214                 fflush(sqlfile);
 215                 fclose(sqlfile);
 216           }
 217
 218
 219         } else {
 220
 221             /*
 222              *  The database is down for some reason
 223              *  So open up the backup file to save records in
 224              */
 225
 226              if((backupfile = fopen(SQLBACKUP, "a")) == (FILE *)NULL) {
 227                  log(L_ERR, "Acct: Couldn't open file %s", SQLBACKUP);
 228              } else {
 229                   /*
 230                    * Lock the sql backup file, prefer lockf() over flock().
 231                    */
 232                    #if defined(F_LOCK) && !defined(BSD)
 233                        (void)lockf((int)backupfile, (int)F_LOCK, (off_t)SQL_LOCK_LEN);
 234                    #else
 235                        (void)flock(backupfile, SQL_LOCK_EX);
 236                    #endif
 237                    if(fwrite(sqlrecord, sizeof(SQLREC), 1, backupfile) < 1) {
 238                        log(L_ERR, "Acct: Couldn't write to file %s", SQLBACKUP);
 239                    }
 240                    fclose(backupfile);
 241               }
 242
 243         }
 244
 245      return 0;
 246
 247 }
 248
 249
 250 /*************************************************************************
 251  *
 252  *      Function: sql_userparse
 253  *
 254  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 255  *
 256  *************************************************************************/
 257 int sql_userparse(VALUE_PAIR **first_pair, SQL_ROW row) {
 258
 259         DICT_ATTR       *attr;
 260         VALUE_PAIR      *pair, *check;
 261
 262
 263         if((attr = dict_attrbyvalue((int)row[2])) == (DICT_ATTR *)NULL) {
 264 #if 1 /* Be quiet. */
 265                 log(L_ERR|L_CONS, "unknown attribute %s", row[2]);
 266 #endif
 267                 return(-1);
 268         }
 269
 270         /* If attribute is already there, skip it because we checked usercheck first
 271            and we want user settings to over ride group settings */
 272         if ((check = pairfind(*first_pair, attr->attr)) != NULL)
 273                 return 0;
 274
 275         pair = pairmake(row[2], row[3], T_OP_EQ);
 276         pairadd(first_pair, pair);
 277
 278         return 0;
 279 }
 280
 281
 282
 283 /*************************************************************************
 284  *
 285  *      Function: sql_getvpdata
 286  *
 287  *      Purpose: Get any group check or reply pairs
 288  *
 289  *************************************************************************/
 290 int sql_getvpdata(char *table, VALUE_PAIR **vp, char *user, int mode) {
 291
 292         char            querystr[256];
 293         SQL_RES         *result;
 294         SQL_ROW         row;
 295         int             rows;
 296
 297         if (mode == PW_VP_USERDATA)
 298                 sprintf(querystr, "SELECT * FROM %s WHERE UserName = '%s'", table, user);
 299         else if (mode == PW_VP_GROUPDATA)
 300                 sprintf(querystr, "SELECT %s.* FROM %s, %s WHERE %s.UserName = '%s' AND %s.GroupName = %s.GroupName ORDER BY %s.id", table, table, sql->config.sql_usergroup_table, sql->config.sql_usergroup_table, user, sql->config.sql_usergroup_table, table, table);
 301         else if (mode == PW_VP_REALMDATA)
 302                 sprintf(querystr, "SELECT %s.* FROM %s, %s WHERE %s.RealmName = '%s' AND %s.GroupName = %s.GroupName ORDER BY %s.id", table, table, sql->config.sql_realmgroup_table, sql->config.sql_realmgroup_table, user, sql->config.sql_realmgroup_table, table, table);
 303         sql_checksocket("Auth");
 304         sql_select_query(sql->AuthSock, querystr);
 305         rows = sql_num_rows(sql->AuthSock);
 306         while ((row = sql_fetch_row(sql->AuthSock))) {
 307                 if (sql_userparse(vp, row) != 0) {
 308                         log(L_ERR|L_CONS, "Error getting data from SQL database");
 309                         sql_finish_select_query(sql->AuthSock);
 310                         return -1;
 311                 }
 312         }
 313         sql_finish_select_query(sql->AuthSock);
 314
 315         return rows;
 316
 317 }
 318
 319
 320 static int got_alrm;
 321 static void alrm_handler() {
 322         got_alrm = 1;
 323 }
 324
 325 /*************************************************************************
 326  *
 327  *      Function: sql_check_ts
 328  *
 329  *      Purpose: Checks the terminal server for a spacific login entry
 330  *
 331  *************************************************************************/
 332 static int sql_check_ts(SQL_ROW row) {
 333
 334         int     pid, st, e;
 335         int     n;
 336         NAS     *nas;
 337         char    session_id[12];
 338         char    *s;
 339         void    (*handler)(int);
 340
 341         /*
 342          *      Find NAS type.
 343          */
 344         if ((nas = nas_find(ipstr2long(row[3]))) == NULL) {
 345                 log(L_ERR, "Accounting: unknown NAS [%s]", row[3]);
 346                 return -1;
 347         }
 348
 349         /*
 350          *      Fork.
 351          */
 352         handler = signal(SIGCHLD, SIG_DFL);
 353         if ((pid = fork()) < 0) {
 354                 log(L_ERR, "Accounting: fork: %s", strerror(errno));
 355                 signal(SIGCHLD, handler);
 356                 return -1;
 357         }
 358
 359         if (pid > 0) {
 360                 /*
 361                  *      Parent - Wait for checkrad to terminate.
 362                  *      We timeout in 10 seconds.
 363                  */
 364                 got_alrm = 0;
 365                 signal(SIGALRM, alrm_handler);
 366                 alarm(10);
 367                 while((e = waitpid(pid, &st, 0)) != pid)
 368                         if (e < 0 && (errno != EINTR || got_alrm))
 369                                 break;
 370                 alarm(0);
 371                 signal(SIGCHLD, handler);
 372                 if (got_alrm) {
 373                         kill(pid, SIGTERM);
 374                         sleep(1);
 375                         kill(pid, SIGKILL);
 376                         log(L_ERR, "Check-TS: timeout waiting for checkrad");
 377                         return 2;
 378                 }
 379                 if (e < 0) {
 380                         log(L_ERR, "Check-TS: unknown error in waitpid()");
 381                         return 2;
 382                 }
 383                 return WEXITSTATUS(st);
 384         }
 385
 386         /*
 387          *      Child - exec checklogin with the right parameters.
 388          */
 389         for (n = 32; n >= 3; n--)
 390                 close(n);
 391
 392         sprintf(session_id, "%.8s", row[1]);
 393
 394         s = CHECKRAD2;
 395         execl(CHECKRAD2, "checkrad", nas->nastype, row[4], row[5],
 396                 row[2], session_id, NULL);
 397         if (errno == ENOENT) {
 398                 s = CHECKRAD1;
 399                 execl(CHECKRAD1, "checklogin", nas->nastype, row[4], row[5],
 400                         row[2], session_id, NULL);
 401         }
 402         log(L_ERR, "Check-TS: exec %s: %s", s, strerror(errno));
 403
 404         /*
 405          *      Exit - 2 means "some error occured".
 406          */
 407         exit(2);
 408
 409 }
 410
 411
 412 /*************************************************************************
 413  *
 414  *      Function: sql_check_multi
 415  *
 416  *      Purpose: Check radius accounting for duplicate logins
 417  *
 418  *************************************************************************/
 419 int sql_check_multi(char *name, VALUE_PAIR *request, int maxsimul) {
 420
 421         char            querystr[256];
 422         VALUE_PAIR      *fra;
 423         SQL_RES         *result;
 424         SQL_ROW         row;
 425         int             count = 0;
 426         UINT4           ipno = 0;
 427         int             mpp = 1;
 428
 429         if (!sql_checksocket("Auth"))
 430                 return 0;
 431         sprintf(querystr, "SELECT COUNT(*) FROM %s WHERE UserName = '%s' AND AcctStopTime = 0", sql->config.sql_acct_table, name);
 432         sql_select_query(sql->AuthSock, querystr);
 433         row = sql_fetch_row(sql->AuthSock);
 434         count = atoi(row[0]);
 435         sql_finish_select_query(sql->AuthSock);
 436
 437         if (count < maxsimul)
 438                 return 0;
 439
 440         /*
 441         *      Setup some stuff, like for MPP detection.
 442         */
 443         if ((fra = pairfind(request, PW_FRAMED_IP_ADDRESS)) != NULL)
 444                 ipno = htonl(fra->lvalue);
 445
 446         count = 0;
 447         sprintf(querystr, "SELECT * FROM %s WHERE UserName = '%s' AND AcctStopTime = 0", sql->config.sql_acct_table, name);
 448         sql_select_query(sql->AuthSock, querystr);
 449         while ((row = sql_fetch_row(sql->AuthSock))) {
 450                 if (sql_check_ts(row) == 1) {
 451                         count++;
 452
 453                         if (ipno && atoi(row[18]) == ipno)
 454                                 mpp = 2;
 455
 456                 } else {
 457                         /*
 458                          *      False record - zap it
 459                          */
 460
 461                         sprintf(querystr, "DELETE FROM %s WHERE RadAcctId = '%s'", sql->config.sql_acct_table, row[0]);
 462                         sql_query(sql->AuthSock, querystr);
 463                         sql_finish_query(sql->AuthSock);
 464
 465                 }
 466         }
 467         sql_finish_select_query(sql->AuthSock);
 468
 469         return (count < maxsimul) ? 0 : mpp;
 470
 471 }