2 * This program is 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_sqlite.c
20 * @brief SQLite driver.
22 * @copyright 2013 Network RADIUS SARL <info@networkradius.com>
23 * @copyright 2007 Apple Inc.
27 #include <freeradius-devel/radiusd.h>
28 #include <freeradius-devel/rad_assert.h>
38 #define BOOTSTRAP_MAX (1048576 * 10)
41 * Allow us to use versions < 3.6.0 beta0
43 #ifndef SQLITE_OPEN_NOMUTEX
44 # define SQLITE_OPEN_NOMUTEX 0
47 #ifndef HAVE_SQLITE3_INT64
48 typedef sqlite_int64 sqlite3_int64;
51 typedef struct rlm_sql_sqlite_conn {
53 sqlite3_stmt *statement;
55 } rlm_sql_sqlite_conn_t;
57 typedef struct rlm_sql_sqlite_config {
59 uint32_t busy_timeout;
60 } rlm_sql_sqlite_config_t;
62 static const CONF_PARSER driver_config[] = {
63 { "filename", FR_CONF_OFFSET(PW_TYPE_FILE_OUTPUT | PW_TYPE_REQUIRED, rlm_sql_sqlite_config_t, filename), NULL },
64 { "busy_timeout", FR_CONF_OFFSET(PW_TYPE_INTEGER, rlm_sql_sqlite_config_t, busy_timeout), "200" },
65 CONF_PARSER_TERMINATOR
68 /** Convert an sqlite status code to an sql_rcode_t
70 * @param status to convert.
72 * - RLM_SQL_OK - If no errors found.
73 * - RLM_SQL_ERROR - If a known, non-fatal, error occurred.
74 * - RLM_SQL_ALT_QUERY - If a constraints violation occurred.
75 * - RLM_SQL_RECONNECT - Anything else, we assume the connection can no longer be used.
77 static sql_rcode_t sql_error_to_rcode(int status)
80 * Lowest byte is error category, other byte may contain
81 * the extended error, depending on version.
83 switch (status & 0xff) {
92 * User/transient errors
94 case SQLITE_ERROR: /* SQL error or missing database */
100 * Constraints violations
102 case SQLITE_CONSTRAINT:
103 return RLM_SQL_ALT_QUERY;
106 * Errors with the handle, that probably require reinitialisation
109 return RLM_SQL_RECONNECT;
113 /** Determine if an error occurred, and what type of error it was
115 * @param db handle to extract error from (may be NULL).
116 * @param status to check (if unused, set to SQLITE_OK).
118 * - RLM_SQL_OK - If no errors found.
119 * - RLM_SQL_ERROR - If a known, non-fatal, error occurred.
120 * - RLM_SQL_ALT_QUERY - If a constraints violation occurred.
121 * - RLM_SQL_RECONNECT - Anything else. We assume the connection can no longer be used.
123 static sql_rcode_t sql_check_error(sqlite3 *db, int status)
125 int hstatus = SQLITE_OK;
128 hstatus = sqlite3_errcode(db);
129 switch (hstatus & 0xff) {
141 switch (status & 0xff) {
152 if (status != SQLITE_OK) return sql_error_to_rcode(status);
153 if (hstatus != SQLITE_OK) return sql_error_to_rcode(status);
158 /** Print an error to the global debug log
160 * If status does not indicate success, write an error to the global error log.
162 * @note The error code will be appended to the fmt string in the format ": code 0x<hex> (<int>)[: <string>]".
164 * @param db handle to extract error from (may be NULL).
165 * @param status to check (if unused, set to SQLITE_OK).
166 * @param fmt to preprend.
167 * @param ... arguments to fmt.
169 static void sql_print_error(sqlite3 *db, int status, char const *fmt, ...)
170 CC_HINT(format (printf, 3, 4)) CC_HINT(nonnull (3));
171 static void sql_print_error(sqlite3 *db, int status, char const *fmt, ...)
175 int hstatus = SQLITE_OK;
178 hstatus = sqlite3_errcode(db);
179 switch (hstatus & 0xff) {
191 switch (status & 0xff) {
205 if ((hstatus == SQLITE_OK) && (status == SQLITE_OK)) return;
208 * At least one error...
211 MEM(p = talloc_vasprintf(NULL, fmt, ap));
215 * Disagreement between handle, and function return code,
218 if ((status != SQLITE_OK) && (status != hstatus)) {
219 #ifdef HAVE_SQLITE3_ERRSTR
220 ERROR("rlm_sql_sqlite: %s: Code 0x%04x (%i): %s", p, status, status, sqlite3_errstr(status));
222 ERROR("rlm_sql_sqlite: %s: Code 0x%04x (%i)", p, status, status);
226 if (hstatus != SQLITE_OK) ERROR("rlm_sql_sqlite: %s: Code 0x%04x (%i): %s",
227 p, hstatus, hstatus, sqlite3_errmsg(db));
230 #ifdef HAVE_SQLITE3_OPEN_V2
231 static int sql_loadfile(TALLOC_CTX *ctx, sqlite3 *db, char const *filename)
234 int statement_cnt = 0;
242 sqlite3_stmt *statement;
245 INFO("rlm_sql_sqlite: Executing SQL statements from file \"%s\"", filename);
247 f = fopen(filename, "r");
249 ERROR("rlm_sql_sqlite: Failed opening SQL file \"%s\": %s", filename,
255 if (fstat(fileno(f), &finfo) < 0) {
256 ERROR("rlm_sql_sqlite: Failed stating SQL file \"%s\": %s", filename,
264 if (finfo.st_size > BOOTSTRAP_MAX) {
266 ERROR("rlm_sql_sqlite: Size of SQL (%zu) file exceeds limit (%uk)",
267 (size_t) finfo.st_size / 1024, BOOTSTRAP_MAX / 1024);
274 MEM(buffer = talloc_array(ctx, char, finfo.st_size + 1));
275 len = fread(buffer, sizeof(char), finfo.st_size + 1, f);
276 if (len > finfo.st_size) {
283 ERROR("rlm_sql_sqlite: Error reading SQL file: %s", fr_syserror(errno));
291 DEBUG("rlm_sql_sqlite: Ignoring empty SQL file");
303 * Check if input data is UTF-8. Allow CR/LF \t, too.
305 for (p = buffer; p < (buffer + len); p += cl) {
307 if ((*p != 0x0a) && (*p != 0x0d) && (*p != '\t')) break;
310 cl = fr_utf8_char((uint8_t *) p, -1);
315 if ((p - buffer) != len) {
316 ERROR("rlm_sql_sqlite: Bootstrap file contains non-UTF8 char at offset %zu", p - buffer);
322 * Statement delimiter is ;\n
325 while ((q = strchr(p, ';'))) {
334 #ifdef HAVE_SQLITE3_PREPARE_V2
335 status = sqlite3_prepare_v2(db, s, len, &statement, &z_tail);
337 status = sqlite3_prepare(db, s, len, &statement, &z_tail);
340 if (sql_check_error(db, status) != RLM_SQL_OK) {
341 sql_print_error(db, status, "Failed preparing statement %i", statement_cnt);
346 status = sqlite3_step(statement);
347 if (sql_check_error(db, status) != RLM_SQL_OK) {
348 sql_print_error(db, status, "Failed executing statement %i", statement_cnt);
349 sqlite3_finalize(statement);
354 status = sqlite3_finalize(statement);
355 if (sql_check_error(db, status) != RLM_SQL_OK) {
356 sql_print_error(db, status, "Failed finalizing statement %i", statement_cnt);
370 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
372 static bool version_done;
375 rlm_sql_sqlite_config_t *driver;
381 if (sqlite3_libversion_number() != SQLITE_VERSION_NUMBER) {
382 WARN("rlm_sql_sqlite: libsqlite version changed since the server was built");
383 WARN("rlm_sql_sqlite: linked: %s built: %s", sqlite3_libversion(), SQLITE_VERSION);
385 INFO("rlm_sql_sqlite: libsqlite version: %s", sqlite3_libversion());
388 MEM(driver = config->driver = talloc_zero(config, rlm_sql_sqlite_config_t));
389 if (cf_section_parse(conf, driver, driver_config) < 0) {
392 if (!driver->filename) {
393 MEM(driver->filename = talloc_typed_asprintf(driver, "%s/%s", get_radius_dir(), config->sql_db));
396 if (stat(driver->filename, &buf) == 0) {
398 } else if (errno == ENOENT) {
401 ERROR("rlm_sql_sqlite: Database exists, but couldn't be opened: %s", fr_syserror(errno));
405 if (cf_pair_find(conf, "bootstrap") && !exists) {
406 # ifdef HAVE_SQLITE3_OPEN_V2
414 INFO("rlm_sql_sqlite: Database doesn't exist, creating it and loading schema");
416 p = strrchr(driver->filename, '/');
418 size_t len = (p - driver->filename) + 1;
420 buff = talloc_array(conf, char, len);
421 strlcpy(buff, driver->filename, len);
423 MEM(buff = talloc_typed_strdup(conf, driver->filename));
426 ret = rad_mkdir(buff, 0700, -1, -1);
429 ERROR("rlm_sql_sqlite: Failed creating directory for SQLite database: %s", fr_syserror(errno));
434 status = sqlite3_open_v2(driver->filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
436 # ifdef HAVE_SQLITE3_ERRSTR
437 ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database: %s",
438 sqlite3_errstr(status));
440 ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database, got code (%i)",
447 if (sql_check_error(db, status) != RLM_SQL_OK) {
448 (void) sqlite3_close(db);
454 * Execute multiple bootstrap SQL files in order
456 for (cp = cf_pair_find(conf, "bootstrap");
458 cp = cf_pair_find_next(conf, cp, "bootstrap")) {
459 p = cf_pair_value(cp);
462 ret = sql_loadfile(conf, db, p);
463 if (ret < 0) goto unlink;
466 status = sqlite3_close(db);
467 if (status != SQLITE_OK) {
469 * Safer to use sqlite3_errstr here, just in case the handle is in a weird state
471 # ifdef HAVE_SQLITE3_ERRSTR
472 ERROR("rlm_sql_sqlite: Error closing SQLite handle: %s", sqlite3_errstr(status));
474 ERROR("rlm_sql_sqlite: Error closing SQLite handle, got code (%i)", status);
482 if ((unlink(driver->filename) < 0) && (errno != ENOENT)) {
483 ERROR("rlm_sql_sqlite: Error removing partially initialised database: %s",
489 WARN("rlm_sql_sqlite: sqlite3_open_v2() not available, cannot bootstrap database. "
490 "Upgrade to SQLite >= 3.5.1 if you need this functionality");
497 static int _sql_socket_destructor(rlm_sql_sqlite_conn_t *conn)
501 DEBUG2("rlm_sql_sqlite: Socket destructor called, closing socket");
504 status = sqlite3_close(conn->db);
505 if (status != SQLITE_OK) WARN("rlm_sql_sqlite: Got SQLite error code (%u) when closing socket", status);
511 static void _sql_greatest(sqlite3_context *ctx, int num_values, sqlite3_value **values)
514 sqlite3_int64 value, max = 0;
516 for (i = 0; i < num_values; i++) {
517 value = sqlite3_value_int64(values[i]);
523 sqlite3_result_int64(ctx, max);
526 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
528 rlm_sql_sqlite_conn_t *conn;
529 rlm_sql_sqlite_config_t *driver = config->driver;
533 MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_sqlite_conn_t));
534 talloc_set_destructor(conn, _sql_socket_destructor);
536 INFO("rlm_sql_sqlite: Opening SQLite database \"%s\"", driver->filename);
537 #ifdef HAVE_SQLITE3_OPEN_V2
538 status = sqlite3_open_v2(driver->filename, &(conn->db), SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX, NULL);
540 status = sqlite3_open(driver->filename, &(conn->db));
543 if (!conn->db || (sql_check_error(conn->db, status) != RLM_SQL_OK)) {
544 sql_print_error(conn->db, status, "Error opening SQLite database \"%s\"", driver->filename);
545 return RLM_SQL_ERROR;
547 status = sqlite3_busy_timeout(conn->db, driver->busy_timeout);
548 if (sql_check_error(conn->db, status) != RLM_SQL_OK) {
549 sql_print_error(conn->db, status, "Error setting busy timeout");
550 return RLM_SQL_ERROR;
554 * Enable extended return codes for extra debugging info.
556 #ifdef HAVE_SQLITE3_EXTENDED_RESULT_CODES
557 status = sqlite3_extended_result_codes(conn->db, 1);
558 if (sql_check_error(conn->db, status) != RLM_SQL_OK) {
559 sql_print_error(conn->db, status, "Error enabling extended result codes");
560 return RLM_SQL_ERROR;
564 #ifdef HAVE_SQLITE3_CREATE_FUNCTION_V2
565 status = sqlite3_create_function_v2(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
566 _sql_greatest, NULL, NULL, NULL);
568 status = sqlite3_create_function(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
569 _sql_greatest, NULL, NULL);
571 if (sql_check_error(conn->db, status) != RLM_SQL_OK) {
572 sql_print_error(conn->db, status, "Failed registering 'GREATEST' sql function");
573 return RLM_SQL_ERROR;
579 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
581 rlm_sql_sqlite_conn_t *conn = handle->conn;
585 #ifdef HAVE_SQLITE3_PREPARE_V2
586 status = sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
588 status = sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
593 return sql_check_error(conn->db, status);
597 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
601 rlm_sql_sqlite_conn_t *conn = handle->conn;
605 #ifdef HAVE_SQLITE3_PREPARE_V2
606 status = sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
608 status = sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
610 rcode = sql_check_error(conn->db, status);
611 if (rcode != RLM_SQL_OK) return rcode;
613 status = sqlite3_step(conn->statement);
614 return sql_check_error(conn->db, status);
617 static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
619 rlm_sql_sqlite_conn_t *conn = handle->conn;
621 if (conn->statement) {
622 return sqlite3_column_count(conn->statement);
628 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
630 rlm_sql_sqlite_conn_t *conn = handle->conn;
632 if (conn->statement) {
633 return sqlite3_data_count(conn->statement);
639 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
641 rlm_sql_sqlite_conn_t *conn = handle->conn;
646 fields = sqlite3_column_count(conn->statement);
647 if (fields <= 0) return RLM_SQL_ERROR;
649 MEM(names = talloc_zero_array(handle, char const *, fields + 1));
651 for (i = 0; i < fields; i++) names[i] = sqlite3_column_name(conn->statement, i);
657 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
660 rlm_sql_sqlite_conn_t *conn = handle->conn;
666 TALLOC_FREE(handle->row);
669 * Executes the SQLite query and interates over the results
671 status = sqlite3_step(conn->statement);
674 * Error getting next row
676 if (sql_check_error(conn->db, status) != RLM_SQL_OK) return RLM_SQL_ERROR;
679 * No more rows to process (were done)
681 if (status == SQLITE_DONE) return RLM_SQL_NO_MORE_ROWS;
684 * We only need to do this once per result set, because
685 * the number of columns won't change.
687 if (conn->col_count == 0) {
688 conn->col_count = sql_num_fields(handle, config);
689 if (conn->col_count == 0) return RLM_SQL_ERROR;
692 MEM(row = handle->row = talloc_zero_array(handle->conn, char *, conn->col_count + 1));
694 for (i = 0; i < conn->col_count; i++) {
695 switch (sqlite3_column_type(conn->statement, i)) {
697 MEM(row[i] = talloc_typed_asprintf(row, "%d", sqlite3_column_int(conn->statement, i)));
701 MEM(row[i] = talloc_typed_asprintf(row, "%f", sqlite3_column_double(conn->statement, i)));
707 p = (char const *) sqlite3_column_text(conn->statement, i);
709 if (p) MEM(row[i] = talloc_typed_strdup(row, p));
718 p = sqlite3_column_blob(conn->statement, i);
720 len = sqlite3_column_bytes(conn->statement, i);
722 MEM(row[i] = talloc_zero_array(row, char, len + 1));
723 memcpy(row[i], p, len);
736 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
738 rlm_sql_sqlite_conn_t *conn = handle->conn;
740 if (conn->statement) {
741 TALLOC_FREE(handle->row);
743 (void) sqlite3_finalize(conn->statement);
744 conn->statement = NULL;
749 * There's no point in checking the code returned by finalize
750 * as it'll have already been encountered elsewhere in the code.
752 * It's just the last error that occurred processing the
758 /** Retrieves any errors associated with the connection handle
760 * @note Caller will free any memory allocated in ctx.
762 * @param ctx to allocate temporary error buffers in.
763 * @param out Array of sql_log_entrys to fill.
764 * @param outlen Length of out array.
765 * @param handle rlm_sql connection handle.
766 * @param config rlm_sql config.
767 * @return number of errors written to the sql_log_entry array.
769 static size_t sql_error(UNUSED TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
770 rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
772 rlm_sql_sqlite_conn_t *conn = handle->conn;
775 rad_assert(outlen > 0);
777 error = sqlite3_errmsg(conn->db);
778 if (!error) return 0;
786 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
788 return sql_free_result(handle, config);
791 static int sql_affected_rows(rlm_sql_handle_t *handle,
792 UNUSED rlm_sql_config_t *config)
794 rlm_sql_sqlite_conn_t *conn = handle->conn;
796 if (conn->db) return sqlite3_changes(conn->db);
802 /* Exported to rlm_sql */
803 extern rlm_sql_module_t rlm_sql_sqlite;
804 rlm_sql_module_t rlm_sql_sqlite = {
805 .name = "rlm_sql_sqlite",
806 .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY,
807 .mod_instantiate = mod_instantiate,
808 .sql_socket_init = sql_socket_init,
809 .sql_query = sql_query,
810 .sql_select_query = sql_select_query,
811 .sql_num_fields = sql_num_fields,
812 .sql_num_rows = sql_num_rows,
813 .sql_affected_rows = sql_affected_rows,
814 .sql_fetch_row = sql_fetch_row,
815 .sql_fields = sql_fields,
816 .sql_free_result = sql_free_result,
817 .sql_error = sql_error,
818 .sql_finish_query = sql_finish_query,
819 .sql_finish_select_query = sql_finish_query