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: 88fd41dc66381550206e284babc8f8fb112d4639 $")
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 */
sql_prints_error(char * out,size_t outlen,rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)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 */
sql_error(TALLOC_CTX * ctx,sql_log_entry_t out[],size_t outlen,rlm_sql_handle_t * handle,rlm_sql_config_t * config)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
sql_check_error(rlm_sql_handle_t * handle,rlm_sql_config_t * config)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
_sql_socket_destructor(rlm_sql_oracle_conn_t * conn)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
sql_socket_init(rlm_sql_handle_t * handle,rlm_sql_config_t * config)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
sql_num_fields(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)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
sql_query(rlm_sql_handle_t * handle,rlm_sql_config_t * config,char const * query)205 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
206 {
207 int status;
208 rlm_sql_oracle_conn_t *conn = handle->conn;
209
210 OraText *oracle_query;
211
212 memcpy(&oracle_query, &query, sizeof(oracle_query));
213
214 if (!conn->ctx) {
215 ERROR("rlm_sql_oracle: Socket not connected");
216
217 return RLM_SQL_RECONNECT;
218 }
219
220 if (OCIStmtPrepare(conn->query, conn->error, oracle_query, strlen(query),
221 OCI_NTV_SYNTAX, OCI_DEFAULT)) {
222 ERROR("rlm_sql_oracle: prepare failed in sql_query");
223
224 return RLM_SQL_ERROR;
225 }
226
227 status = OCIStmtExecute(conn->ctx, conn->query, conn->error, 1, 0,
228 NULL, NULL, OCI_COMMIT_ON_SUCCESS);
229
230 if (status == OCI_SUCCESS) return RLM_SQL_OK;
231 if (status == OCI_ERROR) {
232 ERROR("rlm_sql_oracle: execute query failed in sql_query");
233
234 return sql_check_error(handle, config);
235 }
236
237 return RLM_SQL_ERROR;
238 }
239
sql_select_query(rlm_sql_handle_t * handle,rlm_sql_config_t * config,char const * query)240 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
241 {
242 int status;
243 char **row;
244
245 int i;
246 OCIParam *param;
247 OCIDefine *define;
248
249 ub2 dtype;
250 ub2 dsize;
251
252 sb2 *ind;
253
254 OraText *oracle_query;
255
256 rlm_sql_oracle_conn_t *conn = handle->conn;
257
258 memcpy(&oracle_query, &query, sizeof(oracle_query));
259
260 if (OCIStmtPrepare(conn->query, conn->error, oracle_query, strlen(query), OCI_NTV_SYNTAX,
261 OCI_DEFAULT)) {
262 ERROR("rlm_sql_oracle: prepare failed in sql_select_query");
263
264 return RLM_SQL_ERROR;
265 }
266
267 /*
268 * Retrieve a single row
269 */
270 status = OCIStmtExecute(conn->ctx, conn->query, conn->error, 0, 0, NULL, NULL, OCI_DEFAULT);
271 if (status == OCI_NO_DATA) return RLM_SQL_OK;
272 if (status != OCI_SUCCESS) {
273 ERROR("rlm_sql_oracle: query failed in sql_select_query");
274
275 return sql_check_error(handle, config);
276 }
277
278 /*
279 * We only need to do this once per result set, because
280 * the number of columns won't change.
281 */
282 if (conn->col_count == 0) {
283 conn->col_count = sql_num_fields(handle, config);
284
285 if (conn->col_count == 0) return RLM_SQL_ERROR;
286 }
287
288 MEM(row = talloc_zero_array(conn, char*, conn->col_count + 1));
289 MEM(ind = talloc_zero_array(row, sb2, conn->col_count + 1));
290
291 for (i = 0; i < conn->col_count; i++) {
292 status = OCIParamGet(conn->query, OCI_HTYPE_STMT, conn->error, (dvoid **)¶m, i + 1);
293 if (status != OCI_SUCCESS) {
294 ERROR("rlm_sql_oracle: OCIParamGet() failed in sql_select_query");
295
296 goto error;
297 }
298
299 status = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid*)&dtype, NULL, OCI_ATTR_DATA_TYPE,
300 conn->error);
301 if (status != OCI_SUCCESS) {
302 ERROR("rlm_sql_oracle: OCIAttrGet() failed in sql_select_query");
303
304 goto error;
305 }
306
307 dsize = MAX_DATASTR_LEN;
308
309 /*
310 * Use the retrieved length of dname to allocate an output buffer, and then define the output
311 * variable (but only for char/string type columns).
312 */
313 switch (dtype) {
314 #ifdef SQLT_AFC
315 case SQLT_AFC: /* ansii fixed char */
316 #endif
317 #ifdef SQLT_AFV
318 case SQLT_AFV: /* ansii var char */
319 #endif
320 case SQLT_VCS: /* var char */
321 case SQLT_CHR: /* char */
322 case SQLT_STR: /* string */
323 status = OCIAttrGet((dvoid *)param, OCI_DTYPE_PARAM, (dvoid *)&dsize, NULL,
324 OCI_ATTR_DATA_SIZE, conn->error);
325 if (status != OCI_SUCCESS) {
326 ERROR("rlm_sql_oracle: OCIAttrGet() failed in sql_select_query");
327
328 goto error;
329 }
330
331 MEM(row[i] = talloc_zero_array(row, char, dsize + 1));
332
333 break;
334 case SQLT_DAT:
335 case SQLT_INT:
336 case SQLT_UIN:
337 case SQLT_FLT:
338 case SQLT_PDN:
339 case SQLT_BIN:
340 case SQLT_NUM:
341 MEM(row[i] = talloc_zero_array(row, char, dsize + 1));
342
343 break;
344 default:
345 dsize = 0;
346 row[i] = NULL;
347 break;
348 }
349
350 ind[i] = 0;
351
352 /*
353 * Grab the actual row value and write it to the buffer we allocated.
354 */
355 status = OCIDefineByPos(conn->query, &define, conn->error, i + 1, (ub1 *)row[i], dsize + 1, SQLT_STR,
356 (dvoid *)&ind[i], NULL, NULL, OCI_DEFAULT);
357
358 if (status != OCI_SUCCESS) {
359 ERROR("rlm_sql_oracle: OCIDefineByPos() failed in sql_select_query");
360 goto error;
361 }
362 }
363
364 conn->row = row;
365 conn->ind = ind;
366
367 return RLM_SQL_OK;
368
369 error:
370 talloc_free(row);
371
372 return RLM_SQL_ERROR;
373 }
374
sql_num_rows(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)375 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
376 {
377 rlm_sql_oracle_conn_t *conn = handle->conn;
378 ub4 rows = 0;
379 ub4 size = sizeof(ub4);
380
381 OCIAttrGet((CONST dvoid *)conn->query, OCI_HTYPE_STMT, (dvoid *)&rows, &size, OCI_ATTR_ROW_COUNT, conn->error);
382
383 return rows;
384 }
385
sql_fetch_row(rlm_sql_handle_t * handle,rlm_sql_config_t * config)386 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
387 {
388 int status;
389 rlm_sql_oracle_conn_t *conn = handle->conn;
390
391 if (!conn->ctx) {
392 ERROR("rlm_sql_oracle: Socket not connected");
393
394 return RLM_SQL_RECONNECT;
395 }
396
397 handle->row = NULL;
398
399 status = OCIStmtFetch(conn->query, conn->error, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
400 if (status == OCI_SUCCESS) {
401 handle->row = conn->row;
402
403 return RLM_SQL_OK;
404 }
405
406 if (status == OCI_NO_DATA) {
407 handle->row = 0;
408
409 return RLM_SQL_NO_MORE_ROWS;
410 }
411
412 if (status == OCI_ERROR) {
413 ERROR("rlm_sql_oracle: fetch failed in sql_fetch_row");
414 return sql_check_error(handle, config);
415 }
416
417 return RLM_SQL_ERROR;
418 }
419
sql_free_result(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)420 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
421 {
422 rlm_sql_oracle_conn_t *conn = handle->conn;
423
424 /* Cancel the cursor first */
425 (void) OCIStmtFetch(conn->query, conn->error, 0, OCI_FETCH_NEXT, OCI_DEFAULT);
426
427 TALLOC_FREE(conn->row);
428 conn->ind = NULL; /* ind is a child of row */
429 conn->col_count = 0;
430
431 return RLM_SQL_OK;
432 }
433
sql_finish_query(UNUSED rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)434 static sql_rcode_t sql_finish_query(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
435 {
436 return 0;
437 }
438
sql_finish_select_query(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)439 static sql_rcode_t sql_finish_select_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
440 {
441 rlm_sql_oracle_conn_t *conn = handle->conn;
442
443 TALLOC_FREE(conn->row);
444 conn->ind = NULL; /* ind is a child of row */
445 conn->col_count = 0;
446
447 return 0;
448 }
449
sql_affected_rows(rlm_sql_handle_t * handle,rlm_sql_config_t * config)450 static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
451 {
452 return sql_num_rows(handle, config);
453 }
454
455 /* Exported to rlm_sql */
456 extern rlm_sql_module_t rlm_sql_oracle;
457 rlm_sql_module_t rlm_sql_oracle = {
458 .name = "rlm_sql_oracle",
459 .sql_socket_init = sql_socket_init,
460 .sql_query = sql_query,
461 .sql_select_query = sql_select_query,
462 .sql_num_fields = sql_num_fields,
463 .sql_num_rows = sql_num_rows,
464 .sql_affected_rows = sql_affected_rows,
465 .sql_fetch_row = sql_fetch_row,
466 .sql_free_result = sql_free_result,
467 .sql_error = sql_error,
468 .sql_finish_query = sql_finish_query,
469 .sql_finish_select_query = sql_finish_select_query
470 };
471