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