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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  20  *
  21  * Copyright 2001,2006  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 #include <freeradius-devel/ident.h>
  28 RCSID("$Id$")
  29
  30 #include        <freeradius-devel/radiusd.h>
  31
  32 #include        <sys/file.h>
  33 #include        <sys/stat.h>
  34
  35 #include        <pwd.h>
  36 #include        <ctype.h>
  37 #include        <signal.h>
  38 #include        <sys/wait.h>
  39
  40 #include        "rlm_sql.h"
  41
  42 #ifdef HAVE_PTHREAD_H
  43 #endif
  44
  45
  46 /*
  47  * Connect to a server.  If error, set this socket's state to be
  48  * "sockunconnected" and set a grace period, during which we won't try
  49  * connecting again (to prevent unduly lagging the server and being
  50  * impolite to a DB server that may be having other issues).  If
  51  * successful in connecting, set state to sockconnected.
  52  * - chad
  53  */
  54 static int connect_single_socket(SQLSOCK *sqlsocket, SQL_INST *inst)
  55 {
  56         int rcode;
  57         radlog(L_DBG, "rlm_sql (%s): Attempting to connect %s #%d",
  58                inst->config->xlat_name, inst->module->name, sqlsocket->id);
  59
  60         rcode = (inst->module->sql_init_socket)(sqlsocket, inst->config);
  61         if (rcode == 0) {
  62                 radlog(L_DBG, "rlm_sql (%s): Connected new DB handle, #%d",
  63                        inst->config->xlat_name, sqlsocket->id);
  64                 sqlsocket->state = sockconnected;
  65                 return(0);
  66         }
  67
  68         /*
  69          *  Error, or SQL_DOWN.
  70          */
  71         radlog(L_CONS | L_ERR, "rlm_sql (%s): Failed to connect DB handle #%d", inst->config->xlat_name, sqlsocket->id);
  72         inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  73         sqlsocket->state = sockunconnected;
  74         return(-1);
  75 }
  76
  77
  78 /*************************************************************************
  79  *
  80  *      Function: sql_init_socketpool
  81  *
  82  *      Purpose: Connect to the sql server, if possible
  83  *
  84  *************************************************************************/
  85 int sql_init_socketpool(SQL_INST * inst)
  86 {
  87         int i, rcode;
  88         int success = 0;
  89         SQLSOCK *sqlsocket;
  90
  91         inst->connect_after = 0;
  92         inst->sqlpool = NULL;
  93
  94         for (i = 0; i < inst->config->num_sql_socks; i++) {
  95                 radlog(L_DBG, "rlm_sql (%s): starting %d",
  96                        inst->config->xlat_name, i);
  97
  98                 sqlsocket = rad_malloc(sizeof(*sqlsocket));
  99                 if (sqlsocket == NULL) {
 100                         return -1;
 101                 }
 102                 memset(sqlsocket, 0, sizeof(*sqlsocket));
 103                 sqlsocket->conn = NULL;
 104                 sqlsocket->id = i;
 105                 sqlsocket->state = sockunconnected;
 106
 107 #ifdef HAVE_PTHREAD_H
 108                 rcode = pthread_mutex_init(&sqlsocket->mutex,NULL);
 109                 if (rcode != 0) {
 110                         radlog(L_ERR, "rlm_sql: Failed to init lock: %s",
 111                                strerror(errno));
 112                         return 0;
 113                 }
 114 #endif
 115
 116                 if (time(NULL) > inst->connect_after) {
 117                         /*
 118                          *      This sets the sqlsocket->state, and
 119                          *      possibly also inst->connect_after
 120                          */
 121                         if (connect_single_socket(sqlsocket, inst) == 0) {
 122                                 success = 1;
 123                         }
 124                 }
 125
 126                 /* Add this socket to the list of sockets */
 127                 sqlsocket->next = inst->sqlpool;
 128                 inst->sqlpool = sqlsocket;
 129         }
 130         inst->last_used = NULL;
 131
 132         if (!success) {
 133                 radlog(L_DBG, "rlm_sql (%s): Failed to connect to any SQL server.",
 134                        inst->config->xlat_name);
 135         }
 136
 137         return 1;
 138 }
 139
 140 /*************************************************************************
 141  *
 142  *     Function: sql_poolfree
 143  *
 144  *     Purpose: Clean up and free sql pool
 145  *
 146  *************************************************************************/
 147 void sql_poolfree(SQL_INST * inst)
 148 {
 149         SQLSOCK *cur;
 150         SQLSOCK *next;
 151
 152         for (cur = inst->sqlpool; cur; cur = next) {
 153                 next = cur->next;
 154                 sql_close_socket(inst, cur);
 155         }
 156
 157         inst->sqlpool = NULL;
 158 }
 159
 160
 161 /*************************************************************************
 162  *
 163  *      Function: sql_close_socket
 164  *
 165  *      Purpose: Close and free a sql sqlsocket
 166  *
 167  *************************************************************************/
 168 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket)
 169 {
 170         radlog(L_DBG, "rlm_sql (%s): Closing sqlsocket %d",
 171                inst->config->xlat_name, sqlsocket->id);
 172         if (sqlsocket->state == sockconnected) {
 173                 (inst->module->sql_close)(sqlsocket, inst->config);
 174         }
 175         if (inst->module->sql_destroy_socket) {
 176                 (inst->module->sql_destroy_socket)(sqlsocket, inst->config);
 177         }
 178 #ifdef HAVE_PTHREAD_H
 179         pthread_mutex_destroy(&sqlsocket->mutex);
 180 #endif
 181         free(sqlsocket);
 182         return 1;
 183 }
 184
 185
 186 /*************************************************************************
 187  *
 188  *      Function: sql_get_socket
 189  *
 190  *      Purpose: Return a SQL sqlsocket from the connection pool
 191  *
 192  *************************************************************************/
 193 SQLSOCK * sql_get_socket(SQL_INST * inst)
 194 {
 195         SQLSOCK *cur, *start;
 196         int tried_to_connect = 0;
 197         int unconnected = 0;
 198
 199         /*
 200          *      Start at the last place we left off.
 201          */
 202         start = inst->last_used;
 203         if (!start) start = inst->sqlpool;
 204
 205         cur = start;
 206
 207         while (cur) {
 208 #ifdef HAVE_PTHREAD_H
 209                 /*
 210                  *      If this socket is in use by another thread,
 211                  *      skip it, and try another socket.
 212                  *
 213                  *      If it isn't used, then grab it ourselves.
 214                  */
 215                 if (pthread_mutex_trylock(&cur->mutex) != 0) {
 216                         goto next;
 217                 } /* else we now have the lock */
 218 #endif
 219
 220                 /*
 221                  *      If we happen upon an unconnected socket, and
 222                  *      this instance's grace period on
 223                  *      (re)connecting has expired, then try to
 224                  *      connect it.  This should be really rare.
 225                  */
 226                 if ((cur->state == sockunconnected) && (time(NULL) > inst->connect_after)) {
 227                         radlog(L_INFO, "rlm_sql (%s): Trying to (re)connect unconnected handle %d..", inst->config->xlat_name, cur->id);
 228                         tried_to_connect++;
 229                         connect_single_socket(cur, inst);
 230                 }
 231
 232                 /* if we still aren't connected, ignore this handle */
 233                 if (cur->state == sockunconnected) {
 234                         radlog(L_DBG, "rlm_sql (%s): Ignoring unconnected handle %d..", inst->config->xlat_name, cur->id);
 235                         unconnected++;
 236 #ifdef HAVE_PTHREAD_H
 237                         pthread_mutex_unlock(&cur->mutex);
 238 #endif
 239                         goto next;
 240                 }
 241
 242                 /* should be connected, grab it */
 243                 radlog(L_DBG, "rlm_sql (%s): Reserving sql socket id: %d", inst->config->xlat_name, cur->id);
 244
 245                 if (unconnected != 0 || tried_to_connect != 0) {
 246                         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);
 247                 }
 248
 249                 /*
 250                  *      The socket is returned in the locked
 251                  *      state.
 252                  *
 253                  *      We also remember where we left off,
 254                  *      so that the next search can start from
 255                  *      here.
 256                  *
 257                  *      Note that multiple threads MAY over-write
 258                  *      the 'inst->last_used' variable.  This is OK,
 259                  *      as it's a pointer only used for reading.
 260                  */
 261                 inst->last_used = cur->next;
 262                 return cur;
 263
 264                 /* move along the list */
 265         next:
 266                 cur = cur->next;
 267
 268                 /*
 269                  *      Because we didnt start at the start, once we
 270                  *      hit the end of the linklist, we should go
 271                  *      back to the beginning and work toward the
 272                  *      middle!
 273                  */
 274                 if (!cur) {
 275                         cur = inst->sqlpool;
 276                 }
 277
 278                 /*
 279                  *      If we're at the socket we started
 280                  */
 281                 if (cur == start) {
 282                         break;
 283                 }
 284         }
 285
 286         /* We get here if every DB handle is unconnected and unconnectABLE */
 287         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);
 288         return NULL;
 289 }
 290
 291 /*************************************************************************
 292  *
 293  *      Function: sql_release_socket
 294  *
 295  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 296  *
 297  *************************************************************************/
 298 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket)
 299 {
 300 #ifdef HAVE_PTHREAD_H
 301         pthread_mutex_unlock(&sqlsocket->mutex);
 302 #endif
 303
 304         radlog(L_DBG, "rlm_sql (%s): Released sql socket id: %d",
 305                inst->config->xlat_name, sqlsocket->id);
 306
 307         return 0;
 308 }
 309
 310
 311 /*************************************************************************
 312  *
 313  *      Function: sql_userparse
 314  *
 315  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 316  *
 317  *************************************************************************/
 318 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row)
 319 {
 320         VALUE_PAIR *pair;
 321         char *ptr, *value;
 322         char buf[MAX_STRING_LEN];
 323         char do_xlat = 0;
 324         LRAD_TOKEN token, operator = T_EOL;
 325
 326         /*
 327          *      Verify the 'Attribute' field
 328          */
 329         if (row[2] == NULL || row[2][0] == '\0') {
 330                 radlog(L_ERR, "rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row.");
 331                 return -1;
 332         }
 333
 334         /*
 335          *      Verify the 'op' field
 336          */
 337         if (row[4] != NULL && row[4][0] != '\0') {
 338                 ptr = row[4];
 339                 operator = gettoken(&ptr, buf, sizeof(buf));
 340         }
 341         if (operator <= T_EOL) {
 342                 /*
 343                  *  Complain about empty or invalid 'op' field
 344                  */
 345                 operator = T_OP_CMP_EQ;
 346                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 347                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 348         }
 349
 350         /*
 351          *      The 'Value' field may be empty or NULL
 352          */
 353         value = row[3];
 354         /*
 355          *      If we have a new-style quoted string, where the
 356          *      *entire* string is quoted, do xlat's.
 357          */
 358         if (row[3] != NULL &&
 359            ((row[3][0] == '\'') || (row[3][0] == '`') || (row[3][0] == '"')) &&
 360            (row[3][0] == row[3][strlen(row[3])-1])) {
 361
 362                 token = gettoken(&value, buf, sizeof(buf));
 363                 switch (token) {
 364                         /*
 365                          *      Take the unquoted string.
 366                          */
 367                 case T_SINGLE_QUOTED_STRING:
 368                 case T_DOUBLE_QUOTED_STRING:
 369                         value = buf;
 370                         break;
 371
 372                         /*
 373                          *      Mark the pair to be allocated later.
 374                          */
 375                 case T_BACK_QUOTED_STRING:
 376                         value = NULL;
 377                         do_xlat = 1;
 378                         break;
 379
 380                         /*
 381                          *      Keep the original string.
 382                          */
 383                 default:
 384                         value = row[3];
 385                         break;
 386                 }
 387         }
 388
 389         /*
 390          *      Create the pair
 391          */
 392         pair = pairmake(row[2], value, operator);
 393         if (pair == NULL) {
 394                 radlog(L_ERR, "rlm_sql: Failed to create the pair: %s", librad_errstr);
 395                 return -1;
 396         }
 397         if (do_xlat) {
 398                 pair->flags.do_xlat = 1;
 399                 strlcpy(pair->vp_strvalue, buf, sizeof(pair->vp_strvalue));
 400                 pair->length = 0;
 401         }
 402
 403         /*
 404          *      Add the pair into the packet
 405          */
 406         pairadd(first_pair, pair);
 407         return 0;
 408 }
 409
 410
 411 /*************************************************************************
 412  *
 413  *      Function: rlm_sql_fetch_row
 414  *
 415  *      Purpose: call the module's sql_fetch_row and implement re-connect
 416  *
 417  *************************************************************************/
 418 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 419 {
 420         int ret;
 421
 422         if (sqlsocket->conn) {
 423                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 424         } else {
 425                 ret = SQL_DOWN;
 426         }
 427
 428         if (ret == SQL_DOWN) {
 429                 /* close the socket that failed, but only if it was open */
 430                 if (sqlsocket->conn) {
 431                         (inst->module->sql_close)(sqlsocket, inst->config);
 432                 }
 433
 434                 /* reconnect the socket */
 435                 if (connect_single_socket(sqlsocket, inst) < 0) {
 436                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 437                         return -1;
 438                 }
 439
 440                 /* retry the query on the newly connected socket */
 441                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 442
 443                 if (ret) {
 444                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 445                                inst->config->xlat_name);
 446                         return -1;
 447                 }
 448         }
 449
 450         return ret;
 451 }
 452
 453 /*************************************************************************
 454  *
 455  *      Function: rlm_sql_query
 456  *
 457  *      Purpose: call the module's sql_query and implement re-connect
 458  *
 459  *************************************************************************/
 460 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 461 {
 462         int ret;
 463
 464         /*
 465          *      If there's no query, return an error.
 466          */
 467         if (!query || !*query) {
 468                 return -1;
 469         }
 470
 471         ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 472
 473         if (ret == SQL_DOWN) {
 474                 /* close the socket that failed */
 475                 if (sqlsocket->state == sockconnected) {
 476                         (inst->module->sql_close)(sqlsocket, inst->config);
 477                 }
 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                 if (sqlsocket->state == sockconnected) {
 521                         (inst->module->sql_close)(sqlsocket, inst->config);
 522                 }
 523
 524                 /* reconnect the socket */
 525                 if (connect_single_socket(sqlsocket, inst) < 0) {
 526                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 527                         return -1;
 528                 }
 529
 530                 /* retry the query on the newly connected socket */
 531                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 532
 533                 if (ret) {
 534                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 535                                inst->config->xlat_name);
 536                         return -1;
 537                 }
 538         }
 539
 540         return ret;
 541 }
 542
 543
 544 /*************************************************************************
 545  *
 546  *      Function: sql_getvpdata
 547  *
 548  *      Purpose: Get any group check or reply pairs
 549  *
 550  *************************************************************************/
 551 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query)
 552 {
 553         SQL_ROW row;
 554         int     rows = 0;
 555
 556         /*
 557          *      If there's no query, return an error.
 558          */
 559         if (!query || !*query) {
 560                 return -1;
 561         }
 562
 563         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 564                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 565                 return -1;
 566         }
 567         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 568                 row = sqlsocket->row;
 569                 if (!row)
 570                         break;
 571                 if (sql_userparse(pair, row) != 0) {
 572                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 573                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 574                         return -1;
 575                 }
 576                 rows++;
 577         }
 578         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 579
 580         return rows;
 581 }
 582
 583 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 584 {
 585         FILE   *sqlfile = NULL;
 586
 587         if (inst->config->sqltrace) {
 588                 char buffer[8192];
 589
 590                 if (!radius_xlat(buffer, sizeof(buffer),
 591                                  inst->config->tracefile, request, NULL)) {
 592                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 593                          inst->config->xlat_name);
 594                   return;
 595                 }
 596
 597                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 598                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 599                                inst->config->xlat_name,
 600                                buffer);
 601                 } else {
 602                         int fd = fileno(sqlfile);
 603
 604                         rad_lockfd(fd, MAX_QUERY_LEN);
 605                         fputs(querystr, sqlfile);
 606                         fputs(";\n", sqlfile);
 607                         fclose(sqlfile); /* and release the lock */
 608                 }
 609         }
 610 }