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 typedef struct rlm_sql_sqlite_conn {
  47         sqlite3 *db;
  48         sqlite3_stmt *statement;
  49         int col_count;
  50 } rlm_sql_sqlite_conn_t;
  51
  52 typedef struct rlm_sql_sqlite_config {
  53         char const *filename;
  54         char const *bootstrap;
  55 } rlm_sql_sqlite_config_t;
  56
  57 static const CONF_PARSER driver_config[] = {
  58         {"filename", PW_TYPE_FILE_OUTPUT | PW_TYPE_REQUIRED,
  59          offsetof(rlm_sql_sqlite_config_t, filename), NULL, NULL},
  60         {"bootstrap", PW_TYPE_FILE_INPUT,
  61          offsetof(rlm_sql_sqlite_config_t, bootstrap), NULL, 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_SQLITE_V2_API
  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                        strerror(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                        strerror(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", strerror(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                 (void) sqlite3_prepare_v2(db, s, len, &statement, &z_tail);
 200                 if (sql_check_error(db)) {
 201                         talloc_free(buffer);
 202                         return -1;
 203                 }
 204
 205                 (void) sqlite3_step(statement);
 206                 status = sql_check_error(db);
 207
 208                 (void) sqlite3_finalize(statement);
 209                 if (status || sql_check_error(db)) {
 210                         talloc_free(buffer);
 211                         return -1;
 212                 }
 213
 214                 p = s = q + 1;
 215         }
 216
 217         talloc_free(buffer);
 218         return 0;
 219 }
 220 #endif
 221
 222 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
 223 {
 224         rlm_sql_sqlite_config_t *driver;
 225         int exists;
 226
 227         if (sqlite3_libversion_number() != SQLITE_VERSION_NUMBER) {
 228                 DEBUG2("rlm_sql_sqlite: SQLite library version (%s) is different from the version the server was "
 229                        "originally built against (%s), this may cause issues",
 230                        sqlite3_libversion(), SQLITE_VERSION);
 231         }
 232
 233         MEM(driver = config->driver = talloc_zero(config, rlm_sql_sqlite_config_t));
 234
 235         if (cf_section_parse(conf, driver, driver_config) < 0) {
 236                 return -1;
 237         }
 238
 239         INFO("rlm_sql_sqlite: SQLite library version: %s", sqlite3_libversion());
 240         if (!driver->filename) {
 241                 MEM(driver->filename = talloc_asprintf(driver, "%s/%s", radius_dir, config->sql_db));
 242         }
 243
 244         exists = rad_file_exists(driver->filename);
 245         if (exists < 0) {
 246                 ERROR("rlm_sql_sqlite: Database exists, but couldn't be opened: %s", strerror(errno));
 247
 248                 return -1;
 249         }
 250
 251         if (driver->bootstrap && !exists) {
 252 #ifdef HAVE_SQLITE_V2_API
 253                 int status;
 254                 int ret;
 255                 char *p;
 256                 char *buff;
 257                 sqlite3 *db = NULL;
 258
 259                 INFO("rlm_sql_sqlite: Database doesn't exist, creating it and loading schema");
 260
 261                 p = strrchr(driver->filename, '/');
 262                 if (p) {
 263                         size_t len = (p - driver->filename) + 1;
 264
 265                         buff = talloc_array(conf, char, len);
 266                         strlcpy(buff, driver->filename, len);
 267                 } else {
 268                         MEM(buff = talloc_strdup(conf, driver->filename));
 269                 }
 270
 271                 if (rad_mkdir(buff, 0700) < 0) {
 272                         ERROR("rlm_sql_sqlite: Failed creating directory for SQLite database");
 273
 274                         talloc_free(buff);
 275
 276                         return -1;
 277                 }
 278
 279                 talloc_free(buff);
 280
 281                 status = sqlite3_open_v2(driver->filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
 282                 if (!db) {
 283                         ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database, error "
 284                                "code (%u)", status);
 285
 286                         goto unlink;
 287                 }
 288
 289                 if (sql_check_error(db)) {
 290                         (void) sqlite3_close(db);
 291
 292                         goto unlink;
 293                 }
 294
 295                 ret = sql_loadfile(conf, db, driver->bootstrap);
 296                 status = sqlite3_close(db);
 297                 if (status != SQLITE_OK) {
 298                         ERROR("rlm_sql_sqlite: Error closing SQLite handle, error code (%u)", status);
 299                         goto unlink;
 300                 }
 301                 if (ret < 0) {
 302                         unlink:
 303                         if (unlink(driver->filename) < 0) {
 304                                 ERROR("rlm_sql_sqlite: Error removing partially initialised database: %s",
 305                                        strerror(errno));
 306                         }
 307                         return -1;
 308                 }
 309 #else
 310                 WDEBUG("rlm_sql_sqlite: sqlite3_open_v2() not available, cannot bootstrap database. "
 311                        "Upgrade to SQLite >= 3.5.1 if you need this functionality");
 312 #endif
 313         }
 314
 315         return 0;
 316 }
 317
 318 static int sql_socket_destructor(void *c)
 319 {
 320         int status = 0;
 321         rlm_sql_sqlite_conn_t * conn = c;
 322
 323         DEBUG2("rlm_sql_sqlite: Socket destructor called, closing socket");
 324
 325         if (conn->db) {
 326                 status = sqlite3_close(conn->db);
 327                 if (status != SQLITE_OK) {
 328                         WDEBUG("rlm_sql_sqlite: Got SQLite error code (%u) when closing socket", status);
 329                 }
 330         }
 331
 332         return 0;
 333 }
 334
 335 static void _sql_greatest(sqlite3_context *ctx, int num_values, sqlite3_value **values)
 336 {
 337         int i;
 338         sqlite3_int64 value, max = 0;
 339
 340         for (i = 0; i < num_values; i++) {
 341                 value = sqlite3_value_int64(values[i]);
 342                 if (value > max) {
 343                         max = value;
 344                 }
 345         }
 346
 347         sqlite3_result_int64(ctx, max);
 348 }
 349
 350 static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 351 {
 352         rlm_sql_sqlite_conn_t *conn;
 353         rlm_sql_sqlite_config_t *driver = config->driver;
 354
 355         int status;
 356
 357         MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_sqlite_conn_t));
 358         talloc_set_destructor((void *) conn, sql_socket_destructor);
 359
 360         INFO("rlm_sql_sqlite: Opening SQLite database \"%s\"", driver->filename);
 361
 362 #ifdef HAVE_SQLITE_V2_API
 363         status = sqlite3_open_v2(driver->filename, &(conn->db), SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX, NULL);
 364 #else
 365         status = sqlite3_open(driver->filename, &(conn->db));
 366 #endif
 367         if (!conn->db) {
 368                 ERROR("rlm_sql_sqlite: Failed creating opening/creating SQLite database error code (%u)",
 369                        status);
 370
 371                 return -1;
 372         }
 373
 374         if (sql_check_error(conn->db)) {
 375                 return -1;
 376         }
 377
 378         /*
 379          *      Enable extended return codes for extra debugging info.
 380          */
 381         status = sqlite3_extended_result_codes(conn->db, 1);
 382
 383         if (sql_check_error(conn->db)) {
 384                 return -1;
 385         }
 386
 387 #ifdef HAVE_SQLITE_V2_API
 388         status = sqlite3_create_function_v2(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
 389                                             _sql_greatest, NULL, NULL, NULL);
 390 #else
 391         status = sqlite3_create_function(conn->db, "GREATEST", -1, SQLITE_ANY, NULL,
 392                                          _sql_greatest, NULL, NULL);
 393 #endif
 394         if (status != SQLITE_OK) {
 395                 ERROR("rlm_sql_sqlite: Failed registering 'GREATEST' sql function: %s", sqlite3_errmsg(conn->db));
 396         }
 397
 398         return 0;
 399 }
 400
 401 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
 402 {
 403         rlm_sql_sqlite_conn_t *conn = handle->conn;
 404         char const *z_tail;
 405
 406 #ifdef HAVE_SQLITE_V2_API
 407         (void) sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
 408 #else
 409         (void) sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
 410 #endif
 411
 412         conn->col_count = 0;
 413
 414         return sql_check_error(conn->db);
 415 }
 416
 417
 418 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config, char const *query)
 419 {
 420         int status;
 421         rlm_sql_sqlite_conn_t *conn = handle->conn;
 422         char const *z_tail;
 423
 424 #ifdef HAVE_SQLITE_V2_API
 425         status = sqlite3_prepare_v2(conn->db, query, strlen(query), &conn->statement, &z_tail);
 426 #else
 427         status = sqlite3_prepare(conn->db, query, strlen(query), &conn->statement, &z_tail);
 428 #endif
 429         if (status != SQLITE_OK) {
 430                 return sql_check_error(conn->db);
 431         }
 432
 433         (void) sqlite3_step(conn->statement);
 434
 435         return sql_check_error(conn->db);
 436 }
 437
 438 static sql_rcode_t sql_store_result(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 439 {
 440         return 0;
 441 }
 442
 443 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 444 {
 445         rlm_sql_sqlite_conn_t *conn = handle->conn;
 446
 447         if (conn->statement) {
 448                 return sqlite3_column_count(conn->statement);
 449         }
 450
 451         return 0;
 452 }
 453
 454 static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 455 {
 456         rlm_sql_sqlite_conn_t *conn = handle->conn;
 457
 458         if (conn->statement) {
 459                 return sqlite3_data_count(conn->statement);
 460         }
 461
 462         return 0;
 463 }
 464
 465 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 466 {
 467         int status;
 468         rlm_sql_sqlite_conn_t *conn = handle->conn;
 469
 470         int i = 0;
 471
 472         char **row;
 473
 474         /*
 475          *      Executes the SQLite query and interates over the results
 476          */
 477         status = sqlite3_step(conn->statement);
 478
 479         /*
 480          *      Error getting next row
 481          */
 482         if (sql_check_error(conn->db)) {
 483                 return -1;
 484         }
 485
 486         /*
 487          *      No more rows to process (were done)
 488          */
 489         if (status == SQLITE_DONE) {
 490                 return 1;
 491         }
 492
 493         /*
 494          *      We only need to do this once per result set, because
 495          *      the number of columns won't change.
 496          */
 497         if (conn->col_count == 0) {
 498                 conn->col_count = sql_num_fields(handle, config);
 499                 if (conn->col_count == 0) {
 500                         return -1;
 501                 }
 502         }
 503
 504         /*
 505          *      Free the previous result (also gets called on finish_query)
 506          */
 507         talloc_free(handle->row);
 508
 509         MEM(row = handle->row = talloc_zero_array(handle->conn, char *, conn->col_count + 1));
 510
 511         for (i = 0; i < conn->col_count; i++) {
 512                 switch (sqlite3_column_type(conn->statement, i)) {
 513                 case SQLITE_INTEGER:
 514                         MEM(row[i] = talloc_asprintf(row, "%d", sqlite3_column_int(conn->statement, i)));
 515                         break;
 516
 517                 case SQLITE_FLOAT:
 518                         MEM(row[i] = talloc_asprintf(row, "%f", sqlite3_column_double(conn->statement, i)));
 519                         break;
 520
 521                 case SQLITE_TEXT:
 522                         {
 523                                 char const *p;
 524                                 p = (char const *) sqlite3_column_text(conn->statement, i);
 525
 526                                 if (p) {
 527                                         MEM(row[i] = talloc_strdup(row, p));
 528                                 }
 529                         }
 530                         break;
 531
 532                 case SQLITE_BLOB:
 533                         {
 534                                 uint8_t const *p;
 535                                 size_t len;
 536
 537                                 p = sqlite3_column_blob(conn->statement, i);
 538                                 if (p) {
 539                                         len = sqlite3_column_bytes(conn->statement, i);
 540
 541                                         MEM(row[i] = talloc_zero_array(row, char, len + 1));
 542                                         memcpy(row[i], p, len);
 543                                 }
 544                         }
 545                         break;
 546
 547                 default:
 548                         break;
 549                 }
 550         }
 551
 552         return 0;
 553 }
 554
 555 static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle,
 556                            UNUSED rlm_sql_config_t *config)
 557 {
 558         rlm_sql_sqlite_conn_t *conn = handle->conn;
 559
 560         if (conn->statement) {
 561                 TALLOC_FREE(handle->row);
 562
 563                 (void) sqlite3_finalize(conn->statement);
 564                 conn->statement = NULL;
 565                 conn->col_count = 0;
 566         }
 567
 568         /*
 569          *      There's no point in checking the code returned by finalize
 570          *      as it'll have already been encountered elsewhere in the code.
 571          *
 572          *      It's just the last error that occurred processing the
 573          *      statement.
 574          */
 575         return 0;
 576 }
 577
 578 static char const *sql_error(rlm_sql_handle_t *handle,
 579                              UNUSED rlm_sql_config_t *config)
 580 {
 581         rlm_sql_sqlite_conn_t *conn = handle->conn;
 582
 583         if (conn->db) {
 584                 return sqlite3_errmsg(conn->db);
 585         }
 586
 587         return "Invalid handle";
 588 }
 589
 590 static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle,
 591                             UNUSED rlm_sql_config_t *config)
 592 {
 593         return sql_free_result(handle, config);
 594 }
 595
 596 static int sql_affected_rows(rlm_sql_handle_t *handle,
 597                              UNUSED rlm_sql_config_t *config)
 598 {
 599         rlm_sql_sqlite_conn_t *conn = handle->conn;
 600
 601         if (conn->db) {
 602                 return sqlite3_changes(conn->db);
 603         }
 604
 605         return -1;
 606 }
 607
 608
 609 /* Exported to rlm_sql */
 610 rlm_sql_module_t rlm_sql_sqlite = {
 611         "rlm_sql_sqlite",
 612         mod_instantiate,
 613         sql_socket_init,
 614         sql_query,
 615         sql_select_query,
 616         sql_store_result,
 617         sql_num_fields,
 618         sql_num_rows,
 619         sql_fetch_row,
 620         sql_free_result,
 621         sql_error,
 622         sql_finish_query,
 623         sql_finish_query,
 624         sql_affected_rows
 625 };