src/src/lookups/mysql.c

   1 /* $Cambridge: exim/src/src/lookups/mysql.c,v 1.5 2007/08/23 10:16:51 ph10 Exp $ */
   2
   3 /*************************************************
   4 *     Exim - an Internet mail transport agent    *
   5 *************************************************/
   6
   7 /* Copyright (c) University of Cambridge 1995 - 2007 */
   8 /* See the file NOTICE for conditions of use and distribution. */
   9
  10 /* Thanks to Paul Kelly for contributing the original code for these
  11 functions. */
  12
  13
  14 #include "../exim.h"
  15 #include "lf_functions.h"
  16 #include "mysql.h"       /* The local header */
  17
  18
  19 /* We can't just compile this code and allow the library mechanism to omit the
  20 functions if they are not wanted, because we need to have the MYSQL header
  21 available for compiling. Therefore, compile these functions only if
  22 LOOKUP_MYSQL is defined. However, some compilers don't like compiling empty
  23 modules, so keep them happy with a dummy when skipping the rest. Make it
  24 reference itself to stop picky compilers complaining that it is unused, and put
  25 in a dummy argument to stop even pickier compilers complaining about infinite
  26 loops. */
  27
  28 #ifndef LOOKUP_MYSQL
  29 static void dummy(int x) { dummy(x-1); }
  30 #else
  31
  32
  33 #include <mysql.h>       /* The system header */
  34
  35
  36 /* Structure and anchor for caching connections. */
  37
  38 typedef struct mysql_connection {
  39   struct mysql_connection *next;
  40   uschar  *server;
  41   MYSQL *handle;
  42 } mysql_connection;
  43
  44 static mysql_connection *mysql_connections = NULL;
  45
  46
  47
  48 /*************************************************
  49 *              Open entry point                  *
  50 *************************************************/
  51
  52 /* See local README for interface description. */
  53
  54 void *
  55 mysql_open(uschar *filename, uschar **errmsg)
  56 {
  57 return (void *)(1);    /* Just return something non-null */
  58 }
  59
  60
  61
  62 /*************************************************
  63 *               Tidy entry point                 *
  64 *************************************************/
  65
  66 /* See local README for interface description. */
  67
  68 void
  69 mysql_tidy(void)
  70 {
  71 mysql_connection *cn;
  72 while ((cn = mysql_connections) != NULL)
  73   {
  74   mysql_connections = cn->next;
  75   DEBUG(D_lookup) debug_printf("close MYSQL connection: %s\n", cn->server);
  76   mysql_close(cn->handle);
  77   }
  78 }
  79
  80
  81
  82 /*************************************************
  83 *        Internal search function                *
  84 *************************************************/
  85
  86 /* This function is called from the find entry point to do the search for a
  87 single server.
  88
  89 Arguments:
  90   query        the query string
  91   server       the server string
  92   resultptr    where to store the result
  93   errmsg       where to point an error message
  94   defer_break  TRUE if no more servers are to be tried after DEFER
  95   do_cache     set false if data is changed
  96
  97 The server string is of the form "host/dbname/user/password". The host can be
  98 host:port. This string is in a nextinlist temporary buffer, so can be
  99 overwritten.
 100
 101 Returns:       OK, FAIL, or DEFER
 102 */
 103
 104 static int
 105 perform_mysql_search(uschar *query, uschar *server, uschar **resultptr,
 106   uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
 107 {
 108 MYSQL *mysql_handle = NULL;        /* Keep compilers happy */
 109 MYSQL_RES *mysql_result = NULL;
 110 MYSQL_ROW mysql_row_data;
 111 MYSQL_FIELD *fields;
 112
 113 int i;
 114 int ssize = 0;
 115 int offset = 0;
 116 int yield = DEFER;
 117 unsigned int num_fields;
 118 uschar *result = NULL;
 119 mysql_connection *cn;
 120 uschar *server_copy = NULL;
 121 uschar *sdata[4];
 122
 123 /* Disaggregate the parameters from the server argument. The order is host,
 124 database, user, password. We can write to the string, since it is in a
 125 nextinlist temporary buffer. The copy of the string that is used for caching
 126 has the password removed. This copy is also used for debugging output. */
 127
 128 for (i = 3; i > 0; i--)
 129   {
 130   uschar *pp = Ustrrchr(server, '/');
 131   if (pp == NULL)
 132     {
 133     *errmsg = string_sprintf("incomplete MySQL server data: %s",
 134       (i == 3)? server : server_copy);
 135     *defer_break = TRUE;
 136     return DEFER;
 137     }
 138   *pp++ = 0;
 139   sdata[i] = pp;
 140   if (i == 3) server_copy = string_copy(server);  /* sans password */
 141   }
 142 sdata[0] = server;   /* What's left at the start */
 143
 144 /* See if we have a cached connection to the server */
 145
 146 for (cn = mysql_connections; cn != NULL; cn = cn->next)
 147   {
 148   if (Ustrcmp(cn->server, server_copy) == 0)
 149     {
 150     mysql_handle = cn->handle;
 151     break;
 152     }
 153   }
 154
 155 /* If no cached connection, we must set one up. Mysql allows for a host name
 156 and port to be specified. It also allows the name of a Unix socket to be used.
 157 Unfortunately, this contains slashes, but its use is expected to be rare, so
 158 the rather cumbersome syntax shouldn't inconvenience too many people. We use
 159 this:  host:port(socket)  where all the parts are optional. */
 160
 161 if (cn == NULL)
 162   {
 163   uschar *p;
 164   uschar *socket = NULL;
 165   int port = 0;
 166
 167   if ((p = Ustrchr(sdata[0], '(')) != NULL)
 168     {
 169     *p++ = 0;
 170     socket = p;
 171     while (*p != 0 && *p != ')') p++;
 172     *p = 0;
 173     }
 174
 175   if ((p = Ustrchr(sdata[0], ':')) != NULL)
 176     {
 177     *p++ = 0;
 178     port = Uatoi(p);
 179     }
 180
 181   if (Ustrchr(sdata[0], '/') != NULL)
 182     {
 183     *errmsg = string_sprintf("unexpected slash in MySQL server hostname: %s",
 184       sdata[0]);
 185     *defer_break = TRUE;
 186     return DEFER;
 187     }
 188
 189   /* If the database is the empty string, set it NULL - the query must then
 190   define it. */
 191
 192   if (sdata[1][0] == 0) sdata[1] = NULL;
 193
 194   DEBUG(D_lookup)
 195     debug_printf("MYSQL new connection: host=%s port=%d socket=%s "
 196       "database=%s user=%s\n", sdata[0], port, socket, sdata[1], sdata[2]);
 197
 198   /* Get store for a new handle, initialize it, and connect to the server */
 199
 200   mysql_handle = store_get(sizeof(MYSQL));
 201   mysql_init(mysql_handle);
 202   if (mysql_real_connect(mysql_handle,
 203       /*  host        user         passwd     database */
 204       CS sdata[0], CS sdata[2], CS sdata[3], CS sdata[1],
 205       port, CS socket, 0) == NULL)
 206     {
 207     *errmsg = string_sprintf("MYSQL connection failed: %s",
 208       mysql_error(mysql_handle));
 209     *defer_break = FALSE;
 210     goto MYSQL_EXIT;
 211     }
 212
 213   /* Add the connection to the cache */
 214
 215   cn = store_get(sizeof(mysql_connection));
 216   cn->server = server_copy;
 217   cn->handle = mysql_handle;
 218   cn->next = mysql_connections;
 219   mysql_connections = cn;
 220   }
 221
 222 /* Else use a previously cached connection */
 223
 224 else
 225   {
 226   DEBUG(D_lookup)
 227     debug_printf("MYSQL using cached connection for %s\n", server_copy);
 228   }
 229
 230 /* Run the query */
 231
 232 if (mysql_query(mysql_handle, CS query) != 0)
 233   {
 234   *errmsg = string_sprintf("MYSQL: query failed: %s\n",
 235     mysql_error(mysql_handle));
 236   *defer_break = FALSE;
 237   goto MYSQL_EXIT;
 238   }
 239
 240 /* Pick up the result. If the query was not of the type that returns data,
 241 namely INSERT, UPDATE, or DELETE, an error occurs here. However, this situation
 242 can be detected by calling mysql_field_count(). If its result is zero, no data
 243 was expected (this is all explained clearly in the MySQL manual). In this case,
 244 we return the number of rows affected by the command. In this event, we do NOT
 245 want to cache the result; also the whole cache for the handle must be cleaned
 246 up. Setting do_cache FALSE requests this. */
 247
 248 if ((mysql_result = mysql_use_result(mysql_handle)) == NULL)
 249   {
 250   if ( mysql_field_count(mysql_handle) == 0 )
 251     {
 252     DEBUG(D_lookup) debug_printf("MYSQL: query was not one that returns data\n");
 253     result = string_sprintf("%d", mysql_affected_rows(mysql_handle));
 254     *do_cache = FALSE;
 255     goto MYSQL_EXIT;
 256     }
 257   *errmsg = string_sprintf("MYSQL: lookup result failed: %s\n",
 258     mysql_error(mysql_handle));
 259   *defer_break = FALSE;
 260   goto MYSQL_EXIT;
 261   }
 262
 263 /* Find the number of fields returned. If this is one, we don't add field
 264 names to the data. Otherwise we do. */
 265
 266 num_fields = mysql_num_fields(mysql_result);
 267
 268 /* Get the fields and construct the result string. If there is more than one
 269 row, we insert '\n' between them. */
 270
 271 fields = mysql_fetch_fields(mysql_result);
 272
 273 while ((mysql_row_data = mysql_fetch_row(mysql_result)) != NULL)
 274   {
 275   unsigned long *lengths = mysql_fetch_lengths(mysql_result);
 276
 277   if (result != NULL)
 278       result = string_cat(result, &ssize, &offset, US"\n", 1);
 279
 280   if (num_fields == 1)
 281     {
 282     if (mysql_row_data[0] != NULL)    /* NULL value yields nothing */
 283       result = string_cat(result, &ssize, &offset, US mysql_row_data[0],
 284         lengths[0]);
 285     }
 286
 287   else for (i = 0; i < num_fields; i++)
 288     {
 289     result = lf_quote(US fields[i].name, US mysql_row_data[i], lengths[i],
 290       result, &ssize, &offset);
 291     }
 292   }
 293
 294 /* If result is NULL then no data has been found and so we return FAIL.
 295 Otherwise, we must terminate the string which has been built; string_cat()
 296 always leaves enough room for a terminating zero. */
 297
 298 if (result == NULL)
 299   {
 300   yield = FAIL;
 301   *errmsg = US"MYSQL: no data found";
 302   }
 303 else
 304   {
 305   result[offset] = 0;
 306   store_reset(result + offset + 1);
 307   }
 308
 309 /* Get here by goto from various error checks and from the case where no data
 310 was read (e.g. an update query). */
 311
 312 MYSQL_EXIT:
 313
 314 /* Free mysal store for any result that was got; don't close the connection, as
 315 it is cached. */
 316
 317 if (mysql_result != NULL) mysql_free_result(mysql_result);
 318
 319 /* Non-NULL result indicates a sucessful result */
 320
 321 if (result != NULL)
 322   {
 323   *resultptr = result;
 324   return OK;
 325   }
 326 else
 327   {
 328   DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
 329   return yield;      /* FAIL or DEFER */
 330   }
 331 }
 332
 333
 334
 335
 336 /*************************************************
 337 *               Find entry point                 *
 338 *************************************************/
 339
 340 /* See local README for interface description. The handle and filename
 341 arguments are not used. The code to loop through a list of servers while the
 342 query is deferred with a retryable error is now in a separate function that is
 343 shared with other SQL lookups. */
 344
 345 int
 346 mysql_find(void *handle, uschar *filename, uschar *query, int length,
 347   uschar **result, uschar **errmsg, BOOL *do_cache)
 348 {
 349 return lf_sqlperform(US"MySQL", US"mysql_servers", mysql_servers, query,
 350   result, errmsg, do_cache, perform_mysql_search);
 351 }
 352
 353
 354
 355 /*************************************************
 356 *               Quote entry point                *
 357 *************************************************/
 358
 359 /* The only characters that need to be quoted (with backslash) are newline,
 360 tab, carriage return, backspace, backslash itself, and the quote characters.
 361 Percent, and underscore and not escaped. They are only special in contexts
 362 where they can be wild cards, and this isn't usually the case for data inserted
 363 from messages, since that isn't likely to be treated as a pattern of any kind.
 364 Sadly, MySQL doesn't seem to behave like other programs. If you use something
 365 like "where id="ab\%cd" it does not treat the string as "ab%cd". So you really
 366 can't quote "on spec".
 367
 368 Arguments:
 369   s          the string to be quoted
 370   opt        additional option text or NULL if none
 371
 372 Returns:     the processed string or NULL for a bad option
 373 */
 374
 375 uschar *
 376 mysql_quote(uschar *s, uschar *opt)
 377 {
 378 register int c;
 379 int count = 0;
 380 uschar *t = s;
 381 uschar *quoted;
 382
 383 if (opt != NULL) return NULL;     /* No options recognized */
 384
 385 while ((c = *t++) != 0)
 386   if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
 387
 388 if (count == 0) return s;
 389 t = quoted = store_get(Ustrlen(s) + count + 1);
 390
 391 while ((c = *s++) != 0)
 392   {
 393   if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL)
 394     {
 395     *t++ = '\\';
 396     switch(c)
 397       {
 398       case '\n': *t++ = 'n';
 399       break;
 400       case '\t': *t++ = 't';
 401       break;
 402       case '\r': *t++ = 'r';
 403       break;
 404       case '\b': *t++ = 'b';
 405       break;
 406       default:   *t++ = c;
 407       break;
 408       }
 409     }
 410   else *t++ = c;
 411   }
 412
 413 *t = 0;
 414 return quoted;
 415 }
 416
 417
 418 #endif  /* MYSQL_LOOKUP */
 419
 420 /* End of lookups/mysql.c */