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 #include <freeradius-devel/ident.h>
  26 RCSID("$Id$")
  27
  28 #include <freeradius-devel/radiusd.h>
  29
  30 #include <sys/stat.h>
  31
  32 #include <sqlite3.h>
  33 #include <fcntl.h>
  34
  35 #include "rlm_sql.h"
  36
  37 #define BOOTSTRAP_MAX (1048576 * 10)
  38
  39 typedef struct rlm_sql_sqlite_conn {
  40         sqlite3 *db;
  41         sqlite3_stmt *statement;
  42         int col_count;
  43 } rlm_sql_sqlite_conn_t;
  44
  45 typedef struct rlm_sql_sqlite_config {
  46         const char *filename;
  47         const char *bootstrap;
  48 } rlm_sql_sqlite_config_t;
  49
  50 static const CONF_PARSER driver_config[] = {
  51         {"filename", PW_TYPE_STRING_PTR,
  52          offsetof(rlm_sql_sqlite_config_t, filename), NULL, NULL},
  53         {"bootstrap", PW_TYPE_STRING_PTR,
  54          offsetof(rlm_sql_sqlite_config_t, bootstrap), NULL, NULL},
  55
  56         {NULL, -1, 0, NULL, NULL}
  57 };
  58
  59 static int sql_check_error(sqlite3 *db)
  60 {
  61         int error = sqlite3_errcode(db);
  62         switch(error) {
  63         /*
  64          *      Not errors
  65          */
  66         case SQLITE_OK:
  67         case SQLITE_DONE:
  68         case SQLITE_ROW:
  69                 return 0;
  70         /*
  71          *      User/transient errors
  72          */
  73         case SQLITE_ERROR:      /* SQL error or missing database */
  74         case SQLITE_FULL:
  75         case SQLITE_CONSTRAINT:
  76         case SQLITE_MISMATCH:
  77                 radlog(L_ERR, "rlm_sql_sqlite: Error (%d): %s", error,
  78                        sqlite3_errmsg(db));
  79
  80                 return -1;
  81                 break;
  82
  83         /*
  84          *      Errors with the handle, that probably require reinitialisation
  85          */
  86         default:
  87                 radlog(L_ERR, "rlm_sql_sqlite: Handle is unusable, "
  88                        "error (%d): %s", error, sqlite3_errmsg(db));
  89                 return SQL_DOWN;
  90                 break;
  91         }
  92 }
  93
  94 static int sql_loadfile(sqlite3 *db, const char *filename)
  95 {
  96         FILE *f;
  97         struct stat finfo;
  98
  99         ssize_t len;
 100         char *buff;
 101         char *p, *q, *s;
 102
 103         int status;
 104         sqlite3_stmt *statement;
 105         const char *z_tail;
 106
 107         radlog(L_INFO, "rlm_sql_sqlite: Executing SQL statements from "
 108                "file \"%s\"", filename);
 109
 110         f = fopen(filename, "r");
 111         if (!f) {
 112                 radlog(L_ERR, "rlm_sql_sqlite: Failed opening SQL "
 113                        "file \"%s\": %s", filename,
 114                        strerror(errno));
 115
 116                 return -1;
 117         }
 118
 119         if (fstat(fileno(f), &finfo) < 0) {
 120                 radlog(L_ERR, "rlm_sql_sqlite: Failed stating SQL "
 121                        "file \"%s\": %s", filename,
 122                        strerror(errno));
 123
 124                 fclose(f);
 125
 126                 return -1;
 127         }
 128
 129         if (finfo.st_size > BOOTSTRAP_MAX) {
 130                 too_big:
 131                 radlog(L_ERR, "rlm_sql_sqlite: Size of SQL "
 132                        "(%zu) file exceeds limit (%uk)", (size_t) finfo.st_size / 1024,
 133                        BOOTSTRAP_MAX / 1024);
 134
 135                 fclose(f);
 136
 137                 return -1;
 138         }
 139
 140         MEM(buff = talloc_array(NULL, char, finfo.st_size + 1));
 141         len = fread(buff, sizeof(char), finfo.st_size + 1, f);
 142         if (len > finfo.st_size) {
 143                 talloc_free(buff);
 144
 145                 goto too_big;
 146         }
 147
 148         if (!len) {
 149                 if (ferror(f)) {
 150                         radlog(L_ERR, "rlm_sql_sqlite: Error reading SQL "
 151                                "file: %s", strerror(errno));
 152
 153                         fclose(f);
 154                         talloc_free(buff);
 155
 156                         return -1;
 157                 }
 158
 159                 radlog(L_DBG, "rlm_sql_sqlite: Ignoring empty SQL file");
 160
 161                 fclose(f);
 162                 talloc_free(buff);
 163
 164                 return 0;
 165         }
 166
 167         buff[len] = '\0';
 168
 169         fclose(f);
 170
 171         /*
 172          *      Statement delimiter is ;\n
 173          */
 174         p = s = buff;
 175         while ((q = strchr(p, ';'))) {
 176                 if (q[1] != '\n') {
 177                         p = q + 1;
 178                         continue;
 179                 }
 180
 181                 *q = '\0';
 182                 (void) sqlite3_prepare_v2(db, s, len, &statement, &z_tail);
 183                 if (sql_check_error(db)) {
 184                         talloc_free(buff);
 185                         return -1;
 186                 }
 187
 188                 (void) sqlite3_step(statement);
 189                 status = sql_check_error(db);
 190
 191                 (void) sqlite3_finalize(statement);
 192                 if (status || sql_check_error(db)) {
 193                         talloc_free(buff);
 194                         return -1;
 195                 }
 196
 197                 p = s = q + 1;
 198         }
 199
 200         talloc_free(buff);
 201         return 0;
 202 }
 203
 204 static int sql_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
 205 {
 206         rlm_sql_sqlite_config_t *driver;
 207         int exists;
 208
 209         MEM(driver = config->driver = talloc_zero(config,
 210                                                   rlm_sql_sqlite_config_t));
 211
 212         if (cf_section_parse(conf, driver, driver_config) < 0) {
 213                 return -1;
 214         }
 215
 216         if (!driver->filename) {
 217                 MEM(driver->filename = talloc_asprintf(driver, "%s/%s",
 218                                                        radius_dir,
 219                                                        config->sql_db));
 220         }
 221
 222         exists = rad_file_exists(driver->filename);
 223         if (exists < 0) {
 224                 radlog(L_ERR, "rlm_sql_sqlite: Database exists, but couldn't "
 225                        "be opened: %s", strerror(errno));
 226
 227                 return -1;
 228         }
 229
 230         if (driver->bootstrap && !exists) {
 231                 int status;
 232                 int ret;
 233                 char *p;
 234                 char *buff;
 235                 sqlite3 *db = NULL;
 236
 237                 radlog(L_INFO, "rlm_sql_sqlite: Database doesn't exist, "
 238                        "creating it and loading schema");
 239
 240                 p = strrchr(driver->filename, '/');
 241                 if (p) {
 242                         size_t len = (p - driver->filename) + 1;
 243
 244                         buff = talloc_array(NULL, char, len);
 245                         strlcpy(buff, driver->filename, len);
 246                 } else {
 247                         buff = talloc_strdup(NULL, driver->filename);
 248                 }
 249
 250                 if (rad_mkdir(buff, 0700) < 0) {
 251                         radlog(L_ERR, "rlm_sql_sqlite: Failed creating "
 252                                "directory for SQLite database");
 253
 254                         talloc_free(buff);
 255
 256                         return -1;
 257                 }
 258
 259                 talloc_free(buff);
 260
 261                 status = sqlite3_open_v2(driver->filename, &db,
 262                                          SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 263                                          NULL);
 264
 265                 if (!db) {
 266                         radlog(L_ERR, "rlm_sql_sqlite: Failed creating "
 267                                "opening/creating SQLite database, error "
 268                                "code (%u)", status);
 269
 270                         return -1;
 271                 }
 272
 273                 if (sql_check_error(db)) {
 274                         (void) sqlite3_close(db);
 275
 276                         return -1;
 277                 }
 278
 279                 ret = sql_loadfile(db, driver->bootstrap);
 280
 281                 status = sqlite3_close(db);
 282                 if (status != SQLITE_OK) {
 283                         radlog(L_ERR, "rlm_sql_sqlite: Error closing SQLite "
 284                                "handle, error code (%u)", status);
 285                         return -1;
 286                 }
 287
 288                 if (ret < 0) {
 289                         return -1;
 290                 }
 291         }
 292
 293         return 0;
 294 }
 295
 296 static int sql_socket_destructor(void *c)
 297 {
 298         int status = 0;
 299         rlm_sql_sqlite_conn_t * conn = c;
 300
 301         DEBUG2("rlm_sql_sqlite: Socket destructor called, closing socket");
 302
 303         if (conn->db) {
 304                 status = sqlite3_close(conn->db);
 305                 if (status != SQLITE_OK) {
 306                         DEBUGW("rlm_sql_sqlite: Got SQLite error "
 307                                "code (%u) when closing socket", status);
 308                 }
 309         }
 310
 311         return 0;
 312 }
 313
 314 static int sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 315 {
 316         rlm_sql_sqlite_conn_t *conn;
 317         rlm_sql_sqlite_config_t *driver = config->driver;
 318
 319         int status;
 320
 321         MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_sqlite_conn_t));
 322         talloc_set_destructor((void *) conn, sql_socket_destructor);
 323
 324         radlog(L_INFO, "rlm_sql_sqlite: Opening SQLite database \"%s\"",
 325                driver->filename);
 326
 327         status = sqlite3_open_v2(driver->filename, &(conn->db),
 328                                  SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX,
 329                                  NULL);
 330         if (!conn->db) {
 331                 radlog(L_ERR, "rlm_sql_sqlite: Failed creating "
 332                        "opening/creating SQLite database error "
 333                        "code (%u)", status);
 334
 335                 return -1;
 336         }
 337
 338         if (sql_check_error(conn->db)) {
 339                 return -1;
 340         }
 341
 342         /*
 343          *      Enable extended return codes for extra debugging info.
 344          */
 345         status = sqlite3_extended_result_codes(conn->db, 1);
 346
 347         if (sql_check_error(conn->db)) {
 348                 return -1;
 349         }
 350
 351         return 0;
 352 }
 353
 354 static int sql_select_query(rlm_sql_handle_t *handle,
 355                            UNUSED rlm_sql_config_t *config, char *querystr)
 356 {
 357         int status;
 358         rlm_sql_sqlite_conn_t *conn = handle->conn;
 359         const char *z_tail;
 360
 361         status = sqlite3_prepare_v2(conn->db, querystr,
 362                                     strlen(querystr), &conn->statement,
 363                                     &z_tail);
 364
 365         conn->col_count = 0;
 366
 367         return sql_check_error(conn->db);
 368 }
 369
 370
 371 static int sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config,
 372                      char *querystr)
 373 {
 374         int status;
 375         rlm_sql_sqlite_conn_t *conn = handle->conn;
 376         const char *z_tail;
 377
 378         status = sqlite3_prepare_v2(conn->db, querystr,
 379                                     strlen(querystr), &conn->statement,
 380                                     &z_tail);
 381
 382         status = sqlite3_step(conn->statement);
 383
 384         return sql_check_error(conn->db);
 385 }
 386
 387 static int sql_store_result(UNUSED rlm_sql_handle_t *handle,
 388                             UNUSED rlm_sql_config_t *config)
 389 {
 390         return 0;
 391 }
 392
 393 static int sql_num_fields(rlm_sql_handle_t * handle,
 394                           UNUSED rlm_sql_config_t *config)
 395 {
 396         rlm_sql_sqlite_conn_t *conn = handle->conn;
 397
 398         if (conn->statement) {
 399                 return sqlite3_column_count(conn->statement);
 400         }
 401
 402         return 0;
 403 }
 404
 405 static int sql_num_rows(rlm_sql_handle_t *handle,
 406                         UNUSED rlm_sql_config_t *config)
 407 {
 408         rlm_sql_sqlite_conn_t *conn = handle->conn;
 409
 410         if (conn->statement) {
 411                 return sqlite3_data_count(conn->statement);
 412         }
 413
 414         return 0;
 415 }
 416
 417 static int sql_fetch_row(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
 418 {
 419         int status;
 420         rlm_sql_sqlite_conn_t *conn = handle->conn;
 421
 422         int i = 0;
 423
 424         char **row;
 425
 426         /*
 427          *      Executes the SQLite query and interates over the results
 428          */
 429         status = sqlite3_step(conn->statement);
 430
 431         /*
 432          *      Error getting next row
 433          */
 434         if (sql_check_error(conn->db)) {
 435                 return -1;
 436         }
 437
 438         /*
 439          *      No more rows to process (were done)
 440          */
 441         if (status == SQLITE_DONE) {
 442                 return 1;
 443         }
 444
 445         /*
 446          *      We only need to do this once per result set, because
 447          *      the number of columns won't change.
 448          */
 449         if (conn->col_count == 0) {
 450                 conn->col_count = sql_num_fields(handle, config);
 451                 if (conn->col_count == 0) {
 452                         return -1;
 453                 }
 454         }
 455
 456         /*
 457          *      Free the previous result (also gets called on finish_query)
 458          */
 459         talloc_free(handle->row);
 460
 461         MEM(row = handle->row = talloc_zero_array(handle->conn, char *,
 462                                                   conn->col_count + 1));
 463
 464         for (i = 0; i < conn->col_count; i++)
 465         {
 466                 switch (sqlite3_column_type(conn->statement, i))
 467                 {
 468                 case SQLITE_INTEGER:
 469                         row[i] = talloc_asprintf(row, "%d",
 470                                                  sqlite3_column_int(conn->statement, i));
 471                         break;
 472
 473                 case SQLITE_FLOAT:
 474                         row[i] = talloc_asprintf(row, "%f",
 475                                                  sqlite3_column_double(conn->statement, i));
 476                         break;
 477
 478                 case SQLITE_TEXT:
 479                         {
 480                                 const char *p;
 481                                 p = (const char *) sqlite3_column_text(conn->statement, i);
 482
 483                                 if (p) {
 484                                         row[i] = talloc_strdup(row, p);
 485                                 }
 486                         }
 487                         break;
 488
 489                 case SQLITE_BLOB:
 490                         {
 491                                 const uint8_t *p;
 492                                 size_t len;
 493
 494                                 p = sqlite3_column_blob(conn->statement, i);
 495                                 if (p) {
 496                                         len = sqlite3_column_bytes(conn->statement, i);
 497
 498                                         MEM(row[i] = talloc_zero_array(row, char, len + 1));
 499                                         memcpy(row[i], p, len);
 500                                 }
 501                         }
 502                         break;
 503
 504                 default:
 505                         break;
 506                 }
 507         }
 508
 509         return 0;
 510 }
 511
 512 static int sql_free_result(rlm_sql_handle_t *handle,
 513                            UNUSED rlm_sql_config_t *config)
 514 {
 515         rlm_sql_sqlite_conn_t *conn = handle->conn;
 516
 517         if (conn->statement) {
 518                 TALLOC_FREE(handle->row);
 519
 520                 (void) sqlite3_finalize(conn->statement);
 521                 conn->statement = NULL;
 522                 conn->col_count = 0;
 523         }
 524
 525         /*
 526          *      There's no point in checking the code returned by finalize
 527          *      as it'll have already been encountered elsewhere in the code.
 528          *
 529          *      It's just the last error that occurred processing the
 530          *      statement.
 531          */
 532         return 0;
 533 }
 534
 535 static const char *sql_error(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->db) {
 541                 return sqlite3_errmsg(conn->db);
 542         }
 543
 544         return "Invalid handle";
 545 }
 546
 547 static int sql_finish_query(rlm_sql_handle_t *handle,
 548                             UNUSED rlm_sql_config_t *config)
 549 {
 550         return sql_free_result(handle, config);
 551 }
 552
 553 static int sql_affected_rows(rlm_sql_handle_t *handle,
 554                              UNUSED rlm_sql_config_t *config)
 555 {
 556         rlm_sql_sqlite_conn_t *conn = handle->conn;
 557
 558         if (conn->db) {
 559                 return sqlite3_changes(conn->db);
 560         }
 561
 562         return -1;
 563 }
 564
 565
 566 /* Exported to rlm_sql */
 567 rlm_sql_module_t rlm_sql_sqlite = {
 568         "rlm_sql_sqlite",
 569         sql_instantiate,
 570         sql_socket_init,
 571         NULL,
 572         sql_query,
 573         sql_select_query,
 574         sql_store_result,
 575         sql_num_fields,
 576         sql_num_rows,
 577         sql_fetch_row,
 578         sql_free_result,
 579         sql_error,
 580         NULL,
 581         sql_finish_query,
 582         sql_finish_query,
 583         sql_affected_rows
 584 };