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