2 * sql_mysql.c SQL Module
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
20 * Copyright 2000-2007 The FreeRADIUS server project
21 * Copyright 2000 Mike Machado <mike@innercite.com>
22 * Copyright 2000 Alan DeKok <aland@ox.org>
27 #include <freeradius-devel/radiusd.h>
28 #include <freeradius-devel/rad_assert.h>
34 #ifdef HAVE_MYSQL_MYSQL_H
35 # include <mysql/mysql_version.h>
36 # include <mysql/errmsg.h>
37 # include <mysql/mysql.h>
38 # include <mysql/mysqld_error.h>
39 #elif defined(HAVE_MYSQL_H)
40 # include <mysql_version.h>
43 # include <mysqld_error.h>
48 static int mysql_instance_count = 0;
50 typedef struct rlm_sql_mysql_conn {
55 } rlm_sql_mysql_conn_t;
57 typedef struct rlm_sql_mysql_config {
58 char const *tls_ca_file;
59 char const *tls_ca_path;
60 char const *tls_certificate_file;
61 char const *tls_private_key_file;
62 char const *tls_cipher;
63 } rlm_sql_mysql_config_t;
65 static CONF_PARSER tls_config[] = {
66 { "ca_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_file), NULL },
67 { "ca_path", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_ca_path), NULL },
68 { "certificate_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_certificate_file), NULL },
69 { "private_key_file", FR_CONF_OFFSET(PW_TYPE_FILE_INPUT, rlm_sql_mysql_config_t, tls_private_key_file), NULL },
72 * MySQL Specific TLS attributes
74 { "cipher", FR_CONF_OFFSET(PW_TYPE_STRING, rlm_sql_mysql_config_t, tls_cipher), NULL },
76 { NULL, -1, 0, NULL, NULL }
79 static const CONF_PARSER driver_config[] = {
80 { "tls", FR_CONF_POINTER(PW_TYPE_SUBSECTION, NULL), (void const *) tls_config },
82 {NULL, -1, 0, NULL, NULL}
86 static sql_rcode_t sql_free_result(rlm_sql_handle_t*, rlm_sql_config_t*);
88 static int _sql_socket_destructor(rlm_sql_mysql_conn_t *conn)
90 DEBUG2("rlm_sql_mysql: Socket destructor called, closing socket");
93 mysql_close(conn->sock);
99 static int _mod_destructor(UNUSED rlm_sql_mysql_config_t *driver)
101 mysql_instance_count--;
103 if (mysql_instance_count == 0) {
110 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
112 rlm_sql_mysql_config_t *driver;
114 static bool version_done = false;
119 INFO("rlm_sql_mysql: libmysql version: %s", mysql_get_client_info());
122 if (mysql_instance_count == 0) {
123 if (mysql_library_init(0, NULL, NULL)) {
124 ERROR("rlm_sql_mysql: libmysql initialisation failed");
129 mysql_instance_count++;
131 MEM(driver = config->driver = talloc_zero(config, rlm_sql_mysql_config_t));
132 talloc_set_destructor(driver, _mod_destructor);
134 if (cf_section_parse(conf, driver, driver_config) < 0) {
141 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
143 rlm_sql_mysql_conn_t *conn;
144 rlm_sql_mysql_config_t *driver = config->driver;
145 unsigned long sql_flags;
147 MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_mysql_conn_t));
148 talloc_set_destructor(conn, _sql_socket_destructor);
150 DEBUG("rlm_sql_mysql: Starting connect to MySQL server");
152 mysql_init(&(conn->db));
155 * If any of the TLS options are set, configure TLS
157 * According to MySQL docs this function always returns 0, so we won't
158 * know if ssl setup succeeded until mysql_real_connect is called below.
160 if (driver->tls_ca_file || driver->tls_ca_path ||
161 driver->tls_certificate_file || driver->tls_private_key_file) {
162 mysql_ssl_set(&(conn->db), driver->tls_private_key_file, driver->tls_certificate_file,
163 driver->tls_ca_file, driver->tls_ca_path, driver->tls_cipher);
166 mysql_options(&(conn->db), MYSQL_READ_DEFAULT_GROUP, "freeradius");
169 * We need to know about connection errors, and are capable
170 * of reconnecting automatically.
172 #ifdef MYSQL_OPT_RECONNECT
174 my_bool reconnect = 0;
175 mysql_options(&(conn->db), MYSQL_OPT_RECONNECT, &reconnect);
179 #if (MYSQL_VERSION_ID >= 50000)
180 if (config->query_timeout) {
181 unsigned int connect_timeout = config->query_timeout;
182 unsigned int read_timeout = config->query_timeout;
183 unsigned int write_timeout = config->query_timeout;
186 * The timeout in seconds for each attempt to read from the server.
187 * There are retries if necessary, so the total effective timeout
188 * value is three times the option value.
190 if (config->query_timeout >= 3) read_timeout /= 3;
193 * The timeout in seconds for each attempt to write to the server.
194 * There is a retry if necessary, so the total effective timeout
195 * value is two times the option value.
197 if (config->query_timeout >= 2) write_timeout /= 2;
200 * Connect timeout is actually connect timeout (according to the
201 * docs) there are no automatic retries.
203 mysql_options(&(conn->db), MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
204 mysql_options(&(conn->db), MYSQL_OPT_READ_TIMEOUT, &read_timeout);
205 mysql_options(&(conn->db), MYSQL_OPT_WRITE_TIMEOUT, &write_timeout);
209 #if (MYSQL_VERSION_ID >= 40100)
210 sql_flags = CLIENT_MULTI_RESULTS | CLIENT_FOUND_ROWS;
212 sql_flags = CLIENT_FOUND_ROWS;
215 #ifdef CLIENT_MULTI_STATEMENTS
216 sql_flags |= CLIENT_MULTI_STATEMENTS;
218 conn->sock = mysql_real_connect(&(conn->db),
221 config->sql_password,
223 atoi(config->sql_port),
227 ERROR("rlm_sql_mysql: Couldn't connect socket to MySQL server %s@%s:%s", config->sql_login,
228 config->sql_server, config->sql_db);
229 ERROR("rlm_sql_mysql: MySQL error: %s", mysql_error(&conn->db));
232 return RLM_SQL_ERROR;
235 DEBUG2("rlm_sql_mysql: Connected to database '%s' on %s, server version %s, protocol version %i",
236 config->sql_db, mysql_get_host_info(conn->sock),
237 mysql_get_server_info(conn->sock), mysql_get_proto_info(conn->sock));
242 /** Analyse the last error that occurred on the socket, and determine an actions
244 * @param server Socket from which to extract the server error. May be NULL.
245 * @param client_errno Error from the client.
246 * @return an action for rlm_sql to take.
248 static sql_rcode_t sql_check_error(MYSQL *server, int client_errno)
250 int server_errno = 0;
251 if (server) server_errno = mysql_errno(server);
254 * Process errors from the client
256 if (client_errno > 0) switch (client_errno) {
257 case CR_SERVER_GONE_ERROR:
260 return RLM_SQL_RECONNECT;
262 case CR_OUT_OF_MEMORY:
263 case CR_COMMANDS_OUT_OF_SYNC:
264 case CR_UNKNOWN_ERROR:
266 return RLM_SQL_ERROR;
270 * Process errors from the server
272 if (server_errno > 0) switch (server_errno) {
274 * Constraints errors that signify a duplicate, or that we might
275 * want to try an alternative query.
277 * Errors not found in the 3.23/4.0/4.1 manual page checked for.
278 * Other error constants should always be available.
281 case ER_DUP_UNIQUE: /* Can't write, because of unique constraint, to table '%s'. */
282 case ER_DUP_KEY: /* Can't write; duplicate key in table '%s' */
284 case ER_DUP_ENTRY: /* Duplicate entry '%s' for key %d. */
285 case ER_NO_REFERENCED_ROW: /* Cannot add or update a child row: a foreign key constraint fails */
286 case ER_ROW_IS_REFERENCED: /* Cannot delete or update a parent row: a foreign key constraint fails */
287 #ifdef ER_FOREIGN_DUPLICATE_KEY
288 case ER_FOREIGN_DUPLICATE_KEY: /* Upholding foreign key constraints for table '%s', entry '%s', key %d would lead to a duplicate entry. */
290 #ifdef ER_DUP_ENTRY_WITH_KEY_NAME
291 case ER_DUP_ENTRY_WITH_KEY_NAME: /* Duplicate entry '%s' for key '%s' */
293 #ifdef ER_NO_REFERENCED_ROW_2
294 case ER_NO_REFERENCED_ROW_2:
296 #ifdef ER_ROW_IS_REFERENCED_2
297 case ER_ROW_IS_REFERENCED_2:
299 return RLM_SQL_ALT_QUERY;
302 * Constraints errors that signify an invalid query
303 * that can never succeed.
305 case ER_BAD_NULL_ERROR: /* Column '%s' cannot be null */
306 case ER_NON_UNIQ_ERROR: /* Column '%s' in %s is ambiguous */
307 return RLM_SQL_QUERY_INVALID;
316 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
318 rlm_sql_mysql_conn_t *conn = handle->conn;
323 ERROR("rlm_sql_mysql: Socket not connected");
324 return RLM_SQL_RECONNECT;
327 mysql_query(conn->sock, query);
328 rcode = sql_check_error(conn->sock, 0);
329 if (rcode != RLM_SQL_OK) {
333 /* Only returns non-null string for INSERTS */
334 info = mysql_info(conn->sock);
335 if (info) DEBUG2("rlm_sql_mysql: %s", info);
340 static sql_rcode_t sql_store_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
342 rlm_sql_mysql_conn_t *conn = handle->conn;
347 ERROR("rlm_sql_mysql: Socket not connected");
348 return RLM_SQL_RECONNECT;
352 if (!(conn->result = mysql_store_result(conn->sock))) {
353 rcode = sql_check_error(conn->sock, 0);
354 if (rcode != RLM_SQL_OK) return rcode;
355 #if (MYSQL_VERSION_ID >= 40100)
356 ret = mysql_next_result(conn->sock);
358 /* there are more results */
359 goto retry_store_result;
360 } else if (ret > 0) return sql_check_error(NULL, ret);
366 static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
369 rlm_sql_mysql_conn_t *conn = handle->conn;
371 #if MYSQL_VERSION_ID >= 32224
372 if (!(num = mysql_field_count(conn->sock))) {
374 if (!(num = mysql_num_fields(conn->sock))) {
381 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
385 rcode = sql_query(handle, config, query);
386 if (rcode != RLM_SQL_OK) {
390 rcode = sql_store_result(handle, config);
391 if (rcode != RLM_SQL_OK) {
395 /* Why? Per http://www.mysql.com/doc/n/o/node_591.html,
396 * this cannot return an error. Perhaps just to complain if no
399 sql_num_fields(handle, config);
404 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
406 rlm_sql_mysql_conn_t *conn = handle->conn;
409 return mysql_num_rows(conn->result);
415 static sql_rcode_t sql_fetch_row(rlm_sql_row_t *out, rlm_sql_handle_t *handle, rlm_sql_config_t *config)
417 rlm_sql_mysql_conn_t *conn = handle->conn;
424 * Check pointer before de-referencing it.
426 if (!conn->result) return RLM_SQL_RECONNECT;
429 *out = handle->row = mysql_fetch_row(conn->result);
431 rcode = sql_check_error(conn->sock, 0);
432 if (rcode != RLM_SQL_OK) return rcode;
434 #if (MYSQL_VERSION_ID >= 40100)
435 sql_free_result(handle, config);
437 ret = mysql_next_result(conn->sock);
439 /* there are more results */
440 if ((sql_store_result(handle, config) == 0) && (conn->result != NULL)) {
441 goto retry_fetch_row;
443 } else if (ret > 0) return sql_check_error(NULL, ret);
449 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
451 rlm_sql_mysql_conn_t *conn = handle->conn;
454 mysql_free_result(conn->result);
461 /** Retrieves any warnings associated with the last query
463 * MySQL stores a limited number of warnings associated with the last query
464 * executed. These can be very useful in diagnosing issues, or in some cases
465 * working around bugs in MySQL which causes it to return the wrong error.
467 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
469 * @param ctx to allocate temporary error buffers in.
470 * @param out Array of sql_log_entrys to fill.
471 * @param outlen Length of out array.
472 * @param handle rlm_sql connection handle.
473 * @param config rlm_sql config.
474 * @return number of errors written to the sql_log_entry array or -1 on error.
476 static size_t sql_warnings(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
477 rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
479 rlm_sql_mysql_conn_t *conn = handle->conn;
483 unsigned int num_fields;
487 if (outlen == 0) return 0;
490 * Check to see if any warnings can be retrieved from the server.
492 msgs = mysql_warning_count(conn->sock);
494 DEBUG3("rlm_sql_mysql: No additional diagnostic info on server");
499 * Retrieve any warnings associated with the previous query
500 * that were left lingering on the server.
502 if (mysql_query(conn->sock, "SHOW WARNINGS") != 0) return -1;
503 result = mysql_store_result(conn->sock);
504 if (!result) return -1;
507 * Fields should be [0] = Level, [1] = Code, [2] = Message
509 num_fields = mysql_field_count(conn->sock);
510 if (num_fields < 3) {
511 WARN("rlm_sql_mysql: Failed retrieving warnings, expected 3 fields got %u", num_fields);
512 mysql_free_result(result);
517 while ((row = mysql_fetch_row(result))) {
522 * Translate the MySQL log level into our internal
523 * log levels, so they get colourised correctly.
525 if (strcasecmp(row[0], "warning") == 0) type = L_WARN;
526 else if (strcasecmp(row[0], "note") == 0) type = L_DBG;
529 msg = talloc_asprintf(ctx, "%s: %s", row[1], row[2]);
532 if (++i == outlen) break;
535 mysql_free_result(result);
540 /** Retrieves any errors associated with the connection handle
542 * @note Caller should free any memory allocated in ctx (talloc_free_children()).
544 * @param ctx to allocate temporary error buffers in.
545 * @param out Array of sql_log_entrys to fill.
546 * @param outlen Length of out array.
547 * @param handle rlm_sql connection handle.
548 * @param config rlm_sql config.
549 * @return number of errors written to the sql_log_entry array.
551 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
552 rlm_sql_handle_t *handle, rlm_sql_config_t *config)
554 rlm_sql_mysql_conn_t *conn = handle->conn;
558 rad_assert(conn && conn->sock);
559 rad_assert(outlen > 0);
561 error = mysql_error(conn->sock);
564 * Grab the error now in case it gets cleared on the next operation.
566 if (error && (error[0] != '\0')) {
567 error = talloc_asprintf(ctx, "ERROR %u (%s): %s", mysql_errno(conn->sock), error,
568 mysql_sqlstate(conn->sock));
572 * Don't attempt to get errors from the server, if the last error
573 * was that the server was unavailable.
575 if ((outlen > 1) && (sql_check_error(conn->sock, 0) != RLM_SQL_RECONNECT)) {
578 ret = sql_warnings(ctx, out, outlen - 1, handle, config);
579 if (ret > 0) i += ret;
593 * As a single SQL statement may return multiple results
594 * sets, (for example stored procedures) it is necessary to check
595 * whether more results exist and process them in turn if so.
598 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
600 #if (MYSQL_VERSION_ID >= 40100)
601 rlm_sql_mysql_conn_t *conn = handle->conn;
606 rcode = sql_store_result(handle, config);
607 if (rcode != RLM_SQL_OK) {
609 } else if (conn->result != NULL) {
610 DEBUG("rlm_sql_mysql: SQL statement returned unexpected result");
611 sql_free_result(handle, config);
614 ret = mysql_next_result(conn->sock);
616 /* there are more results */
617 goto skip_next_result;
618 } else if (ret > 0) return sql_check_error(NULL, ret);
623 static sql_rcode_t sql_finish_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
625 #if (MYSQL_VERSION_ID >= 40100)
627 rlm_sql_mysql_conn_t *conn = handle->conn;
629 sql_free_result(handle, config);
630 #if (MYSQL_VERSION_ID >= 40100)
631 ret = mysql_next_result(conn->sock);
633 /* there are more results */
634 sql_finish_query(handle, config);
635 } else if (ret > 0) return sql_check_error(NULL, ret);
640 static int sql_affected_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
642 rlm_sql_mysql_conn_t *conn = handle->conn;
644 return mysql_affected_rows(conn->sock);
648 /* Exported to rlm_sql */
649 extern rlm_sql_module_t rlm_sql_mysql;
650 rlm_sql_module_t rlm_sql_mysql = {
651 .name = "rlm_sql_mysql",
652 .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY,
653 .mod_instantiate = mod_instantiate,
654 .sql_socket_init = sql_socket_init,
655 .sql_query = sql_query,
656 .sql_select_query = sql_select_query,
657 .sql_store_result = sql_store_result,
658 .sql_num_fields = sql_num_fields,
659 .sql_num_rows = sql_num_rows,
660 .sql_affected_rows = sql_affected_rows,
661 .sql_fetch_row = sql_fetch_row,
662 .sql_free_result = sql_free_result,
663 .sql_error = sql_error,
664 .sql_finish_query = sql_finish_query,
665 .sql_finish_select_query = sql_finish_select_query