1 /* $NetBSD: dict_mysql.c,v 1.3 2020/03/18 19:05:16 christos Exp $ */ 2 3 /*++ 4 /* NAME 5 /* dict_mysql 3 6 /* SUMMARY 7 /* dictionary manager interface to MySQL databases 8 /* SYNOPSIS 9 /* #include <dict_mysql.h> 10 /* 11 /* DICT *dict_mysql_open(name, open_flags, dict_flags) 12 /* const char *name; 13 /* int open_flags; 14 /* int dict_flags; 15 /* DESCRIPTION 16 /* dict_mysql_open() creates a dictionary of type 'mysql'. This 17 /* dictionary is an interface for the postfix key->value mappings 18 /* to mysql. The result is a pointer to the installed dictionary, 19 /* or a null pointer in case of problems. 20 /* 21 /* The mysql dictionary can manage multiple connections to different 22 /* sql servers on different hosts. It assumes that the underlying data 23 /* on each host is identical (mirrored) and maintains one connection 24 /* at any given time. If any connection fails, any other available 25 /* ones will be opened and used. The intent of this feature is to eliminate 26 /* a single point of failure for mail systems that would otherwise rely 27 /* on a single mysql server. 28 /* .PP 29 /* Arguments: 30 /* .IP name 31 /* Either the path to the MySQL configuration file (if it starts 32 /* with '/' or '.'), or the prefix which will be used to obtain 33 /* main.cf configuration parameters for this search. 34 /* 35 /* In the first case, the configuration parameters below are 36 /* specified in the file as \fIname\fR=\fIvalue\fR pairs. 37 /* 38 /* In the second case, the configuration parameters are 39 /* prefixed with the value of \fIname\fR and an underscore, 40 /* and they are specified in main.cf. For example, if this 41 /* value is \fImysqlsource\fR, the parameters would look like 42 /* \fImysqlsource_user\fR, \fImysqlsource_table\fR, and so on. 43 /* 44 /* .IP other_name 45 /* reference for outside use. 46 /* .IP open_flags 47 /* Must be O_RDONLY. 48 /* .IP dict_flags 49 /* See dict_open(3). 50 /* .PP 51 /* Configuration parameters: 52 /* .IP user 53 /* Username for connecting to the database. 54 /* .IP password 55 /* Password for the above. 56 /* .IP dbname 57 /* Name of the database. 58 /* .IP domain 59 /* List of domains the queries should be restricted to. If 60 /* specified, only FQDN addresses whose domain parts matching this 61 /* list will be queried against the SQL database. Lookups for 62 /* partial addresses are also suppressed. This can significantly 63 /* reduce the query load on the server. 64 /* .IP query 65 /* Query template, before the query is actually issued, variable 66 /* substitutions are performed. See mysql_table(5) for details. If 67 /* No query is specified, the legacy variables \fItable\fR, 68 /* \fIselect_field\fR, \fIwhere_field\fR and \fIadditional_conditions\fR 69 /* are used to construct the query template. 70 /* .IP result_format 71 /* The format used to expand results from queries. Substitutions 72 /* are performed as described in mysql_table(5). Defaults to returning 73 /* the lookup result unchanged. 74 /* .IP expansion_limit 75 /* Limit (if any) on the total number of lookup result values. Lookups which 76 /* exceed the limit fail with dict->error=DICT_ERR_RETRY. Note that each 77 /* non-empty (and non-NULL) column of a multi-column result row counts as 78 /* one result. 79 /* .IP table 80 /* When \fIquery\fR is not set, name of the table used to construct the 81 /* query string. This provides compatibility with older releases. 82 /* .IP select_field 83 /* When \fIquery\fR is not set, name of the result field used to 84 /* construct the query string. This provides compatibility with older 85 /* releases. 86 /* .IP where_field 87 /* When \fIquery\fR is not set, name of the where clause field used to 88 /* construct the query string. This provides compatibility with older 89 /* releases. 90 /* .IP additional_conditions 91 /* When \fIquery\fR is not set, additional where clause conditions used 92 /* to construct the query string. This provides compatibility with older 93 /* releases. 94 /* .IP hosts 95 /* List of hosts to connect to. 96 /* .IP option_file 97 /* Read options from the given file instead of the default my.cnf 98 /* location. 99 /* .IP option_group 100 /* Read options from the given group. 101 /* .IP require_result_set 102 /* Require that every query produces a result set. 103 /* .IP tls_cert_file 104 /* File containing client's X509 certificate. 105 /* .IP tls_key_file 106 /* File containing the private key corresponding to \fItls_cert_file\fR. 107 /* .IP tls_CAfile 108 /* File containing certificates for all of the X509 Certification 109 /* Authorities the client will recognize. Takes precedence over 110 /* \fItls_CApath\fR. 111 /* .IP tls_CApath 112 /* Directory containing X509 Certification Authority certificates 113 /* in separate individual files. 114 /* .IP tls_verify_cert 115 /* Verify that the server's name matches the common name of the 116 /* certificate. 117 /* .PP 118 /* For example, if you want the map to reference databases of 119 /* the name "your_db" and execute a query like this: select 120 /* forw_addr from aliases where alias like '<some username>' 121 /* against any database called "vmailer_info" located on hosts 122 /* host1.some.domain and host2.some.domain, logging in as user 123 /* "vmailer" and password "passwd" then the configuration file 124 /* should read: 125 /* .PP 126 /* user = vmailer 127 /* .br 128 /* password = passwd 129 /* .br 130 /* dbname = vmailer_info 131 /* .br 132 /* table = aliases 133 /* .br 134 /* select_field = forw_addr 135 /* .br 136 /* where_field = alias 137 /* .br 138 /* hosts = host1.some.domain host2.some.domain 139 /* .PP 140 /* SEE ALSO 141 /* dict(3) generic dictionary manager 142 /* AUTHOR(S) 143 /* Scott Cotton, Joshua Marcus 144 /* IC Group, Inc. 145 /* scott@icgroup.com 146 /* 147 /* Liviu Daia 148 /* Institute of Mathematics of the Romanian Academy 149 /* P.O. BOX 1-764 150 /* RO-014700 Bucharest, ROMANIA 151 /* 152 /* John Fawcett 153 /* 154 /* Wietse Venema 155 /* Google, Inc. 156 /* 111 8th Avenue 157 /* New York, NY 10011, USA 158 /*--*/ 159 160 /* System library. */ 161 #include "sys_defs.h" 162 163 #ifdef HAS_MYSQL 164 #include <sys/socket.h> 165 #include <netinet/in.h> 166 #include <arpa/inet.h> 167 #include <netdb.h> 168 #include <stdio.h> 169 #include <string.h> 170 #include <stdlib.h> 171 #include <syslog.h> 172 #include <time.h> 173 #include <mysql.h> 174 #include <limits.h> 175 #include <errno.h> 176 177 #ifdef STRCASECMP_IN_STRINGS_H 178 #include <strings.h> 179 #endif 180 181 /* Utility library. */ 182 183 #include "dict.h" 184 #include "msg.h" 185 #include "mymalloc.h" 186 #include "argv.h" 187 #include "vstring.h" 188 #include "split_at.h" 189 #include "find_inet.h" 190 #include "myrand.h" 191 #include "events.h" 192 #include "stringops.h" 193 194 /* Global library. */ 195 196 #include "cfg_parser.h" 197 #include "db_common.h" 198 199 /* Application-specific. */ 200 201 #include "dict_mysql.h" 202 203 /* MySQL 8.x API change */ 204 205 #if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 50023 206 #define DICT_MYSQL_SSL_VERIFY_SERVER_CERT MYSQL_OPT_SSL_VERIFY_SERVER_CERT 207 #elif MYSQL_VERSION_ID >= 80000 208 #define DICT_MYSQL_SSL_VERIFY_SERVER_CERT MYSQL_OPT_SSL_MODE 209 #endif 210 211 /* need some structs to help organize things */ 212 typedef struct { 213 MYSQL *db; 214 char *hostname; 215 char *name; 216 unsigned port; 217 unsigned type; /* TYPEUNIX | TYPEINET */ 218 unsigned stat; /* STATUNTRIED | STATFAIL | STATCUR */ 219 time_t ts; /* used for attempting reconnection 220 * every so often if a host is down */ 221 } HOST; 222 223 typedef struct { 224 int len_hosts; /* number of hosts */ 225 HOST **db_hosts; /* the hosts on which the databases 226 * reside */ 227 } PLMYSQL; 228 229 typedef struct { 230 DICT dict; 231 CFG_PARSER *parser; 232 char *query; 233 char *result_format; 234 char *option_file; 235 char *option_group; 236 void *ctx; 237 int expansion_limit; 238 char *username; 239 char *password; 240 char *dbname; 241 ARGV *hosts; 242 PLMYSQL *pldb; 243 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 244 HOST *active_host; 245 char *tls_cert_file; 246 char *tls_key_file; 247 char *tls_CAfile; 248 char *tls_CApath; 249 char *tls_ciphers; 250 #if defined(DICT_MYSQL_SSL_VERIFY_SERVER_CERT) 251 int tls_verify_cert; 252 #endif 253 #endif 254 int require_result_set; 255 } DICT_MYSQL; 256 257 #define STATACTIVE (1<<0) 258 #define STATFAIL (1<<1) 259 #define STATUNTRIED (1<<2) 260 261 #define TYPEUNIX (1<<0) 262 #define TYPEINET (1<<1) 263 264 #define RETRY_CONN_MAX 100 265 #define RETRY_CONN_INTV 60 /* 1 minute */ 266 #define IDLE_CONN_INTV 60 /* 1 minute */ 267 268 /* internal function declarations */ 269 static PLMYSQL *plmysql_init(ARGV *); 270 static int plmysql_query(DICT_MYSQL *, const char *, VSTRING *, MYSQL_RES **); 271 static void plmysql_dealloc(PLMYSQL *); 272 static void plmysql_close_host(HOST *); 273 static void plmysql_down_host(HOST *); 274 static void plmysql_connect_single(DICT_MYSQL *, HOST *); 275 static const char *dict_mysql_lookup(DICT *, const char *); 276 DICT *dict_mysql_open(const char *, int, int); 277 static void dict_mysql_close(DICT *); 278 static void mysql_parse_config(DICT_MYSQL *, const char *); 279 static HOST *host_init(const char *); 280 281 /* dict_mysql_quote - escape SQL metacharacters in input string */ 282 283 static void dict_mysql_quote(DICT *dict, const char *name, VSTRING *result) 284 { 285 DICT_MYSQL *dict_mysql = (DICT_MYSQL *) dict; 286 int len = strlen(name); 287 int buflen; 288 289 /* 290 * We won't get integer overflows in 2*len + 1, because Postfix input 291 * keys have reasonable size limits, better safe than sorry. 292 */ 293 if (len > (INT_MAX - VSTRING_LEN(result) - 1) / 2) 294 msg_panic("dict_mysql_quote: integer overflow in %lu+2*%d+1", 295 (unsigned long) VSTRING_LEN(result), len); 296 buflen = 2 * len + 1; 297 VSTRING_SPACE(result, buflen); 298 299 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 300 if (dict_mysql->active_host) 301 mysql_real_escape_string(dict_mysql->active_host->db, 302 vstring_end(result), name, len); 303 else 304 #endif 305 mysql_escape_string(vstring_end(result), name, len); 306 307 VSTRING_SKIP(result); 308 } 309 310 /* dict_mysql_lookup - find database entry */ 311 312 static const char *dict_mysql_lookup(DICT *dict, const char *name) 313 { 314 const char *myname = "dict_mysql_lookup"; 315 DICT_MYSQL *dict_mysql = (DICT_MYSQL *) dict; 316 MYSQL_RES *query_res; 317 MYSQL_ROW row; 318 static VSTRING *result; 319 static VSTRING *query; 320 int i; 321 int j; 322 int numrows; 323 int expansion; 324 const char *r; 325 db_quote_callback_t quote_func = dict_mysql_quote; 326 int domain_rc; 327 328 dict->error = 0; 329 330 /* 331 * Don't frustrate future attempts to make Postfix UTF-8 transparent. 332 */ 333 #ifdef SNAPSHOT 334 if ((dict->flags & DICT_FLAG_UTF8_ACTIVE) == 0 335 && !valid_utf8_string(name, strlen(name))) { 336 if (msg_verbose) 337 msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'", 338 myname, dict_mysql->parser->name, name); 339 return (0); 340 } 341 #endif 342 343 /* 344 * Optionally fold the key. 345 */ 346 if (dict->flags & DICT_FLAG_FOLD_FIX) { 347 if (dict->fold_buf == 0) 348 dict->fold_buf = vstring_alloc(10); 349 vstring_strcpy(dict->fold_buf, name); 350 name = lowercase(vstring_str(dict->fold_buf)); 351 } 352 353 /* 354 * If there is a domain list for this map, then only search for addresses 355 * in domains on the list. This can significantly reduce the load on the 356 * server. 357 */ 358 if ((domain_rc = db_common_check_domain(dict_mysql->ctx, name)) == 0) { 359 if (msg_verbose) 360 msg_info("%s: Skipping lookup of '%s'", myname, name); 361 return (0); 362 } 363 if (domain_rc < 0) { 364 msg_warn("%s:%s 'domain' pattern match failed for '%s'", 365 dict->type, dict->name, name); 366 DICT_ERR_VAL_RETURN(dict, domain_rc, (char *) 0); 367 } 368 #define INIT_VSTR(buf, len) do { \ 369 if (buf == 0) \ 370 buf = vstring_alloc(len); \ 371 VSTRING_RESET(buf); \ 372 VSTRING_TERMINATE(buf); \ 373 } while (0) 374 375 INIT_VSTR(query, 10); 376 377 /* 378 * Suppress the lookup if the query expansion is empty 379 * 380 * This initial expansion is outside the context of any specific host 381 * connection, we just want to check the key pre-requisites, so when 382 * quoting happens separately for each connection, we don't bother with 383 * quoting... 384 */ 385 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 386 quote_func = 0; 387 #endif 388 if (!db_common_expand(dict_mysql->ctx, dict_mysql->query, 389 name, 0, query, quote_func)) 390 return (0); 391 392 /* do the query - set dict->error & cleanup if there's an error */ 393 if (plmysql_query(dict_mysql, name, query, &query_res) == 0) { 394 dict->error = DICT_ERR_RETRY; 395 return (0); 396 } 397 if (query_res == 0) 398 return (0); 399 numrows = mysql_num_rows(query_res); 400 if (msg_verbose) 401 msg_info("%s: retrieved %d rows", myname, numrows); 402 if (numrows == 0) { 403 mysql_free_result(query_res); 404 return 0; 405 } 406 INIT_VSTR(result, 10); 407 408 for (expansion = i = 0; i < numrows && dict->error == 0; i++) { 409 row = mysql_fetch_row(query_res); 410 for (j = 0; j < mysql_num_fields(query_res); j++) { 411 if (db_common_expand(dict_mysql->ctx, dict_mysql->result_format, 412 row[j], name, result, 0) 413 && dict_mysql->expansion_limit > 0 414 && ++expansion > dict_mysql->expansion_limit) { 415 msg_warn("%s: %s: Expansion limit exceeded for key: '%s'", 416 myname, dict_mysql->parser->name, name); 417 dict->error = DICT_ERR_RETRY; 418 break; 419 } 420 } 421 } 422 mysql_free_result(query_res); 423 r = vstring_str(result); 424 return ((dict->error == 0 && *r) ? r : 0); 425 } 426 427 /* dict_mysql_check_stat - check the status of a host */ 428 429 static int dict_mysql_check_stat(HOST *host, unsigned stat, unsigned type, 430 time_t t) 431 { 432 if ((host->stat & stat) && (!type || host->type & type)) { 433 /* try not to hammer the dead hosts too often */ 434 if (host->stat == STATFAIL && host->ts > 0 && host->ts >= t) 435 return 0; 436 return 1; 437 } 438 return 0; 439 } 440 441 /* dict_mysql_find_host - find a host with the given status */ 442 443 static HOST *dict_mysql_find_host(PLMYSQL *PLDB, unsigned stat, unsigned type) 444 { 445 time_t t; 446 int count = 0; 447 int idx; 448 int i; 449 450 t = time((time_t *) 0); 451 for (i = 0; i < PLDB->len_hosts; i++) { 452 if (dict_mysql_check_stat(PLDB->db_hosts[i], stat, type, t)) 453 count++; 454 } 455 456 if (count) { 457 idx = (count > 1) ? 458 1 + count * (double) myrand() / (1.0 + RAND_MAX) : 1; 459 460 for (i = 0; i < PLDB->len_hosts; i++) { 461 if (dict_mysql_check_stat(PLDB->db_hosts[i], stat, type, t) && 462 --idx == 0) 463 return PLDB->db_hosts[i]; 464 } 465 } 466 return 0; 467 } 468 469 /* dict_mysql_get_active - get an active connection */ 470 471 static HOST *dict_mysql_get_active(DICT_MYSQL *dict_mysql) 472 { 473 const char *myname = "dict_mysql_get_active"; 474 PLMYSQL *PLDB = dict_mysql->pldb; 475 HOST *host; 476 int count = RETRY_CONN_MAX; 477 478 /* Try the active connections first; prefer the ones to UNIX sockets. */ 479 if ((host = dict_mysql_find_host(PLDB, STATACTIVE, TYPEUNIX)) != NULL || 480 (host = dict_mysql_find_host(PLDB, STATACTIVE, TYPEINET)) != NULL) { 481 if (msg_verbose) 482 msg_info("%s: found active connection to host %s", myname, 483 host->hostname); 484 return host; 485 } 486 487 /* 488 * Try the remaining hosts. "count" is a safety net, in case the loop 489 * takes more than RETRY_CONN_INTV and the dead hosts are no longer 490 * skipped. 491 */ 492 while (--count > 0 && 493 ((host = dict_mysql_find_host(PLDB, STATUNTRIED | STATFAIL, 494 TYPEUNIX)) != NULL || 495 (host = dict_mysql_find_host(PLDB, STATUNTRIED | STATFAIL, 496 TYPEINET)) != NULL)) { 497 if (msg_verbose) 498 msg_info("%s: attempting to connect to host %s", myname, 499 host->hostname); 500 plmysql_connect_single(dict_mysql, host); 501 if (host->stat == STATACTIVE) 502 return host; 503 } 504 505 /* bad news... */ 506 return 0; 507 } 508 509 /* dict_mysql_event - callback: close idle connections */ 510 511 static void dict_mysql_event(int unused_event, void *context) 512 { 513 HOST *host = (HOST *) context; 514 515 if (host->db) 516 plmysql_close_host(host); 517 } 518 519 /* 520 * plmysql_query - process a MySQL query. Return 'true' on success. 521 * On failure, log failure and try other db instances. 522 * on failure of all db instances, return 'false'; 523 * close unnecessary active connections 524 */ 525 526 static int plmysql_query(DICT_MYSQL *dict_mysql, 527 const char *name, 528 VSTRING *query, 529 MYSQL_RES **result) 530 { 531 HOST *host; 532 MYSQL_RES *first_result = 0; 533 int query_error; 534 535 /* 536 * Helper to avoid spamming the log with warnings. 537 */ 538 #define SET_ERROR_AND_WARN_ONCE(err, ...) \ 539 do { \ 540 if (err == 0) { \ 541 err = 1; \ 542 msg_warn(__VA_ARGS__); \ 543 } \ 544 } while (0) 545 546 while ((host = dict_mysql_get_active(dict_mysql)) != NULL) { 547 548 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 549 550 /* 551 * The active host is used to escape strings in the context of the 552 * active connection's character encoding. 553 */ 554 dict_mysql->active_host = host; 555 VSTRING_RESET(query); 556 VSTRING_TERMINATE(query); 557 db_common_expand(dict_mysql->ctx, dict_mysql->query, 558 name, 0, query, dict_mysql_quote); 559 dict_mysql->active_host = 0; 560 #endif 561 562 query_error = 0; 563 errno = 0; 564 565 /* 566 * The query must complete. 567 */ 568 if (mysql_query(host->db, vstring_str(query)) != 0) { 569 query_error = 1; 570 msg_warn("%s:%s: query failed: %s", 571 dict_mysql->dict.type, dict_mysql->dict.name, 572 mysql_error(host->db)); 573 } 574 575 /* 576 * Collect all result sets to avoid synchronization errors. 577 */ 578 else { 579 int next_res_status; 580 581 do { 582 MYSQL_RES *temp_result; 583 584 /* 585 * Keep the first result set. Reject multiple result sets. 586 */ 587 if ((temp_result = mysql_store_result(host->db)) != 0) { 588 if (first_result == 0) { 589 first_result = temp_result; 590 } else { 591 SET_ERROR_AND_WARN_ONCE(query_error, 592 "%s:%s: query failed: multiple result sets " 593 "returning data are not supported", 594 dict_mysql->dict.type, 595 dict_mysql->dict.name); 596 mysql_free_result(temp_result); 597 } 598 } 599 600 /* 601 * No result: the mysql_field_count() function must return 0 602 * to indicate that mysql_store_result() completed normally. 603 */ 604 else if (mysql_field_count(host->db) != 0) { 605 SET_ERROR_AND_WARN_ONCE(query_error, 606 "%s:%s: query failed (mysql_store_result): %s", 607 dict_mysql->dict.type, 608 dict_mysql->dict.name, 609 mysql_error(host->db)); 610 } 611 612 /* 613 * Are there more results? -1 = no, 0 = yes, > 0 = error. 614 */ 615 if ((next_res_status = mysql_next_result(host->db)) > 0) { 616 SET_ERROR_AND_WARN_ONCE(query_error, 617 "%s:%s: query failed (mysql_next_result): %s", 618 dict_mysql->dict.type, 619 dict_mysql->dict.name, 620 mysql_error(host->db)); 621 } 622 } while (next_res_status == 0); 623 624 /* 625 * Enforce the require_result_set setting. 626 */ 627 if (first_result == 0 && dict_mysql->require_result_set) { 628 SET_ERROR_AND_WARN_ONCE(query_error, 629 "%s:%s: query failed: query returned no result set" 630 "(require_result_set = yes)", 631 dict_mysql->dict.type, 632 dict_mysql->dict.name); 633 } 634 } 635 636 /* 637 * See what we got. 638 */ 639 if (query_error) { 640 plmysql_down_host(host); 641 if (errno == 0) 642 errno = ENOTSUP; 643 if (first_result) { 644 mysql_free_result(first_result); 645 first_result = 0; 646 } 647 } else { 648 if (msg_verbose) 649 msg_info("%s:%s: successful query result from host %s", 650 dict_mysql->dict.type, dict_mysql->dict.name, 651 host->hostname); 652 event_request_timer(dict_mysql_event, (void *) host, 653 IDLE_CONN_INTV); 654 break; 655 } 656 } 657 658 *result = first_result; 659 return (query_error == 0); 660 } 661 662 /* 663 * plmysql_connect_single - 664 * used to reconnect to a single database when one is down or none is 665 * connected yet. Log all errors and set the stat field of host accordingly 666 */ 667 static void plmysql_connect_single(DICT_MYSQL *dict_mysql, HOST *host) 668 { 669 if ((host->db = mysql_init(NULL)) == NULL) 670 msg_fatal("dict_mysql: insufficient memory"); 671 if (dict_mysql->option_file) 672 mysql_options(host->db, MYSQL_READ_DEFAULT_FILE, dict_mysql->option_file); 673 if (dict_mysql->option_group && dict_mysql->option_group[0]) 674 mysql_options(host->db, MYSQL_READ_DEFAULT_GROUP, dict_mysql->option_group); 675 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 676 if (dict_mysql->tls_key_file || dict_mysql->tls_cert_file || 677 dict_mysql->tls_CAfile || dict_mysql->tls_CApath || dict_mysql->tls_ciphers) 678 mysql_ssl_set(host->db, 679 dict_mysql->tls_key_file, dict_mysql->tls_cert_file, 680 dict_mysql->tls_CAfile, dict_mysql->tls_CApath, 681 dict_mysql->tls_ciphers); 682 #if defined(DICT_MYSQL_SSL_VERIFY_SERVER_CERT) 683 if (dict_mysql->tls_verify_cert != -1) 684 mysql_options(host->db, DICT_MYSQL_SSL_VERIFY_SERVER_CERT, 685 &dict_mysql->tls_verify_cert); 686 #endif 687 #endif 688 if (mysql_real_connect(host->db, 689 (host->type == TYPEINET ? host->name : 0), 690 dict_mysql->username, 691 dict_mysql->password, 692 dict_mysql->dbname, 693 host->port, 694 (host->type == TYPEUNIX ? host->name : 0), 695 CLIENT_MULTI_RESULTS)) { 696 if (msg_verbose) 697 msg_info("dict_mysql: successful connection to host %s", 698 host->hostname); 699 host->stat = STATACTIVE; 700 } else { 701 msg_warn("connect to mysql server %s: %s", 702 host->hostname, mysql_error(host->db)); 703 plmysql_down_host(host); 704 } 705 } 706 707 /* plmysql_close_host - close an established MySQL connection */ 708 static void plmysql_close_host(HOST *host) 709 { 710 mysql_close(host->db); 711 host->db = 0; 712 host->stat = STATUNTRIED; 713 } 714 715 /* 716 * plmysql_down_host - close a failed connection AND set a "stay away from 717 * this host" timer 718 */ 719 static void plmysql_down_host(HOST *host) 720 { 721 mysql_close(host->db); 722 host->db = 0; 723 host->ts = time((time_t *) 0) + RETRY_CONN_INTV; 724 host->stat = STATFAIL; 725 event_cancel_timer(dict_mysql_event, (void *) host); 726 } 727 728 /* mysql_parse_config - parse mysql configuration file */ 729 730 static void mysql_parse_config(DICT_MYSQL *dict_mysql, const char *mysqlcf) 731 { 732 const char *myname = "mysql_parse_config"; 733 CFG_PARSER *p = dict_mysql->parser; 734 VSTRING *buf; 735 char *hosts; 736 737 dict_mysql->username = cfg_get_str(p, "user", "", 0, 0); 738 dict_mysql->password = cfg_get_str(p, "password", "", 0, 0); 739 dict_mysql->dbname = cfg_get_str(p, "dbname", "", 1, 0); 740 dict_mysql->result_format = cfg_get_str(p, "result_format", "%s", 1, 0); 741 dict_mysql->option_file = cfg_get_str(p, "option_file", NULL, 0, 0); 742 dict_mysql->option_group = cfg_get_str(p, "option_group", "client", 0, 0); 743 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 744 dict_mysql->tls_key_file = cfg_get_str(p, "tls_key_file", NULL, 0, 0); 745 dict_mysql->tls_cert_file = cfg_get_str(p, "tls_cert_file", NULL, 0, 0); 746 dict_mysql->tls_CAfile = cfg_get_str(p, "tls_CAfile", NULL, 0, 0); 747 dict_mysql->tls_CApath = cfg_get_str(p, "tls_CApath", NULL, 0, 0); 748 dict_mysql->tls_ciphers = cfg_get_str(p, "tls_ciphers", NULL, 0, 0); 749 #if defined(DICT_MYSQL_SSL_VERIFY_SERVER_CERT) 750 dict_mysql->tls_verify_cert = cfg_get_bool(p, "tls_verify_cert", -1); 751 #endif 752 #endif 753 dict_mysql->require_result_set = cfg_get_bool(p, "require_result_set", 1); 754 755 /* 756 * XXX: The default should be non-zero for safety, but that is not 757 * backwards compatible. 758 */ 759 dict_mysql->expansion_limit = cfg_get_int(dict_mysql->parser, 760 "expansion_limit", 0, 0, 0); 761 762 if ((dict_mysql->query = cfg_get_str(p, "query", NULL, 0, 0)) == 0) { 763 764 /* 765 * No query specified -- fallback to building it from components (old 766 * style "select %s from %s where %s") 767 */ 768 buf = vstring_alloc(64); 769 db_common_sql_build_query(buf, p); 770 dict_mysql->query = vstring_export(buf); 771 } 772 773 /* 774 * Must parse all templates before we can use db_common_expand() 775 */ 776 dict_mysql->ctx = 0; 777 (void) db_common_parse(&dict_mysql->dict, &dict_mysql->ctx, 778 dict_mysql->query, 1); 779 (void) db_common_parse(0, &dict_mysql->ctx, dict_mysql->result_format, 0); 780 db_common_parse_domain(p, dict_mysql->ctx); 781 782 /* 783 * Maps that use substring keys should only be used with the full input 784 * key. 785 */ 786 if (db_common_dict_partial(dict_mysql->ctx)) 787 dict_mysql->dict.flags |= DICT_FLAG_PATTERN; 788 else 789 dict_mysql->dict.flags |= DICT_FLAG_FIXED; 790 if (dict_mysql->dict.flags & DICT_FLAG_FOLD_FIX) 791 dict_mysql->dict.fold_buf = vstring_alloc(10); 792 793 hosts = cfg_get_str(p, "hosts", "", 0, 0); 794 795 dict_mysql->hosts = argv_split(hosts, CHARS_COMMA_SP); 796 if (dict_mysql->hosts->argc == 0) { 797 argv_add(dict_mysql->hosts, "localhost", ARGV_END); 798 argv_terminate(dict_mysql->hosts); 799 if (msg_verbose) 800 msg_info("%s: %s: no hostnames specified, defaulting to '%s'", 801 myname, mysqlcf, dict_mysql->hosts->argv[0]); 802 } 803 myfree(hosts); 804 } 805 806 /* dict_mysql_open - open MYSQL data base */ 807 808 DICT *dict_mysql_open(const char *name, int open_flags, int dict_flags) 809 { 810 DICT_MYSQL *dict_mysql; 811 CFG_PARSER *parser; 812 813 /* 814 * Sanity checks. 815 */ 816 if (open_flags != O_RDONLY) 817 return (dict_surrogate(DICT_TYPE_MYSQL, name, open_flags, dict_flags, 818 "%s:%s map requires O_RDONLY access mode", 819 DICT_TYPE_MYSQL, name)); 820 821 /* 822 * Open the configuration file. 823 */ 824 if ((parser = cfg_parser_alloc(name)) == 0) 825 return (dict_surrogate(DICT_TYPE_MYSQL, name, open_flags, dict_flags, 826 "open %s: %m", name)); 827 828 dict_mysql = (DICT_MYSQL *) dict_alloc(DICT_TYPE_MYSQL, name, 829 sizeof(DICT_MYSQL)); 830 dict_mysql->dict.lookup = dict_mysql_lookup; 831 dict_mysql->dict.close = dict_mysql_close; 832 dict_mysql->dict.flags = dict_flags; 833 dict_mysql->parser = parser; 834 mysql_parse_config(dict_mysql, name); 835 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 836 dict_mysql->active_host = 0; 837 #endif 838 dict_mysql->pldb = plmysql_init(dict_mysql->hosts); 839 if (dict_mysql->pldb == NULL) 840 msg_fatal("couldn't initialize pldb!\n"); 841 dict_mysql->dict.owner = cfg_get_owner(dict_mysql->parser); 842 return (DICT_DEBUG (&dict_mysql->dict)); 843 } 844 845 /* 846 * plmysql_init - initialize a MYSQL database. 847 * Return NULL on failure, or a PLMYSQL * on success. 848 */ 849 static PLMYSQL *plmysql_init(ARGV *hosts) 850 { 851 PLMYSQL *PLDB; 852 int i; 853 854 if ((PLDB = (PLMYSQL *) mymalloc(sizeof(PLMYSQL))) == 0) 855 msg_fatal("mymalloc of pldb failed"); 856 857 PLDB->len_hosts = hosts->argc; 858 if ((PLDB->db_hosts = (HOST **) mymalloc(sizeof(HOST *) * hosts->argc)) == 0) 859 return (0); 860 for (i = 0; i < hosts->argc; i++) 861 PLDB->db_hosts[i] = host_init(hosts->argv[i]); 862 863 return PLDB; 864 } 865 866 867 /* host_init - initialize HOST structure */ 868 static HOST *host_init(const char *hostname) 869 { 870 const char *myname = "mysql host_init"; 871 HOST *host = (HOST *) mymalloc(sizeof(HOST)); 872 const char *d = hostname; 873 char *s; 874 875 host->db = 0; 876 host->hostname = mystrdup(hostname); 877 host->port = 0; 878 host->stat = STATUNTRIED; 879 host->ts = 0; 880 881 /* 882 * Ad-hoc parsing code. Expect "unix:pathname" or "inet:host:port", where 883 * both "inet:" and ":port" are optional. 884 */ 885 if (strncmp(d, "unix:", 5) == 0) { 886 d += 5; 887 host->type = TYPEUNIX; 888 } else { 889 if (strncmp(d, "inet:", 5) == 0) 890 d += 5; 891 host->type = TYPEINET; 892 } 893 host->name = mystrdup(d); 894 if ((s = split_at_right(host->name, ':')) != 0) 895 host->port = ntohs(find_inet_port(s, "tcp")); 896 if (strcasecmp(host->name, "localhost") == 0) { 897 /* The MySQL way: this will actually connect over the UNIX socket */ 898 myfree(host->name); 899 host->name = 0; 900 host->type = TYPEUNIX; 901 } 902 if (msg_verbose > 1) 903 msg_info("%s: host=%s, port=%d, type=%s", myname, 904 host->name ? host->name : "localhost", 905 host->port, host->type == TYPEUNIX ? "unix" : "inet"); 906 return host; 907 } 908 909 /* dict_mysql_close - close MYSQL database */ 910 911 static void dict_mysql_close(DICT *dict) 912 { 913 DICT_MYSQL *dict_mysql = (DICT_MYSQL *) dict; 914 915 plmysql_dealloc(dict_mysql->pldb); 916 cfg_parser_free(dict_mysql->parser); 917 myfree(dict_mysql->username); 918 myfree(dict_mysql->password); 919 myfree(dict_mysql->dbname); 920 myfree(dict_mysql->query); 921 myfree(dict_mysql->result_format); 922 if (dict_mysql->option_file) 923 myfree(dict_mysql->option_file); 924 if (dict_mysql->option_group) 925 myfree(dict_mysql->option_group); 926 #if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000 927 if (dict_mysql->tls_key_file) 928 myfree(dict_mysql->tls_key_file); 929 if (dict_mysql->tls_cert_file) 930 myfree(dict_mysql->tls_cert_file); 931 if (dict_mysql->tls_CAfile) 932 myfree(dict_mysql->tls_CAfile); 933 if (dict_mysql->tls_CApath) 934 myfree(dict_mysql->tls_CApath); 935 if (dict_mysql->tls_ciphers) 936 myfree(dict_mysql->tls_ciphers); 937 #endif 938 if (dict_mysql->hosts) 939 argv_free(dict_mysql->hosts); 940 if (dict_mysql->ctx) 941 db_common_free_ctx(dict_mysql->ctx); 942 if (dict->fold_buf) 943 vstring_free(dict->fold_buf); 944 dict_free(dict); 945 } 946 947 /* plmysql_dealloc - free memory associated with PLMYSQL close databases */ 948 static void plmysql_dealloc(PLMYSQL *PLDB) 949 { 950 int i; 951 952 for (i = 0; i < PLDB->len_hosts; i++) { 953 event_cancel_timer(dict_mysql_event, (void *) (PLDB->db_hosts[i])); 954 if (PLDB->db_hosts[i]->db) 955 mysql_close(PLDB->db_hosts[i]->db); 956 myfree(PLDB->db_hosts[i]->hostname); 957 if (PLDB->db_hosts[i]->name) 958 myfree(PLDB->db_hosts[i]->name); 959 myfree((void *) PLDB->db_hosts[i]); 960 } 961 myfree((void *) PLDB->db_hosts); 962 myfree((void *) (PLDB)); 963 } 964 965 #endif 966