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