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