src/modules/rlm_sql/drivers/rlm_sql_sqlite/rlm_sql_sqlite.c

   1 /*
   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.
   6  *
   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.
  11  *
  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
  15  */
  16
  17 /**
  18  * $Id$
  19  * @file rlm_sql_sqlite.c
  20  * @brief SQLite driver.
  21  *
  22  * @copyright 2013 Network RADIUS SARL <info@networkradius.com>
  23  * @copyright 2007 Apple Inc.
  24  */
  25 RCSID("$Id$")
  26
  27 #include <freeradius-devel/radiusd.h>
  28 #include <freeradius-devel/rad_assert.h>
  29
  30 #include <fcntl.h>
  31 #include <sys/stat.h>
  32
  33 #include <sqlite3.h>
  34
  35 #include "rlm_sql.h"
  36 #include "config.h"
  37
  38 #define BOOTSTRAP_MAX (1048576 * 10)
  39
  40 /*
  41  *      Allow us to use versions < 3.6.0 beta0
  42  */
  43 #ifndef SQLITE_OPEN_NOMUTEX
  44 #  define SQLITE_OPEN_NOMUTEX 0
  45 #endif
  46
  47 #ifndef HAVE_SQLITE3_INT64
  48 typedef sqlite3_int64 sqlite_int64
  49 #endif
  50
  51 typedef struct rlm_sql_sqlite_conn {
  52         sqlite3 *db;
  53         sqlite3_stmt *statement;
  54         int col_count;
  55 } rlm_sql_sqlite_conn_t;
  56
  57 typedef struct rlm_sql_sqlite_config {
  58         char const *filename;
  59 } rlm_sql_sqlite_config_t;
  60
  61 static const CONF_PARSER driver_config[] = {
  62         { "filename", FR_CONF_OFFSET(PW_TYPE_FILE_OUTPUT | PW_TYPE_REQUIRED, rlm_sql_sqlite_config_t, filename), NULL },
  63
  64         {NULL, -1, 0, NULL, NULL}
  65 };
  66
  67 static sql_rcode_t sql_check_error(sqlite3 *db)
  68 {
  69         int error = sqlite3_errcode(db);
  70         switch (error) {
  71         /*
  72          *      Not errors
  73          */
  74         case SQLITE_OK:
  75         case SQLITE_DONE:
  76         case SQLITE_ROW:
  77                 return RLM_SQL_OK;
  78         /*
  79          *      User/transient errors
  80          */
  81         case SQLITE_ERROR:      /* SQL error or missing database */
  82         case SQLITE_FULL:
  83         case SQLITE_CONSTRAINT:
  84         case SQLITE_MISMATCH:
  85                 return RLM_SQL_ERROR;
  86
  87         /*
  88          *      Errors with the handle, that probably require reinitialisation
  89          */
  90         default:
  91                 ERROR("rlm_sql_sqlite: Handle is unusable, error (%d): %s", error, sqlite3_errmsg(db));
  92                 return RLM_SQL_RECONNECT;
  93         }
  94 }
  95
  96 #ifdef HAVE_SQLITE3_OPEN_V2
  97 static int sql_loadfile(TALLOC_CTX *ctx, sqlite3 *db, char const *filename)
  98 {
  99         ssize_t len;
 100         char *buffer;
 101         char *p, *q, *s;
 102         int cl;
 103         FILE *f;
 104         struct stat finfo;
 105
 106         int status;
 107         sqlite3_stmt *statement;
 108         char const *z_tail;
 109
 110         INFO("rlm_sql_sqlite: Executing SQL statements from file \"%s\"", filename);
 111
 112         f = fopen(filename, "r");
 113         if (!f) {
 114                 ERROR("rlm_sql_sqlite: Failed opening SQL file \"%s\": %s", filename,
 115                        fr_syserror(errno));
 116
 117                 return -1;
 118         }
 119
 120         if (fstat(fileno(f), &finfo) < 0) {
 121                 ERROR("rlm_sql_sqlite: Failed stating SQL file \"%s\": %s", filename,
 122                        fr_syserror(errno));
 123
 124                 fclose(f);
 125
 126                 return -1;
 127         }
 128
 129         if (finfo.st_size > BOOTSTRAP_MAX) {
 130                 too_big:
 131                 ERROR("rlm_sql_sqlite: Size of SQL (%zu) file exceeds limit (%uk)",
 132                        (size_t) finfo.st_size / 1024, BOOTSTRAP_MAX / 1024);
 133
 134                 fclose(f);
 135
 136                 return -1;
 137         }
 138
 139         MEM(buffer = talloc_array(ctx, char, finfo.st_size + 1));
 140         len = fread(buffer, sizeof(char), finfo.st_size + 1, f);
 141         if (len > finfo.st_size) {
 142                 talloc_free(buffer);
 143                 goto too_big;
 144         }
 145
 146         if (!len) {
 147                 if (ferror(f)) {
 148                         ERROR("rlm_sql_sqlite: Error reading SQL file: %s", fr_syserror(errno));
 149
 150                         fclose(f);
 151                         talloc_free(buffer);
 152
 153                         return -1;
 154                 }
 155
 156                 DEBUG("rlm_sql_sqlite: Ignoring empty SQL file");
 157
 158                 fclose(f);
 159                 talloc_free(buffer);
 160
 161                 return 0;
 162         }
 163
 164         buffer[len] = '\0';
 165         fclose(f);
 166
 167         /*
 168          *      Check if input data is UTF-8.  Allow CR/LF \t, too.
 169          */
 170         for (p = buffer; p < (buffer + len); p += cl) {
 171                 if (*p < ' ') {
 172                         if ((*p != 0x0a) && (*p != 0x0d) && (*p != '\t')) break;
 173                         cl = 1;
 174                 } else {
 175                         cl = fr_utf8_char((uint8_t *) p);
 176                         if (!cl) break;
 177                 }
 178         }
 179
 180         if ((p - buffer) != len) {
 181                 ERROR("rlm_sql_sqlite: Bootstrap file contains non-UTF8 char at offset %zu", p - buffer);
 182                 talloc_free(buffer);
 183                 return -1;
 184         }
 185
 186         /*
 187          *      Statement delimiter is ;\n
 188          */
 189         s = p = buffer;
 190         while ((q = strchr(p, ';'))) {
 191                 if (q[1] != '\n') {
 192                         p = q + 1;
 193                         continue;
 194                 }
 195
 196                 *q = '\0';
 197
 198 #ifdef HAVE_SQLITE3_PREPARE_V2
 199                 (void) sqlite3_prepare_v2(db, s, len, &statement, &z_tail);
 200 #else
 201                 (void) sqlite3_prepare(db, s, len, &>statement, &z_tail);
 202 #endif
 203                 if (sql_check_error(db) != RLM_SQL_OK) {
 204                         talloc_free(buffer);
 205                         return -1;
 206                 }
 207
 208                 (void) sqlite3_step(statement);
 209                 status = sql_check_error(db);
 210
 211                 (void) sqlite3_finalize(statement);
 212                 if ((status != RLM_SQL_OK) || sql_check_error(db)) {
 213                         talloc_free(buffer);
 214                         return -1;
 215                 }
 216
 217                 p = s = q + 1;
 218         }
 219
 220         talloc_free(buffer);
 221         return 0;
 222 }
 223 #endif
 224
 225 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
 226 {
 227         static bool version_done;
 228
 229         bool exists;
 230         rlm_sql_sqlite_config_t *driver;
 231         struct stat buf;
 232
 233         if (!version_done) {
 234                 version_done = true;
 235
 236                 if (sqlite3_libversion_number() != SQLITE_VERSION_NUMBER) {
 237                         WARN("rlm_sql_sqlite: libsqlite version changed since the server was built");
 238                         WARN("rlm_sql_sqlite: linked: %s built: %s", sqlite3_libversion(), SQLITE_VERSION);
 239                 }
 240                 INFO("rlm_sql_sqlite: libsqlite version: %s", sqlite3_libversion());
 241         }
 242
 243         MEM(driver = config->driver = talloc_zero(config, rlm_sql_sqlite_config_t));
 244         if (cf_section_parse(conf, driver, driver_config) < 0) {
 245                 return -1;
 246         }
 247         if (!driver->filename) {
 248                 MEM(driver->filename = talloc_typed_asprintf(driver, "%s/%s", get_radius_dir(), config->sql_db));
 249         }
 250
 251         if (stat(driver->filename, &buf) == 0) {
 252                 exists = true;
 253         } else if (errno == ENOENT) {
 254                 exists = false;
 255         } else {
 256                 ERROR("rlm_sql_sqlite: Database exists, but couldn't be opened: %s", fr_syserror(errno));
 257                 return -1;
 258         }
 259
 260         if (cf_pair_find(conf, "bootstrap") && !exists) {
 261 #  ifdef HAVE_SQLITE3_OPEN_V2
 262                 int status;
 263                 int ret;
 264                 char const *p;
 265                 char *buff;
 266                 sqlite3 *db = NULL;
 267                 CONF_PAIR *cp;
 268
 269                 INFO("rlm_sql_sqlite: Database doesn't exist, creating it and loading schema");
 270
 271                 p = strrchr(driver->filename, '/');
 272                 if (p) {
 273                         size_t len = (p - driver->filename) + 1;
 274
 275                         buff = talloc_array(conf, char, len);
 276                         strlcpy(buff, driver->filename, len);
 277                 } else {
 278                         MEM(buff = talloc_typed_strdup(conf, driver->filename));
 279                 }
 280
 281                 ret = rad_mkdir(buff, 0700, -1, -1);
 282                 talloc_free(buff);
 283                 if (ret < 0) {
 284                         ERROR("rlm_sql_sqlite: Failed creating directory for SQLite database: %s", fr_syserror(errno));
 285
 286                         return -1;
 287                 };
 288
 289                 status = sqlite3_open_v2(driver->filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
 290                 if (!db) {
 291 #    ifdef HAVE_SQLITE3_ERRSTR
 292                         ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database: %s",
 293                               sqlite3_errstr(status));
 294 #    else
 295                         ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database, got code (%i)",
 296                               status);
 297 #    endif
 298
 299                         goto unlink;
 300                 }
 301
 302                 if (sql_check_error(db) != RLM_SQL_OK) {
 303                         (void) sqlite3_close(db);
 304
 305                         goto unlink;
 306                 }
 307
 308                 /*
 309                  *      Execute multiple bootstrap SQL files in order
 310                  */
 311                 for (cp = cf_pair_find(conf, "bootstrap");
 312                      cp;
 313                      cp = cf_pair_find_next(conf, cp, "bootstrap")) {
 314                         p = cf_pair_value(cp);
 315                         if (!p) continue;
 316
 317                         ret = sql_loadfile(conf, db, p);
 318                         if (ret < 0) goto unlink;
 319                 }
 320
 321                 status = sqlite3_close(db);
 322                 if (status != SQLITE_OK) {
 323                 /*
 324                  *      Safer to use sqlite3_errstr here, just in case the handle is in a weird state
 325                  */
 326 #  ifdef HAVE_SQLITE3_ERRSTR
 327                         ERROR("rlm_sql_sqlite: Error closing SQLite handle: %s", sqlite3_errstr(status));
 328 #  else
 329                         ERROR("rlm_sql_sqlite: Error closing SQLite handle, got code (%i)", status);
 330 #  endif
 331
 332                         goto unlink;
 333                 }
 334
 335                 if (ret < 0) {
 336                 unlink:
 337                         if ((unlink(driver->filename) < 0) && (errno != ENOENT)) {
 338                                 ERROR("rlm_sql_sqlite: Error removing partially initialised database: %s",
 339                                       fr_syserror(errno));
 340                         }
 341                         return -1;
 342                 }
 343 #else
 344                 WARN("rlm_sql_sqlite: sqlite3_open_v2() not available, cannot bootstrap database. "
 345                        "Upgrade to SQLite >= 3.5.1 if you need this functionality");
 346 #endif
 347         }
 348
 349         return 0;
 350 }
 351
 352 static int _sql_socket_destructor(rlm_sql_sqlite_conn_t *conn)
 353 {
 354         int status = 0;
 355
 356         DEBUG2("rlm_sql_sqlite: Socket destructor called, closing socket");
 357
 358         if (conn->db) {
 359                 status = sqlite3_close(conn->db);
 360                 if (status != SQLITE_OK) {
 361                         WARN("rlm_sql_sqlite: Got SQLite error code (%u) when closing socket", status);
 362                 }
 363         }
 364
 365         return 0;
 366 }
 367
 368 static void _sql_greatest(sqlite3_context *ctx, int num_values, sqlite3_value **values)
 369 {
 370         int i;
 371         sqlite3_int64 value, max = 0;
 372
 373         for (i = 0; i < num_values; i++) {
 374                 value = sqlite3_value_int64(values[i]);
 375                 if (value > max) {
 376                         max = value;
 377                 }
 378         }
 379
 380         sqlite3_result_int64(ctx, max);
 381 }
 382
 383 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 384 {
 385         rlm_sql_sqlite_conn_t *conn;
 386         rlm_sql_sqlite_config_t *driver = config->driver;
 387
 388         int status;
 389
 390         MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_sqlite_conn_t));
 391         talloc_set_destructor(conn, _sql_socket_destructor);
 392
 393         INFO("rlm_sql_sqlite: Opening SQLite database \"%s\"", driver->filename);
 394 #ifdef HAVE_SQLITE3_OPEN_V2
 395         status = sqlite3_open_v2(driver->filename, &(conn->db), SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX, NULL);
 396 #else
 397         status = sqlite3_open(driver->filename, &(conn->db));
 398 #endif
 399         if (!conn->db) {
 400 #ifdef HAVE_SQLITE3_ERRSTR
 401                 ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite: %s", sqlite3_errstr(status));
 402 #else
 403                 ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database error code (%i)",
 404                       status);
 405 #endif
 406
 407                 return RLM_SQL_ERROR;
 408         }
 409
 410         if (sql_check_error(conn->db) != RLM_SQL_OK) return RLM_SQL_ERROR;
 411
 412         /*
 413          *      Enable extended return codes for extra debugging info.
 414          */
 415 #ifdef HAVE_SQLITE3_EXTENDED_RESULT_CODES
 416         (void) sqlite3_extended_result_codes(conn->db, 1);
 417 #endif
 418         if (sql_check_error(conn->db) != RLM_SQL_OK) return RLM_SQL_ERROR;
 419
 420 #ifdef HAVE_SQLITE3_CREATE_FUNCTION_V2
 421         status = sqlite3_create_function_v2(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
 422                                             _sql_greatest, NULL, NULL, NULL);
 423 #else
 424         status = sqlite3_create_function(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
 425                                          _sql_greatest, NULL, NULL);
 426 #endif
 427         if (status != SQLITE_OK) {
 428                 ERROR("rlm_sql_sqlite: Failed registering 'GREATEST' sql function: %s", sqlite3_errmsg(conn->db));
 429         }
 430
 431         return RLM_SQL_OK;
 432 }
 433
 434 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
 435 {
 436         rlm_sql_sqlite_conn_t *conn = handle->conn;
 437         char const *z_tail;
 438
 439 #ifdef HAVE_SQLITE3_PREPARE_V2
 440         (void) sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
 441 #else
 442         (void) sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
 443 #endif
 444
 445         conn->col_count = 0;
 446
 447         return sql_check_error(conn->db);
 448 }
 449
 450
 451 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
 452 {
 453         int status;
 454         rlm_sql_sqlite_conn_t *conn = handle->conn;
 455         char const *z_tail;
 456
 457 #ifdef HAVE_SQLITE3_PREPARE_V2
 458         status = sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
 459 #else
 460         status = sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
 461 #endif
 462         if (status != SQLITE_OK) return sql_check_error(conn->db);
 463
 464         (void) sqlite3_step(conn->statement);
 465
 466         return sql_check_error(conn->db);
 467 }
 468
 469 static sql_rcode_t sql_store_result(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 470 {
 471         return 0;
 472 }
 473
 474 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 475 {
 476         rlm_sql_sqlite_conn_t *conn = handle->conn;
 477
 478         if (conn->statement) {
 479                 return sqlite3_column_count(conn->statement);
 480         }
 481
 482         return 0;
 483 }
 484
 485 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 486 {
 487         rlm_sql_sqlite_conn_t *conn = handle->conn;
 488
 489         if (conn->statement) {
 490                 return sqlite3_data_count(conn->statement);
 491         }
 492
 493         return 0;
 494 }
 495
 496 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 497 {
 498         int status;
 499         rlm_sql_sqlite_conn_t *conn = handle->conn;
 500
 501         int i = 0;
 502
 503         char **row;
 504
 505         /*
 506          *      Executes the SQLite query and interates over the results
 507          */
 508         status = sqlite3_step(conn->statement);
 509
 510         /*
 511          *      Error getting next row
 512          */
 513         if (sql_check_error(conn->db) != RLM_SQL_OK) return RLM_SQL_ERROR;
 514
 515         /*
 516          *      No more rows to process (were done)
 517          */
 518         if (status == SQLITE_DONE) {
 519                 return 1;
 520         }
 521
 522         /*
 523          *      We only need to do this once per result set, because
 524          *      the number of columns won't change.
 525          */
 526         if (conn->col_count == 0) {
 527                 conn->col_count = sql_num_fields(handle, config);
 528                 if (conn->col_count == 0) return RLM_SQL_ERROR;
 529         }
 530
 531         /*
 532          *      Free the previous result (also gets called on finish_query)
 533          */
 534         talloc_free(handle->row);
 535
 536         MEM(row = handle->row = talloc_zero_array(handle->conn, char *, conn->col_count + 1));
 537
 538         for (i = 0; i < conn->col_count; i++) {
 539                 switch (sqlite3_column_type(conn->statement, i)) {
 540                 case SQLITE_INTEGER:
 541                         MEM(row[i] = talloc_typed_asprintf(row, "%d", sqlite3_column_int(conn->statement, i)));
 542                         break;
 543
 544                 case SQLITE_FLOAT:
 545                         MEM(row[i] = talloc_typed_asprintf(row, "%f", sqlite3_column_double(conn->statement, i)));
 546                         break;
 547
 548                 case SQLITE_TEXT:
 549                         {
 550                                 char const *p;
 551                                 p = (char const *) sqlite3_column_text(conn->statement, i);
 552
 553                                 if (p) {
 554                                         MEM(row[i] = talloc_typed_strdup(row, p));
 555                                 }
 556                         }
 557                         break;
 558
 559                 case SQLITE_BLOB:
 560                         {
 561                                 uint8_t const *p;
 562                                 size_t len;
 563
 564                                 p = sqlite3_column_blob(conn->statement, i);
 565                                 if (p) {
 566                                         len = sqlite3_column_bytes(conn->statement, i);
 567
 568                                         MEM(row[i] = talloc_zero_array(row, char, len + 1));
 569                                         memcpy(row[i], p, len);
 570                                 }
 571                         }
 572                         break;
 573
 574                 default:
 575                         break;
 576                 }
 577         }
 578
 579         return 0;
 580 }
 581
 582 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle,
 583                            UNUSED rlm_sql_config_t *config)
 584 {
 585         rlm_sql_sqlite_conn_t *conn = handle->conn;
 586
 587         if (conn->statement) {
 588                 TALLOC_FREE(handle->row);
 589
 590                 (void) sqlite3_finalize(conn->statement);
 591                 conn->statement = NULL;
 592                 conn->col_count = 0;
 593         }
 594
 595         /*
 596          *      There's no point in checking the code returned by finalize
 597          *      as it'll have already been encountered elsewhere in the code.
 598          *
 599          *      It's just the last error that occurred processing the
 600          *      statement.
 601          */
 602         return 0;
 603 }
 604
 605 /** Retrieves any errors associated with the connection handle
 606  *
 607  * @note Caller will free any memory allocated in ctx.
 608  *
 609  * @param ctx to allocate temporary error buffers in.
 610  * @param out Array of sql_log_entrys to fill.
 611  * @param outlen Length of out array.
 612  * @param handle rlm_sql connection handle.
 613  * @param config rlm_sql config.
 614  * @return number of errors written to the sql_log_entry array.
 615  */
 616 static size_t sql_error(UNUSED TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
 617                         rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 618 {
 619         rlm_sql_sqlite_conn_t *conn = handle->conn;
 620         char const *error;
 621
 622         rad_assert(outlen > 0);
 623
 624         error = sqlite3_errmsg(conn->db);
 625         if (!error) return 0;
 626
 627         out[0].type = L_ERR;
 628         out[0].msg = error;
 629
 630         return 1;
 631 }
 632
 633 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 634 {
 635         return sql_free_result(handle, config);
 636 }
 637
 638 static int sql_affected_rows(rlm_sql_handle_t *handle,
 639                              UNUSED rlm_sql_config_t *config)
 640 {
 641         rlm_sql_sqlite_conn_t *conn = handle->conn;
 642
 643         if (conn->db) {
 644                 return sqlite3_changes(conn->db);
 645         }
 646
 647         return -1;
 648 }
 649
 650
 651 /* Exported to rlm_sql */
 652 extern rlm_sql_module_t rlm_sql_sqlite;
 653 rlm_sql_module_t rlm_sql_sqlite = {
 654         .name                           = "rlm_sql_sqlite",
 655         .mod_instantiate                = mod_instantiate,
 656         .sql_socket_init                = sql_socket_init,
 657         .sql_query                      = sql_query,
 658         .sql_select_query               = sql_select_query,
 659         .sql_store_result               = sql_store_result,
 660         .sql_num_fields                 = sql_num_fields,
 661         .sql_num_rows                   = sql_num_rows,
 662         .sql_affected_rows              = sql_affected_rows,
 663         .sql_fetch_row                  = sql_fetch_row,
 664         .sql_free_result                = sql_free_result,
 665         .sql_error              = sql_error,
 666         .sql_finish_query               = sql_finish_query,
 667         .sql_finish_select_query        = sql_finish_query
 668 };