2 * This program is free software; you can redistribute it and/or modify
3 * it under the terms of the GNU General Public License as published by
4 * the Free Software Foundation; either version 2 of the License, or (at
5 * your option) any later version.
7 * This program is distributed in the hope that it will be useful,
8 * but WITHOUT ANY WARRANTY; without even the implied warranty of
9 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10 * GNU General Public License for more details.
12 * You should have received a copy of the GNU General Public License
13 * along with this program; if not, write to the Free Software
14 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
19 * @file rlm_sql_mysql.c
20 * @brief MySQL driver.
22 * @copyright 2014-2015 Arran Cudbard-Bell <a.cudbardb@freeradius.org>
23 * @copyright 2000-2007,2015 The FreeRADIUS server project
24 * @copyright 2000 Mike Machado <mike@innercite.com>
25 * @copyright 2000 Alan DeKok <aland@ox.org>
29 #include <freeradius-devel/radiusd.h>
30 #include <freeradius-devel/rad_assert.h>
36 #ifdef HAVE_MYSQL_MYSQL_H
37 # include <mysql/mysql_version.h>
38 # include <mysql/errmsg.h>
39 # include <mysql/mysql.h>
40 # include <mysql/mysqld_error.h>
41 #elif defined(HAVE_MYSQL_H)
42 # include <mysql_version.h>
45 # include <mysqld_error.h>
50 static int mysql_instance_count = 0;
53 SERVER_WARNINGS_AUTO = 0,
56 } rlm_sql_mysql_warnings;
58 static const FR_NAME_NUMBER server_warnings_table[] = {
59 { "auto", SERVER_WARNINGS_AUTO },
60 { "yes", SERVER_WARNINGS_YES },
61 { "no", SERVER_WARNINGS_NO },
65 typedef struct rlm_sql_mysql_conn {
69 } rlm_sql_mysql_conn_t;
71 typedef struct rlm_sql_mysql_config {
72 char const *tls_ca_file; //!< Path to the CA used to validate the server's certificate.
73 char const *tls_ca_path; //!< Directory containing CAs that may be used to validate the
74 //!< servers certificate.
75 char const *tls_certificate_file; //!< Public certificate we present to the server.
76 char const *tls_private_key_file; //!< Private key for the certificate we present to the server.
77 char const *tls_cipher;
79 char const *warnings_str; //!< Whether we always query the server for additional warnings.
80 rlm_sql_mysql_warnings warnings; //!< mysql_warning_count() doesn't
81 //!< appear to work with NDB cluster
82 } rlm_sql_mysql_config_t;
84 static CONF_PARSER tls_config[] = {
85 { "ca_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_file), NULL },
86 { "ca_path", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_path), NULL },
87 { "certificate_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_certificate_file), NULL },
88 { "private_key_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_private_key_file), NULL },
91 * MySQL Specific TLS attributes
93 { "cipher", FR_CONF_OFFSET(PW_TYPE_STRING, rlm_sql_mysql_config_t, tls_cipher), NULL },
94 CONF_PARSER_TERMINATOR
97 static const CONF_PARSER driver_config[] = {
98 { "tls", FR_CONF_POINTER(PW_TYPE_SUBSECTION, NULL), (void const *) tls_config },
100 { "warnings", FR_CONF_OFFSET(PW_TYPE_STRING, rlm_sql_mysql_config_t, warnings_str), "auto" },
101 CONF_PARSER_TERMINATOR
105 static sql_rcode_t sql_free_result(rlm_sql_handle_t*, rlm_sql_config_t*);
107 static int _sql_socket_destructor(rlm_sql_mysql_conn_t *conn)
109 DEBUG2("rlm_sql_mysql: Socket destructor called, closing socket");
112 mysql_close(conn->sock);
118 static int _mod_destructor(UNUSED rlm_sql_mysql_config_t *driver)
120 if (--mysql_instance_count == 0) mysql_library_end();
125 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
127 rlm_sql_mysql_config_t *driver;
130 static bool version_done = false;
135 INFO("rlm_sql_mysql: libmysql version: %s", mysql_get_client_info());
138 if (mysql_instance_count == 0) {
139 if (mysql_library_init(0, NULL, NULL)) {
140 ERROR("rlm_sql_mysql: libmysql initialisation failed");
145 mysql_instance_count++;
147 MEM(driver = config->driver = talloc_zero(config, rlm_sql_mysql_config_t));
148 talloc_set_destructor(driver, _mod_destructor);
150 if (cf_section_parse(conf, driver, driver_config) < 0) {
154 warnings = fr_str2int(server_warnings_table, driver->warnings_str, -1);
156 ERROR("rlm_sql_mysql: Invalid warnings value \"%s\", must be yes, no, or auto", driver->warnings_str);
159 driver->warnings = (rlm_sql_mysql_warnings)warnings;
164 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
166 rlm_sql_mysql_conn_t *conn;
167 rlm_sql_mysql_config_t *driver = config->driver;
168 unsigned long sql_flags;
170 MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_mysql_conn_t));
171 talloc_set_destructor(conn, _sql_socket_destructor);
173 DEBUG("rlm_sql_mysql: Starting connect to MySQL server");
175 mysql_init(&(conn->db));
178 * If any of the TLS options are set, configure TLS
180 * According to MySQL docs this function always returns 0, so we won't
181 * know if ssl setup succeeded until mysql_real_connect is called below.
183 if (driver->tls_ca_file || driver->tls_ca_path ||
184 driver->tls_certificate_file || driver->tls_private_key_file) {
185 mysql_ssl_set(&(conn->db), driver->tls_private_key_file, driver->tls_certificate_file,
186 driver->tls_ca_file, driver->tls_ca_path, driver->tls_cipher);
189 mysql_options(&(conn->db), MYSQL_READ_DEFAULT_GROUP, "freeradius");
192 * We need to know about connection errors, and are capable
193 * of reconnecting automatically.
195 #ifdef MYSQL_OPT_RECONNECT
197 my_bool reconnect = 0;
198 mysql_options(&(conn->db), MYSQL_OPT_RECONNECT, &reconnect);
202 #if (MYSQL_VERSION_ID >= 50000)
203 if (config->query_timeout) {
204 unsigned int connect_timeout = config->query_timeout;
205 unsigned int read_timeout = config->query_timeout;
206 unsigned int write_timeout = config->query_timeout;
209 * The timeout in seconds for each attempt to read from the server.
210 * There are retries if necessary, so the total effective timeout
211 * value is three times the option value.
213 if (config->query_timeout >= 3) read_timeout /= 3;
216 * The timeout in seconds for each attempt to write to the server.
217 * There is a retry if necessary, so the total effective timeout
218 * value is two times the option value.
220 if (config->query_timeout >= 2) write_timeout /= 2;
223 * Connect timeout is actually connect timeout (according to the
224 * docs) there are no automatic retries.
226 mysql_options(&(conn->db), MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
227 mysql_options(&(conn->db), MYSQL_OPT_READ_TIMEOUT, &read_timeout);
228 mysql_options(&(conn->db), MYSQL_OPT_WRITE_TIMEOUT, &write_timeout);
232 #if (MYSQL_VERSION_ID >= 40100)
233 sql_flags = CLIENT_MULTI_RESULTS | CLIENT_FOUND_ROWS;
235 sql_flags = CLIENT_FOUND_ROWS;
238 #ifdef CLIENT_MULTI_STATEMENTS
239 sql_flags |= CLIENT_MULTI_STATEMENTS;
241 conn->sock = mysql_real_connect(&(conn->db),
244 config->sql_password,
250 ERROR("rlm_sql_mysql: Couldn't connect to MySQL server %s@%s:%s", config->sql_login,
251 config->sql_server, config->sql_db);
252 ERROR("rlm_sql_mysql: MySQL error: %s", mysql_error(&conn->db));
255 return RLM_SQL_ERROR;
258 DEBUG2("rlm_sql_mysql: Connected to database '%s' on %s, server version %s, protocol version %i",
259 config->sql_db, mysql_get_host_info(conn->sock),
260 mysql_get_server_info(conn->sock), mysql_get_proto_info(conn->sock));
265 /** Analyse the last error that occurred on the socket, and determine an action
267 * @param server Socket from which to extract the server error. May be NULL.
268 * @param client_errno Error from the client.
269 * @return an action for rlm_sql to take.
271 static sql_rcode_t sql_check_error(MYSQL *server, int client_errno)
276 * The client and server error numbers are in the
279 if (server) sql_errno = mysql_errno(server);
280 if ((sql_errno == 0) && (client_errno != 0)) sql_errno = client_errno;
282 if (sql_errno > 0) switch (sql_errno) {
283 case CR_SERVER_GONE_ERROR:
286 return RLM_SQL_RECONNECT;
288 case CR_OUT_OF_MEMORY:
289 case CR_COMMANDS_OUT_OF_SYNC:
290 case CR_UNKNOWN_ERROR:
292 return RLM_SQL_ERROR;
295 * Constraints errors that signify a duplicate, or that we might
296 * want to try an alternative query.
298 * Error constants not found in the 3.23/4.0/4.1 manual page
300 * Other error constants should always be available.
302 case ER_DUP_UNIQUE: /* Can't write, because of unique constraint, to table '%s'. */
303 case ER_DUP_KEY: /* Can't write; duplicate key in table '%s' */
305 case ER_DUP_ENTRY: /* Duplicate entry '%s' for key %d. */
306 case ER_NO_REFERENCED_ROW: /* Cannot add or update a child row: a foreign key constraint fails */
307 case ER_ROW_IS_REFERENCED: /* Cannot delete or update a parent row: a foreign key constraint fails */
308 #ifdef ER_FOREIGN_DUPLICATE_KEY
309 case ER_FOREIGN_DUPLICATE_KEY: /* Upholding foreign key constraints for table '%s', entry '%s', key %d would lead to a duplicate entry. */
311 #ifdef ER_DUP_ENTRY_WITH_KEY_NAME
312 case ER_DUP_ENTRY_WITH_KEY_NAME: /* Duplicate entry '%s' for key '%s' */
314 #ifdef ER_NO_REFERENCED_ROW_2
315 case ER_NO_REFERENCED_ROW_2:
317 #ifdef ER_ROW_IS_REFERENCED_2
318 case ER_ROW_IS_REFERENCED_2:
320 return RLM_SQL_ALT_QUERY;
323 * Constraints errors that signify an invalid query
324 * that can never succeed.
326 case ER_BAD_NULL_ERROR: /* Column '%s' cannot be null */
327 case ER_NON_UNIQ_ERROR: /* Column '%s' in %s is ambiguous */
328 return RLM_SQL_QUERY_INVALID;
335 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
337 rlm_sql_mysql_conn_t *conn = handle->conn;
342 ERROR("rlm_sql_mysql: Socket not connected");
343 return RLM_SQL_RECONNECT;
346 mysql_query(conn->sock, query);
347 rcode = sql_check_error(conn->sock, 0);
348 if (rcode != RLM_SQL_OK) {
352 /* Only returns non-null string for INSERTS */
353 info = mysql_info(conn->sock);
354 if (info) DEBUG2("rlm_sql_mysql: %s", info);
359 static sql_rcode_t sql_store_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
361 rlm_sql_mysql_conn_t *conn = handle->conn;
366 ERROR("rlm_sql_mysql: Socket not connected");
367 return RLM_SQL_RECONNECT;
371 conn->result = mysql_store_result(conn->sock);
373 rcode = sql_check_error(conn->sock, 0);
374 if (rcode != RLM_SQL_OK) return rcode;
375 #if (MYSQL_VERSION_ID >= 40100)
376 ret = mysql_next_result(conn->sock);
378 /* there are more results */
379 goto retry_store_result;
380 } else if (ret > 0) return sql_check_error(NULL, ret);
381 /* ret == -1 signals no more results */
387 static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
390 rlm_sql_mysql_conn_t *conn = handle->conn;
392 #if MYSQL_VERSION_ID >= 32224
394 * Count takes a connection handle
396 if (!(num = mysql_field_count(conn->sock))) {
399 * Fields takes a result struct
401 if (!(num = mysql_num_fields(conn->result))) {
408 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
412 rcode = sql_query(handle, config, query);
413 if (rcode != RLM_SQL_OK) {
417 rcode = sql_store_result(handle, config);
418 if (rcode != RLM_SQL_OK) {
422 /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
423 * this cannot return an error. Perhaps just to complain if no
426 sql_num_fields(handle, config);
431 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
433 rlm_sql_mysql_conn_t *conn = handle->conn;
436 return mysql_num_rows(conn->result);
442 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
444 rlm_sql_mysql_conn_t *conn = handle->conn;
446 unsigned int fields, i;
447 MYSQL_FIELD *field_info;
450 fields = mysql_num_fields(conn->result);
451 if (fields == 0) return RLM_SQL_ERROR;
454 * https://bugs.mysql.com/bug.php?id=32318
455 * Hints that we don't have to free field_info.
457 field_info = mysql_fetch_fields(conn->result);
458 if (!field_info) return RLM_SQL_ERROR;
460 MEM(names = talloc_zero_array(handle, char const *, fields + 1));
462 for (i = 0; i < fields; i++) names[i] = field_info[i].name;
468 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
470 rlm_sql_mysql_conn_t *conn = handle->conn;
474 unsigned int num_fields, i;
475 unsigned long *field_lens;
478 * Check pointer before de-referencing it.
481 return RLM_SQL_RECONNECT;
484 TALLOC_FREE(handle->row); /* Clear previous row set */
487 row = mysql_fetch_row(conn->result);
489 rcode = sql_check_error(conn->sock, 0);
490 if (rcode != RLM_SQL_OK) return rcode;
492 #if (MYSQL_VERSION_ID >= 40100)
493 sql_free_result(handle, config);
495 ret = mysql_next_result(conn->sock);
497 /* there are more results */
498 if ((sql_store_result(handle, config) == 0) && (conn->result != NULL)) {
499 goto retry_fetch_row;
501 } else if (ret > 0) return sql_check_error(NULL, ret);
502 /* If ret is -1 then there are no more rows */
504 return RLM_SQL_NO_MORE_ROWS;
507 num_fields = mysql_num_fields(conn->result);
508 if (!num_fields) return RLM_SQL_NO_MORE_ROWS;
510 field_lens = mysql_fetch_lengths(conn->result);
512 MEM(handle->row = talloc_zero_array(handle, char *, num_fields + 1));
513 for (i = 0; i < num_fields; i++) {
514 MEM(handle->row[i] = talloc_bstrndup(handle->row, row[i], field_lens[i]));
520 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
522 rlm_sql_mysql_conn_t *conn = handle->conn;
525 mysql_free_result(conn->result);
528 TALLOC_FREE(handle->row);
533 /** Retrieves any warnings associated with the last query
535 * MySQL stores a limited number of warnings associated with the last query
536 * executed. These can be very useful in diagnosing issues, or in some cases
537 * working around bugs in MySQL which causes it to return the wrong error.
539 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
541 * @param ctx to allocate temporary error buffers in.
542 * @param out Array of sql_log_entrys to fill.
543 * @param outlen Length of out array.
544 * @param handle rlm_sql connection handle.
545 * @param config rlm_sql config.
546 * @return number of errors written to the sql_log_entry array or -1 on error.
548 static size_t sql_warnings(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
549 rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
551 rlm_sql_mysql_conn_t *conn = handle->conn;
555 unsigned int num_fields;
558 if (outlen == 0) return 0;
561 * Retrieve any warnings associated with the previous query
562 * that were left lingering on the server.
564 if (mysql_query(conn->sock, "SHOW WARNINGS") != 0) return -1;
565 result = mysql_store_result(conn->sock);
566 if (!result) return -1;
569 * Fields should be [0] = Level, [1] = Code, [2] = Message
571 num_fields = mysql_field_count(conn->sock);
572 if (num_fields < 3) {
573 WARN("rlm_sql_mysql: Failed retrieving warnings, expected 3 fields got %u", num_fields);
574 mysql_free_result(result);
579 while ((row = mysql_fetch_row(result))) {
584 * Translate the MySQL log level into our internal
585 * log levels, so they get colourised correctly.
587 if (strcasecmp(row[0], "warning") == 0) type = L_WARN;
588 else if (strcasecmp(row[0], "note") == 0) type = L_DBG;
591 msg = talloc_asprintf(ctx, "%s: %s", row[1], row[2]);
594 if (++i == outlen) break;
597 mysql_free_result(result);
602 /** Retrieves any errors associated with the connection handle
604 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
606 * @param ctx to allocate temporary error buffers in.
607 * @param out Array of sql_log_entrys to fill.
608 * @param outlen Length of out array.
609 * @param handle rlm_sql connection handle.
610 * @param config rlm_sql config.
611 * @return number of errors written to the sql_log_entry array.
613 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
614 rlm_sql_handle_t *handle, rlm_sql_config_t *config)
616 rlm_sql_mysql_conn_t *conn = handle->conn;
617 rlm_sql_mysql_config_t *driver = config->driver;
621 rad_assert(conn && conn->sock);
622 rad_assert(outlen > 0);
624 error = mysql_error(conn->sock);
627 * Grab the error now in case it gets cleared on the next operation.
629 if (error && (error[0] != '\0')) {
630 error = talloc_asprintf(ctx, "ERROR %u (%s): %s", mysql_errno(conn->sock), error,
631 mysql_sqlstate(conn->sock));
635 * Don't attempt to get errors from the server, if the last error
636 * was that the server was unavailable.
638 if ((outlen > 1) && (sql_check_error(conn->sock, 0) != RLM_SQL_RECONNECT)) {
642 switch (driver->warnings) {
643 case SERVER_WARNINGS_AUTO:
645 * Check to see if any warnings can be retrieved from the server.
647 msgs = mysql_warning_count(conn->sock);
649 DEBUG3("rlm_sql_mysql: No additional diagnostic info on server");
654 case SERVER_WARNINGS_YES:
655 ret = sql_warnings(ctx, out, outlen - 1, handle, config);
656 if (ret > 0) i += ret;
659 case SERVER_WARNINGS_NO:
678 * As a single SQL statement may return multiple results
679 * sets, (for example stored procedures) it is necessary to check
680 * whether more results exist and process them in turn if so.
683 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
685 #if (MYSQL_VERSION_ID >= 40100)
686 rlm_sql_mysql_conn_t *conn = handle->conn;
691 * If there's no result associated with the
692 * connection handle, assume the first result in the
693 * result set hasn't been retrieved.
695 * MySQL docs says there's no performance penalty for
696 * calling mysql_store_result for queries which don't
699 if (conn->result == NULL) {
700 result = mysql_store_result(conn->sock);
701 if (result) mysql_free_result(result);
703 * ...otherwise call sql_free_result to free an
704 * already stored result.
707 sql_free_result(handle, config); /* sql_free_result sets conn->result to NULL */
711 * Drain any other results associated with the handle
713 * mysql_next_result advances the result cursor so that
714 * the next call to mysql_store_result will retrieve
715 * the next result from the server.
717 * Unfortunately this really does appear to be the
718 * only way to return the handle to a consistent state.
720 while (((ret = mysql_next_result(conn->sock)) == 0) &&
721 (result = mysql_store_result(conn->sock))) {
722 mysql_free_result(result);
724 if (ret > 0) return sql_check_error(NULL, ret);
729 static int sql_affected_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
731 rlm_sql_mysql_conn_t *conn = handle->conn;
733 return mysql_affected_rows(conn->sock);
737 /* Exported to rlm_sql */
738 extern rlm_sql_module_t rlm_sql_mysql;
739 rlm_sql_module_t rlm_sql_mysql = {
740 .name = "rlm_sql_mysql",
741 .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY,
742 .mod_instantiate = mod_instantiate,
743 .sql_socket_init = sql_socket_init,
744 .sql_query = sql_query,
745 .sql_select_query = sql_select_query,
746 .sql_store_result = sql_store_result,
747 .sql_num_fields = sql_num_fields,
748 .sql_num_rows = sql_num_rows,
749 .sql_affected_rows = sql_affected_rows,
750 .sql_fields = sql_fields,
751 .sql_fetch_row = sql_fetch_row,
752 .sql_free_result = sql_free_result,
753 .sql_error = sql_error,
754 .sql_finish_query = sql_finish_query,
755 .sql_finish_select_query = sql_finish_query