src/src/lookups/mysql.c

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