src/src/lookups/pgsql.c

   1 /* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.7 2006/07/14 14:42:57 ph10 Exp $ */
   2
   3 /*************************************************
   4 *     Exim - an Internet mail transport agent    *
   5 *************************************************/
   6
   7 /* Copyright (c) University of Cambridge 1995 - 2006 */
   8 /* See the file NOTICE for conditions of use and distribution. */
   9
  10 /* Thanks to Petr Cech for contributing the original code for these
  11 functions. Thanks to Joachim Wieland for the initial patch for the Unix domain
  12 socket extension. */
  13
  14 #include "../exim.h"
  15 #include "lf_functions.h"
  16 #include "pgsql.h"       /* The local header */
  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 PGSQL header
  20 available for compiling. Therefore, compile these functions only if
  21 LOOKUP_PGSQL 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_PGSQL
  28 static void dummy(int x) { dummy(x-1); }
  29 #else
  30
  31
  32 #include <libpq-fe.h>       /* The system header */
  33
  34 /* Structure and anchor for caching connections. */
  35
  36 typedef struct pgsql_connection {
  37   struct pgsql_connection *next;
  38   uschar *server;
  39   PGconn *handle;
  40 } pgsql_connection;
  41
  42 static pgsql_connection *pgsql_connections = NULL;
  43
  44
  45
  46 /*************************************************
  47 *              Open entry point                  *
  48 *************************************************/
  49
  50 /* See local README for interface description. */
  51
  52 void *
  53 pgsql_open(uschar *filename, uschar **errmsg)
  54 {
  55 return (void *)(1);    /* Just return something non-null */
  56 }
  57
  58
  59
  60 /*************************************************
  61 *               Tidy entry point                 *
  62 *************************************************/
  63
  64 /* See local README for interface description. */
  65
  66 void
  67 pgsql_tidy(void)
  68 {
  69 pgsql_connection *cn;
  70 while ((cn = pgsql_connections) != NULL)
  71   {
  72   pgsql_connections = cn->next;
  73   DEBUG(D_lookup) debug_printf("close PGSQL connection: %s\n", cn->server);
  74   PQfinish(cn->handle);
  75   }
  76 }
  77
  78
  79 /*************************************************
  80 *       Notice processor function for pgsql      *
  81 *************************************************/
  82
  83 /* This function is passed to pgsql below, and called for any PostgreSQL
  84 "notices". By default they are written to stderr, which is undesirable.
  85
  86 Arguments:
  87   arg        an opaque user cookie (not used)
  88   message    the notice
  89
  90 Returns:     nothing
  91 */
  92
  93 static void
  94 notice_processor(void *arg, const char *message)
  95 {
  96 arg = arg;   /* Keep compiler happy */
  97 DEBUG(D_lookup) debug_printf("PGSQL: %s\n", message);
  98 }
  99
 100
 101
 102 /*************************************************
 103 *        Internal search function                *
 104 *************************************************/
 105
 106 /* This function is called from the find entry point to do the search for a
 107 single server. The server string is of the form "server/dbname/user/password".
 108
 109 PostgreSQL supports connections through Unix domain sockets. This is usually
 110 faster and costs less cpu time than a TCP/IP connection. However it can only be
 111 used if the mail server runs on the same machine as the database server. A
 112 configuration line for PostgreSQL via Unix domain sockets looks like this:
 113
 114 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
 115
 116 We enclose the path name in parentheses so that its slashes aren't visually
 117 confused with the delimeters for the other pgsql_server settings.
 118
 119 For TCP/IP connections, the server is a host name and optional port (with a
 120 colon separator).
 121
 122 NOTE:
 123  1) All three '/' must be present.
 124  2) If host is omitted the local unix socket is used.
 125
 126 Arguments:
 127   query        the query string
 128   server       the server string; this is in dynamic memory and can be updated
 129   resultptr    where to store the result
 130   errmsg       where to point an error message
 131   defer_break  TRUE if no more servers are to be tried after DEFER
 132   do_cache     set FALSE if data is changed
 133
 134 Returns:       OK, FAIL, or DEFER
 135 */
 136
 137 static int
 138 perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr,
 139   uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
 140 {
 141 PGconn *pg_conn = NULL;
 142 PGresult *pg_result = NULL;
 143
 144 int i;
 145 int ssize = 0;
 146 int offset = 0;
 147 int yield = DEFER;
 148 unsigned int num_fields, num_tuples;
 149 uschar *result = NULL;
 150 pgsql_connection *cn;
 151 uschar *server_copy = NULL;
 152 uschar *sdata[3];
 153
 154 /* Disaggregate the parameters from the server argument. The order is host or
 155 path, database, user, password. We can write to the string, since it is in a
 156 nextinlist temporary buffer. The copy of the string that is used for caching
 157 has the password removed. This copy is also used for debugging output. */
 158
 159 for (i = 2; i >= 0; i--)
 160   {
 161   uschar *pp = Ustrrchr(server, '/');
 162   if (pp == NULL)
 163     {
 164     *errmsg = string_sprintf("incomplete pgSQL server data: %s",
 165       (i == 2)? server : server_copy);
 166     *defer_break = TRUE;
 167     return DEFER;
 168     }
 169   *pp++ = 0;
 170   sdata[i] = pp;
 171   if (i == 2) server_copy = string_copy(server);  /* sans password */
 172   }
 173
 174 /* The total server string has now been truncated so that what is left at the
 175 start is the identification of the server (host or path). See if we have a
 176 cached connection to the server. */
 177
 178 for (cn = pgsql_connections; cn != NULL; cn = cn->next)
 179   {
 180   if (Ustrcmp(cn->server, server_copy) == 0)
 181     {
 182     pg_conn = cn->handle;
 183     break;
 184     }
 185   }
 186
 187 /* If there is no cached connection, we must set one up. */
 188
 189 if (cn == NULL)
 190   {
 191   uschar *port = US"";
 192
 193   /* For a Unix domain socket connection, the path is in parentheses */
 194
 195   if (*server == '(')
 196     {
 197     uschar *last_slash, *last_dot, *p;
 198
 199     p = ++server;
 200     while (*p != 0 && *p != ')') p++;
 201     *p = 0;
 202
 203     last_slash = Ustrrchr(server, '/');
 204     last_dot = Ustrrchr(server, '.');
 205
 206     DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s "
 207       "database=%s user=%s\n", server, sdata[0], sdata[1]);
 208
 209     /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
 210     We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
 211     argument and put '5432' into the port variable. */
 212
 213     if (last_slash == NULL || last_dot == NULL)
 214       {
 215       *errmsg = string_sprintf("PGSQL invalid filename for socket: %s",
 216         server);
 217       *defer_break = TRUE;
 218       return DEFER;
 219       }
 220
 221     /* Terminate the path name and set up the port: we'll have something like
 222     server = "/var/run/postgresql" and port = "5432". */
 223
 224     *last_slash = 0;
 225     port = last_dot + 1;
 226     }
 227
 228   /* Host connection; sort out the port */
 229
 230   else
 231     {
 232     uschar *p;
 233     if ((p = Ustrchr(server, ':')) != NULL)
 234       {
 235       *p++ = 0;
 236       port = p;
 237       }
 238
 239     if (Ustrchr(server, '/') != NULL)
 240       {
 241       *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
 242         server);
 243       *defer_break = TRUE;
 244       return DEFER;
 245       }
 246
 247     DEBUG(D_lookup) debug_printf("PGSQL new connection: host=%s port=%s "
 248       "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
 249     }
 250
 251   /* If the database is the empty string, set it NULL - the query must then
 252   define it. */
 253
 254   if (sdata[0][0] == 0) sdata[0] = NULL;
 255
 256   /* Get store for a new handle, initialize it, and connect to the server */
 257
 258   pg_conn=PQsetdbLogin(
 259     /*  host      port  options tty   database       user       passwd */
 260     CS server, CS port,  NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
 261
 262   if(PQstatus(pg_conn) == CONNECTION_BAD)
 263     {
 264     store_reset(server_copy);
 265     *errmsg = string_sprintf("PGSQL connection failed: %s",
 266       PQerrorMessage(pg_conn));
 267     PQfinish(pg_conn);
 268     *defer_break = FALSE;
 269     goto PGSQL_EXIT;
 270     }
 271
 272   /* Set the client encoding to SQL_ASCII, which means that the server will
 273   not try to interpret the query as being in any fancy encoding such as UTF-8
 274   or other multibyte code that might cause problems with escaping. */
 275
 276   PQsetClientEncoding(pg_conn, "SQL_ASCII");
 277
 278   /* Set the notice processor to prevent notices from being written to stderr
 279   (which is what the default does). Our function (above) just produces debug
 280   output. */
 281
 282   PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
 283
 284   /* Add the connection to the cache */
 285
 286   cn = store_get(sizeof(pgsql_connection));
 287   cn->server = server_copy;
 288   cn->handle = pg_conn;
 289   cn->next = pgsql_connections;
 290   pgsql_connections = cn;
 291   }
 292
 293 /* Else use a previously cached connection */
 294
 295 else
 296   {
 297   DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n",
 298     server_copy);
 299   }
 300
 301 /* Run the query */
 302
 303   pg_result = PQexec(pg_conn, CS query);
 304   switch(PQresultStatus(pg_result))
 305     {
 306     case PGRES_EMPTY_QUERY:
 307     case PGRES_COMMAND_OK:
 308     /* The command was successful but did not return any data since it was
 309      * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
 310      * high level code to not cache this query, and clean the current cache for
 311      * this handle by setting *do_cache FALSE. */
 312     result = string_copy(US PQcmdTuples(pg_result));
 313     offset = Ustrlen(result);
 314     *do_cache = FALSE;
 315     DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data "
 316       "but was successful. Rows affected: %s\n", result);
 317
 318     case PGRES_TUPLES_OK:
 319     break;
 320
 321     default:
 322     /* This was the original code:
 323     *errmsg = string_sprintf("PGSQL: query failed: %s\n",
 324                              PQresultErrorMessage(pg_result));
 325     This was suggested by a user:
 326     */
 327
 328     *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
 329                              PQresultErrorMessage(pg_result),
 330                              PQresStatus(PQresultStatus(pg_result)), query);
 331     *defer_break = FALSE;
 332     goto PGSQL_EXIT;
 333     }
 334
 335 /* Result is in pg_result. Find the number of fields returned. If this is one,
 336 we don't add field names to the data. Otherwise we do. If the query did not
 337 return anything we skip the for loop; this also applies to the case
 338 PGRES_COMMAND_OK. */
 339
 340 num_fields = PQnfields(pg_result);
 341 num_tuples = PQntuples(pg_result);
 342
 343 /* Get the fields and construct the result string. If there is more than one
 344 row, we insert '\n' between them. */
 345
 346 for (i = 0; i < num_tuples; i++)
 347   {
 348   if (result != NULL)
 349     result = string_cat(result, &ssize, &offset, US"\n", 1);
 350
 351    if (num_fields == 1)
 352     {
 353     result = string_cat(result, &ssize, &offset,
 354       US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
 355     }
 356
 357    else
 358     {
 359     int j;
 360     for (j = 0; j < num_fields; j++)
 361       {
 362       uschar *tmp = US PQgetvalue(pg_result, i, j);
 363       result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result,
 364         &ssize, &offset);
 365       }
 366     }
 367   }
 368
 369 /* If result is NULL then no data has been found and so we return FAIL.
 370 Otherwise, we must terminate the string which has been built; string_cat()
 371 always leaves enough room for a terminating zero. */
 372
 373 if (result == NULL)
 374   {
 375   yield = FAIL;
 376   *errmsg = US"PGSQL: no data found";
 377   }
 378 else
 379   {
 380   result[offset] = 0;
 381   store_reset(result + offset + 1);
 382   }
 383
 384 /* Get here by goto from various error checks. */
 385
 386 PGSQL_EXIT:
 387
 388 /* Free store for any result that was got; don't close the connection, as
 389 it is cached. */
 390
 391 if (pg_result != NULL) PQclear(pg_result);
 392
 393 /* Non-NULL result indicates a sucessful result */
 394
 395 if (result != NULL)
 396   {
 397   *resultptr = result;
 398   return OK;
 399   }
 400 else
 401   {
 402   DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
 403   return yield;      /* FAIL or DEFER */
 404   }
 405 }
 406
 407
 408
 409
 410 /*************************************************
 411 *               Find entry point                 *
 412 *************************************************/
 413
 414 /* See local README for interface description. The handle and filename
 415 arguments are not used. Loop through a list of servers while the query is
 416 deferred with a retryable error. */
 417
 418 int
 419 pgsql_find(void *handle, uschar *filename, uschar *query, int length,
 420   uschar **result, uschar **errmsg, BOOL *do_cache)
 421 {
 422 int sep = 0;
 423 uschar *server;
 424 uschar *list = pgsql_servers;
 425 uschar buffer[512];
 426
 427 DEBUG(D_lookup) debug_printf("PGSQL query: %s\n", query);
 428
 429 while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer)))
 430         != NULL)
 431   {
 432   BOOL defer_break;
 433   int rc = perform_pgsql_search(query, server, result, errmsg, &defer_break,
 434     do_cache);
 435   if (rc != DEFER || defer_break) return rc;
 436   }
 437
 438 if (pgsql_servers == NULL)
 439   *errmsg = US"no PGSQL servers defined (pgsql_servers option)";
 440
 441 return DEFER;
 442 }
 443
 444
 445
 446 /*************************************************
 447 *               Quote entry point                *
 448 *************************************************/
 449
 450 /* The characters that always need to be quoted (with backslash) are newline,
 451 tab, carriage return, backspace, backslash itself, and the quote characters.
 452 Percent and underscore are only special in contexts where they can be wild
 453 cards, and this isn't usually the case for data inserted from messages, since
 454 that isn't likely to be treated as a pattern of any kind. However, pgsql seems
 455 to allow escaping "on spec". If you use something like "where id="ab\%cd" it
 456 does treat the string as "ab%cd". So we can safely quote percent and
 457 underscore. [This is different to MySQL, where you can't do this.]
 458
 459 The original code quoted single quotes as \' which is documented as valid in
 460 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
 461 the SQL standard '' way of representing a single quote as data. However, in
 462 June 2006 there was some security issue with using \' and so this has been
 463 changed.
 464
 465 [Note: There is a function called PQescapeStringConn() that quotes strings.
 466 This cannot be used because it needs a PGconn argument (the connection handle).
 467 Why, I don't know. Seems odd for just string escaping...]
 468
 469 Arguments:
 470   s          the string to be quoted
 471   opt        additional option text or NULL if none
 472
 473 Returns:     the processed string or NULL for a bad option
 474 */
 475
 476 uschar *
 477 pgsql_quote(uschar *s, uschar *opt)
 478 {
 479 register int c;
 480 int count = 0;
 481 uschar *t = s;
 482 uschar *quoted;
 483
 484 if (opt != NULL) return NULL;     /* No options recognized */
 485
 486 while ((c = *t++) != 0)
 487   if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++;
 488
 489 if (count == 0) return s;
 490 t = quoted = store_get(Ustrlen(s) + count + 1);
 491
 492 while ((c = *s++) != 0)
 493   {
 494   if (c == '\'')
 495     {
 496     *t++ = '\'';
 497     *t++ = '\'';
 498     }
 499   else if (Ustrchr("\n\t\r\b\"\\%_", c) != NULL)
 500     {
 501     *t++ = '\\';
 502     switch(c)
 503       {
 504       case '\n': *t++ = 'n';
 505       break;
 506       case '\t': *t++ = 't';
 507       break;
 508       case '\r': *t++ = 'r';
 509       break;
 510       case '\b': *t++ = 'b';
 511       break;
 512       default:   *t++ = c;
 513       break;
 514       }
 515     }
 516   else *t++ = c;
 517   }
 518
 519 *t = 0;
 520 return quoted;
 521 }
 522
 523 #endif  /* PGSQL_LOOKUP */
 524
 525 /* End of lookups/pgsql.c */