1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
5 /* Copyright (c) University of Cambridge 1995 - 2015 */
6 /* See the file NOTICE for conditions of use and distribution. */
8 /* Thanks to Paul Kelly for contributing the original code for these
13 #include "lf_functions.h"
15 #include <mysql.h> /* The system header */
17 /* We define symbols for *_VERSION_ID (numeric), *_VERSION_STR (char*)
18 and *_BASE_STR (char*). It's a bit of guesswork. Especially for mariadb
19 with versions before 10.2, as they do not define there there specific symbols.
22 // Newer (>= 10.2) MariaDB
23 #if defined MARIADB_VERSION_ID
24 #define EXIM_MxSQL_VERSION_ID MARIADB_VERSION_ID
26 // MySQL defines MYSQL_VERSION_ID, and MariaDB does so
27 // https://dev.mysql.com/doc/refman/5.7/en/c-api-server-client-versions.html
28 #elif defined LIBMYSQL_VERSION_ID
29 #define EXIM_MxSQL_VERSION_ID LIBMYSQL_VERSION_ID
30 #elif defined MYSQL_VERSION_ID
31 #define EXIM_MxSQL_VERSION_ID MYSQL_VERSION_ID
34 #define EXIM_MYSQL_VERSION_ID 0
37 // Newer (>= 10.2) MariaDB
38 #ifdef MARIADB_CLIENT_VERSION_STR
39 #define EXIM_MxSQL_VERSION_STR MARIADB_CLIENT_VERSION_STR
41 // Mysql uses MYSQL_SERVER_VERSION
42 #elif defined LIBMYSQL_VERSION
43 #define EXIM_MxSQL_VERSION_STR LIBMYSQL_VERSION
44 #elif defined MYSQL_SERVER_VERSION
45 #define EXIM_MxSQL_VERSION_STR MYSQL_SERVER_VERSION
48 #define EXIM_MxSQL_VERSION_STR "N.A."
51 #if defined MARIADB_BASE_VERSION
52 #define EXIM_MxSQL_BASE_STR MARIADB_BASE_VERSION
54 #elif defined MARIADB_PACKAGE_VERSION
55 #define EXIM_MxSQL_BASE_STR "mariadb"
57 #elif defined MYSQL_BASE_VERSION
58 #define EXIM_MxSQL_BASE_STR MYSQL_BASE_VERSION
61 #define EXIM_MxSQL_BASE_STR "n.A."
65 /* Structure and anchor for caching connections. */
67 typedef struct mysql_connection {
68 struct mysql_connection *next;
73 static mysql_connection *mysql_connections = NULL;
77 /*************************************************
79 *************************************************/
81 /* See local README for interface description. */
84 mysql_open(uschar *filename, uschar **errmsg)
86 return (void *)(1); /* Just return something non-null */
91 /*************************************************
93 *************************************************/
95 /* See local README for interface description. */
100 mysql_connection *cn;
101 while ((cn = mysql_connections) != NULL)
103 mysql_connections = cn->next;
104 DEBUG(D_lookup) debug_printf("close MYSQL connection: %s\n", cn->server);
105 mysql_close(cn->handle);
111 /*************************************************
112 * Internal search function *
113 *************************************************/
115 /* This function is called from the find entry point to do the search for a
119 query the query string
120 server the server string
121 resultptr where to store the result
122 errmsg where to point an error message
123 defer_break TRUE if no more servers are to be tried after DEFER
124 do_cache set zero if data is changed
126 The server string is of the form "host/dbname/user/password". The host can be
127 host:port. This string is in a nextinlist temporary buffer, so can be
130 Returns: OK, FAIL, or DEFER
134 perform_mysql_search(const uschar *query, uschar *server, uschar **resultptr,
135 uschar **errmsg, BOOL *defer_break, uint *do_cache)
137 MYSQL *mysql_handle = NULL; /* Keep compilers happy */
138 MYSQL_RES *mysql_result = NULL;
139 MYSQL_ROW mysql_row_data;
146 unsigned int num_fields;
147 uschar *result = NULL;
148 mysql_connection *cn;
149 uschar *server_copy = NULL;
152 /* Disaggregate the parameters from the server argument. The order is host,
153 database, user, password. We can write to the string, since it is in a
154 nextinlist temporary buffer. The copy of the string that is used for caching
155 has the password removed. This copy is also used for debugging output. */
157 for (i = 3; i > 0; i--)
159 uschar *pp = Ustrrchr(server, '/');
162 *errmsg = string_sprintf("incomplete MySQL server data: %s",
163 (i == 3)? server : server_copy);
169 if (i == 3) server_copy = string_copy(server); /* sans password */
171 sdata[0] = server; /* What's left at the start */
173 /* See if we have a cached connection to the server */
175 for (cn = mysql_connections; cn; cn = cn->next)
176 if (Ustrcmp(cn->server, server_copy) == 0)
178 mysql_handle = cn->handle;
182 /* If no cached connection, we must set one up. Mysql allows for a host name
183 and port to be specified. It also allows the name of a Unix socket to be used.
184 Unfortunately, this contains slashes, but its use is expected to be rare, so
185 the rather cumbersome syntax shouldn't inconvenience too many people. We use
186 this: host:port(socket)[group] where all the parts are optional.
187 The "group" parameter specifies an option group from a MySQL option file. */
192 uschar *socket = NULL;
194 uschar *group = US"exim";
196 if ((p = Ustrchr(sdata[0], '[')))
200 while (*p && *p != ']') p++;
204 if ((p = Ustrchr(sdata[0], '(')))
208 while (*p && *p != ')') p++;
212 if ((p = Ustrchr(sdata[0], ':')))
218 if (Ustrchr(sdata[0], '/'))
220 *errmsg = string_sprintf("unexpected slash in MySQL server hostname: %s",
226 /* If the database is the empty string, set it NULL - the query must then
229 if (sdata[1][0] == 0) sdata[1] = NULL;
232 debug_printf("MYSQL new connection: host=%s port=%d socket=%s "
233 "database=%s user=%s\n", sdata[0], port, socket, sdata[1], sdata[2]);
235 /* Get store for a new handle, initialize it, and connect to the server */
237 mysql_handle = store_get(sizeof(MYSQL));
238 mysql_init(mysql_handle);
239 mysql_options(mysql_handle, MYSQL_READ_DEFAULT_GROUP, CS group);
240 if (mysql_real_connect(mysql_handle,
241 /* host user passwd database */
242 CS sdata[0], CS sdata[2], CS sdata[3], CS sdata[1],
243 port, CS socket, CLIENT_MULTI_RESULTS) == NULL)
245 *errmsg = string_sprintf("MYSQL connection failed: %s",
246 mysql_error(mysql_handle));
247 *defer_break = FALSE;
251 /* Add the connection to the cache */
253 cn = store_get(sizeof(mysql_connection));
254 cn->server = server_copy;
255 cn->handle = mysql_handle;
256 cn->next = mysql_connections;
257 mysql_connections = cn;
260 /* Else use a previously cached connection */
265 debug_printf("MYSQL using cached connection for %s\n", server_copy);
270 if (mysql_query(mysql_handle, CS query) != 0)
272 *errmsg = string_sprintf("MYSQL: query failed: %s\n",
273 mysql_error(mysql_handle));
274 *defer_break = FALSE;
278 /* Pick up the result. If the query was not of the type that returns data,
279 namely INSERT, UPDATE, or DELETE, an error occurs here. However, this situation
280 can be detected by calling mysql_field_count(). If its result is zero, no data
281 was expected (this is all explained clearly in the MySQL manual). In this case,
282 we return the number of rows affected by the command. In this event, we do NOT
283 want to cache the result; also the whole cache for the handle must be cleaned
284 up. Setting do_cache zero requests this. */
286 if ((mysql_result = mysql_use_result(mysql_handle)) == NULL)
288 if ( mysql_field_count(mysql_handle) == 0 )
290 DEBUG(D_lookup) debug_printf("MYSQL: query was not one that returns data\n");
291 result = string_sprintf("%d", mysql_affected_rows(mysql_handle));
295 *errmsg = string_sprintf("MYSQL: lookup result failed: %s\n",
296 mysql_error(mysql_handle));
297 *defer_break = FALSE;
301 /* Find the number of fields returned. If this is one, we don't add field
302 names to the data. Otherwise we do. */
304 num_fields = mysql_num_fields(mysql_result);
306 /* Get the fields and construct the result string. If there is more than one
307 row, we insert '\n' between them. */
309 fields = mysql_fetch_fields(mysql_result);
311 while ((mysql_row_data = mysql_fetch_row(mysql_result)) != NULL)
313 unsigned long *lengths = mysql_fetch_lengths(mysql_result);
316 result = string_catn(result, &ssize, &offset, US"\n", 1);
320 if (mysql_row_data[0] != NULL) /* NULL value yields nothing */
321 result = string_catn(result, &ssize, &offset, US mysql_row_data[0],
325 else for (i = 0; i < num_fields; i++)
327 result = lf_quote(US fields[i].name, US mysql_row_data[i], lengths[i],
328 result, &ssize, &offset);
332 /* more results? -1 = no, >0 = error, 0 = yes (keep looping)
333 This is needed because of the CLIENT_MULTI_RESULTS on mysql_real_connect(),
334 we don't expect any more results. */
336 while((i = mysql_next_result(mysql_handle)) >= 0) {
337 if(i == 0) { /* Just ignore more results */
338 DEBUG(D_lookup) debug_printf("MYSQL: got unexpected more results\n");
342 *errmsg = string_sprintf("MYSQL: lookup result error when checking for more results: %s\n",
343 mysql_error(mysql_handle));
347 /* If result is NULL then no data has been found and so we return FAIL.
348 Otherwise, we must terminate the string which has been built; string_cat()
349 always leaves enough room for a terminating zero. */
354 *errmsg = US"MYSQL: no data found";
359 store_reset(result + offset + 1);
362 /* Get here by goto from various error checks and from the case where no data
363 was read (e.g. an update query). */
367 /* Free mysal store for any result that was got; don't close the connection, as
370 if (mysql_result != NULL) mysql_free_result(mysql_result);
372 /* Non-NULL result indicates a successful result */
381 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
382 return yield; /* FAIL or DEFER */
389 /*************************************************
391 *************************************************/
393 /* See local README for interface description. The handle and filename
394 arguments are not used. The code to loop through a list of servers while the
395 query is deferred with a retryable error is now in a separate function that is
396 shared with other SQL lookups. */
399 mysql_find(void *handle, uschar *filename, const uschar *query, int length,
400 uschar **result, uschar **errmsg, uint *do_cache)
402 return lf_sqlperform(US"MySQL", US"mysql_servers", mysql_servers, query,
403 result, errmsg, do_cache, perform_mysql_search);
408 /*************************************************
409 * Quote entry point *
410 *************************************************/
412 /* The only characters that need to be quoted (with backslash) are newline,
413 tab, carriage return, backspace, backslash itself, and the quote characters.
414 Percent, and underscore and not escaped. They are only special in contexts
415 where they can be wild cards, and this isn't usually the case for data inserted
416 from messages, since that isn't likely to be treated as a pattern of any kind.
417 Sadly, MySQL doesn't seem to behave like other programs. If you use something
418 like "where id="ab\%cd" it does not treat the string as "ab%cd". So you really
419 can't quote "on spec".
422 s the string to be quoted
423 opt additional option text or NULL if none
425 Returns: the processed string or NULL for a bad option
429 mysql_quote(uschar *s, uschar *opt)
436 if (opt != NULL) return NULL; /* No options recognized */
438 while ((c = *t++) != 0)
439 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
441 if (count == 0) return s;
442 t = quoted = store_get(Ustrlen(s) + count + 1);
444 while ((c = *s++) != 0)
446 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL)
451 case '\n': *t++ = 'n';
453 case '\t': *t++ = 't';
455 case '\r': *t++ = 'r';
457 case '\b': *t++ = 'b';
471 /*************************************************
472 * Version reporting entry point *
473 *************************************************/
475 /* See local README for interface description. */
477 #include "../version.h"
480 mysql_version_report(FILE *f)
482 fprintf(f, "Library version: MySQL: Compile: %lu %s [%s]\n"
483 " Runtime: %lu %s\n",
484 (long)EXIM_MxSQL_VERSION_ID, EXIM_MxSQL_VERSION_STR, EXIM_MxSQL_BASE_STR,
485 mysql_get_client_version(), mysql_get_client_info());
487 fprintf(f, " Exim version %s\n", EXIM_VERSION_STR);
491 /* These are the lookup_info blocks for this driver */
493 static lookup_info mysql_lookup_info = {
494 US"mysql", /* lookup name */
495 lookup_querystyle, /* query-style lookup */
496 mysql_open, /* open function */
497 NULL, /* no check function */
498 mysql_find, /* find function */
499 NULL, /* no close function */
500 mysql_tidy, /* tidy function */
501 mysql_quote, /* quoting function */
502 mysql_version_report /* version reporting */
506 #define mysql_lookup_module_info _lookup_module_info
509 static lookup_info *_lookup_list[] = { &mysql_lookup_info };
510 lookup_module_info mysql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
512 /* End of lookups/mysql.c */