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