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