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,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(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
  56
  57         static char     msgbuf[512];
  58         sb4             errcode = 0;
  59         rlm_sql_oracle_sock *oracle_sock = sqlsocket->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(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
  83
  84         if (strstr(sql_error(sqlsocket, config), "ORA-03113") ||
  85                         strstr(sql_error(sqlsocket, 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(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 104
 105         rlm_sql_oracle_sock *oracle_sock = sqlsocket->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         sqlsocket->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(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 137
 138         rlm_sql_oracle_sock *oracle_sock;
 139
 140         if (!sqlsocket->conn) {
 141                 sqlsocket->conn = (rlm_sql_oracle_sock *)rad_malloc(sizeof(rlm_sql_oracle_sock));
 142                 if (!sqlsocket->conn) {
 143                         return -1;
 144                 }
 145         }
 146         memset(sqlsocket->conn,0,sizeof(rlm_sql_oracle_sock));
 147
 148         oracle_sock = sqlsocket->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(sqlsocket, 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(sqlsocket, config));
 182                 sql_close(sqlsocket,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(SQLSOCK *sqlsocket, SQL_CONFIG *config)
 198 {
 199         free(sqlsocket->conn);
 200         sqlsocket->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(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 213
 214         ub4             count;
 215         rlm_sql_oracle_sock *oracle_sock = sqlsocket->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(sqlsocket, 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(SQLSOCK *sqlsocket, SQL_CONFIG *config, char *querystr) {
 240
 241         int     x;
 242         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 243
 244         if (config->sqltrace)
 245                 DEBUG(querystr);
 246         if (oracle_sock->conn == NULL) {
 247                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 248                 return SQL_DOWN;
 249         }
 250
 251         if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 252                                 querystr, strlen(querystr),
 253                                 OCI_NTV_SYNTAX, OCI_DEFAULT))  {
 254                 radlog(L_ERR,"rlm_sql_oracle: prepare failed in sql_query: %s",sql_error(sqlsocket, config));
 255                 return -1;
 256         }
 257
 258         x = OCIStmtExecute(oracle_sock->conn,
 259                                 oracle_sock->queryHandle,
 260                                 oracle_sock->errHandle,
 261                                 (ub4) 1,
 262                                 (ub4) 0,
 263                                 (OCISnapshot *) NULL,
 264                                 (OCISnapshot *) NULL,
 265                                 (ub4) OCI_COMMIT_ON_SUCCESS);
 266
 267         if (x == OCI_SUCCESS) {
 268                 return 0;
 269         }
 270
 271         if (x == OCI_ERROR) {
 272                 radlog(L_ERR,"rlm_sql_oracle: execute query failed in sql_query: %s",
 273                                 sql_error(sqlsocket, config));
 274                 return sql_check_error(sqlsocket, config);
 275         }
 276         else {
 277                 return -1;
 278         }
 279 }
 280
 281
 282 /*************************************************************************
 283  *
 284  *      Function: sql_select_query
 285  *
 286  *      Purpose: Issue a select query to the database
 287  *
 288  *************************************************************************/
 289 static int sql_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config, char *querystr) {
 290
 291         int             x;
 292         int             y;
 293         int             colcount;
 294         OCIParam        *param;
 295         OCIDefine       *define;
 296         ub2             dtype;
 297         ub2             dsize;
 298         char            **rowdata=NULL;
 299         sb2             *indicators;
 300         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 301
 302         if (config->sqltrace)
 303                 DEBUG(querystr);
 304         if (oracle_sock->conn == NULL) {
 305                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 306                 return SQL_DOWN;
 307         }
 308
 309         if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 310                                 querystr, strlen(querystr),
 311                                 OCI_NTV_SYNTAX, OCI_DEFAULT))  {
 312                 radlog(L_ERR,"rlm_sql_oracle: prepare failed in sql_select_query: %s",sql_error(sqlsocket, config));
 313                 return -1;
 314         }
 315
 316         /* Query only one row by default (for now) */
 317         x = OCIStmtExecute(oracle_sock->conn,
 318                                 oracle_sock->queryHandle,
 319                                 oracle_sock->errHandle,
 320                                 (ub4) 0,
 321                                 (ub4) 0,
 322                                 (OCISnapshot *) NULL,
 323                                 (OCISnapshot *) NULL,
 324                                 (ub4) OCI_DEFAULT);
 325
 326         if (x == OCI_NO_DATA) {
 327                 /* Nothing to fetch */
 328                 return 0;
 329         }
 330
 331         if (x != OCI_SUCCESS) {
 332                 radlog(L_ERR,"rlm_sql_oracle: query failed in sql_select_query: %s",
 333                                 sql_error(sqlsocket, config));
 334                 return sql_check_error(sqlsocket, config);
 335         }
 336
 337         /*
 338          * Define where the output from fetch calls will go
 339          *
 340          * This is a gross hack, but it works - we convert
 341          * all data to strings for ease of use.  Fortunately, most
 342          * of the data we deal with is already in string format.
 343          */
 344         colcount = sql_num_fields(sqlsocket, config);
 345
 346         /* DEBUG2("sql_select_query(): colcount=%d",colcount); */
 347
 348         /*
 349          *      FIXME: These malloc's can probably go, as the schema
 350          *      is fixed...
 351          */
 352         rowdata=(char **)rad_malloc(sizeof(char *) * (colcount+1) );
 353         memset(rowdata, 0, (sizeof(char *) * (colcount+1) ));
 354         indicators = (sb2 *) rad_malloc(sizeof(sb2) * (colcount+1) );
 355         memset(indicators, 0, sizeof(sb2) * (colcount+1));
 356
 357         for (y=1; y <= colcount; y++) {
 358                 x=OCIParamGet(oracle_sock->queryHandle, OCI_HTYPE_STMT,
 359                                 oracle_sock->errHandle,
 360                                 (dvoid **)&param,
 361                                 (ub4) y);
 362                 if (x != OCI_SUCCESS) {
 363                         radlog(L_ERR,"rlm_sql_oracle: OCIParamGet() failed in sql_select_query: %s",
 364                                 sql_error(sqlsocket, config));
 365                         return -1;
 366                 }
 367
 368                 x=OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM,
 369                            (dvoid*)&dtype, (ub4*)0, OCI_ATTR_DATA_TYPE,
 370                            oracle_sock->errHandle);
 371                 if (x != OCI_SUCCESS) {
 372                         radlog(L_ERR,"rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 373                                 sql_error(sqlsocket, config));
 374                         return -1;
 375                 }
 376
 377                 dsize=MAX_DATASTR_LEN;
 378
 379                 /*
 380                  * Use the retrieved length of dname to allocate an output
 381                  * buffer, and then define the output variable (but only
 382                  * for char/string type columns).
 383                  */
 384                 switch(dtype) {
 385 #ifdef SQLT_AFC
 386                 case SQLT_AFC:  /* ansii fixed char */
 387 #endif
 388 #ifdef SQLT_AFV
 389                 case SQLT_AFV:  /* ansii var char */
 390 #endif
 391                 case SQLT_VCS:  /* var char */
 392                 case SQLT_CHR:  /* char */
 393                 case SQLT_STR:  /* string */
 394                         x=OCIAttrGet((dvoid*)param, (ub4) OCI_DTYPE_PARAM,
 395                                    (dvoid*) &dsize, (ub4 *)0, (ub4) OCI_ATTR_DATA_SIZE,
 396                                    oracle_sock->errHandle);
 397                         if (x != OCI_SUCCESS) {
 398                                 radlog(L_ERR,"rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 399                                         sql_error(sqlsocket, config));
 400                                 return -1;
 401                         }
 402                         rowdata[y-1]=rad_malloc(dsize+1);
 403                         break;
 404                 case SQLT_DAT:
 405                 case SQLT_INT:
 406                 case SQLT_UIN:
 407                 case SQLT_FLT:
 408                 case SQLT_PDN:
 409                 case SQLT_BIN:
 410                 case SQLT_NUM:
 411                         rowdata[y-1]=rad_malloc(dsize+1);
 412                         break;
 413                 default:
 414                         dsize=0;
 415                         rowdata[y-1]=NULL;
 416                         break;
 417                 }
 418
 419                 indicators[y-1] = 0;
 420                 x=OCIDefineByPos(oracle_sock->queryHandle,
 421                                 &define,
 422                                 oracle_sock->errHandle,
 423                                 y,
 424                                 (ub1 *) rowdata[y-1],
 425                                 dsize+1,
 426                                 SQLT_STR,
 427                                 &indicators[y-1],
 428                                 (dvoid *) 0,
 429                                 (dvoid *) 0,
 430                                 OCI_DEFAULT);
 431
 432                 /*
 433                  *      FIXME: memory leaks of indicators & rowdata?
 434                  */
 435                 if (x != OCI_SUCCESS) {
 436                         radlog(L_ERR,"rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query: %s",
 437                                 sql_error(sqlsocket, config));
 438                         return -1;
 439                 }
 440         }
 441
 442         oracle_sock->results=rowdata;
 443         oracle_sock->indicators=indicators;
 444
 445         return 0;
 446 }
 447
 448
 449 /*************************************************************************
 450  *
 451  *      Function: sql_store_result
 452  *
 453  *      Purpose: database specific store_result function. Returns a result
 454  *               set for the query.
 455  *
 456  *************************************************************************/
 457 static int sql_store_result(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 458         /* Not needed for Oracle */
 459         return 0;
 460 }
 461
 462
 463 /*************************************************************************
 464  *
 465  *      Function: sql_num_rows
 466  *
 467  *      Purpose: database specific num_rows. Returns number of rows in
 468  *               query
 469  *
 470  *************************************************************************/
 471 static int sql_num_rows(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 472
 473         ub4     rows=0;
 474         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 475
 476         OCIAttrGet((CONST dvoid *)oracle_sock->queryHandle,
 477                         OCI_HTYPE_STMT,
 478                         (dvoid *)&rows,
 479                         (ub4 *) sizeof(ub4),
 480                         OCI_ATTR_ROW_COUNT,
 481                         oracle_sock->errHandle);
 482
 483         return rows;
 484 }
 485
 486
 487 /*************************************************************************
 488  *
 489  *      Function: sql_fetch_row
 490  *
 491  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 492  *               with all the data for the query in 'sqlsocket->row'. Returns
 493  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 494  *
 495  *************************************************************************/
 496 static int sql_fetch_row(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 497
 498         int     x;
 499         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 500
 501         if (oracle_sock->conn == NULL) {
 502                 radlog(L_ERR, "rlm_sql_oracle: Socket not connected");
 503                 return SQL_DOWN;
 504         }
 505
 506         sqlsocket->row = NULL;
 507
 508         x=OCIStmtFetch(oracle_sock->queryHandle,
 509                         oracle_sock->errHandle,
 510                         1,
 511                         OCI_FETCH_NEXT,
 512                         OCI_DEFAULT);
 513
 514         if (x == OCI_SUCCESS) {
 515                 sqlsocket->row = oracle_sock->results;
 516                 return 0;
 517         }
 518
 519         if (x == OCI_ERROR) {
 520                 radlog(L_ERR,"rlm_sql_oracle: fetch failed in sql_fetch_row: %s",
 521                                 sql_error(sqlsocket, config));
 522                 return sql_check_error(sqlsocket, config);
 523         }
 524         else {
 525                 return -1;
 526         }
 527 }
 528
 529
 530
 531 /*************************************************************************
 532  *
 533  *      Function: sql_free_result
 534  *
 535  *      Purpose: database specific free_result. Frees memory allocated
 536  *               for a result set
 537  *
 538  *************************************************************************/
 539 static int sql_free_result(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 540
 541         int x;
 542         int num_fields;
 543
 544         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 545
 546         /* Cancel the cursor first */
 547         x=OCIStmtFetch(oracle_sock->queryHandle,
 548                         oracle_sock->errHandle,
 549                         0,
 550                         OCI_FETCH_NEXT,
 551                         OCI_DEFAULT);
 552
 553         num_fields = sql_num_fields(sqlsocket, config);
 554         if (num_fields >= 0) {
 555                 for(x=0; x < num_fields; x++) {
 556                         free(oracle_sock->results[x]);
 557                 }
 558                 free(oracle_sock->results);
 559                 free(oracle_sock->indicators);
 560         }
 561         oracle_sock->results=NULL;
 562         return 0;
 563 }
 564
 565
 566
 567 /*************************************************************************
 568  *
 569  *      Function: sql_finish_query
 570  *
 571  *      Purpose: End the query, such as freeing memory
 572  *
 573  *************************************************************************/
 574 static int sql_finish_query(SQLSOCK *sqlsocket, SQL_CONFIG *config)
 575 {
 576         return 0;
 577 }
 578
 579
 580
 581 /*************************************************************************
 582  *
 583  *      Function: sql_finish_select_query
 584  *
 585  *      Purpose: End the select query, such as freeing memory or result
 586  *
 587  *************************************************************************/
 588 static int sql_finish_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 589
 590         int     x=0;
 591         rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
 592
 593         if (oracle_sock->results) {
 594                 while(oracle_sock->results[x]) free(oracle_sock->results[x++]);
 595                 free(oracle_sock->results);
 596                 free(oracle_sock->indicators);
 597                 oracle_sock->results=NULL;
 598         }
 599
 600         return 0;
 601 }
 602
 603
 604 /*************************************************************************
 605  *
 606  *      Function: sql_affected_rows
 607  *
 608  *      Purpose: Return the number of rows affected by the query (update,
 609  *               or insert)
 610  *
 611  *************************************************************************/
 612 static int sql_affected_rows(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 613
 614         return sql_num_rows(sqlsocket, config);
 615 }
 616
 617
 618 /* Exported to rlm_sql */
 619 rlm_sql_module_t rlm_sql_oracle = {
 620         "rlm_sql_oracle",
 621         sql_init_socket,
 622         sql_destroy_socket,
 623         sql_query,
 624         sql_select_query,
 625         sql_store_result,
 626         sql_num_fields,
 627         sql_num_rows,
 628         sql_fetch_row,
 629         sql_free_result,
 630         sql_error,
 631         sql_close,
 632         sql_finish_query,
 633         sql_finish_select_query,
 634         sql_affected_rows
 635 };