1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
5 /* Copyright (c) University of Cambridge 1995 - 2018 */
6 /* Copyright (c) The Exim Maintainers 2020 */
7 /* See the file NOTICE for conditions of use and distribution. */
9 /* Thanks to Paul Kelly for contributing the original code for these
14 #include "lf_functions.h"
16 #include <mysql.h> /* The system header */
18 /* We define symbols for *_VERSION_ID (numeric), *_VERSION_STR (char*)
19 and *_BASE_STR (char*). It's a bit of guesswork. Especially for mariadb
20 with versions before 10.2, as they do not define there there specific symbols.
23 /* Newer (>= 10.2) MariaDB */
24 #if defined MARIADB_VERSION_ID
25 #define EXIM_MxSQL_VERSION_ID MARIADB_VERSION_ID
27 /* MySQL defines MYSQL_VERSION_ID, and MariaDB does so */
28 /* https://dev.mysql.com/doc/refman/5.7/en/c-api-server-client-versions.html */
29 #elif defined LIBMYSQL_VERSION_ID
30 #define EXIM_MxSQL_VERSION_ID LIBMYSQL_VERSION_ID
31 #elif defined MYSQL_VERSION_ID
32 #define EXIM_MxSQL_VERSION_ID MYSQL_VERSION_ID
35 #define EXIM_MYSQL_VERSION_ID 0
38 /* Newer (>= 10.2) MariaDB */
39 #ifdef MARIADB_CLIENT_VERSION_STR
40 #define EXIM_MxSQL_VERSION_STR MARIADB_CLIENT_VERSION_STR
42 /* Mysql uses MYSQL_SERVER_VERSION */
43 #elif defined LIBMYSQL_VERSION
44 #define EXIM_MxSQL_VERSION_STR LIBMYSQL_VERSION
45 #elif defined MYSQL_SERVER_VERSION
46 #define EXIM_MxSQL_VERSION_STR MYSQL_SERVER_VERSION
49 #define EXIM_MxSQL_VERSION_STR "unknown"
52 #if defined MARIADB_BASE_VERSION
53 #define EXIM_MxSQL_BASE_STR MARIADB_BASE_VERSION
55 #elif defined MARIADB_PACKAGE_VERSION
56 #define EXIM_MxSQL_BASE_STR "mariadb"
58 #elif defined MYSQL_BASE_VERSION
59 #define EXIM_MxSQL_BASE_STR MYSQL_BASE_VERSION
62 #define EXIM_MxSQL_BASE_STR "n.A."
66 /* Structure and anchor for caching connections. */
68 typedef struct mysql_connection {
69 struct mysql_connection *next;
74 static mysql_connection *mysql_connections = NULL;
78 /*************************************************
80 *************************************************/
82 /* See local README for interface description. */
85 mysql_open(const uschar * filename, uschar ** errmsg)
87 return (void *)(1); /* Just return something non-null */
92 /*************************************************
94 *************************************************/
96 /* See local README for interface description. */
101 mysql_connection *cn;
102 while ((cn = mysql_connections) != NULL)
104 mysql_connections = cn->next;
105 DEBUG(D_lookup) debug_printf_indent("close MYSQL connection: %s\n", cn->server);
106 mysql_close(cn->handle);
112 /*************************************************
113 * Internal search function *
114 *************************************************/
116 /* This function is called from the find entry point to do the search for a
120 query the query string
121 server the server string
122 resultptr where to store the result
123 errmsg where to point an error message
124 defer_break TRUE if no more servers are to be tried after DEFER
125 do_cache set zero if data is changed
128 The server string is of the form "host/dbname/user/password". The host can be
129 host:port. This string is in a nextinlist temporary buffer, so can be
132 Returns: OK, FAIL, or DEFER
136 perform_mysql_search(const uschar *query, uschar *server, uschar **resultptr,
137 uschar **errmsg, BOOL *defer_break, uint *do_cache, const uschar * opts)
139 MYSQL *mysql_handle = NULL; /* Keep compilers happy */
140 MYSQL_RES *mysql_result = NULL;
141 MYSQL_ROW mysql_row_data;
146 unsigned int num_fields;
147 gstring * 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 (int 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)
177 { mysql_handle = cn->handle; break; }
179 /* If no cached connection, we must set one up. Mysql allows for a host name
180 and port to be specified. It also allows the name of a Unix socket to be used.
181 Unfortunately, this contains slashes, but its use is expected to be rare, so
182 the rather cumbersome syntax shouldn't inconvenience too many people. We use
183 this: host:port(socket)[group] where all the parts are optional.
184 The "group" parameter specifies an option group from a MySQL option file. */
189 uschar *socket = NULL;
191 uschar *group = US"exim";
193 if ((p = Ustrchr(sdata[0], '[')))
197 while (*p && *p != ']') p++;
201 if ((p = Ustrchr(sdata[0], '(')))
205 while (*p && *p != ')') p++;
209 if ((p = Ustrchr(sdata[0], ':')))
215 if (Ustrchr(sdata[0], '/'))
217 *errmsg = string_sprintf("unexpected slash in MySQL server hostname: %s",
223 /* If the database is the empty string, set it NULL - the query must then
226 if (sdata[1][0] == 0) sdata[1] = NULL;
229 debug_printf_indent("MYSQL new connection: host=%s port=%d socket=%s "
230 "database=%s user=%s\n", sdata[0], port, socket, sdata[1], sdata[2]);
232 /* Get store for a new handle, initialize it, and connect to the server */
234 mysql_handle = store_get(sizeof(MYSQL), GET_UNTAINTED);
235 mysql_init(mysql_handle);
236 mysql_options(mysql_handle, MYSQL_READ_DEFAULT_GROUP, CS group);
237 if (mysql_real_connect(mysql_handle,
238 /* host user passwd database */
239 CS sdata[0], CS sdata[2], CS sdata[3], CS sdata[1],
240 port, CS socket, CLIENT_MULTI_RESULTS) == NULL)
242 *errmsg = string_sprintf("MYSQL connection failed: %s",
243 mysql_error(mysql_handle));
244 *defer_break = FALSE;
248 /* Add the connection to the cache */
250 cn = store_get(sizeof(mysql_connection), GET_UNTAINTED);
251 cn->server = server_copy;
252 cn->handle = mysql_handle;
253 cn->next = mysql_connections;
254 mysql_connections = cn;
257 /* Else use a previously cached connection */
262 debug_printf_indent("MYSQL using cached connection for %s\n", server_copy);
267 if (mysql_query(mysql_handle, CS query) != 0)
269 *errmsg = string_sprintf("MYSQL: query failed: %s\n",
270 mysql_error(mysql_handle));
271 *defer_break = FALSE;
275 /* Pick up the result. If the query was not of the type that returns data,
276 namely INSERT, UPDATE, or DELETE, an error occurs here. However, this situation
277 can be detected by calling mysql_field_count(). If its result is zero, no data
278 was expected (this is all explained clearly in the MySQL manual). In this case,
279 we return the number of rows affected by the command. In this event, we do NOT
280 want to cache the result; also the whole cache for the handle must be cleaned
281 up. Setting do_cache zero requests this. */
283 if (!(mysql_result = mysql_use_result(mysql_handle)))
285 if (mysql_field_count(mysql_handle) == 0)
287 DEBUG(D_lookup) debug_printf_indent("MYSQL: query was not one that returns data\n");
288 result = string_cat(result,
289 string_sprintf("%lld", mysql_affected_rows(mysql_handle)));
293 *errmsg = string_sprintf("MYSQL: lookup result failed: %s\n",
294 mysql_error(mysql_handle));
295 *defer_break = FALSE;
299 /* Find the number of fields returned. If this is one, we don't add field
300 names to the data. Otherwise we do. */
302 num_fields = mysql_num_fields(mysql_result);
304 /* Get the fields and construct the result string. If there is more than one
305 row, we insert '\n' between them. */
307 fields = mysql_fetch_fields(mysql_result);
309 while ((mysql_row_data = mysql_fetch_row(mysql_result)))
311 unsigned long * lengths = mysql_fetch_lengths(mysql_result);
314 result = string_catn(result, US"\n", 1);
317 for (int i = 0; i < num_fields; i++)
318 result = lf_quote(US fields[i].name, US mysql_row_data[i], lengths[i],
321 else if (mysql_row_data[0] != NULL) /* NULL value yields nothing */
322 result = lengths[0] == 0 && !result
323 ? string_get(1) /* for 0-len string result ensure non-null gstring */
324 : string_catn(result, US mysql_row_data[0], lengths[0]);
327 /* more results? -1 = no, >0 = error, 0 = yes (keep looping)
328 This is needed because of the CLIENT_MULTI_RESULTS on mysql_real_connect(),
329 we don't expect any more results. */
331 while((i = mysql_next_result(mysql_handle)) >= 0)
334 *errmsg = string_sprintf(
335 "MYSQL: lookup result error when checking for more results: %s\n",
336 mysql_error(mysql_handle));
339 else /* just ignore more results */
340 DEBUG(D_lookup) debug_printf_indent("MYSQL: got unexpected more results\n");
342 /* If result is NULL then no data has been found and so we return FAIL.
343 Otherwise, we must terminate the string which has been built; string_cat()
344 always leaves enough room for a terminating zero. */
349 *errmsg = US"MYSQL: no data found";
352 /* Get here by goto from various error checks and from the case where no data
353 was read (e.g. an update query). */
357 /* Free mysal store for any result that was got; don't close the connection, as
360 if (mysql_result) mysql_free_result(mysql_result);
362 /* Non-NULL result indicates a successful result */
366 *resultptr = string_from_gstring(result);
367 gstring_release_unused(result);
372 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
373 return yield; /* FAIL or DEFER */
380 /*************************************************
382 *************************************************/
384 /* See local README for interface description. The handle and filename
385 arguments are not used. The code to loop through a list of servers while the
386 query is deferred with a retryable error is now in a separate function that is
387 shared with other SQL lookups. */
390 mysql_find(void * handle, const uschar * filename, const uschar * query,
391 int length, uschar ** result, uschar ** errmsg, uint * do_cache,
394 return lf_sqlperform(US"MySQL", US"mysql_servers", mysql_servers, query,
395 result, errmsg, do_cache, opts, perform_mysql_search);
400 /*************************************************
401 * Quote entry point *
402 *************************************************/
404 /* The only characters that need to be quoted (with backslash) are newline,
405 tab, carriage return, backspace, backslash itself, and the quote characters.
406 Percent, and underscore and not escaped. They are only special in contexts
407 where they can be wild cards, and this isn't usually the case for data inserted
408 from messages, since that isn't likely to be treated as a pattern of any kind.
409 Sadly, MySQL doesn't seem to behave like other programs. If you use something
410 like "where id="ab\%cd" it does not treat the string as "ab%cd". So you really
411 can't quote "on spec".
414 s the string to be quoted
415 opt additional option text or NULL if none
416 idx lookup type index
418 Returns: the processed string or NULL for a bad option
422 mysql_quote(uschar * s, uschar * opt, unsigned idx)
425 uschar * t = s, * quoted;
427 if (opt) return NULL; /* No options recognized */
430 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
432 /* Old code: if (count == 0) return s;
433 Now always allocate and copy, to track the quoted status. */
435 t = quoted = store_get_quoted(Ustrlen(s) + count + 1, s, idx);
439 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL)
444 case '\n': *t++ = 'n'; break;
445 case '\t': *t++ = 't'; break;
446 case '\r': *t++ = 'r'; break;
447 case '\b': *t++ = 'b'; break;
448 default: *t++ = c; break;
459 /*************************************************
460 * Version reporting entry point *
461 *************************************************/
463 /* See local README for interface description. */
465 #include "../version.h"
468 mysql_version_report(gstring * g)
470 g = string_fmt_append(g,
471 "Library version: MySQL: Compile: %lu %s [%s]\n"
472 " Runtime: %lu %s\n",
473 (long)EXIM_MxSQL_VERSION_ID, EXIM_MxSQL_VERSION_STR, EXIM_MxSQL_BASE_STR,
474 mysql_get_client_version(), mysql_get_client_info());
476 g = string_fmt_append(g,
477 " Exim version %s\n", EXIM_VERSION_STR);
482 /* These are the lookup_info blocks for this driver */
484 static lookup_info mysql_lookup_info = {
485 .name = US"mysql", /* lookup name */
486 .type = lookup_querystyle, /* query-style lookup */
487 .open = mysql_open, /* open function */
488 .check = NULL, /* no check function */
489 .find = mysql_find, /* find function */
490 .close = NULL, /* no close function */
491 .tidy = mysql_tidy, /* tidy function */
492 .quote = mysql_quote, /* quoting function */
493 .version_report = mysql_version_report /* version reporting */
497 #define mysql_lookup_module_info _lookup_module_info
500 static lookup_info *_lookup_list[] = { &mysql_lookup_info };
501 lookup_module_info mysql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
503 /* End of lookups/mysql.c */