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