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 RCSID("$Id$")
  23
  24 #include <freeradius-devel/radiusd.h>
  25
  26 #include <sys/stat.h>
  27
  28 /*
  29  *      There are typos in the Oracle Instaclient where the definition controlling prototype
  30  *      format is _STDC_ (not __STDC__).
  31  *
  32  *      There are still cases where the oracle headers do not declare ANSI C function types
  33  *      but this at least cuts down the errors.
  34  *
  35  *      -Wno-strict-prototypes does the rest.
  36  */
  37 #if defined(__STDC__) && __STDC__
  38 #  define _STDC_
  39 #endif
  40
  41 #include <oci.h>
  42 #include "rlm_sql.h"
  43
  44 typedef struct rlm_sql_oracle_conn_t {
  45         OCIEnv          *env;
  46         OCIStmt         *query;
  47         OCIError        *error;
  48         OCISvcCtx       *ctx;
  49         sb2             *ind;
  50         char            **row;
  51         int             id;
  52         int             col_count;      //!< Number of columns associated with the result set
  53         struct timeval  tv;
  54 } rlm_sql_oracle_conn_t;
  55
  56 #define MAX_DATASTR_LEN 64
  57
  58
  59 /*************************************************************************
  60  *
  61  *      Function: sql_error
  62  *
  63  *      Purpose: database specific error. Returns error associated with
  64  *             connection
  65  *
  66  *************************************************************************/
  67 static char const *sql_error(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
  68 {
  69
  70         static char     msgbuf[512];
  71         sb4             errcode = 0;
  72         rlm_sql_oracle_conn_t *conn = handle->conn;
  73
  74         if (!conn) return "rlm_sql_oracle: no connection to db";
  75
  76         msgbuf[0] = '\0';
  77
  78         OCIErrorGet((dvoid *) conn->error, 1, (OraText *) NULL, &errcode, (OraText *) msgbuf,
  79                     sizeof(msgbuf), OCI_HTYPE_ERROR);
  80         if (errcode) {
  81                 return msgbuf;
  82         }
  83
  84         return NULL;
  85 }
  86
  87 /*************************************************************************
  88  *
  89  *      Function: sql_check_error
  90  *
  91  *      Purpose: check the error to see if the server is down
  92  *
  93  *************************************************************************/
  94 static int sql_check_error(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
  95 {
  96
  97         if (strstr(sql_error(handle, config), "ORA-03113") || strstr(sql_error(handle, config), "ORA-03114")) {
  98                 ERROR("rlm_sql_oracle: OCI_SERVER_NOT_CONNECTED");
  99                 return RLM_SQL_RECONNECT;
 100         }
 101         else {
 102                 ERROR("rlm_sql_oracle: OCI_SERVER_NORMAL");
 103                 return -1;
 104         }
 105 }
 106
 107 static int sql_socket_destructor(void *c)
 108 {
 109         rlm_sql_oracle_conn_t *conn = c;
 110
 111         if (conn->ctx) {
 112                 OCILogoff(conn->ctx, conn->error);
 113         }
 114
 115         if (conn->query) {
 116                 OCIHandleFree((dvoid *)conn->query, OCI_HTYPE_STMT);
 117         }
 118
 119         if (conn->error) {
 120                 OCIHandleFree((dvoid *)conn->error, OCI_HTYPE_ERROR);
 121         }
 122
 123         if (conn->env) {
 124                 OCIHandleFree((dvoid *)conn->env, OCI_HTYPE_ENV);
 125         }
 126
 127         return 0;
 128 }
 129
 130 /*************************************************************************
 131  *
 132  *      Function: sql_socket_init
 133  *
 134  *      Purpose: Establish connection to the db
 135  *
 136  *************************************************************************/
 137 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 138 {
 139
 140         rlm_sql_oracle_conn_t *conn;
 141
 142         MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_oracle_conn_t));
 143         talloc_set_destructor((void *) conn, sql_socket_destructor);
 144
 145         /*
 146          *      Initialises the oracle environment
 147          */
 148         if (OCIEnvCreate(&conn->env, OCI_DEFAULT | OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL)) {
 149                 ERROR("rlm_sql_oracle: Couldn't init Oracle OCI environment (OCIEnvCreate())");
 150
 151                 return -1;
 152         }
 153
 154         /*
 155          *      Allocates an error handle
 156          */
 157         if (OCIHandleAlloc((dvoid *)conn->env, (dvoid **)&conn->error, OCI_HTYPE_ERROR, 0, NULL)) {
 158                 ERROR("rlm_sql_oracle: Couldn't init Oracle ERROR handle (OCIHandleAlloc())");
 159
 160                 return -1;
 161         }
 162
 163         /*
 164          *      Allocate handles for select and update queries
 165          */
 166         if (OCIHandleAlloc((dvoid *)conn->env, (dvoid **)&conn->query, OCI_HTYPE_STMT, 0, NULL)) {
 167                 ERROR("rlm_sql_oracle: Couldn't init Oracle query handles: %s", sql_error(handle, config));
 168
 169                 return -1;
 170         }
 171
 172         /*
 173          *      Login to the oracle server
 174          */
 175         if (OCILogon(conn->env, conn->error, &conn->ctx,
 176                      (OraText const *)config->sql_login, strlen(config->sql_login),
 177                      (OraText const *)config->sql_password, strlen(config->sql_password),
 178                      (OraText const *)config->sql_db, strlen(config->sql_db))) {
 179                 ERROR("rlm_sql_oracle: Oracle logon failed: '%s'", sql_error(handle, config));
 180
 181                 return -1;
 182         }
 183
 184         return 0;
 185 }
 186
 187 /*************************************************************************
 188  *
 189  *      Function: sql_num_fields
 190  *
 191  *      Purpose: database specific num_fields function. Returns number
 192  *             of columns from query
 193  *
 194  *************************************************************************/
 195 static int sql_num_fields(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 196 {
 197         int count;
 198         rlm_sql_oracle_conn_t *conn = handle->conn;
 199
 200         /* get the number of columns in the select list */
 201         if (OCIAttrGet((dvoid *)conn->query, OCI_HTYPE_STMT, (dvoid *)&count, NULL, OCI_ATTR_PARAM_COUNT,
 202                        conn->error)) {
 203                 ERROR("rlm_sql_oracle: Error retrieving column count : %s", sql_error(handle, config));
 204
 205                 return -1;
 206         }
 207
 208         return count;
 209 }
 210
 211 /*************************************************************************
 212  *
 213  *      Function: sql_query
 214  *
 215  *      Purpose: Issue a non-SELECT query (ie: update/delete/insert) to
 216  *             the database.
 217  *
 218  *************************************************************************/
 219 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
 220 {
 221         int status;
 222         rlm_sql_oracle_conn_t *conn = handle->conn;
 223
 224         OraText *oracle_query;
 225
 226         memcpy(&oracle_query, &query, sizeof(oracle_query));
 227
 228         if (!conn->ctx) {
 229                 ERROR("rlm_sql_oracle: Socket not connected");
 230
 231                 return RLM_SQL_RECONNECT;
 232         }
 233
 234         if (OCIStmtPrepare(conn->query, conn->error, oracle_query, strlen(query),
 235                            OCI_NTV_SYNTAX, OCI_DEFAULT)) {
 236                 ERROR("rlm_sql_oracle: prepare failed in sql_query: %s", sql_error(handle, config));
 237
 238                 return -1;
 239         }
 240
 241         status = OCIStmtExecute(conn->ctx, conn->query, conn->error, 1, 0,
 242                                 NULL, NULL, OCI_COMMIT_ON_SUCCESS);
 243
 244         if (status == OCI_SUCCESS) {
 245                 return 0;
 246         }
 247
 248         if (status == OCI_ERROR) {
 249                 ERROR("rlm_sql_oracle: execute query failed in sql_query: %s", sql_error(handle, config));
 250                 return sql_check_error(handle, config);
 251         }
 252
 253         return -1;
 254 }
 255
 256
 257 /*************************************************************************
 258  *
 259  *      Function: sql_select_query
 260  *
 261  *      Purpose: Issue a select query to the database
 262  *
 263  *************************************************************************/
 264 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
 265 {
 266
 267         int             status;
 268         char            **row;
 269
 270         int             i;
 271         OCIParam        *param;
 272         OCIDefine       *define;
 273
 274         ub2             dtype;
 275         ub2             dsize;
 276
 277         sb2             *ind;
 278
 279         OraText         *oracle_query;
 280
 281         rlm_sql_oracle_conn_t *conn = handle->conn;
 282
 283         memcpy(&oracle_query, &query, sizeof(oracle_query));
 284
 285         if (OCIStmtPrepare(conn->query, conn->error, oracle_query, strlen(query), OCI_NTV_SYNTAX,
 286                            OCI_DEFAULT)) {
 287                 ERROR("rlm_sql_oracle: prepare failed in sql_select_query: %s", sql_error(handle, config));
 288
 289                 return -1;
 290         }
 291
 292         /*
 293          *      Retrieve a single row
 294          */
 295         status = OCIStmtExecute(conn->ctx, conn->query, conn->error, 0, 0, NULL, NULL, OCI_DEFAULT);
 296         if (status == OCI_NO_DATA) {
 297                 return 0;
 298         }
 299
 300         if (status != OCI_SUCCESS) {
 301                 ERROR("rlm_sql_oracle: query failed in sql_select_query: %s", sql_error(handle, config));
 302
 303                 return sql_check_error(handle, config);
 304         }
 305
 306         /*
 307          *      We only need to do this once per result set, because
 308          *      the number of columns won't change.
 309          */
 310         if (conn->col_count == 0) {
 311                 conn->col_count = sql_num_fields(handle, config);
 312
 313                 if (conn->col_count == 0) {
 314                         return -1;
 315                 }
 316         }
 317
 318         MEM(row = talloc_zero_array(conn, char*, conn->col_count + 1));
 319         MEM(ind = talloc_zero_array(row, sb2, conn->col_count + 1));
 320
 321         for (i = 0; i < conn->col_count; i++) {
 322                 status = OCIParamGet(conn->query, OCI_HTYPE_STMT, conn->error, (dvoid **)&param, i + 1);
 323                 if (status != OCI_SUCCESS) {
 324                         ERROR("rlm_sql_oracle: OCIParamGet() failed in sql_select_query: %s",
 325                                sql_error(handle, config));
 326
 327                         goto error;
 328                 }
 329
 330                 status = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid*)&dtype, NULL, OCI_ATTR_DATA_TYPE,
 331                                     conn->error);
 332                 if (status != OCI_SUCCESS) {
 333                         ERROR("rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 334                                sql_error(handle, config));
 335
 336                         goto error;
 337                 }
 338
 339                 dsize = MAX_DATASTR_LEN;
 340
 341                 /*
 342                  *      Use the retrieved length of dname to allocate an output buffer, and then define the output
 343                  *      variable (but only for char/string type columns).
 344                  */
 345                 switch(dtype) {
 346 #ifdef SQLT_AFC
 347                 case SQLT_AFC:  /* ansii fixed char */
 348 #endif
 349 #ifdef SQLT_AFV
 350                 case SQLT_AFV:  /* ansii var char */
 351 #endif
 352                 case SQLT_VCS:  /* var char */
 353                 case SQLT_CHR:  /* char */
 354                 case SQLT_STR:  /* string */
 355                         status = OCIAttrGet((dvoid *)param, OCI_DTYPE_PARAM, (dvoid *)&dsize, NULL,
 356                                             OCI_ATTR_DATA_SIZE, conn->error);
 357                         if (status != OCI_SUCCESS) {
 358                                 ERROR("rlm_sql_oracle: OCIAttrGet() failed in sql_select_query: %s",
 359                                        sql_error(handle, config));
 360
 361                                 goto error;
 362                         }
 363
 364                         MEM(row[i] = talloc_zero_array(row, char, dsize + 1));
 365
 366                         break;
 367                 case SQLT_DAT:
 368                 case SQLT_INT:
 369                 case SQLT_UIN:
 370                 case SQLT_FLT:
 371                 case SQLT_PDN:
 372                 case SQLT_BIN:
 373                 case SQLT_NUM:
 374                         MEM(row[i] = talloc_zero_array(row, char, dsize + 1));
 375
 376                         break;
 377                 default:
 378                         dsize = 0;
 379                         row[i] = NULL;
 380                         break;
 381                 }
 382
 383                 ind[i] = 0;
 384
 385                 /*
 386                  *      Grab the actual row value and write it to the buffer we allocated.
 387                  */
 388                 status = OCIDefineByPos(conn->query, &define, conn->error, i + 1, (ub1 *)row[i], dsize + 1, SQLT_STR,
 389                                         (dvoid *)&ind[i], NULL, NULL, OCI_DEFAULT);
 390
 391                 if (status != OCI_SUCCESS) {
 392                         ERROR("rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query: %s",
 393                                sql_error(handle, config));
 394
 395                         goto error;
 396                 }
 397         }
 398
 399         conn->row = row;
 400         conn->ind = ind;
 401
 402         return 0;
 403
 404  error:
 405         talloc_free(row);
 406
 407         return -1;
 408 }
 409
 410
 411 /*************************************************************************
 412  *
 413  *      Function: sql_store_result
 414  *
 415  *      Purpose: database specific store_result function. Returns a result
 416  *             set for the query.
 417  *
 418  *************************************************************************/
 419 static sql_rcode_t sql_store_result(UNUSED rlm_sql_handle_t *handle,UNUSED rlm_sql_config_t *config)
 420 {
 421         /* Not needed for Oracle */
 422         return 0;
 423 }
 424
 425
 426 /*************************************************************************
 427  *
 428  *      Function: sql_num_rows
 429  *
 430  *      Purpose: database specific num_rows. Returns number of rows in
 431  *             query
 432  *
 433  *************************************************************************/
 434 static int sql_num_rows(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 435 {
 436         rlm_sql_oracle_conn_t *conn = handle->conn;
 437         ub4 rows = 0;
 438         ub4 size = sizeof(ub4);
 439
 440         OCIAttrGet((CONST dvoid *)conn->query, OCI_HTYPE_STMT, (dvoid *)&rows, &size, OCI_ATTR_ROW_COUNT, conn->error);
 441
 442         return rows;
 443 }
 444
 445
 446 /*************************************************************************
 447  *
 448  *      Function: sql_fetch_row
 449  *
 450  *      Purpose: database specific fetch_row. Returns a rlm_sql_row_t struct
 451  *             with all the data for the query in 'handle->row'. Returns
 452  *               0 on success, -1 on failure, RLM_SQL_RECONNECT if database is down.
 453  *
 454  *************************************************************************/
 455 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 456 {
 457
 458         int status;
 459         rlm_sql_oracle_conn_t *conn = handle->conn;
 460
 461         if (!conn->ctx) {
 462                 ERROR("rlm_sql_oracle: Socket not connected");
 463
 464                 return RLM_SQL_RECONNECT;
 465         }
 466
 467         handle->row = NULL;
 468
 469         status = OCIStmtFetch(conn->query, conn->error, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
 470         if (status == OCI_SUCCESS) {
 471                 handle->row = conn->row;
 472
 473                 return 0;
 474         }
 475
 476         if (status == OCI_ERROR) {
 477                 ERROR("rlm_sql_oracle: fetch failed in sql_fetch_row: %s", sql_error(handle, config));
 478                 return sql_check_error(handle, config);
 479         }
 480
 481         return -1;
 482 }
 483
 484
 485
 486 /*************************************************************************
 487  *
 488  *      Function: sql_free_result
 489  *
 490  *      Purpose: database specific free_result. Frees memory allocated
 491  *             for a result set
 492  *
 493  *************************************************************************/
 494 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 495 {
 496         rlm_sql_oracle_conn_t *conn = handle->conn;
 497
 498         /* Cancel the cursor first */
 499         (void) OCIStmtFetch(conn->query, conn->error, 0, OCI_FETCH_NEXT, OCI_DEFAULT);
 500
 501         TALLOC_FREE(conn->row);
 502         conn->ind = NULL;       /* ind is a child of row */
 503         conn->col_count = 0;
 504
 505         return 0;
 506 }
 507
 508
 509
 510 /*************************************************************************
 511  *
 512  *      Function: sql_finish_query
 513  *
 514  *      Purpose: End the query, such as freeing memory
 515  *
 516  *************************************************************************/
 517 static sql_rcode_t sql_finish_query(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 518 {
 519         return 0;
 520 }
 521
 522
 523
 524 /*************************************************************************
 525  *
 526  *      Function: sql_finish_select_query
 527  *
 528  *      Purpose: End the select query, such as freeing memory or result
 529  *
 530  *************************************************************************/
 531 static sql_rcode_t sql_finish_select_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 532 {
 533         rlm_sql_oracle_conn_t *conn = handle->conn;
 534
 535         TALLOC_FREE(conn->row);
 536         conn->ind = NULL;       /* ind is a child of row */
 537         conn->col_count = 0;
 538
 539         return 0;
 540 }
 541
 542
 543 /*************************************************************************
 544  *
 545  *      Function: sql_affected_rows
 546  *
 547  *      Purpose: Return the number of rows affected by the query (update,
 548  *             or insert)
 549  *
 550  *************************************************************************/
 551 static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
 552         return sql_num_rows(handle, config);
 553 }
 554
 555
 556 /* Exported to rlm_sql */
 557 rlm_sql_module_t rlm_sql_oracle = {
 558         "rlm_sql_oracle",
 559         NULL,
 560         sql_socket_init,
 561         sql_query,
 562         sql_select_query,
 563         sql_store_result,
 564         sql_num_fields,
 565         sql_num_rows,
 566         sql_fetch_row,
 567         sql_free_result,
 568         sql_error,
 569         sql_finish_query,
 570         sql_finish_select_query,
 571         sql_affected_rows
 572 };