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, (const char *) querystr) < 0)
  83               log(L_ERR, "Acct: Couldn't update SQL accounting after NAS reboot - %s", sql_error(sql->AcctSock));
  84
  85          if (sqlfile) {
  86               fputs(querystr, sqlfile);
  87               fputs(";\n", sqlfile);
  88               fclose(sqlfile);
  89           }
  90           return 0;
  91       }
  92
  93         if (sqlrecord->AcctStatusTypeId == PW_STATUS_ALIVE) {
  94                 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);
  95                 if (sql_query(sql->AcctSock, (const char *) querystr) < 0)
  96                 log(L_ERR, "Acct: Couldn't update SQL accounting after NAS reboot - %s", sql_error(sql->AcctSock));
  97
  98                 if (sqlfile) {
  99                         fputs(querystr, sqlfile);
 100                         fputs(";\n", sqlfile);
 101                         fclose(sqlfile);
 102                 }
 103                 return 0;
 104         }
 105
 106
 107           /* Got start record */
 108           if(sqlrecord->AcctStatusTypeId == PW_STATUS_START) {
 109
 110              /* Set start time on record with only a stop record */
 111              snprintf(querystr, 2048, "UPDATE %s SET AcctStartTime = '%s' WHERE AcctSessionId = '%s' AND UserName = '%s' AND NASIPAddress = '%s'",
 112              sql->config.sql_acct_table,
 113              sqlrecord->AcctTimeStamp,
 114              sqlrecord->AcctSessionId,
 115              sqlrecord->UserName,
 116              sqlrecord->NASIPAddress
 117              );
 118              if (sql_query(sql->AcctSock, (const char *) querystr) < 0)
 119                 log(L_ERR, "Acct: Couldn't update SQL accounting START record - %s", sql_error(sql->AcctSock));
 120
 121              num = sql_affected_rows(sql->AcctSock);
 122              if (num == 0) {
 123
 124                 /* Insert new record with blank stop time until stop record is got */
 125                 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)",
 126                 sql->config.sql_acct_table,
 127                 sqlrecord->AcctSessionId,
 128                 sqlrecord->UserName,
 129                 sqlrecord->Realm,
 130                 sqlrecord->NASIPAddress,
 131                 sqlrecord->NASPortId,
 132                 sqlrecord->NASPortType,
 133                 sqlrecord->AcctTimeStamp,
 134                 sqlrecord->AcctAuthentic,
 135                 sqlrecord->ConnectInfo,
 136                 sqlrecord->CalledStationId,
 137                 sqlrecord->CallingStationId,
 138                 sqlrecord->ServiceType,
 139                 sqlrecord->FramedProtocol,
 140                 sqlrecord->FramedIPAddress,
 141                 sqlrecord->AcctDelayTime
 142                 );
 143
 144                 if (sql_query(sql->AcctSock, (const char *) querystr) < 0)
 145                   log(L_ERR, "Acct: Couldn't insert SQL accounting START record - %s", sql_error(sql->AcctSock));
 146              }
 147
 148            /* Got stop record */
 149            } else {
 150
 151              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);
 152               sql_query(sql->AcctSock, querystr);
 153               if (!(result = sql_store_result(sql->AcctSock)) && sql_num_fields(sql->AcctSock)) {
 154                    log(L_ERR,"SQL Error: Cannot get result");
 155                    log(L_ERR,"SQL error: %s",sql_error(sql->AcctSock));
 156                     sql_close(sql->AcctSock);
 157                     sql->AcctSock = NULL;
 158               } else {
 159                     num = sql_num_rows(result);
 160                     sql_free_result(result);
 161               }
 162
 163              if (num > 0) {
 164
 165                 /* Set stop time on matching record with start time */
 166                 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'",
 167                 sql->config.sql_acct_table,
 168                 sqlrecord->AcctTimeStamp,
 169                 sqlrecord->AcctSessionTime,
 170                 sqlrecord->AcctInputOctets,
 171                 sqlrecord->AcctOutputOctets,
 172                 sqlrecord->AcctTerminateCause,
 173                 sqlrecord->AcctSessionId,
 174                 sqlrecord->UserName,
 175                 sqlrecord->NASIPAddress
 176                 );
 177
 178
 179                 if (sql_query(sql->config.AcctSock, (const char *) querystr) < 0)
 180                    log(L_ERR, "Acct: Couldn't update SQL accounting STOP record - %s", sql_error(sql->AcctSock));
 181
 182              } else if (num == 0) {
 183
 184
 185                 /* Insert record with no start time until matching start record comes */
 186                 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)",
 187                 sql->config.sql_acct_table,
 188                 sqlrecord->AcctSessionId,
 189                 sqlrecord->UserName,
 190                 sqlrecord->Realm,
 191                 sqlrecord->NASIPAddress,
 192                 sqlrecord->NASPortId,
 193                 sqlrecord->NASPortType,
 194                 sqlrecord->AcctTimeStamp,
 195                 sqlrecord->AcctSessionTime,
 196                 sqlrecord->AcctAuthentic,
 197                 sqlrecord->ConnectInfo,
 198                 sqlrecord->AcctInputOctets,
 199                 sqlrecord->AcctOutputOctets,
 200                 sqlrecord->CalledStationId,
 201                 sqlrecord->CallingStationId,
 202                 sqlrecord->AcctTerminateCause,
 203                 sqlrecord->ServiceType,
 204                 sqlrecord->FramedProtocol,
 205                 sqlrecord->FramedIPAddress,
 206                 sqlrecord->AcctDelayTime
 207                 );
 208
 209                 if (sql->config.sql_query(sql->AcctSock, (const char *) querystr) < 0)
 210                    log(L_ERR, "Acct: Couldn't insert SQL accounting STOP record - %s", sql_error(sql->AcctSock));
 211              }
 212
 213           }
 214           if (sqlfile) {
 215                 fputs(querystr, sqlfile);
 216                 fputs(";\n", sqlfile);
 217                 fflush(sqlfile);
 218                 fclose(sqlfile);
 219           }
 220
 221
 222         } else {
 223
 224             /*
 225              *  The database is down for some reason
 226              *  So open up the backup file to save records in
 227              */
 228
 229              if((backupfile = fopen(SQLBACKUP, "a")) == (FILE *)NULL) {
 230                  log(L_ERR, "Acct: Couldn't open file %s", SQLBACKUP);
 231              } else {
 232                   /*
 233                    * Lock the sql backup file, prefer lockf() over flock().
 234                    */
 235                    #if defined(F_LOCK) && !defined(BSD)
 236                        (void)lockf((int)backupfile, (int)F_LOCK, (off_t)SQL_LOCK_LEN);
 237                    #else
 238                        (void)flock(backupfile, SQL_LOCK_EX);
 239                    #endif
 240                    if(fwrite(sqlrecord, sizeof(SQLREC), 1, backupfile) < 1) {
 241                        log(L_ERR, "Acct: Couldn't write to file %s", SQLBACKUP);
 242                    }
 243                    fclose(backupfile);
 244               }
 245
 246         }
 247
 248      return 0;
 249
 250 }
 251
 252
 253 /*************************************************************************
 254  *
 255  *      Function: sql_userparse
 256  *
 257  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 258  *
 259  *************************************************************************/
 260 int sql_userparse(VALUE_PAIR **first_pair, SQL_ROW row) {
 261
 262         DICT_ATTR       *attr;
 263         VALUE_PAIR      *pair, *check;
 264
 265
 266         if((attr = dict_attrfind(row[2])) == (DICT_ATTR *)NULL) {
 267 #if 1 /* Be quiet. */
 268                 log(L_ERR|L_CONS, "unknown attribute %s", row[2]);
 269 #endif
 270                 return(-1);
 271         }
 272
 273         /* If attribute is already there, skip it because we checked usercheck first
 274            and we want user settings to over ride group settings */
 275         if ((check = pairfind(*first_pair, attr->value)) != NULL)
 276                 return 0;
 277
 278         pair = makepair(row[2], row[3], PW_OPERATOR_EQUAL);
 279         pairadd(first_pair, pair);
 280
 281         return 0;
 282 }
 283
 284
 285
 286 /*************************************************************************
 287  *
 288  *      Function: sql_getvpdata
 289  *
 290  *      Purpose: Get any group check or reply pairs
 291  *
 292  *************************************************************************/
 293 int sql_getvpdata(char *table, VALUE_PAIR **vp, char *user, int mode) {
 294
 295         char            querystr[256];
 296         SQL_RES         *result;
 297         SQL_ROW         row;
 298         int             rows;
 299
 300         if (mode == PW_VP_USERDATA)
 301                 sprintf(querystr, "SELECT * FROM %s WHERE UserName = '%s'", table, user);
 302         else if (mode == PW_VP_GROUPDATA)
 303                 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);
 304         else if (mode == PW_VP_REALMDATA)
 305                 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);
 306         sql_checksocket("Auth");
 307         sql_query(sql->AuthSock, querystr);
 308         if ((result = sql_store_result(sql->AuthSock)) && sql_num_fields(sql->AuthSock)) {
 309                 rows = sql_num_rows(result);
 310                 while ((row = sql_fetch_row(result))) {
 311
 312                         if (sql_userparse(vp, row) != 0) {
 313                                 log(L_ERR|L_CONS, "Error getting data from SQL database");
 314                                 sql_free_result(result);
 315                                 return -1;
 316                         }
 317                 }
 318                 sql_free_result(result);
 319         }
 320
 321         return rows;
 322
 323 }
 324
 325
 326 static int got_alrm;
 327 static void alrm_handler()
 328 {
 329         got_alrm = 1;
 330 }
 331
 332 /*************************************************************************
 333  *
 334  *      Function: sql_check_ts
 335  *
 336  *      Purpose: Checks the terminal server for a spacific login entry
 337  *
 338  *************************************************************************/
 339 static int sql_check_ts(SQL_ROW row) {
 340
 341         int     pid, st, e;
 342         int     n;
 343         NAS     *nas;
 344         char    session_id[12];
 345         char    *s;
 346         void    (*handler)(int);
 347
 348         /*
 349          *      Find NAS type.
 350          */
 351         if ((nas = nas_find(ipstr2long(row[3]))) == NULL) {
 352                 log(L_ERR, "Accounting: unknown NAS [%s]", row[3]);
 353                 return -1;
 354         }
 355
 356         /*
 357          *      Fork.
 358          */
 359         handler = signal(SIGCHLD, SIG_DFL);
 360         if ((pid = fork()) < 0) {
 361                 log(L_ERR, "Accounting: fork: %s", strerror(errno));
 362                 signal(SIGCHLD, handler);
 363                 return -1;
 364         }
 365
 366         if (pid > 0) {
 367                 /*
 368                  *      Parent - Wait for checkrad to terminate.
 369                  *      We timeout in 10 seconds.
 370                  */
 371                 got_alrm = 0;
 372                 signal(SIGALRM, alrm_handler);
 373                 alarm(10);
 374                 while((e = waitpid(pid, &st, 0)) != pid)
 375                         if (e < 0 && (errno != EINTR || got_alrm))
 376                                 break;
 377                 alarm(0);
 378                 signal(SIGCHLD, handler);
 379                 if (got_alrm) {
 380                         kill(pid, SIGTERM);
 381                         sleep(1);
 382                         kill(pid, SIGKILL);
 383                         log(L_ERR, "Check-TS: timeout waiting for checkrad");
 384                         return 2;
 385                 }
 386                 if (e < 0) {
 387                         log(L_ERR, "Check-TS: unknown error in waitpid()");
 388                         return 2;
 389                 }
 390                 return WEXITSTATUS(st);
 391         }
 392
 393         /*
 394          *      Child - exec checklogin with the right parameters.
 395          */
 396         for (n = 32; n >= 3; n--)
 397                 close(n);
 398
 399         sprintf(session_id, "%.8s", row[1]);
 400
 401         s = CHECKRAD2;
 402         execl(CHECKRAD2, "checkrad", nas->nastype, row[4], row[5],
 403                 row[2], session_id, NULL);
 404         if (errno == ENOENT) {
 405                 s = CHECKRAD1;
 406                 execl(CHECKRAD1, "checklogin", nas->nastype, row[4], row[5],
 407                         row[2], session_id, NULL);
 408         }
 409         log(L_ERR, "Check-TS: exec %s: %s", s, strerror(errno));
 410
 411         /*
 412          *      Exit - 2 means "some error occured".
 413          */
 414         exit(2);
 415
 416 }
 417
 418
 419 /*************************************************************************
 420  *
 421  *      Function: sql_check_multi
 422  *
 423  *      Purpose: Check radius accounting for duplicate logins
 424  *
 425  *************************************************************************/
 426 int sql_check_multi(char *name, VALUE_PAIR *request, int maxsimul) {
 427
 428         char            querystr[256];
 429         VALUE_PAIR      *fra;
 430         SQL_RES         *result;
 431         SQL_ROW         row;
 432         int             count = 0;
 433         UINT4           ipno = 0;
 434         int             mpp = 1;
 435
 436         if (!sql_checksocket("Auth"))
 437                 return 0;
 438         sprintf(querystr, "SELECT COUNT(*) FROM %s WHERE UserName = '%s' AND AcctStopTime = 0", sql->config.sql_acct_table, name);
 439         sql_query(sql->AuthSock, querystr);
 440         if (!(result = sql_store_result(sql->AuthSock)) && sql_num_fields(sql->AuthSock)) {
 441                 log(L_ERR,"SQL Error: Cannot get result");
 442                 log(L_ERR,"SQL error: %s",sql_error(sql->AuthSock));
 443                 sql_close(sql->AuthSock);
 444                 sql->AuthSock = NULL;
 445         } else {
 446                 row = sql_fetch_row(result);
 447                 count = atoi(row[0]);
 448                 sql_free_result(result);
 449         }
 450
 451         if (count < maxsimul)
 452                 return 0;
 453
 454         /*
 455         *      Setup some stuff, like for MPP detection.
 456         */
 457         if ((fra = pairfind(request, PW_FRAMED_IP_ADDRESS)) != NULL)
 458                 ipno = htonl(fra->lvalue);
 459
 460         count = 0;
 461         sprintf(querystr, "SELECT * FROM %s WHERE UserName = '%s' AND AcctStopTime = 0", sql->config.sql_acct_table, name);
 462         sql_query(sql->AuthSock, querystr);
 463         if (!(result = sql_store_result(sql->AuthSock)) && sql_num_fields(sql->AuthSock)) {
 464                 log(L_ERR,"SQL Error: Cannot get result");
 465                 log(L_ERR,"SQL error: %s",sql_error(sql->AuthSock));
 466                 sql_close(sql->AuthSock);
 467                 sql->AuthSock = NULL;
 468         } else {
 469                 while ((row = sql_fetch_row(result))) {
 470                         if (sql_check_ts(row) == 1) {
 471                                 count++;
 472
 473                                 if (ipno && atoi(row[18]) == ipno)
 474                                         mpp = 2;
 475
 476                         } else {
 477                                 /*
 478                                  *      False record - zap it
 479                                  */
 480
 481                                 sprintf(querystr, "DELETE FROM %s WHERE RadAcctId = '%s'", sql->config.sql_acct_table, row[0]);
 482                                 sql_query(sql->AuthSock, querystr);
 483
 484                         }
 485                 }
 486                 sql_free_result(result);
 487         }
 488
 489         return (count < maxsimul) ? 0 : mpp;
 490
 491 }