1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
5 /* Copyright (c) University of Cambridge 1995 - 2015 */
6 /* See the file NOTICE for conditions of use and distribution. */
8 /* Interface to an Oracle database. This code was originally supplied by
9 Paul Kelly, but I have hacked it around for various reasons, and tried to add
10 some comments from my position of Oracle ignorance. */
16 /* The Oracle system headers */
22 #define PARSE_NO_DEFER 0 /* parse straight away */
23 #define PARSE_V7_LNG 2
24 #define MAX_ITEM_BUFFER_SIZE 1024 /* largest size of a cell of data */
25 #define MAX_SELECT_LIST_SIZE 32 /* maximum number of columns (not rows!) */
27 /* Paul's comment on this was "change this to 512 for 64bit cpu", but I don't
28 understand why. The Oracle manual just asks for 256 bytes.
30 That was years ago. Jin Choi suggested (March 2007) that this change should
31 be made in the source, as at worst it wastes 256 bytes, and it saves people
32 having to discover about this for themselves as more and more systems are
33 64-bit. So I have changed 256 to 512. */
37 /* Internal/external datatype codes */
46 /* ORACLE error codes used in demonstration programs */
48 #define VAR_NOT_IN_LIST 1007
49 #define NO_DATA_FOUND 1403
51 typedef struct Ora_Describe {
54 sb1 buf[MAX_ITEM_BUFFER_SIZE];
62 typedef struct Ora_Define {
63 ub1 buf[MAX_ITEM_BUFFER_SIZE];
67 ub2 col_retlen, col_retcode;
70 /* Structure and anchor for caching connections. */
72 typedef struct oracle_connection {
73 struct oracle_connection *next;
75 struct cda_def *handle;
79 static oracle_connection *oracle_connections = NULL;
85 /*************************************************
86 * Set up message after error *
87 *************************************************/
89 /* Sets up a message from a local string plus whatever Oracle gives.
92 oracle_handle the handle of the connection
94 msg local text message
98 oracle_error(struct cda_def *oracle_handle, int rc, uschar *msg)
101 oerhms(oracle_handle, rc, tmp, sizeof(tmp));
102 return string_sprintf("ORACLE %s: %s", msg, tmp);
107 /*************************************************
108 * Describe and define the select list items *
109 *************************************************/
111 /* Figures out sizes, types, and numbers.
116 desc descriptions put here
118 Returns: number of fields
122 describe_define(Cda_Def *cda, Ora_Define *def, Ora_Describe *desc)
124 sword col, deflen, deftyp;
126 static sword numwidth = 8;
128 /* Describe the select-list items. */
130 for (col = 0; col < MAX_SELECT_LIST_SIZE; col++)
132 desc[col].buflen = MAX_ITEM_BUFFER_SIZE;
134 if (odescr(cda, col + 1, &desc[col].dbsize,
135 &desc[col].dbtype, &desc[col].buf[0],
136 &desc[col].buflen, &desc[col].dsize,
137 &desc[col].precision, &desc[col].scale,
138 &desc[col].nullok) != 0)
140 /* Break on end of select list. */
141 if (cda->rc == VAR_NOT_IN_LIST) break; else return -1;
144 /* Adjust sizes and types for display, handling NUMBER with scale as float. */
146 if (desc[col].dbtype == NUMBER_TYPE)
148 desc[col].dbsize = numwidth;
149 if (desc[col].scale != 0)
151 defptr = (ub1 *)&def[col].flt_buf;
152 deflen = (sword) sizeof(float);
154 desc[col].dbtype = FLOAT_TYPE;
158 defptr = (ub1 *)&def[col].int_buf;
159 deflen = (sword) sizeof(sword);
161 desc[col].dbtype = INT_TYPE;
166 if (desc[col].dbtype == DATE_TYPE)
167 desc[col].dbsize = 9;
168 if (desc[col].dbtype == ROWID_TYPE)
169 desc[col].dbsize = 18;
170 defptr = def[col].buf;
171 deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ?
172 MAX_ITEM_BUFFER_SIZE : desc[col].dbsize + 1;
173 deftyp = STRING_TYPE;
174 desc[col].dbtype = STRING_TYPE;
177 /* Define an output variable */
179 if (odefin(cda, col + 1,
180 defptr, deflen, deftyp,
181 -1, &def[col].indp, (text *) 0, -1, -1,
182 &def[col].col_retlen,
183 &def[col].col_retcode) != 0)
185 } /* Loop for each column */
192 /*************************************************
194 *************************************************/
196 /* See local README for interface description. */
199 oracle_open(uschar *filename, uschar **errmsg)
201 return (void *)(1); /* Just return something non-null */
206 /*************************************************
208 *************************************************/
210 /* See local README for interface description. */
215 oracle_connection *cn;
216 while ((cn = oracle_connections) != NULL)
218 oracle_connections = cn->next;
219 DEBUG(D_lookup) debug_printf("close ORACLE connection: %s\n", cn->server);
226 /*************************************************
227 * Internal search function *
228 *************************************************/
230 /* This function is called from the find entry point to do the search for a
234 query the query string
235 server the server string
236 resultptr where to store the result
237 errmsg where to point an error message
238 defer_break TRUE if no more servers are to be tried after DEFER
240 The server string is of the form "host/dbname/user/password", for compatibility
241 with MySQL and pgsql, but at present, the dbname is not used. This string is in
242 a nextinlist temporary buffer, so can be overwritten.
244 Returns: OK, FAIL, or DEFER
248 perform_oracle_search(uschar *query, uschar *server, uschar **resultptr,
249 uschar **errmsg, BOOL *defer_break)
252 struct cda_def *oracle_handle = NULL;
253 Ora_Describe *desc = NULL;
254 Ora_Define *def = NULL;
259 unsigned int num_fields = 0;
260 gstring * result = NULL;
261 oracle_connection *cn = NULL;
262 uschar *server_copy = NULL;
265 /* Disaggregate the parameters from the server argument. The order is host,
266 database, user, password. We can write to the string, since it is in a
267 nextinlist temporary buffer. The copy of the string that is used for caching
268 has the password removed. This copy is also used for debugging output. */
270 for (i = 3; i > 0; i--)
272 uschar *pp = Ustrrchr(server, '/');
275 *errmsg = string_sprintf("incomplete ORACLE server data: %s", server);
281 if (i == 3) server_copy = string_copy(server); /* sans password */
283 sdata[0] = server; /* What's left at the start */
285 /* If the database is the empty string, set it NULL - the query must then
288 if (sdata[1][0] == 0) sdata[1] = NULL;
290 /* See if we have a cached connection to the server */
292 for (cn = oracle_connections; cn; cn = cn->next)
293 if (strcmp(cn->server, server_copy) == 0)
295 oracle_handle = cn->handle;
300 /* If no cached connection, we must set one up */
304 DEBUG(D_lookup) debug_printf("ORACLE new connection: host=%s database=%s "
305 "user=%s\n", sdata[0], sdata[1], sdata[2]);
307 /* Get store for a new connection, initialize it, and connect to the server */
309 oracle_handle = store_get(sizeof(struct cda_def));
310 hda = store_get(HDA_SIZE);
311 memset(hda,'\0',HDA_SIZE);
314 * Perform a default (blocking) login
316 * sdata[0] = tnsname (service name - typically host name)
317 * sdata[1] = dbname - not used at present
318 * sdata[2] = username
322 if(olog(oracle_handle, hda, sdata[2], -1, sdata[3], -1, sdata[0], -1,
323 (ub4)OCI_LM_DEF) != 0)
325 *errmsg = oracle_error(oracle_handle, oracle_handle->rc,
326 US"connection failed");
327 *defer_break = FALSE;
328 goto ORACLE_EXIT_NO_VALS;
331 /* Add the connection to the cache */
333 cn = store_get(sizeof(oracle_connection));
334 cn->server = server_copy;
335 cn->handle = oracle_handle;
336 cn->next = oracle_connections;
338 oracle_connections = cn;
341 /* Else use a previously cached connection - we can write to the server string
342 to obliterate the password because it is in a nextinlist temporary buffer. */
347 debug_printf("ORACLE using cached connection for %s\n", server_copy);
350 /* We have a connection. Open a cursor and run the query */
352 cda = store_get(sizeof(Cda_Def));
354 if (oopen(cda, oracle_handle, (text *)0, -1, -1, (text *)0, -1) != 0)
356 *errmsg = oracle_error(oracle_handle, cda->rc, "failed to open cursor");
357 *defer_break = FALSE;
358 goto ORACLE_EXIT_NO_VALS;
361 if (oparse(cda, (text *)query, (sb4) -1,
362 (sword)PARSE_NO_DEFER, (ub4)PARSE_V7_LNG) != 0)
364 *errmsg = oracle_error(oracle_handle, cda->rc, "query failed");
365 *defer_break = FALSE;
367 goto ORACLE_EXIT_NO_VALS;
370 /* Find the number of fields returned and sort out their types. If the number
371 is one, we don't add field names to the data. Otherwise we do. */
373 def = store_get(sizeof(Ora_Define)*MAX_SELECT_LIST_SIZE);
374 desc = store_get(sizeof(Ora_Describe)*MAX_SELECT_LIST_SIZE);
376 if ((num_fields = describe_define(cda,def,desc)) == -1)
378 *errmsg = oracle_error(oracle_handle, cda->rc, "describe_define failed");
379 *defer_break = FALSE;
385 *errmsg = oracle_error(oracle_handle, cda->rc, "oexec failed");
386 *defer_break = FALSE;
390 /* Get the fields and construct the result string. If there is more than one
391 row, we insert '\n' between them. */
393 while (cda->rc != NO_DATA_FOUND) /* Loop for each row */
396 if(cda->rc == NO_DATA_FOUND) break;
398 if (result) result = string_catn(result, "\n", 1);
400 /* Single field - just add on the data */
403 result = string_catn(result, def[0].buf, def[0].col_retlen);
405 /* Multiple fields - precede by file name, removing {lead,trail}ing WS */
407 else for (i = 0; i < num_fields; i++)
410 uschar *s = US desc[i].buf;
412 while (*s != 0 && isspace(*s)) s++;
414 while (slen > 0 && isspace(s[slen-1])) slen--;
415 result = string_catn(result, s, slen);
416 result = string_catn(result, US"=", 1);
418 /* int and float type wont ever need escaping. Otherwise, quote the value
419 if it contains spaces or is empty. */
421 if (desc[i].dbtype != INT_TYPE && desc[i].dbtype != FLOAT_TYPE &&
422 (def[i].buf[0] == 0 || strchr(def[i].buf, ' ') != NULL))
425 result = string_catn(result, "\"", 1);
426 for (j = 0; j < def[i].col_retlen; j++)
428 if (def[i].buf[j] == '\"' || def[i].buf[j] == '\\')
429 result = string_catn(result, "\\", 1);
430 result = string_catn(result, def[i].buf+j, 1);
432 result = string_catn(result, "\"", 1);
435 else switch(desc[i].dbtype)
438 result = string_cat(result, string_sprintf("%d", def[i].int_buf));
442 result = string_cat(result, string_sprintf("%f", def[i].flt_buf));
446 result = string_catn(result, def[i].buf, def[i].col_retlen);
450 *errmsg = string_sprintf("ORACLE: unknown field type %d", desc[i].dbtype);
451 *defer_break = FALSE;
456 result = string_catn(result, " ", 1);
460 /* If result is NULL then no data has been found and so we return FAIL.
461 Otherwise, we must terminate the string which has been built; string_cat()
462 always leaves enough room for a terminating zero. */
467 *errmsg = "ORACLE: no data found";
470 store_reset(result->s + result->ptr + 1);
472 /* Get here by goto from various error checks. */
476 /* Close the cursor; don't close the connection, as it is cached. */
482 /* Non-NULL result indicates a successful result */
486 *resultptr = string_from_gstring(result);
491 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
492 return yield; /* FAIL or DEFER */
499 /*************************************************
501 *************************************************/
503 /* See local README for interface description. The handle and filename
504 arguments are not used. Loop through a list of servers while the query is
505 deferred with a retryable error. */
508 oracle_find(void *handle, uschar *filename, uschar *query, int length,
509 uschar **result, uschar **errmsg, uint *do_cache)
513 uschar *list = oracle_servers;
516 do_cache = do_cache; /* Placate picky compilers */
518 DEBUG(D_lookup) debug_printf("ORACLE query: %s\n", query);
520 while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer))) != NULL)
523 int rc = perform_oracle_search(query, server, result, errmsg, &defer_break);
524 if (rc != DEFER || defer_break) return rc;
527 if (oracle_servers == NULL)
528 *errmsg = "no ORACLE servers defined (oracle_servers option)";
535 /*************************************************
536 * Quote entry point *
537 *************************************************/
539 /* The only characters that need to be quoted (with backslash) are newline,
540 tab, carriage return, backspace, backslash itself, and the quote characters.
541 Percent and underscore are not escaped. They are only special in contexts where
542 they can be wild cards, and this isn't usually the case for data inserted from
543 messages, since that isn't likely to be treated as a pattern of any kind.
546 s the string to be quoted
547 opt additional option text or NULL if none
549 Returns: the processed string or NULL for a bad option
553 oracle_quote(uschar *s, uschar *opt)
560 if (opt != NULL) return NULL; /* No options are recognized */
562 while ((c = *t++) != 0)
563 if (strchr("\n\t\r\b\'\"\\", c) != NULL) count++;
565 if (count == 0) return s;
566 t = quoted = store_get((int)strlen(s) + count + 1);
568 while ((c = *s++) != 0)
570 if (strchr("\n\t\r\b\'\"\\", c) != NULL)
575 case '\n': *t++ = 'n';
577 case '\t': *t++ = 't';
579 case '\r': *t++ = 'r';
581 case '\b': *t++ = 'b';
595 /*************************************************
596 * Version reporting entry point *
597 *************************************************/
599 /* See local README for interface description. */
601 #include "../version.h"
604 oracle_version_report(FILE *f)
607 fprintf(f, "Library version: Oracle: Exim version %s\n", EXIM_VERSION_STR);
612 static lookup_info _lookup_info = {
613 US"oracle", /* lookup name */
614 lookup_querystyle, /* query-style lookup */
615 oracle_open, /* open function */
616 NULL, /* check function */
617 oracle_find, /* find function */
618 NULL, /* no close function */
619 oracle_tidy, /* tidy function */
620 oracle_quote, /* quoting function */
621 oracle_version_report /* version reporting */
625 #define oracle_lookup_module_info _lookup_module_info
628 static lookup_info *_lookup_list[] = { &_lookup_info };
629 lookup_module_info oracle_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
631 /* End of lookups/oracle.c */