src/src/lookups/mysql.c

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