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