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