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