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   .name = US"oracle",                   /* lookup name */
 612   .type = lookup_querystyle,            /* query-style lookup */
 613   .open = oracle_open,                  /* open function */
 614   .check = NULL,                        /* check function */
 615   .find = oracle_find,                  /* find function */
 616   .close = NULL,                        /* no close function */
 617   .tidy = oracle_tidy,                  /* tidy function */
 618   .quote = oracle_quote,                /* quoting function */
 619   .version_report = 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 */