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 {
70 } rlm_sql_mysql_conn_t;
72 typedef struct rlm_sql_mysql_config {
73 char const *tls_ca_file; //!< Path to the CA used to validate the server's certificate.
74 char const *tls_ca_path; //!< Directory containing CAs that may be used to validate the
75 //!< servers certificate.
76 char const *tls_certificate_file; //!< Public certificate we present to the server.
77 char const *tls_private_key_file; //!< Private key for the certificate we present to the server.
78 char const *tls_cipher;
80 char const *warnings_str; //!< Whether we always query the server for additional warnings.
81 rlm_sql_mysql_warnings warnings; //!< mysql_warning_count() doesn't
82 //!< appear to work with NDB cluster
83 } rlm_sql_mysql_config_t;
85 static CONF_PARSER tls_config[] = {
86 { "ca_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_file), NULL },
87 { "ca_path", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_path), NULL },
88 { "certificate_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_certificate_file), NULL },
89 { "private_key_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_private_key_file), NULL },
92 * MySQL Specific TLS attributes
94 { "cipher", FR_CONF_OFFSET(PW_TYPE_STRING, rlm_sql_mysql_config_t, tls_cipher), NULL },
95 CONF_PARSER_TERMINATOR
98 static const CONF_PARSER driver_config[] = {
99 { "tls", FR_CONF_POINTER(PW_TYPE_SUBSECTION, NULL), (void const *) tls_config },
101 { "warnings", FR_CONF_OFFSET(PW_TYPE_STRING, rlm_sql_mysql_config_t, warnings_str), "auto" },
102 CONF_PARSER_TERMINATOR
106 static sql_rcode_t sql_free_result(rlm_sql_handle_t*, rlm_sql_config_t*);
108 static int _sql_socket_destructor(rlm_sql_mysql_conn_t *conn)
110 DEBUG2("rlm_sql_mysql: Socket destructor called, closing socket");
113 mysql_close(conn->sock);
119 static int _mod_destructor(UNUSED rlm_sql_mysql_config_t *driver)
121 if (--mysql_instance_count == 0) mysql_library_end();
126 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
128 rlm_sql_mysql_config_t *driver;
131 static bool version_done = false;
136 INFO("rlm_sql_mysql: libmysql version: %s", mysql_get_client_info());
139 if (mysql_instance_count == 0) {
140 if (mysql_library_init(0, NULL, NULL)) {
141 ERROR("rlm_sql_mysql: libmysql initialisation failed");
146 mysql_instance_count++;
148 MEM(driver = config->driver = talloc_zero(config, rlm_sql_mysql_config_t));
149 talloc_set_destructor(driver, _mod_destructor);
151 if (cf_section_parse(conf, driver, driver_config) < 0) {
155 warnings = fr_str2int(server_warnings_table, driver->warnings_str, -1);
157 ERROR("rlm_sql_mysql: Invalid warnings value \"%s\", must be yes, no, or auto", driver->warnings_str);
160 driver->warnings = (rlm_sql_mysql_warnings)warnings;
165 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
167 rlm_sql_mysql_conn_t *conn;
168 rlm_sql_mysql_config_t *driver = config->driver;
169 unsigned long sql_flags;
171 MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_mysql_conn_t));
172 talloc_set_destructor(conn, _sql_socket_destructor);
174 DEBUG("rlm_sql_mysql: Starting connect to MySQL server");
176 mysql_init(&(conn->db));
179 * If any of the TLS options are set, configure TLS
181 * According to MySQL docs this function always returns 0, so we won't
182 * know if ssl setup succeeded until mysql_real_connect is called below.
184 if (driver->tls_ca_file || driver->tls_ca_path ||
185 driver->tls_certificate_file || driver->tls_private_key_file) {
186 mysql_ssl_set(&(conn->db), driver->tls_private_key_file, driver->tls_certificate_file,
187 driver->tls_ca_file, driver->tls_ca_path, driver->tls_cipher);
190 mysql_options(&(conn->db), MYSQL_READ_DEFAULT_GROUP, "freeradius");
193 * We need to know about connection errors, and are capable
194 * of reconnecting automatically.
196 #ifdef MYSQL_OPT_RECONNECT
198 my_bool reconnect = 0;
199 mysql_options(&(conn->db), MYSQL_OPT_RECONNECT, &reconnect);
203 #if (MYSQL_VERSION_ID >= 50000)
204 if (config->query_timeout) {
205 unsigned int connect_timeout = config->query_timeout;
206 unsigned int read_timeout = config->query_timeout;
207 unsigned int write_timeout = config->query_timeout;
210 * The timeout in seconds for each attempt to read from the server.
211 * There are retries if necessary, so the total effective timeout
212 * value is three times the option value.
214 if (config->query_timeout >= 3) read_timeout /= 3;
217 * The timeout in seconds for each attempt to write to the server.
218 * There is a retry if necessary, so the total effective timeout
219 * value is two times the option value.
221 if (config->query_timeout >= 2) write_timeout /= 2;
224 * Connect timeout is actually connect timeout (according to the
225 * docs) there are no automatic retries.
227 mysql_options(&(conn->db), MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
228 mysql_options(&(conn->db), MYSQL_OPT_READ_TIMEOUT, &read_timeout);
229 mysql_options(&(conn->db), MYSQL_OPT_WRITE_TIMEOUT, &write_timeout);
233 #if (MYSQL_VERSION_ID >= 40100)
234 sql_flags = CLIENT_MULTI_RESULTS | CLIENT_FOUND_ROWS;
236 sql_flags = CLIENT_FOUND_ROWS;
239 #ifdef CLIENT_MULTI_STATEMENTS
240 sql_flags |= CLIENT_MULTI_STATEMENTS;
242 conn->sock = mysql_real_connect(&(conn->db),
245 config->sql_password,
251 ERROR("rlm_sql_mysql: Couldn't connect to MySQL server %s@%s:%s", config->sql_login,
252 config->sql_server, config->sql_db);
253 ERROR("rlm_sql_mysql: MySQL error: %s", mysql_error(&conn->db));
256 return RLM_SQL_ERROR;
259 DEBUG2("rlm_sql_mysql: Connected to database '%s' on %s, server version %s, protocol version %i",
260 config->sql_db, mysql_get_host_info(conn->sock),
261 mysql_get_server_info(conn->sock), mysql_get_proto_info(conn->sock));
266 /** Analyse the last error that occurred on the socket, and determine an action
268 * @param server Socket from which to extract the server error. May be NULL.
269 * @param client_errno Error from the client.
270 * @return an action for rlm_sql to take.
272 static sql_rcode_t sql_check_error(MYSQL *server, int client_errno)
277 * The client and server error numbers are in the
280 if (server) sql_errno = mysql_errno(server);
281 if ((sql_errno == 0) && (client_errno != 0)) sql_errno = client_errno;
283 if (sql_errno > 0) switch (sql_errno) {
284 case CR_SERVER_GONE_ERROR:
287 return RLM_SQL_RECONNECT;
289 case CR_OUT_OF_MEMORY:
290 case CR_COMMANDS_OUT_OF_SYNC:
291 case CR_UNKNOWN_ERROR:
293 return RLM_SQL_ERROR;
296 * Constraints errors that signify a duplicate, or that we might
297 * want to try an alternative query.
299 * Error constants not found in the 3.23/4.0/4.1 manual page
301 * Other error constants should always be available.
303 case ER_DUP_UNIQUE: /* Can't write, because of unique constraint, to table '%s'. */
304 case ER_DUP_KEY: /* Can't write; duplicate key in table '%s' */
306 case ER_DUP_ENTRY: /* Duplicate entry '%s' for key %d. */
307 case ER_NO_REFERENCED_ROW: /* Cannot add or update a child row: a foreign key constraint fails */
308 case ER_ROW_IS_REFERENCED: /* Cannot delete or update a parent row: a foreign key constraint fails */
309 #ifdef ER_FOREIGN_DUPLICATE_KEY
310 case ER_FOREIGN_DUPLICATE_KEY: /* Upholding foreign key constraints for table '%s', entry '%s', key %d would lead to a duplicate entry. */
312 #ifdef ER_DUP_ENTRY_WITH_KEY_NAME
313 case ER_DUP_ENTRY_WITH_KEY_NAME: /* Duplicate entry '%s' for key '%s' */
315 #ifdef ER_NO_REFERENCED_ROW_2
316 case ER_NO_REFERENCED_ROW_2:
318 #ifdef ER_ROW_IS_REFERENCED_2
319 case ER_ROW_IS_REFERENCED_2:
321 return RLM_SQL_ALT_QUERY;
324 * Constraints errors that signify an invalid query
325 * that can never succeed.
327 case ER_BAD_NULL_ERROR: /* Column '%s' cannot be null */
328 case ER_NON_UNIQ_ERROR: /* Column '%s' in %s is ambiguous */
329 return RLM_SQL_QUERY_INVALID;
336 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
338 rlm_sql_mysql_conn_t *conn = handle->conn;
343 ERROR("rlm_sql_mysql: Socket not connected");
344 return RLM_SQL_RECONNECT;
347 mysql_query(conn->sock, query);
348 rcode = sql_check_error(conn->sock, 0);
349 if (rcode != RLM_SQL_OK) {
353 /* Only returns non-null string for INSERTS */
354 info = mysql_info(conn->sock);
355 if (info) DEBUG2("rlm_sql_mysql: %s", info);
360 static sql_rcode_t sql_store_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
362 rlm_sql_mysql_conn_t *conn = handle->conn;
367 ERROR("rlm_sql_mysql: Socket not connected");
368 return RLM_SQL_RECONNECT;
372 if (!(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);
386 static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
389 rlm_sql_mysql_conn_t *conn = handle->conn;
391 #if MYSQL_VERSION_ID >= 32224
393 * Count takes a connection handle
395 if (!(num = mysql_field_count(conn->sock))) {
398 * Fields takes a result struct
400 if (!(num = mysql_num_fields(conn->result))) {
407 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
411 rcode = sql_query(handle, config, query);
412 if (rcode != RLM_SQL_OK) {
416 rcode = sql_store_result(handle, config);
417 if (rcode != RLM_SQL_OK) {
421 /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
422 * this cannot return an error. Perhaps just to complain if no
425 sql_num_fields(handle, config);
430 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
432 rlm_sql_mysql_conn_t *conn = handle->conn;
435 return mysql_num_rows(conn->result);
441 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
443 rlm_sql_mysql_conn_t *conn = handle->conn;
445 unsigned int fields, i;
446 MYSQL_FIELD *field_info;
449 fields = mysql_num_fields(conn->result);
450 if (fields == 0) return RLM_SQL_ERROR;
453 * https://bugs.mysql.com/bug.php?id=32318
454 * Hints that we don't have to free field_info.
456 field_info = mysql_fetch_fields(conn->result);
457 if (!field_info) return RLM_SQL_ERROR;
459 MEM(names = talloc_zero_array(handle, char const *, fields + 1));
461 for (i = 0; i < fields; i++) names[i] = field_info[i].name;
467 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
469 rlm_sql_mysql_conn_t *conn = handle->conn;
474 * Check pointer before de-referencing it.
477 return RLM_SQL_RECONNECT;
481 handle->row = mysql_fetch_row(conn->result);
483 rcode = sql_check_error(conn->sock, 0);
484 if (rcode != RLM_SQL_OK) return rcode;
486 #if (MYSQL_VERSION_ID >= 40100)
487 sql_free_result(handle, config);
489 ret = mysql_next_result(conn->sock);
491 /* there are more results */
492 if ((sql_store_result(handle, config) == 0) && (conn->result != NULL)) {
493 goto retry_fetch_row;
495 } else if (ret > 0) return sql_check_error(NULL, ret);
501 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
503 rlm_sql_mysql_conn_t *conn = handle->conn;
506 mysql_free_result(conn->result);
513 /** Retrieves any warnings associated with the last query
515 * MySQL stores a limited number of warnings associated with the last query
516 * executed. These can be very useful in diagnosing issues, or in some cases
517 * working around bugs in MySQL which causes it to return the wrong error.
519 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
521 * @param ctx to allocate temporary error buffers in.
522 * @param out Array of sql_log_entrys to fill.
523 * @param outlen Length of out array.
524 * @param handle rlm_sql connection handle.
525 * @param config rlm_sql config.
526 * @return number of errors written to the sql_log_entry array or -1 on error.
528 static size_t sql_warnings(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
529 rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
531 rlm_sql_mysql_conn_t *conn = handle->conn;
535 unsigned int num_fields;
538 if (outlen == 0) return 0;
541 * Retrieve any warnings associated with the previous query
542 * that were left lingering on the server.
544 if (mysql_query(conn->sock, "SHOW WARNINGS") != 0) return -1;
545 result = mysql_store_result(conn->sock);
546 if (!result) return -1;
549 * Fields should be [0] = Level, [1] = Code, [2] = Message
551 num_fields = mysql_field_count(conn->sock);
552 if (num_fields < 3) {
553 WARN("rlm_sql_mysql: Failed retrieving warnings, expected 3 fields got %u", num_fields);
554 mysql_free_result(result);
559 while ((row = mysql_fetch_row(result))) {
564 * Translate the MySQL log level into our internal
565 * log levels, so they get colourised correctly.
567 if (strcasecmp(row[0], "warning") == 0) type = L_WARN;
568 else if (strcasecmp(row[0], "note") == 0) type = L_DBG;
571 msg = talloc_asprintf(ctx, "%s: %s", row[1], row[2]);
574 if (++i == outlen) break;
577 mysql_free_result(result);
582 /** Retrieves any errors associated with the connection handle
584 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
586 * @param ctx to allocate temporary error buffers in.
587 * @param out Array of sql_log_entrys to fill.
588 * @param outlen Length of out array.
589 * @param handle rlm_sql connection handle.
590 * @param config rlm_sql config.
591 * @return number of errors written to the sql_log_entry array.
593 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
594 rlm_sql_handle_t *handle, rlm_sql_config_t *config)
596 rlm_sql_mysql_conn_t *conn = handle->conn;
597 rlm_sql_mysql_config_t *driver = config->driver;
601 rad_assert(conn && conn->sock);
602 rad_assert(outlen > 0);
604 error = mysql_error(conn->sock);
607 * Grab the error now in case it gets cleared on the next operation.
609 if (error && (error[0] != '\0')) {
610 error = talloc_asprintf(ctx, "ERROR %u (%s): %s", mysql_errno(conn->sock), error,
611 mysql_sqlstate(conn->sock));
615 * Don't attempt to get errors from the server, if the last error
616 * was that the server was unavailable.
618 if ((outlen > 1) && (sql_check_error(conn->sock, 0) != RLM_SQL_RECONNECT)) {
622 switch (driver->warnings) {
623 case SERVER_WARNINGS_AUTO:
625 * Check to see if any warnings can be retrieved from the server.
627 msgs = mysql_warning_count(conn->sock);
629 DEBUG3("rlm_sql_mysql: No additional diagnostic info on server");
634 case SERVER_WARNINGS_YES:
635 ret = sql_warnings(ctx, out, outlen - 1, handle, config);
636 if (ret > 0) i += ret;
639 case SERVER_WARNINGS_NO:
658 * As a single SQL statement may return multiple results
659 * sets, (for example stored procedures) it is necessary to check
660 * whether more results exist and process them in turn if so.
663 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
665 #if (MYSQL_VERSION_ID >= 40100)
666 rlm_sql_mysql_conn_t *conn = handle->conn;
671 * If there's no result associated with the
672 * connection handle, assume the first result in the
673 * result set hasn't been retrieved.
675 * MySQL docs says there's no performance penalty for
676 * calling mysql_store_result for queries which don't
679 if (conn->result == NULL) {
680 result = mysql_store_result(conn->sock);
681 if (result) mysql_free_result(result);
683 * ...otherwise call sql_free_result to free an
684 * already stored result.
687 sql_free_result(handle, config); /* sql_free_result sets conn->result to NULL */
691 * Drain any other results associated with the handle
693 * mysql_next_result advances the result cursor so that
694 * the next call to mysql_store_result will retrieve
695 * the next result from the server.
697 * Unfortunately this really does appear to be the
698 * only way to return the handle to a consistent state.
700 while (((ret = mysql_next_result(conn->sock)) == 0) &&
701 (result = mysql_store_result(conn->sock))) {
702 mysql_free_result(result);
704 if (ret > 0) return sql_check_error(NULL, ret);
709 static int sql_affected_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
711 rlm_sql_mysql_conn_t *conn = handle->conn;
713 return mysql_affected_rows(conn->sock);
717 /* Exported to rlm_sql */
718 extern rlm_sql_module_t rlm_sql_mysql;
719 rlm_sql_module_t rlm_sql_mysql = {
720 .name = "rlm_sql_mysql",
721 .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY,
722 .mod_instantiate = mod_instantiate,
723 .sql_socket_init = sql_socket_init,
724 .sql_query = sql_query,
725 .sql_select_query = sql_select_query,
726 .sql_store_result = sql_store_result,
727 .sql_num_fields = sql_num_fields,
728 .sql_num_rows = sql_num_rows,
729 .sql_affected_rows = sql_affected_rows,
730 .sql_fields = sql_fields,
731 .sql_fetch_row = sql_fetch_row,
732 .sql_free_result = sql_free_result,
733 .sql_error = sql_error,
734 .sql_finish_query = sql_finish_query,
735 .sql_finish_select_query = sql_finish_query