1 /* $Cambridge: exim/src/src/lookups/mysql.c,v 1.7 2010/03/05 15:59:29 nm4 Exp $ */
3 /*************************************************
4 * Exim - an Internet mail transport agent *
5 *************************************************/
7 /* Copyright (c) University of Cambridge 1995 - 2009 */
8 /* See the file NOTICE for conditions of use and distribution. */
10 /* Thanks to Paul Kelly for contributing the original code for these
15 #include "lf_functions.h"
16 #include "mysql.h" /* The local header */
19 /* We can't just compile this code and allow the library mechanism to omit the
20 functions if they are not wanted, because we need to have the MYSQL header
21 available for compiling. Therefore, compile these functions only if
22 LOOKUP_MYSQL is defined. However, some compilers don't like compiling empty
23 modules, so keep them happy with a dummy when skipping the rest. Make it
24 reference itself to stop picky compilers complaining that it is unused, and put
25 in a dummy argument to stop even pickier compilers complaining about infinite
29 static void dummy(int x) { dummy(x-1); }
33 #include <mysql.h> /* The system header */
36 /* Structure and anchor for caching connections. */
38 typedef struct mysql_connection {
39 struct mysql_connection *next;
44 static mysql_connection *mysql_connections = NULL;
48 /*************************************************
50 *************************************************/
52 /* See local README for interface description. */
55 mysql_open(uschar *filename, uschar **errmsg)
57 return (void *)(1); /* Just return something non-null */
62 /*************************************************
64 *************************************************/
66 /* See local README for interface description. */
72 while ((cn = mysql_connections) != NULL)
74 mysql_connections = cn->next;
75 DEBUG(D_lookup) debug_printf("close MYSQL connection: %s\n", cn->server);
76 mysql_close(cn->handle);
82 /*************************************************
83 * Internal search function *
84 *************************************************/
86 /* This function is called from the find entry point to do the search for a
90 query the query string
91 server the server string
92 resultptr where to store the result
93 errmsg where to point an error message
94 defer_break TRUE if no more servers are to be tried after DEFER
95 do_cache set false if data is changed
97 The server string is of the form "host/dbname/user/password". The host can be
98 host:port. This string is in a nextinlist temporary buffer, so can be
101 Returns: OK, FAIL, or DEFER
105 perform_mysql_search(uschar *query, uschar *server, uschar **resultptr,
106 uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
108 MYSQL *mysql_handle = NULL; /* Keep compilers happy */
109 MYSQL_RES *mysql_result = NULL;
110 MYSQL_ROW mysql_row_data;
117 unsigned int num_fields;
118 uschar *result = NULL;
119 mysql_connection *cn;
120 uschar *server_copy = NULL;
123 /* Disaggregate the parameters from the server argument. The order is host,
124 database, user, password. We can write to the string, since it is in a
125 nextinlist temporary buffer. The copy of the string that is used for caching
126 has the password removed. This copy is also used for debugging output. */
128 for (i = 3; i > 0; i--)
130 uschar *pp = Ustrrchr(server, '/');
133 *errmsg = string_sprintf("incomplete MySQL server data: %s",
134 (i == 3)? server : server_copy);
140 if (i == 3) server_copy = string_copy(server); /* sans password */
142 sdata[0] = server; /* What's left at the start */
144 /* See if we have a cached connection to the server */
146 for (cn = mysql_connections; cn != NULL; cn = cn->next)
148 if (Ustrcmp(cn->server, server_copy) == 0)
150 mysql_handle = cn->handle;
155 /* If no cached connection, we must set one up. Mysql allows for a host name
156 and port to be specified. It also allows the name of a Unix socket to be used.
157 Unfortunately, this contains slashes, but its use is expected to be rare, so
158 the rather cumbersome syntax shouldn't inconvenience too many people. We use
159 this: host:port(socket) where all the parts are optional. */
164 uschar *socket = NULL;
167 if ((p = Ustrchr(sdata[0], '(')) != NULL)
171 while (*p != 0 && *p != ')') p++;
175 if ((p = Ustrchr(sdata[0], ':')) != NULL)
181 if (Ustrchr(sdata[0], '/') != NULL)
183 *errmsg = string_sprintf("unexpected slash in MySQL server hostname: %s",
189 /* If the database is the empty string, set it NULL - the query must then
192 if (sdata[1][0] == 0) sdata[1] = NULL;
195 debug_printf("MYSQL new connection: host=%s port=%d socket=%s "
196 "database=%s user=%s\n", sdata[0], port, socket, sdata[1], sdata[2]);
198 /* Get store for a new handle, initialize it, and connect to the server */
200 mysql_handle = store_get(sizeof(MYSQL));
201 mysql_init(mysql_handle);
202 if (mysql_real_connect(mysql_handle,
203 /* host user passwd database */
204 CS sdata[0], CS sdata[2], CS sdata[3], CS sdata[1],
205 port, CS socket, CLIENT_MULTI_RESULTS) == NULL)
207 *errmsg = string_sprintf("MYSQL connection failed: %s",
208 mysql_error(mysql_handle));
209 *defer_break = FALSE;
213 /* Add the connection to the cache */
215 cn = store_get(sizeof(mysql_connection));
216 cn->server = server_copy;
217 cn->handle = mysql_handle;
218 cn->next = mysql_connections;
219 mysql_connections = cn;
222 /* Else use a previously cached connection */
227 debug_printf("MYSQL using cached connection for %s\n", server_copy);
232 if (mysql_query(mysql_handle, CS query) != 0)
234 *errmsg = string_sprintf("MYSQL: query failed: %s\n",
235 mysql_error(mysql_handle));
236 *defer_break = FALSE;
240 /* Pick up the result. If the query was not of the type that returns data,
241 namely INSERT, UPDATE, or DELETE, an error occurs here. However, this situation
242 can be detected by calling mysql_field_count(). If its result is zero, no data
243 was expected (this is all explained clearly in the MySQL manual). In this case,
244 we return the number of rows affected by the command. In this event, we do NOT
245 want to cache the result; also the whole cache for the handle must be cleaned
246 up. Setting do_cache FALSE requests this. */
248 if ((mysql_result = mysql_use_result(mysql_handle)) == NULL)
250 if ( mysql_field_count(mysql_handle) == 0 )
252 DEBUG(D_lookup) debug_printf("MYSQL: query was not one that returns data\n");
253 result = string_sprintf("%d", mysql_affected_rows(mysql_handle));
257 *errmsg = string_sprintf("MYSQL: lookup result failed: %s\n",
258 mysql_error(mysql_handle));
259 *defer_break = FALSE;
263 /* Find the number of fields returned. If this is one, we don't add field
264 names to the data. Otherwise we do. */
266 num_fields = mysql_num_fields(mysql_result);
268 /* Get the fields and construct the result string. If there is more than one
269 row, we insert '\n' between them. */
271 fields = mysql_fetch_fields(mysql_result);
273 while ((mysql_row_data = mysql_fetch_row(mysql_result)) != NULL)
275 unsigned long *lengths = mysql_fetch_lengths(mysql_result);
278 result = string_cat(result, &ssize, &offset, US"\n", 1);
282 if (mysql_row_data[0] != NULL) /* NULL value yields nothing */
283 result = string_cat(result, &ssize, &offset, US mysql_row_data[0],
287 else for (i = 0; i < num_fields; i++)
289 result = lf_quote(US fields[i].name, US mysql_row_data[i], lengths[i],
290 result, &ssize, &offset);
294 /* more results? -1 = no, >0 = error, 0 = yes (keep looping)
295 This is needed because of the CLIENT_MULTI_RESULTS on mysql_real_connect(),
296 we don't expect any more results. */
298 while((i = mysql_next_result(mysql_handle)) >= 0) {
299 if(i == 0) { /* Just ignore more results */
300 DEBUG(D_lookup) debug_printf("MYSQL: got unexpected more results\n");
304 *errmsg = string_sprintf("MYSQL: lookup result error when checking for more results: %s\n",
305 mysql_error(mysql_handle));
309 /* If result is NULL then no data has been found and so we return FAIL.
310 Otherwise, we must terminate the string which has been built; string_cat()
311 always leaves enough room for a terminating zero. */
316 *errmsg = US"MYSQL: no data found";
321 store_reset(result + offset + 1);
324 /* Get here by goto from various error checks and from the case where no data
325 was read (e.g. an update query). */
329 /* Free mysal store for any result that was got; don't close the connection, as
332 if (mysql_result != NULL) mysql_free_result(mysql_result);
334 /* Non-NULL result indicates a sucessful result */
343 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
344 return yield; /* FAIL or DEFER */
351 /*************************************************
353 *************************************************/
355 /* See local README for interface description. The handle and filename
356 arguments are not used. The code to loop through a list of servers while the
357 query is deferred with a retryable error is now in a separate function that is
358 shared with other SQL lookups. */
361 mysql_find(void *handle, uschar *filename, uschar *query, int length,
362 uschar **result, uschar **errmsg, BOOL *do_cache)
364 return lf_sqlperform(US"MySQL", US"mysql_servers", mysql_servers, query,
365 result, errmsg, do_cache, perform_mysql_search);
370 /*************************************************
371 * Quote entry point *
372 *************************************************/
374 /* The only characters that need to be quoted (with backslash) are newline,
375 tab, carriage return, backspace, backslash itself, and the quote characters.
376 Percent, and underscore and not escaped. They are only special in contexts
377 where they can be wild cards, and this isn't usually the case for data inserted
378 from messages, since that isn't likely to be treated as a pattern of any kind.
379 Sadly, MySQL doesn't seem to behave like other programs. If you use something
380 like "where id="ab\%cd" it does not treat the string as "ab%cd". So you really
381 can't quote "on spec".
384 s the string to be quoted
385 opt additional option text or NULL if none
387 Returns: the processed string or NULL for a bad option
391 mysql_quote(uschar *s, uschar *opt)
398 if (opt != NULL) return NULL; /* No options recognized */
400 while ((c = *t++) != 0)
401 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
403 if (count == 0) return s;
404 t = quoted = store_get(Ustrlen(s) + count + 1);
406 while ((c = *s++) != 0)
408 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL)
413 case '\n': *t++ = 'n';
415 case '\t': *t++ = 't';
417 case '\r': *t++ = 'r';
419 case '\b': *t++ = 'b';
433 #endif /* MYSQL_LOOKUP */
435 /* End of lookups/mysql.c */