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