Commit | Line | Data |
---|---|---|
034d99ab | 1 | /* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.7 2006/07/14 14:42:57 ph10 Exp $ */ |
0756eb3c PH |
2 | |
3 | /************************************************* | |
4 | * Exim - an Internet mail transport agent * | |
5 | *************************************************/ | |
6 | ||
d7d7b7b9 | 7 | /* Copyright (c) University of Cambridge 1995 - 2006 */ |
0756eb3c PH |
8 | /* See the file NOTICE for conditions of use and distribution. */ |
9 | ||
10 | /* Thanks to Petr Cech for contributing the original code for these | |
11 | functions. Thanks to Joachim Wieland for the initial patch for the Unix domain | |
12 | socket extension. */ | |
13 | ||
14 | #include "../exim.h" | |
15 | #include "lf_functions.h" | |
16 | #include "pgsql.h" /* The local header */ | |
17 | ||
18 | /* We can't just compile this code and allow the library mechanism to omit the | |
19 | functions if they are not wanted, because we need to have the PGSQL header | |
20 | available for compiling. Therefore, compile these functions only if | |
21 | LOOKUP_PGSQL is defined. However, some compilers don't like compiling empty | |
22 | modules, so keep them happy with a dummy when skipping the rest. Make it | |
23 | reference itself to stop picky compilers complaining that it is unused, and put | |
24 | in a dummy argument to stop even pickier compilers complaining about infinite | |
25 | loops. */ | |
26 | ||
27 | #ifndef LOOKUP_PGSQL | |
28 | static void dummy(int x) { dummy(x-1); } | |
29 | #else | |
30 | ||
31 | ||
32 | #include <libpq-fe.h> /* The system header */ | |
33 | ||
34 | /* Structure and anchor for caching connections. */ | |
35 | ||
36 | typedef struct pgsql_connection { | |
37 | struct pgsql_connection *next; | |
38 | uschar *server; | |
39 | PGconn *handle; | |
40 | } pgsql_connection; | |
41 | ||
42 | static pgsql_connection *pgsql_connections = NULL; | |
43 | ||
44 | ||
45 | ||
46 | /************************************************* | |
47 | * Open entry point * | |
48 | *************************************************/ | |
49 | ||
50 | /* See local README for interface description. */ | |
51 | ||
52 | void * | |
53 | pgsql_open(uschar *filename, uschar **errmsg) | |
54 | { | |
55 | return (void *)(1); /* Just return something non-null */ | |
56 | } | |
57 | ||
58 | ||
59 | ||
60 | /************************************************* | |
61 | * Tidy entry point * | |
62 | *************************************************/ | |
63 | ||
64 | /* See local README for interface description. */ | |
65 | ||
66 | void | |
67 | pgsql_tidy(void) | |
68 | { | |
69 | pgsql_connection *cn; | |
70 | while ((cn = pgsql_connections) != NULL) | |
71 | { | |
72 | pgsql_connections = cn->next; | |
73 | DEBUG(D_lookup) debug_printf("close PGSQL connection: %s\n", cn->server); | |
74 | PQfinish(cn->handle); | |
75 | } | |
76 | } | |
77 | ||
78 | ||
034d99ab PH |
79 | /************************************************* |
80 | * Notice processor function for pgsql * | |
81 | *************************************************/ | |
82 | ||
83 | /* This function is passed to pgsql below, and called for any PostgreSQL | |
84 | "notices". By default they are written to stderr, which is undesirable. | |
85 | ||
86 | Arguments: | |
87 | arg an opaque user cookie (not used) | |
88 | message the notice | |
89 | ||
90 | Returns: nothing | |
91 | */ | |
92 | ||
93 | static void | |
94 | notice_processor(void *arg, const char *message) | |
95 | { | |
96 | arg = arg; /* Keep compiler happy */ | |
97 | DEBUG(D_lookup) debug_printf("PGSQL: %s\n", message); | |
98 | } | |
99 | ||
100 | ||
0756eb3c PH |
101 | |
102 | /************************************************* | |
103 | * Internal search function * | |
104 | *************************************************/ | |
105 | ||
106 | /* This function is called from the find entry point to do the search for a | |
107 | single server. The server string is of the form "server/dbname/user/password". | |
108 | ||
109 | PostgreSQL supports connections through Unix domain sockets. This is usually | |
110 | faster and costs less cpu time than a TCP/IP connection. However it can only be | |
111 | used if the mail server runs on the same machine as the database server. A | |
112 | configuration line for PostgreSQL via Unix domain sockets looks like this: | |
113 | ||
114 | hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>] | |
115 | ||
116 | We enclose the path name in parentheses so that its slashes aren't visually | |
117 | confused with the delimeters for the other pgsql_server settings. | |
118 | ||
119 | For TCP/IP connections, the server is a host name and optional port (with a | |
120 | colon separator). | |
121 | ||
122 | NOTE: | |
123 | 1) All three '/' must be present. | |
124 | 2) If host is omitted the local unix socket is used. | |
125 | ||
126 | Arguments: | |
127 | query the query string | |
128 | server the server string; this is in dynamic memory and can be updated | |
129 | resultptr where to store the result | |
130 | errmsg where to point an error message | |
131 | defer_break TRUE if no more servers are to be tried after DEFER | |
132 | do_cache set FALSE if data is changed | |
133 | ||
134 | Returns: OK, FAIL, or DEFER | |
135 | */ | |
136 | ||
137 | static int | |
138 | perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr, | |
139 | uschar **errmsg, BOOL *defer_break, BOOL *do_cache) | |
140 | { | |
141 | PGconn *pg_conn = NULL; | |
142 | PGresult *pg_result = NULL; | |
143 | ||
144 | int i; | |
145 | int ssize = 0; | |
146 | int offset = 0; | |
147 | int yield = DEFER; | |
148 | unsigned int num_fields, num_tuples; | |
149 | uschar *result = NULL; | |
150 | pgsql_connection *cn; | |
151 | uschar *server_copy = NULL; | |
152 | uschar *sdata[3]; | |
153 | ||
154 | /* Disaggregate the parameters from the server argument. The order is host or | |
155 | path, database, user, password. We can write to the string, since it is in a | |
156 | nextinlist temporary buffer. The copy of the string that is used for caching | |
157 | has the password removed. This copy is also used for debugging output. */ | |
158 | ||
159 | for (i = 2; i >= 0; i--) | |
160 | { | |
161 | uschar *pp = Ustrrchr(server, '/'); | |
162 | if (pp == NULL) | |
163 | { | |
164 | *errmsg = string_sprintf("incomplete pgSQL server data: %s", | |
165 | (i == 2)? server : server_copy); | |
166 | *defer_break = TRUE; | |
167 | return DEFER; | |
168 | } | |
169 | *pp++ = 0; | |
170 | sdata[i] = pp; | |
171 | if (i == 2) server_copy = string_copy(server); /* sans password */ | |
172 | } | |
173 | ||
174 | /* The total server string has now been truncated so that what is left at the | |
175 | start is the identification of the server (host or path). See if we have a | |
176 | cached connection to the server. */ | |
177 | ||
178 | for (cn = pgsql_connections; cn != NULL; cn = cn->next) | |
179 | { | |
180 | if (Ustrcmp(cn->server, server_copy) == 0) | |
181 | { | |
182 | pg_conn = cn->handle; | |
183 | break; | |
184 | } | |
185 | } | |
186 | ||
187 | /* If there is no cached connection, we must set one up. */ | |
188 | ||
189 | if (cn == NULL) | |
190 | { | |
191 | uschar *port = US""; | |
192 | ||
193 | /* For a Unix domain socket connection, the path is in parentheses */ | |
194 | ||
195 | if (*server == '(') | |
196 | { | |
197 | uschar *last_slash, *last_dot, *p; | |
198 | ||
199 | p = ++server; | |
200 | while (*p != 0 && *p != ')') p++; | |
201 | *p = 0; | |
202 | ||
203 | last_slash = Ustrrchr(server, '/'); | |
204 | last_dot = Ustrrchr(server, '.'); | |
205 | ||
206 | DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s " | |
207 | "database=%s user=%s\n", server, sdata[0], sdata[1]); | |
208 | ||
209 | /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432 | |
210 | We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname | |
211 | argument and put '5432' into the port variable. */ | |
212 | ||
213 | if (last_slash == NULL || last_dot == NULL) | |
214 | { | |
215 | *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", | |
216 | server); | |
217 | *defer_break = TRUE; | |
218 | return DEFER; | |
219 | } | |
220 | ||
221 | /* Terminate the path name and set up the port: we'll have something like | |
222 | server = "/var/run/postgresql" and port = "5432". */ | |
223 | ||
224 | *last_slash = 0; | |
225 | port = last_dot + 1; | |
226 | } | |
227 | ||
228 | /* Host connection; sort out the port */ | |
229 | ||
230 | else | |
231 | { | |
232 | uschar *p; | |
233 | if ((p = Ustrchr(server, ':')) != NULL) | |
234 | { | |
235 | *p++ = 0; | |
236 | port = p; | |
237 | } | |
238 | ||
239 | if (Ustrchr(server, '/') != NULL) | |
240 | { | |
241 | *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s", | |
242 | server); | |
243 | *defer_break = TRUE; | |
244 | return DEFER; | |
245 | } | |
246 | ||
247 | DEBUG(D_lookup) debug_printf("PGSQL new connection: host=%s port=%s " | |
248 | "database=%s user=%s\n", server, port, sdata[0], sdata[1]); | |
249 | } | |
250 | ||
251 | /* If the database is the empty string, set it NULL - the query must then | |
252 | define it. */ | |
253 | ||
254 | if (sdata[0][0] == 0) sdata[0] = NULL; | |
255 | ||
256 | /* Get store for a new handle, initialize it, and connect to the server */ | |
257 | ||
258 | pg_conn=PQsetdbLogin( | |
259 | /* host port options tty database user passwd */ | |
260 | CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]); | |
261 | ||
262 | if(PQstatus(pg_conn) == CONNECTION_BAD) | |
263 | { | |
264 | store_reset(server_copy); | |
265 | *errmsg = string_sprintf("PGSQL connection failed: %s", | |
266 | PQerrorMessage(pg_conn)); | |
267 | PQfinish(pg_conn); | |
268 | *defer_break = FALSE; | |
269 | goto PGSQL_EXIT; | |
270 | } | |
271 | ||
16282d2b PH |
272 | /* Set the client encoding to SQL_ASCII, which means that the server will |
273 | not try to interpret the query as being in any fancy encoding such as UTF-8 | |
274 | or other multibyte code that might cause problems with escaping. */ | |
275 | ||
276 | PQsetClientEncoding(pg_conn, "SQL_ASCII"); | |
277 | ||
034d99ab PH |
278 | /* Set the notice processor to prevent notices from being written to stderr |
279 | (which is what the default does). Our function (above) just produces debug | |
280 | output. */ | |
281 | ||
282 | PQsetNoticeProcessor(pg_conn, notice_processor, NULL); | |
283 | ||
0756eb3c PH |
284 | /* Add the connection to the cache */ |
285 | ||
286 | cn = store_get(sizeof(pgsql_connection)); | |
287 | cn->server = server_copy; | |
288 | cn->handle = pg_conn; | |
289 | cn->next = pgsql_connections; | |
290 | pgsql_connections = cn; | |
291 | } | |
292 | ||
293 | /* Else use a previously cached connection */ | |
294 | ||
295 | else | |
296 | { | |
297 | DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n", | |
298 | server_copy); | |
299 | } | |
300 | ||
301 | /* Run the query */ | |
302 | ||
303 | pg_result = PQexec(pg_conn, CS query); | |
304 | switch(PQresultStatus(pg_result)) | |
305 | { | |
306 | case PGRES_EMPTY_QUERY: | |
307 | case PGRES_COMMAND_OK: | |
308 | /* The command was successful but did not return any data since it was | |
309 | * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the | |
310 | * high level code to not cache this query, and clean the current cache for | |
311 | * this handle by setting *do_cache FALSE. */ | |
312 | result = string_copy(US PQcmdTuples(pg_result)); | |
313 | offset = Ustrlen(result); | |
314 | *do_cache = FALSE; | |
315 | DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data " | |
316 | "but was successful. Rows affected: %s\n", result); | |
317 | ||
318 | case PGRES_TUPLES_OK: | |
319 | break; | |
320 | ||
321 | default: | |
e08c430f | 322 | /* This was the original code: |
0756eb3c PH |
323 | *errmsg = string_sprintf("PGSQL: query failed: %s\n", |
324 | PQresultErrorMessage(pg_result)); | |
e08c430f PH |
325 | This was suggested by a user: |
326 | */ | |
327 | ||
328 | *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n", | |
329 | PQresultErrorMessage(pg_result), | |
330 | PQresStatus(PQresultStatus(pg_result)), query); | |
0756eb3c PH |
331 | *defer_break = FALSE; |
332 | goto PGSQL_EXIT; | |
333 | } | |
334 | ||
335 | /* Result is in pg_result. Find the number of fields returned. If this is one, | |
336 | we don't add field names to the data. Otherwise we do. If the query did not | |
337 | return anything we skip the for loop; this also applies to the case | |
338 | PGRES_COMMAND_OK. */ | |
339 | ||
340 | num_fields = PQnfields(pg_result); | |
341 | num_tuples = PQntuples(pg_result); | |
342 | ||
343 | /* Get the fields and construct the result string. If there is more than one | |
344 | row, we insert '\n' between them. */ | |
345 | ||
346 | for (i = 0; i < num_tuples; i++) | |
347 | { | |
348 | if (result != NULL) | |
349 | result = string_cat(result, &ssize, &offset, US"\n", 1); | |
350 | ||
351 | if (num_fields == 1) | |
352 | { | |
353 | result = string_cat(result, &ssize, &offset, | |
354 | US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0)); | |
355 | } | |
356 | ||
357 | else | |
358 | { | |
359 | int j; | |
360 | for (j = 0; j < num_fields; j++) | |
361 | { | |
362 | uschar *tmp = US PQgetvalue(pg_result, i, j); | |
363 | result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result, | |
364 | &ssize, &offset); | |
365 | } | |
366 | } | |
367 | } | |
368 | ||
369 | /* If result is NULL then no data has been found and so we return FAIL. | |
370 | Otherwise, we must terminate the string which has been built; string_cat() | |
371 | always leaves enough room for a terminating zero. */ | |
372 | ||
373 | if (result == NULL) | |
374 | { | |
375 | yield = FAIL; | |
376 | *errmsg = US"PGSQL: no data found"; | |
377 | } | |
378 | else | |
379 | { | |
380 | result[offset] = 0; | |
381 | store_reset(result + offset + 1); | |
382 | } | |
383 | ||
384 | /* Get here by goto from various error checks. */ | |
385 | ||
386 | PGSQL_EXIT: | |
387 | ||
388 | /* Free store for any result that was got; don't close the connection, as | |
389 | it is cached. */ | |
390 | ||
391 | if (pg_result != NULL) PQclear(pg_result); | |
392 | ||
393 | /* Non-NULL result indicates a sucessful result */ | |
394 | ||
395 | if (result != NULL) | |
396 | { | |
397 | *resultptr = result; | |
398 | return OK; | |
399 | } | |
400 | else | |
401 | { | |
402 | DEBUG(D_lookup) debug_printf("%s\n", *errmsg); | |
403 | return yield; /* FAIL or DEFER */ | |
404 | } | |
405 | } | |
406 | ||
407 | ||
408 | ||
409 | ||
410 | /************************************************* | |
411 | * Find entry point * | |
412 | *************************************************/ | |
413 | ||
414 | /* See local README for interface description. The handle and filename | |
415 | arguments are not used. Loop through a list of servers while the query is | |
416 | deferred with a retryable error. */ | |
417 | ||
418 | int | |
419 | pgsql_find(void *handle, uschar *filename, uschar *query, int length, | |
420 | uschar **result, uschar **errmsg, BOOL *do_cache) | |
421 | { | |
422 | int sep = 0; | |
423 | uschar *server; | |
424 | uschar *list = pgsql_servers; | |
425 | uschar buffer[512]; | |
426 | ||
427 | DEBUG(D_lookup) debug_printf("PGSQL query: %s\n", query); | |
428 | ||
429 | while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer))) | |
430 | != NULL) | |
431 | { | |
432 | BOOL defer_break; | |
433 | int rc = perform_pgsql_search(query, server, result, errmsg, &defer_break, | |
434 | do_cache); | |
435 | if (rc != DEFER || defer_break) return rc; | |
436 | } | |
437 | ||
438 | if (pgsql_servers == NULL) | |
439 | *errmsg = US"no PGSQL servers defined (pgsql_servers option)"; | |
440 | ||
441 | return DEFER; | |
442 | } | |
443 | ||
444 | ||
445 | ||
446 | /************************************************* | |
447 | * Quote entry point * | |
448 | *************************************************/ | |
449 | ||
450 | /* The characters that always need to be quoted (with backslash) are newline, | |
451 | tab, carriage return, backspace, backslash itself, and the quote characters. | |
452 | Percent and underscore are only special in contexts where they can be wild | |
453 | cards, and this isn't usually the case for data inserted from messages, since | |
454 | that isn't likely to be treated as a pattern of any kind. However, pgsql seems | |
455 | to allow escaping "on spec". If you use something like "where id="ab\%cd" it | |
456 | does treat the string as "ab%cd". So we can safely quote percent and | |
457 | underscore. [This is different to MySQL, where you can't do this.] | |
458 | ||
5547e2c5 PH |
459 | The original code quoted single quotes as \' which is documented as valid in |
460 | the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to | |
461 | the SQL standard '' way of representing a single quote as data. However, in | |
462 | June 2006 there was some security issue with using \' and so this has been | |
463 | changed. | |
464 | ||
465 | [Note: There is a function called PQescapeStringConn() that quotes strings. | |
466 | This cannot be used because it needs a PGconn argument (the connection handle). | |
467 | Why, I don't know. Seems odd for just string escaping...] | |
468 | ||
0756eb3c PH |
469 | Arguments: |
470 | s the string to be quoted | |
471 | opt additional option text or NULL if none | |
472 | ||
473 | Returns: the processed string or NULL for a bad option | |
474 | */ | |
475 | ||
476 | uschar * | |
477 | pgsql_quote(uschar *s, uschar *opt) | |
478 | { | |
479 | register int c; | |
480 | int count = 0; | |
481 | uschar *t = s; | |
482 | uschar *quoted; | |
483 | ||
484 | if (opt != NULL) return NULL; /* No options recognized */ | |
485 | ||
486 | while ((c = *t++) != 0) | |
487 | if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++; | |
488 | ||
489 | if (count == 0) return s; | |
490 | t = quoted = store_get(Ustrlen(s) + count + 1); | |
491 | ||
492 | while ((c = *s++) != 0) | |
493 | { | |
5547e2c5 PH |
494 | if (c == '\'') |
495 | { | |
496 | *t++ = '\''; | |
497 | *t++ = '\''; | |
498 | } | |
499 | else if (Ustrchr("\n\t\r\b\"\\%_", c) != NULL) | |
0756eb3c PH |
500 | { |
501 | *t++ = '\\'; | |
502 | switch(c) | |
503 | { | |
504 | case '\n': *t++ = 'n'; | |
505 | break; | |
506 | case '\t': *t++ = 't'; | |
507 | break; | |
508 | case '\r': *t++ = 'r'; | |
509 | break; | |
510 | case '\b': *t++ = 'b'; | |
511 | break; | |
512 | default: *t++ = c; | |
513 | break; | |
514 | } | |
515 | } | |
516 | else *t++ = c; | |
517 | } | |
518 | ||
519 | *t = 0; | |
520 | return quoted; | |
521 | } | |
522 | ||
523 | #endif /* PGSQL_LOOKUP */ | |
524 | ||
525 | /* End of lookups/pgsql.c */ |