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