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