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