Copyright updates:
[exim.git] / src / src / lookups / oracle.c
CommitLineData
0756eb3c
PH
1/*************************************************
2* Exim - an Internet mail transport agent *
3*************************************************/
4
8d6d5106 5/* Copyright (c) University of Cambridge 1995 - 2015 */
1e1ddfac 6/* Copyright (c) The Exim Maintainers 2020 */
0756eb3c
PH
7/* See the file NOTICE for conditions of use and distribution. */
8
9/* Interface to an Oracle database. This code was originally supplied by
10Paul Kelly, but I have hacked it around for various reasons, and tried to add
11some comments from my position of Oracle ignorance. */
12
13
14#include "../exim.h"
15
16
0756eb3c
PH
17/* The Oracle system headers */
18
19#include <oratypes.h>
20#include <ocidfn.h>
21#include <ocikpr.h>
22
0756eb3c
PH
23#define PARSE_NO_DEFER 0 /* parse straight away */
24#define PARSE_V7_LNG 2
25#define MAX_ITEM_BUFFER_SIZE 1024 /* largest size of a cell of data */
26#define MAX_SELECT_LIST_SIZE 32 /* maximum number of columns (not rows!) */
27
28/* Paul's comment on this was "change this to 512 for 64bit cpu", but I don't
dee5a20a 29understand why. The Oracle manual just asks for 256 bytes.
0756eb3c 30
dee5a20a
PH
31That was years ago. Jin Choi suggested (March 2007) that this change should
32be made in the source, as at worst it wastes 256 bytes, and it saves people
33having to discover about this for themselves as more and more systems are
3464-bit. So I have changed 256 to 512. */
35
36#define HDA_SIZE 512
0756eb3c
PH
37
38/* Internal/external datatype codes */
39
40#define NUMBER_TYPE 2
41#define INT_TYPE 3
42#define FLOAT_TYPE 4
43#define STRING_TYPE 5
44#define ROWID_TYPE 11
45#define DATE_TYPE 12
46
47/* ORACLE error codes used in demonstration programs */
48
49#define VAR_NOT_IN_LIST 1007
50#define NO_DATA_FOUND 1403
51
52typedef struct Ora_Describe {
53 sb4 dbsize;
54 sb2 dbtype;
55 sb1 buf[MAX_ITEM_BUFFER_SIZE];
56 sb4 buflen;
57 sb4 dsize;
58 sb2 precision;
59 sb2 scale;
60 sb2 nullok;
61} Ora_Describe;
62
63typedef struct Ora_Define {
64 ub1 buf[MAX_ITEM_BUFFER_SIZE];
65 float flt_buf;
66 sword int_buf;
67 sb2 indp;
68 ub2 col_retlen, col_retcode;
69} Ora_Define;
70
71/* Structure and anchor for caching connections. */
72
73typedef struct oracle_connection {
74 struct oracle_connection *next;
75 uschar *server;
76 struct cda_def *handle;
77 void *hda_mem;
78} oracle_connection;
79
80static oracle_connection *oracle_connections = NULL;
81
82
83
84
85
86/*************************************************
87* Set up message after error *
88*************************************************/
89
90/* Sets up a message from a local string plus whatever Oracle gives.
91
92Arguments:
93 oracle_handle the handle of the connection
94 rc the return code
95 msg local text message
96*/
97
98static uschar *
99oracle_error(struct cda_def *oracle_handle, int rc, uschar *msg)
100{
101uschar tmp[1024];
102oerhms(oracle_handle, rc, tmp, sizeof(tmp));
103return string_sprintf("ORACLE %s: %s", msg, tmp);
104}
105
106
107
108/*************************************************
109* Describe and define the select list items *
110*************************************************/
111
112/* Figures out sizes, types, and numbers.
113
114Arguments:
115 cda the connection
116 def
117 desc descriptions put here
118
119Returns: number of fields
120*/
121
122static sword
123describe_define(Cda_Def *cda, Ora_Define *def, Ora_Describe *desc)
124{
125sword col, deflen, deftyp;
126static ub1 *defptr;
127static sword numwidth = 8;
128
129/* Describe the select-list items. */
130
131for (col = 0; col < MAX_SELECT_LIST_SIZE; col++)
132 {
133 desc[col].buflen = MAX_ITEM_BUFFER_SIZE;
134
135 if (odescr(cda, col + 1, &desc[col].dbsize,
136 &desc[col].dbtype, &desc[col].buf[0],
137 &desc[col].buflen, &desc[col].dsize,
138 &desc[col].precision, &desc[col].scale,
139 &desc[col].nullok) != 0)
140 {
141 /* Break on end of select list. */
142 if (cda->rc == VAR_NOT_IN_LIST) break; else return -1;
143 }
144
145 /* Adjust sizes and types for display, handling NUMBER with scale as float. */
146
147 if (desc[col].dbtype == NUMBER_TYPE)
148 {
149 desc[col].dbsize = numwidth;
150 if (desc[col].scale != 0)
151 {
152 defptr = (ub1 *)&def[col].flt_buf;
153 deflen = (sword) sizeof(float);
154 deftyp = FLOAT_TYPE;
155 desc[col].dbtype = FLOAT_TYPE;
156 }
157 else
158 {
159 defptr = (ub1 *)&def[col].int_buf;
160 deflen = (sword) sizeof(sword);
161 deftyp = INT_TYPE;
162 desc[col].dbtype = INT_TYPE;
163 }
164 }
165 else
166 {
167 if (desc[col].dbtype == DATE_TYPE)
168 desc[col].dbsize = 9;
169 if (desc[col].dbtype == ROWID_TYPE)
170 desc[col].dbsize = 18;
171 defptr = def[col].buf;
172 deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ?
173 MAX_ITEM_BUFFER_SIZE : desc[col].dbsize + 1;
174 deftyp = STRING_TYPE;
175 desc[col].dbtype = STRING_TYPE;
176 }
177
178 /* Define an output variable */
179
180 if (odefin(cda, col + 1,
181 defptr, deflen, deftyp,
182 -1, &def[col].indp, (text *) 0, -1, -1,
183 &def[col].col_retlen,
184 &def[col].col_retcode) != 0)
185 return -1;
186 } /* Loop for each column */
187
188return col;
189}
190
191
192
193/*************************************************
194* Open entry point *
195*************************************************/
196
197/* See local README for interface description. */
198
e6d225ae 199static void *
d447dbd1 200oracle_open(const uschar * filename, uschar ** errmsg)
0756eb3c
PH
201{
202return (void *)(1); /* Just return something non-null */
203}
204
205
206
207/*************************************************
208* Tidy entry point *
209*************************************************/
210
211/* See local README for interface description. */
212
e6d225ae 213static void
0756eb3c
PH
214oracle_tidy(void)
215{
216oracle_connection *cn;
217while ((cn = oracle_connections) != NULL)
218 {
219 oracle_connections = cn->next;
42c7f0b4 220 DEBUG(D_lookup) debug_printf_indent("close ORACLE connection: %s\n", cn->server);
0756eb3c
PH
221 ologof(cn->handle);
222 }
223}
224
225
226
227/*************************************************
228* Internal search function *
229*************************************************/
230
231/* This function is called from the find entry point to do the search for a
232single server.
233
234Arguments:
235 query the query string
236 server the server string
237 resultptr where to store the result
238 errmsg where to point an error message
239 defer_break TRUE if no more servers are to be tried after DEFER
240
241The server string is of the form "host/dbname/user/password", for compatibility
242with MySQL and pgsql, but at present, the dbname is not used. This string is in
243a nextinlist temporary buffer, so can be overwritten.
244
245Returns: OK, FAIL, or DEFER
246*/
247
248static int
249perform_oracle_search(uschar *query, uschar *server, uschar **resultptr,
250 uschar **errmsg, BOOL *defer_break)
251{
252Cda_Def *cda = NULL;
253struct cda_def *oracle_handle = NULL;
254Ora_Describe *desc = NULL;
255Ora_Define *def = NULL;
256void *hda = NULL;
257
0756eb3c
PH
258int yield = DEFER;
259unsigned int num_fields = 0;
acec9514 260gstring * result = NULL;
0756eb3c
PH
261oracle_connection *cn = NULL;
262uschar *server_copy = NULL;
263uschar *sdata[4];
0756eb3c
PH
264
265/* Disaggregate the parameters from the server argument. The order is host,
266database, user, password. We can write to the string, since it is in a
267nextinlist temporary buffer. The copy of the string that is used for caching
268has the password removed. This copy is also used for debugging output. */
269
d7978c0f 270for (int i = 3; i > 0; i--)
0756eb3c
PH
271 {
272 uschar *pp = Ustrrchr(server, '/');
273 if (pp == NULL)
274 {
275 *errmsg = string_sprintf("incomplete ORACLE server data: %s", server);
276 *defer_break = TRUE;
277 return DEFER;
278 }
279 *pp++ = 0;
280 sdata[i] = pp;
281 if (i == 3) server_copy = string_copy(server); /* sans password */
282 }
283sdata[0] = server; /* What's left at the start */
284
285/* If the database is the empty string, set it NULL - the query must then
286define it. */
287
288if (sdata[1][0] == 0) sdata[1] = NULL;
289
290/* See if we have a cached connection to the server */
291
acec9514 292for (cn = oracle_connections; cn; cn = cn->next)
0756eb3c
PH
293 if (strcmp(cn->server, server_copy) == 0)
294 {
295 oracle_handle = cn->handle;
296 hda = cn->hda_mem;
297 break;
298 }
0756eb3c
PH
299
300/* If no cached connection, we must set one up */
301
acec9514 302if (!cn)
0756eb3c 303 {
42c7f0b4 304 DEBUG(D_lookup) debug_printf_indent("ORACLE new connection: host=%s database=%s "
0756eb3c
PH
305 "user=%s\n", sdata[0], sdata[1], sdata[2]);
306
307 /* Get store for a new connection, initialize it, and connect to the server */
308
f3ebb786
JH
309 oracle_handle = store_get(sizeof(struct cda_def), FALSE);
310 hda = store_get(HDA_SIZE, FALSE);
0756eb3c
PH
311 memset(hda,'\0',HDA_SIZE);
312
313 /*
314 * Perform a default (blocking) login
315 *
316 * sdata[0] = tnsname (service name - typically host name)
317 * sdata[1] = dbname - not used at present
318 * sdata[2] = username
319 * sdata[3] = passwd
320 */
321
322 if(olog(oracle_handle, hda, sdata[2], -1, sdata[3], -1, sdata[0], -1,
323 (ub4)OCI_LM_DEF) != 0)
324 {
325 *errmsg = oracle_error(oracle_handle, oracle_handle->rc,
326 US"connection failed");
327 *defer_break = FALSE;
328 goto ORACLE_EXIT_NO_VALS;
329 }
330
331 /* Add the connection to the cache */
332
f3ebb786 333 cn = store_get(sizeof(oracle_connection), FALSE);
0756eb3c
PH
334 cn->server = server_copy;
335 cn->handle = oracle_handle;
336 cn->next = oracle_connections;
337 cn->hda_mem = hda;
338 oracle_connections = cn;
339 }
340
341/* Else use a previously cached connection - we can write to the server string
342to obliterate the password because it is in a nextinlist temporary buffer. */
343
344else
345 {
346 DEBUG(D_lookup)
42c7f0b4 347 debug_printf_indent("ORACLE using cached connection for %s\n", server_copy);
0756eb3c
PH
348 }
349
350/* We have a connection. Open a cursor and run the query */
351
f3ebb786 352cda = store_get(sizeof(Cda_Def), FALSE);
0756eb3c
PH
353
354if (oopen(cda, oracle_handle, (text *)0, -1, -1, (text *)0, -1) != 0)
355 {
356 *errmsg = oracle_error(oracle_handle, cda->rc, "failed to open cursor");
357 *defer_break = FALSE;
358 goto ORACLE_EXIT_NO_VALS;
359 }
360
361if (oparse(cda, (text *)query, (sb4) -1,
362 (sword)PARSE_NO_DEFER, (ub4)PARSE_V7_LNG) != 0)
363 {
364 *errmsg = oracle_error(oracle_handle, cda->rc, "query failed");
365 *defer_break = FALSE;
366 oclose(cda);
367 goto ORACLE_EXIT_NO_VALS;
368 }
369
370/* Find the number of fields returned and sort out their types. If the number
371is one, we don't add field names to the data. Otherwise we do. */
372
f3ebb786
JH
373def = store_get(sizeof(Ora_Define)*MAX_SELECT_LIST_SIZE, FALSE);
374desc = store_get(sizeof(Ora_Describe)*MAX_SELECT_LIST_SIZE, FALSE);
0756eb3c
PH
375
376if ((num_fields = describe_define(cda,def,desc)) == -1)
377 {
378 *errmsg = oracle_error(oracle_handle, cda->rc, "describe_define failed");
379 *defer_break = FALSE;
380 goto ORACLE_EXIT;
381 }
382
383if (oexec(cda)!=0)
384 {
385 *errmsg = oracle_error(oracle_handle, cda->rc, "oexec failed");
386 *defer_break = FALSE;
387 goto ORACLE_EXIT;
388 }
389
390/* Get the fields and construct the result string. If there is more than one
391row, we insert '\n' between them. */
392
393while (cda->rc != NO_DATA_FOUND) /* Loop for each row */
394 {
395 ofetch(cda);
396 if(cda->rc == NO_DATA_FOUND) break;
397
acec9514 398 if (result) result = string_catn(result, "\n", 1);
0756eb3c
PH
399
400 /* Single field - just add on the data */
401
402 if (num_fields == 1)
acec9514 403 result = string_catn(result, def[0].buf, def[0].col_retlen);
0756eb3c
PH
404
405 /* Multiple fields - precede by file name, removing {lead,trail}ing WS */
406
d7978c0f 407 else for (int i = 0; i < num_fields; i++)
0756eb3c
PH
408 {
409 int slen;
410 uschar *s = US desc[i].buf;
411
412 while (*s != 0 && isspace(*s)) s++;
413 slen = Ustrlen(s);
414 while (slen > 0 && isspace(s[slen-1])) slen--;
acec9514
JH
415 result = string_catn(result, s, slen);
416 result = string_catn(result, US"=", 1);
0756eb3c 417
aded2255 418 /* int and float type won't ever need escaping. Otherwise, quote the value
0756eb3c
PH
419 if it contains spaces or is empty. */
420
421 if (desc[i].dbtype != INT_TYPE && desc[i].dbtype != FLOAT_TYPE &&
422 (def[i].buf[0] == 0 || strchr(def[i].buf, ' ') != NULL))
423 {
acec9514 424 result = string_catn(result, "\"", 1);
d7978c0f 425 for (int j = 0; j < def[i].col_retlen; j++)
0756eb3c
PH
426 {
427 if (def[i].buf[j] == '\"' || def[i].buf[j] == '\\')
acec9514
JH
428 result = string_catn(result, "\\", 1);
429 result = string_catn(result, def[i].buf+j, 1);
0756eb3c 430 }
acec9514 431 result = string_catn(result, "\"", 1);
0756eb3c
PH
432 }
433
434 else switch(desc[i].dbtype)
435 {
436 case INT_TYPE:
acec9514
JH
437 result = string_cat(result, string_sprintf("%d", def[i].int_buf));
438 break;
0756eb3c
PH
439
440 case FLOAT_TYPE:
acec9514
JH
441 result = string_cat(result, string_sprintf("%f", def[i].flt_buf));
442 break;
0756eb3c
PH
443
444 case STRING_TYPE:
acec9514
JH
445 result = string_catn(result, def[i].buf, def[i].col_retlen);
446 break;
0756eb3c
PH
447
448 default:
acec9514
JH
449 *errmsg = string_sprintf("ORACLE: unknown field type %d", desc[i].dbtype);
450 *defer_break = FALSE;
451 result = NULL;
452 goto ORACLE_EXIT;
0756eb3c
PH
453 }
454
acec9514 455 result = string_catn(result, " ", 1);
0756eb3c
PH
456 }
457 }
458
459/* If result is NULL then no data has been found and so we return FAIL.
460Otherwise, we must terminate the string which has been built; string_cat()
461always leaves enough room for a terminating zero. */
462
acec9514 463if (!result)
0756eb3c
PH
464 {
465 yield = FAIL;
466 *errmsg = "ORACLE: no data found";
467 }
468else
f3ebb786 469 gstring_release_unused(result);
0756eb3c
PH
470
471/* Get here by goto from various error checks. */
472
473ORACLE_EXIT:
474
475/* Close the cursor; don't close the connection, as it is cached. */
476
477oclose(cda);
478
479ORACLE_EXIT_NO_VALS:
480
4c04137d 481/* Non-NULL result indicates a successful result */
0756eb3c 482
acec9514 483if (result)
0756eb3c 484 {
acec9514 485 *resultptr = string_from_gstring(result);
0756eb3c
PH
486 return OK;
487 }
488else
489 {
42c7f0b4 490 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
0756eb3c
PH
491 return yield; /* FAIL or DEFER */
492 }
493}
494
495
496
497
498/*************************************************
499* Find entry point *
500*************************************************/
501
502/* See local README for interface description. The handle and filename
503arguments are not used. Loop through a list of servers while the query is
504deferred with a retryable error. */
505
e6d225ae 506static int
d447dbd1 507oracle_find(void * handle, const uschar * filename, uschar * query, int length,
67a57a5a 508 uschar ** result, uschar ** errmsg, uint * do_cache, const uschar * opts)
0756eb3c
PH
509{
510int sep = 0;
511uschar *server;
512uschar *list = oracle_servers;
513uschar buffer[512];
514
515do_cache = do_cache; /* Placate picky compilers */
516
42c7f0b4 517DEBUG(D_lookup) debug_printf_indent("ORACLE query: %s\n", query);
0756eb3c 518
67a57a5a 519while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer))))
0756eb3c
PH
520 {
521 BOOL defer_break;
522 int rc = perform_oracle_search(query, server, result, errmsg, &defer_break);
523 if (rc != DEFER || defer_break) return rc;
524 }
525
67a57a5a 526if (!oracle_servers)
0756eb3c
PH
527 *errmsg = "no ORACLE servers defined (oracle_servers option)";
528
529return DEFER;
530}
531
532
533
534/*************************************************
535* Quote entry point *
536*************************************************/
537
538/* The only characters that need to be quoted (with backslash) are newline,
539tab, carriage return, backspace, backslash itself, and the quote characters.
540Percent and underscore are not escaped. They are only special in contexts where
541they can be wild cards, and this isn't usually the case for data inserted from
542messages, since that isn't likely to be treated as a pattern of any kind.
543
544Arguments:
545 s the string to be quoted
546 opt additional option text or NULL if none
547
548Returns: the processed string or NULL for a bad option
549*/
550
e6d225ae 551static uschar *
0756eb3c
PH
552oracle_quote(uschar *s, uschar *opt)
553{
554register int c;
555int count = 0;
556uschar *t = s;
557uschar *quoted;
558
559if (opt != NULL) return NULL; /* No options are recognized */
560
561while ((c = *t++) != 0)
562 if (strchr("\n\t\r\b\'\"\\", c) != NULL) count++;
563
564if (count == 0) return s;
f3ebb786 565t = quoted = store_get((int)strlen(s) + count + 1, is_tainted(s));
0756eb3c
PH
566
567while ((c = *s++) != 0)
568 {
569 if (strchr("\n\t\r\b\'\"\\", c) != NULL)
570 {
571 *t++ = '\\';
572 switch(c)
573 {
574 case '\n': *t++ = 'n';
575 break;
576 case '\t': *t++ = 't';
577 break;
578 case '\r': *t++ = 'r';
579 break;
580 case '\b': *t++ = 'b';
581 break;
582 default: *t++ = c;
583 break;
584 }
585 }
586 else *t++ = c;
587 }
588
589*t = 0;
590return quoted;
591}
592
6545de78
PP
593
594/*************************************************
595* Version reporting entry point *
596*************************************************/
597
598/* See local README for interface description. */
599
600#include "../version.h"
601
602void
603oracle_version_report(FILE *f)
604{
605#ifdef DYNLOOKUP
606fprintf(f, "Library version: Oracle: Exim version %s\n", EXIM_VERSION_STR);
607#endif
608}
609
610
e6d225ae 611static lookup_info _lookup_info = {
9f400174
JH
612 .name = US"oracle", /* lookup name */
613 .type = lookup_querystyle, /* query-style lookup */
614 .open = oracle_open, /* open function */
615 .check = NULL, /* check function */
616 .find = oracle_find, /* find function */
617 .close = NULL, /* no close function */
618 .tidy = oracle_tidy, /* tidy function */
619 .quote = oracle_quote, /* quoting function */
620 .version_report = oracle_version_report /* version reporting */
e6d225ae
DW
621};
622
623#ifdef DYNLOOKUP
624#define oracle_lookup_module_info _lookup_module_info
625#endif
626
627static lookup_info *_lookup_list[] = { &_lookup_info };
628lookup_module_info oracle_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
0756eb3c
PH
629
630/* End of lookups/oracle.c */