src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c

   1 /*
   2  * sql_oracle.c Oracle (OCI) routines for rlm_sql
   3  *
   4  *   This program is free software; you can redistribute it and/or modify
   5  *   it under the terms of the GNU General Public License as published by
   6  *   the Free Software Foundation; either version 2 of the License, or
   7  *   (at your option) any later version.
   8  *
   9  *   This program is distributed in the hope that it will be useful,
  10  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  *   GNU General Public License for more details.
  13  *
  14  *   You should have received a copy of the GNU General Public License
  15  *   along with this program; if not, write to the Free Software
  16  *   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  17  *
  18  * Copyright 2000  The FreeRADIUS server project
  19  * Copyright 2000  David Kerry <davidk@snti.com>
  20  */
  21
  22 #include <freeradius-devel/autoconf.h>
  23
  24 #include <stdio.h>
  25 #include <sys/stat.h>
  26 #include <sys/time.h>
  27 #include <stdlib.h>
  28 #include <string.h>
  29
  30 #include        <freeradius-devel/radiusd.h>
  31
  32 #include <oci.h>
  33 #include "rlm_sql.h"
  34
  35 typedef struct rlm_sql_oracle_sock {
  36         OCIEnv          *env;
  37         OCIError        *errHandle;
  38         OCISvcCtx       *conn;
  39         OCIStmt         *queryHandle;
  40         sb2             *indicators;
  41         char            **results;
  42         int             id;
  43         int             in_use;
  44         struct timeval  tv;
  45 } rlm_sql_oracle_sock;
  46
  47 #define MAX_DATASTR_LEN 64
  48
  49
  50 /*************************************************************************
  51  *
  52  *      Function: sql_error
  53  *
  54  *      Purpose: database specific error. Returns error associated with
  55  *               connection
  56  *
  57  *************************************************************************/
  58 static char *sql_error(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
  59
  60         static char     msgbuf[512];
  61         sb4             errcode = 0;
  62         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
  63
  64         if (!oracle_sock) return "rlm_sql_oracle: no connection to db";
  65
  66         memset((void *) msgbuf, (int)'\0', sizeof(msgbuf));
  67
  68         OCIErrorGet((dvoid *) oracle_sock->errHandle, (ub4) 1, (text *) NULL,
  69                 &errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
  70         if (errcode) {
  71                 return msgbuf;
  72         }
  73         else {
  74                 return NULL;
  75         }
  76 }
  77
  78 /*************************************************************************
  79  *
  80  *      Function: sql_check_error
  81  *
  82  *      Purpose: check the error to see if the server is down
  83  *
  84  *************************************************************************/
  85 static int sql_check_error(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
  86
  87         if (strstr(sql_error(sqlsocket, config), "ORA-03113") ||
  88                         strstr(sql_error(sqlsocket, config), "ORA-03114")) {
  89                 radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NOT_CONNECTED");
  90                 return SQL_DOWN;
  91         }
  92         else {
  93                 radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NORMAL");
  94                 return -1;
  95         }
  96 }
  97
  98 /*************************************************************************
  99  *
 100  *      Function: sql_close
 101  *
 102  *      Purpose: database specific close. Closes an open database
 103  *               connection and cleans up any open handles.
 104  *
 105  *************************************************************************/
 106 static int sql_close(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 107
 108         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 109
 110         if (oracle_sock->conn) {
 111                 OCILogoff (oracle_sock->conn, oracle_sock->errHandle);
 112         }
 113
 114         if (oracle_sock->queryHandle) {
 115                 OCIHandleFree((dvoid *)oracle_sock->queryHandle, (ub4) OCI_HTYPE_STMT);
 116         }
 117         if (oracle_sock->errHandle) {
 118                 OCIHandleFree((dvoid *)oracle_sock->errHandle, (ub4) OCI_HTYPE_ERROR);
 119         }
 120         if (oracle_sock->env) {
 121                 OCIHandleFree((dvoid *)oracle_sock->env, (ub4) OCI_HTYPE_ENV);
 122         }
 123
 124         oracle_sock->conn = NULL;
 125         free(oracle_sock);
 126         sqlsocket->conn = NULL;
 127
 128         return 0;
 129 }
 130
 131
 132 /*************************************************************************
 133  *
 134  *      Function: sql_init_socket
 135  *
 136  *      Purpose: Establish connection to the db
 137  *
 138  *************************************************************************/
 139 static int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 140
 141         rlm_sql_oracle_sock *oracle_sock;
 142
 143         if (!sqlsocket->conn) {
 144                 sqlsocket->conn = (rlm_sql_oracle_sock *)rad_malloc(sizeof(rlm_sql_oracle_sock));
 145                 if (!sqlsocket->conn) {
 146                         return -1;
 147                 }
 148         }
 149         memset(sqlsocket->conn,0,sizeof(rlm_sql_oracle_sock));
 150
 151         oracle_sock = sqlsocket->conn;
 152
 153         if (OCIEnvCreate(&oracle_sock->env, OCI_DEFAULT|OCI_THREADED, (dvoid *)0,
 154                 (dvoid * (*)(dvoid *, size_t)) 0,
 155                 (dvoid * (*)(dvoid *, dvoid *, size_t))0,
 156                 (void (*)(dvoid *, dvoid *)) 0,
 157                 0, (dvoid **)0 )) {
 158                 radlog(L_ERR,"rlm_sql_oracle: Couldn't init Oracle OCI environment (OCIEnvCreate())");
 159                 return -1;
 160         }
 161
 162         if (OCIHandleAlloc((dvoid *) oracle_sock->env, (dvoid **) &oracle_sock->errHandle,
 163                 (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
 164         {
 165                 radlog(L_ERR,"rlm_sql_oracle: Couldn't init Oracle ERROR handle (OCIHandleAlloc())");
 166                 return -1;
 167         }
 168
 169         /* Allocate handles for select and update queries */
 170         if (OCIHandleAlloc((dvoid *)oracle_sock->env, (dvoid **) &oracle_sock->queryHandle,
 171                                 (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
 172         {
 173                 radlog(L_ERR,"rlm_sql_oracle: Couldn't init Oracle query handles: %s",
 174                         sql_error(sqlsocket, config));
 175                 return -1;
 176         }
 177
 178
 179         if (OCILogon(oracle_sock->env, oracle_sock->errHandle, &oracle_sock->conn,
 180                         config->sql_login, strlen(config->sql_login),
 181                         config->sql_password,  strlen(config->sql_password),
 182                         config->sql_db, strlen(config->sql_db)))
 183         {
 184                 radlog(L_ERR,"rlm_sql_oracle: Oracle logon failed: '%s'", sql_error(sqlsocket, config));
 185                 sql_close(sqlsocket,config);
 186                 return -1;
 187         }
 188
 189         return 0;
 190 }
 191
 192
 193 /*************************************************************************
 194  *
 195  *      Function: sql_destroy_socket
 196  *
 197  *      Purpose: Free socket and private connection data
 198  *
 199  *************************************************************************/
 200 static int sql_destroy_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config)
 201 {
 202         free(sqlsocket->conn);
 203         sqlsocket->conn = NULL;
 204         return 0;
 205 }
 206
 207 /*************************************************************************
 208  *
 209  *      Function: sql_num_fields
 210  *
 211  *      Purpose: database specific num_fields function. Returns number
 212  *               of columns from query
 213  *
 214  *************************************************************************/
 215 static int sql_num_fields(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 216
 217         ub4             count;
 218         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 219
 220         /* get the number of columns in the select list */
 221         if (OCIAttrGet ((dvoid *)oracle_sock->queryHandle,
 222                         (ub4)OCI_HTYPE_STMT,
 223                         (dvoid *) &count,
 224                         (ub4 *) 0,
 225                         (ub4)OCI_ATTR_PARAM_COUNT,
 226                         oracle_sock->errHandle)) {
 227                 radlog(L_ERR,"rlm_sql_oracle: Error retrieving column count in sql_num_fields: %s",
 228                         sql_error(sqlsocket, config));
 229                 return -1;
 230         }
 231         return count;
 232 }
 233
 234 /*************************************************************************
 235  *
 236  *      Function: sql_query
 237  *
 238  *      Purpose: Issue a non-SELECT query (ie: update/delete/insert) to
 239  *               the database.
 240  *
 241  *************************************************************************/
 242 static int sql_query(SQLSOCK *sqlsocket, SQL_CONFIG *config, char *querystr) {
 243
 244         int     x;
 245         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 246
 247         if (config->sqltrace)
 248                 DEBUG(querystr);
 249         if (oracle_sock->conn == NULL) {
 250                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 251                 return SQL_DOWN;
 252         }
 253
 254         if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 255                                 querystr, strlen(querystr),
 256                                 OCI_NTV_SYNTAX, OCI_DEFAULT))  {
 257                 radlog(L_ERR,"rlm_sql_oracle: prepare failed in sql_query: %s",sql_error(sqlsocket, config));
 258                 return -1;
 259         }
 260
 261         x = OCIStmtExecute(oracle_sock->conn,
 262                                 oracle_sock->queryHandle,
 263                                 oracle_sock->errHandle,
 264                                 (ub4) 1,
 265                                 (ub4) 0,
 266                                 (OCISnapshot *) NULL,
 267                                 (OCISnapshot *) NULL,
 268                                 (ub4) OCI_COMMIT_ON_SUCCESS);
 269
 270         if (x == OCI_SUCCESS) {
 271                 return 0;
 272         }
 273
 274         if (x == OCI_ERROR) {
 275                 radlog(L_ERR,"rlm_sql_oracle: execute query failed in sql_query: %s",
 276                                 sql_error(sqlsocket, config));
 277                 return sql_check_error(sqlsocket, config);
 278         }
 279         else {
 280                 return -1;
 281         }
 282 }
 283
 284
 285 /*************************************************************************
 286  *
 287  *      Function: sql_select_query
 288  *
 289  *      Purpose: Issue a select query to the database
 290  *
 291  *************************************************************************/
 292 static int sql_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config, char *querystr) {
 293
 294         int             x;
 295         int             y;
 296         int             colcount;
 297         OCIParam        *param;
 298         OCIDefine       *define;
 299         ub2             dtype;
 300         ub2             dsize;
 301         char            **rowdata=NULL;
 302         sb2             *indicators;
 303         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 304
 305         if (config->sqltrace)
 306                 DEBUG(querystr);
 307         if (oracle_sock->conn == NULL) {
 308                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 309                 return SQL_DOWN;
 310         }
 311
 312         if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 313                                 querystr, strlen(querystr),
 314                                 OCI_NTV_SYNTAX, OCI_DEFAULT))  {
 315                 radlog(L_ERR,"rlm_sql_oracle: prepare failed in sql_select_query: %s",sql_error(sqlsocket, config));
 316                 return -1;
 317         }
 318
 319         /* Query only one row by default (for now) */
 320         x = OCIStmtExecute(oracle_sock->conn,
 321                                 oracle_sock->queryHandle,
 322                                 oracle_sock->errHandle,
 323                                 (ub4) 0,
 324                                 (ub4) 0,
 325                                 (OCISnapshot *) NULL,
 326                                 (OCISnapshot *) NULL,
 327                                 (ub4) OCI_DEFAULT);
 328
 329         if (x == OCI_NO_DATA) {
 330                 /* Nothing to fetch */
 331                 return 0;
 332         }
 333
 334         if (x != OCI_SUCCESS) {
 335                 radlog(L_ERR,"rlm_sql_oracle: query failed in sql_select_query: %s",
 336                                 sql_error(sqlsocket, config));
 337                 return sql_check_error(sqlsocket, config);
 338         }
 339
 340         /*
 341          * Define where the output from fetch calls will go
 342          *
 343          * This is a gross hack, but it works - we convert
 344          * all data to strings for ease of use.  Fortunately, most
 345          * of the data we deal with is already in string format.
 346          */
 347         colcount = sql_num_fields(sqlsocket, config);
 348
 349         /* DEBUG2("sql_select_query(): colcount=%d",colcount); */
 350
 351         /*
 352          *      FIXME: These malloc's can probably go, as the schema
 353          *      is fixed...
 354          */
 355         rowdata=(char **)rad_malloc(sizeof(char *) * (colcount+1) );
 356         memset(rowdata, 0, (sizeof(char *) * (colcount+1) ));
 357         indicators = (sb2 *) rad_malloc(sizeof(sb2) * (colcount+1) );
 358         memset(indicators, 0, sizeof(sb2) * (colcount+1));
 359
 360         for (y=1; y <= colcount; y++) {
 361                 x=OCIParamGet(oracle_sock->queryHandle, OCI_HTYPE_STMT,
 362                                 oracle_sock->errHandle,
 363                                 (dvoid **)&param,
 364                                 (ub4) y);
 365                 if (x != OCI_SUCCESS) {
 366                         radlog(L_ERR,"rlm_sql_oracle: OCIParamGet() failed in sql_select_query: %s",
 367                                 sql_error(sqlsocket, config));
 368                         return -1;
 369                 }
 370
 371                 x=OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM,
 372                            (dvoid*)&dtype, (ub4*)0, OCI_ATTR_DATA_TYPE,
 373                            oracle_sock->errHandle);
 374                 if (x != OCI_SUCCESS) {
 375                         radlog(L_ERR,"rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 376                                 sql_error(sqlsocket, config));
 377                         return -1;
 378                 }
 379
 380                 dsize=MAX_DATASTR_LEN;
 381
 382                 /*
 383                  * Use the retrieved length of dname to allocate an output
 384                  * buffer, and then define the output variable (but only
 385                  * for char/string type columns).
 386                  */
 387                 switch(dtype) {
 388 #ifdef SQLT_AFC
 389                 case SQLT_AFC:  /* ansii fixed char */
 390 #endif
 391 #ifdef SQLT_AFV
 392                 case SQLT_AFV:  /* ansii var char */
 393 #endif
 394                 case SQLT_VCS:  /* var char */
 395                 case SQLT_CHR:  /* char */
 396                 case SQLT_STR:  /* string */
 397                         x=OCIAttrGet((dvoid*)param, (ub4) OCI_DTYPE_PARAM,
 398                                    (dvoid*) &dsize, (ub4 *)0, (ub4) OCI_ATTR_DATA_SIZE,
 399                                    oracle_sock->errHandle);
 400                         if (x != OCI_SUCCESS) {
 401                                 radlog(L_ERR,"rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 402                                         sql_error(sqlsocket, config));
 403                                 return -1;
 404                         }
 405                         rowdata[y-1]=rad_malloc(dsize+1);
 406                         break;
 407                 case SQLT_DAT:
 408                 case SQLT_INT:
 409                 case SQLT_UIN:
 410                 case SQLT_FLT:
 411                 case SQLT_PDN:
 412                 case SQLT_BIN:
 413                 case SQLT_NUM:
 414                         rowdata[y-1]=rad_malloc(dsize+1);
 415                         break;
 416                 default:
 417                         dsize=0;
 418                         rowdata[y-1]=NULL;
 419                         break;
 420                 }
 421
 422                 indicators[y-1] = 0;
 423                 x=OCIDefineByPos(oracle_sock->queryHandle,
 424                                 &define,
 425                                 oracle_sock->errHandle,
 426                                 y,
 427                                 (ub1 *) rowdata[y-1],
 428                                 dsize+1,
 429                                 SQLT_STR,
 430                                 &indicators[y-1],
 431                                 (dvoid *) 0,
 432                                 (dvoid *) 0,
 433                                 OCI_DEFAULT);
 434
 435                 /*
 436                  *      FIXME: memory leaks of indicators & rowdata?
 437                  */
 438                 if (x != OCI_SUCCESS) {
 439                         radlog(L_ERR,"rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query: %s",
 440                                 sql_error(sqlsocket, config));
 441                         return -1;
 442                 }
 443         }
 444
 445         oracle_sock->results=rowdata;
 446         oracle_sock->indicators=indicators;
 447
 448         return 0;
 449 }
 450
 451
 452 /*************************************************************************
 453  *
 454  *      Function: sql_store_result
 455  *
 456  *      Purpose: database specific store_result function. Returns a result
 457  *               set for the query.
 458  *
 459  *************************************************************************/
 460 static int sql_store_result(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 461         /* Not needed for Oracle */
 462         return 0;
 463 }
 464
 465
 466 /*************************************************************************
 467  *
 468  *      Function: sql_num_rows
 469  *
 470  *      Purpose: database specific num_rows. Returns number of rows in
 471  *               query
 472  *
 473  *************************************************************************/
 474 static int sql_num_rows(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 475
 476         ub4     rows=0;
 477         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 478
 479         OCIAttrGet((CONST dvoid *)oracle_sock->queryHandle,
 480                         OCI_HTYPE_STMT,
 481                         (dvoid *)&rows,
 482                         (ub4 *) sizeof(ub4),
 483                         OCI_ATTR_ROW_COUNT,
 484                         oracle_sock->errHandle);
 485
 486         return rows;
 487 }
 488
 489
 490 /*************************************************************************
 491  *
 492  *      Function: sql_fetch_row
 493  *
 494  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 495  *               with all the data for the query in 'sqlsocket->row'. Returns
 496  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 497  *
 498  *************************************************************************/
 499 static int sql_fetch_row(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 500
 501         int     x;
 502         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 503
 504         if (oracle_sock->conn == NULL) {
 505                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 506                 return SQL_DOWN;
 507         }
 508
 509         sqlsocket->row = NULL;
 510
 511         x=OCIStmtFetch(oracle_sock->queryHandle,
 512                         oracle_sock->errHandle,
 513                         1,
 514                         OCI_FETCH_NEXT,
 515                         OCI_DEFAULT);
 516
 517         if (x == OCI_SUCCESS) {
 518                 sqlsocket->row = oracle_sock->results;
 519                 return 0;
 520         }
 521
 522         if (x == OCI_ERROR) {
 523                 radlog(L_ERR,"rlm_sql_oracle: fetch failed in sql_fetch_row: %s",
 524                                 sql_error(sqlsocket, config));
 525                 return sql_check_error(sqlsocket, config);
 526         }
 527         else {
 528                 return -1;
 529         }
 530 }
 531
 532
 533
 534 /*************************************************************************
 535  *
 536  *      Function: sql_free_result
 537  *
 538  *      Purpose: database specific free_result. Frees memory allocated
 539  *               for a result set
 540  *
 541  *************************************************************************/
 542 static int sql_free_result(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 543
 544         int x;
 545         int num_fields;
 546
 547         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 548
 549         /* Cancel the cursor first */
 550         x=OCIStmtFetch(oracle_sock->queryHandle,
 551                         oracle_sock->errHandle,
 552                         0,
 553                         OCI_FETCH_NEXT,
 554                         OCI_DEFAULT);
 555
 556         num_fields = sql_num_fields(sqlsocket, config);
 557         if (num_fields >= 0) {
 558                 for(x=0; x < num_fields; x++) {
 559                         free(oracle_sock->results[x]);
 560                 }
 561                 free(oracle_sock->results);
 562                 free(oracle_sock->indicators);
 563         }
 564         oracle_sock->results=NULL;
 565         return 0;
 566 }
 567
 568
 569
 570 /*************************************************************************
 571  *
 572  *      Function: sql_finish_query
 573  *
 574  *      Purpose: End the query, such as freeing memory
 575  *
 576  *************************************************************************/
 577 static int sql_finish_query(SQLSOCK *sqlsocket, SQL_CONFIG *config)
 578 {
 579         return 0;
 580 }
 581
 582
 583
 584 /*************************************************************************
 585  *
 586  *      Function: sql_finish_select_query
 587  *
 588  *      Purpose: End the select query, such as freeing memory or result
 589  *
 590  *************************************************************************/
 591 static int sql_finish_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 592
 593         int     x=0;
 594         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 595
 596         if (oracle_sock->results) {
 597                 while(oracle_sock->results[x]) free(oracle_sock->results[x++]);
 598                 free(oracle_sock->results);
 599                 free(oracle_sock->indicators);
 600                 oracle_sock->results=NULL;
 601         }
 602
 603         return 0;
 604 }
 605
 606
 607 /*************************************************************************
 608  *
 609  *      Function: sql_affected_rows
 610  *
 611  *      Purpose: Return the number of rows affected by the query (update,
 612  *               or insert)
 613  *
 614  *************************************************************************/
 615 static int sql_affected_rows(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 616
 617         return sql_num_rows(sqlsocket, config);
 618 }
 619
 620
 621 /* Exported to rlm_sql */
 622 rlm_sql_module_t rlm_sql_oracle = {
 623         "rlm_sql_oracle",
 624         sql_init_socket,
 625         sql_destroy_socket,
 626         sql_query,
 627         sql_select_query,
 628         sql_store_result,
 629         sql_num_fields,
 630         sql_num_rows,
 631         sql_fetch_row,
 632         sql_free_result,
 633         sql_error,
 634         sql_close,
 635         sql_finish_query,
 636         sql_finish_select_query,
 637         sql_affected_rows
 638 };