src/modules/rlm_sql/drivers/rlm_sql_sqlite/sql_sqlite.c

   1 /*
   2  *  sql_sqlite.c
   3  *  freeradius
   4  *
   5  * Version:     $Id$
   6  *
   7  *   This program is free software; you can redistribute it and/or modify
   8  *   it under the terms of the GNU General Public License version 2 only, as published by
   9  *   the Free Software Foundation.
  10  *
  11  *   This program is distributed in the hope that it will be useful,
  12  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  *   GNU General Public License for more details.
  15  *
  16  *   You should have received a copy of the GNU General Public License version 2
  17  *   along with this program; if not, write to the Free Software
  18  *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  19  *
  20  * Copyright 2007 Apple Inc.
  21  */
  22
  23 #include <freeradius-devel/ident.h>
  24 RCSID("$Id$")
  25
  26 #include <freeradius-devel/radiusd.h>
  27
  28 #include <sys/stat.h>
  29
  30 #include <sqlite3.h>
  31
  32 #include "rlm_sql.h"
  33
  34 typedef struct rlm_sql_sqlite_sock {
  35         sqlite3 *pDb;
  36         sqlite3_stmt *pStmt;
  37         int columnCount;
  38 } rlm_sql_sqlite_sock;
  39
  40
  41 /*************************************************************************
  42  *
  43  *      Function: sql_create_socket
  44  *
  45  *      Purpose: Establish connection to the db
  46  *
  47  *************************************************************************/
  48 static int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config)
  49 {
  50         int status;
  51         rlm_sql_sqlite_sock *sqlite_sock;
  52         char *filename;
  53         char buffer[2048];
  54
  55         if (!sqlsocket->conn) {
  56                 sqlsocket->conn = (rlm_sql_sqlite_sock *)rad_malloc(sizeof(rlm_sql_sqlite_sock));
  57                 if (!sqlsocket->conn) {
  58                         return -1;
  59                 }
  60         }
  61         sqlite_sock = sqlsocket->conn;
  62         memset(sqlite_sock, 0, sizeof(rlm_sql_sqlite_sock));
  63
  64         filename = config->sql_file;
  65         if (!filename) {
  66                 snprintf(buffer, sizeof(buffer), "%s/sqlite_radius_client_database",
  67                          radius_dir);
  68                 filename = buffer;
  69         }
  70         radlog(L_INFO, "rlm_sql_sqlite: Opening sqlite database %s for #%d",
  71                filename, sqlsocket->id);
  72
  73         status = sqlite3_open(filename, &sqlite_sock->pDb);
  74         radlog(L_INFO, "rlm_sql_sqlite: sqlite3_open() = %d\n", status);
  75         return (status != SQLITE_OK) * -1;
  76 }
  77
  78
  79 /*************************************************************************
  80  *
  81  *      Function: sql_destroy_socket
  82  *
  83  *      Purpose: Free socket and any private connection data
  84  *
  85  *************************************************************************/
  86 static int sql_destroy_socket(SQLSOCK *sqlsocket, UNUSED SQL_CONFIG *config)
  87 {
  88         int status = 0;
  89         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
  90
  91         if (sqlite_sock && sqlite_sock->pDb) {
  92                 status = sqlite3_close(sqlite_sock->pDb);
  93                 radlog(L_INFO, "rlm_sql_sqlite: sqlite3_close() = %d\n", status);
  94         }
  95         else {
  96                 radlog(L_INFO, "rlm_sql_sqlite: sql_destroy_socket noop.\n");
  97         }
  98
  99         return 0;
 100 }
 101
 102
 103 /*************************************************************************
 104  *
 105  *      Function: sql_query
 106  *
 107  *      Purpose: Issue a query to the database
 108  *
 109  *************************************************************************/
 110 static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr)
 111 {
 112         int status;
 113         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 114         const char *zTail;
 115
 116         if (config->sqltrace)
 117                 radlog(L_DBG,"rlm_sql_sqlite: query:  %s", querystr);
 118         if (sqlite_sock->pDb == NULL) {
 119                 radlog(L_ERR, "rlm_sql_sqlite: Socket not connected");
 120                 return SQL_DOWN;
 121         }
 122
 123         status = sqlite3_prepare(sqlite_sock->pDb, querystr, strlen(querystr), &sqlite_sock->pStmt, &zTail);
 124         radlog(L_DBG, "rlm_sql_sqlite: sqlite3_prepare() = %d\n", status);
 125         sqlite_sock->columnCount = 0;
 126
 127         return (status == SQLITE_OK) ? 0 : SQL_DOWN;
 128 }
 129
 130
 131 /*************************************************************************
 132  *
 133  *      Function: sql_select_query
 134  *
 135  *      Purpose: Issue a select query to the database
 136  *
 137  *************************************************************************/
 138 static int sql_select_query(SQLSOCK *sqlsocket, SQL_CONFIG *config,
 139                             char *querystr)
 140 {
 141         if (strstr(querystr, "nas") != NULL)
 142                 return sql_query(sqlsocket, config, querystr);
 143
 144         return 0;
 145 }
 146
 147
 148 /*************************************************************************
 149  *
 150  *      Function: sql_store_result
 151  *
 152  *      Purpose: database specific store_result function. Returns a result
 153  *               set for the query.
 154  *
 155  *************************************************************************/
 156 static int sql_store_result(UNUSED SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 157 {
 158         return 0;
 159 }
 160
 161
 162 /*************************************************************************
 163  *
 164  *      Function: sql_num_fields
 165  *
 166  *      Purpose: database specific num_fields function. Returns number
 167  *               of columns from query
 168  *
 169  *************************************************************************/
 170 static int sql_num_fields(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 171 {
 172         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 173
 174         if (sqlite_sock->pStmt)
 175                 return sqlite3_column_count(sqlite_sock->pStmt);
 176
 177         return 0;
 178 }
 179
 180
 181 /*************************************************************************
 182  *
 183  *      Function: sql_num_rows
 184  *
 185  *      Purpose: database specific num_rows. Returns number of rows in
 186  *               query
 187  *
 188  *************************************************************************/
 189 static int sql_num_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 190 {
 191         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 192
 193         if (sqlite_sock->pStmt)
 194                 return sqlite3_data_count(sqlite_sock->pStmt);
 195
 196         return 0;
 197 }
 198
 199
 200 /*************************************************************************
 201  *      Function: sql_free_rowdata
 202  *************************************************************************/
 203 static void sql_free_rowdata(SQLSOCK * sqlsocket, int colcount)
 204 {
 205         char **rowdata = sqlsocket->row;
 206         int colindex;
 207
 208         if (rowdata != NULL) {
 209                 for (colindex = 0; colindex < colcount; colindex++) {
 210                         if (rowdata[colindex] != NULL) {
 211                                 free(rowdata[colindex]);
 212                                 rowdata[colindex] = NULL;
 213                         }
 214                 }
 215                 free(sqlsocket->row);
 216                 sqlsocket->row = NULL;
 217         }
 218 }
 219
 220
 221 /*************************************************************************
 222  *
 223  *      Function: sql_fetch_row
 224  *
 225  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 226  *               with all the data for the query in 'sqlsocket->row'. Returns
 227  *               0 on success, -1 on failure, SQL_DOWN if database is down.
 228  *
 229  *************************************************************************/
 230 static int sql_fetch_row(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 231 {
 232         int returnCode = -1;
 233         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 234         const char *blob;
 235         int blobLen;
 236         int status;
 237         int colindex = 0;
 238         int colcount = 0;
 239         int coltype = 0;
 240         int colintvalue = 0;
 241         int ret_blob_size = 0;
 242         char **rowdata = NULL;
 243         const unsigned char *textStr;
 244         char intStr[256];
 245
 246         status = sqlite3_step(sqlite_sock->pStmt);
 247         radlog(L_DBG, "rlm_sql_sqlite: sqlite3_step = %d\n", status);
 248         if (status == SQLITE_DONE) {
 249                 sql_free_rowdata(sqlsocket, sqlite_sock->columnCount);
 250                 return 0;
 251         }
 252         else if (status == SQLITE_ROW) {
 253                 if (sqlite_sock->columnCount == 0) {
 254                         sqlite_sock->columnCount = sql_num_fields(sqlsocket, config);
 255                 }
 256                 colcount = sqlite_sock->columnCount;
 257                 if (colcount == 0)
 258                         return -1;
 259
 260                 sql_free_rowdata(sqlsocket, colcount);
 261
 262                 ret_blob_size = sizeof(char *) * (colcount+1);
 263                 rowdata = (char **)rad_malloc(ret_blob_size);           /* Space for pointers */
 264                 if (rowdata != NULL) {
 265                         memset(rowdata, 0, ret_blob_size);                              /* NULL-pad the pointers */
 266                         sqlsocket->row = rowdata;
 267                 }
 268
 269                 for (colindex = 0; colindex < colcount; colindex++)
 270                 {
 271                         coltype = sqlite3_column_type(sqlite_sock->pStmt, colindex);
 272                         switch (coltype)
 273                         {
 274                                 case SQLITE_INTEGER:
 275                                         colintvalue = sqlite3_column_int(sqlite_sock->pStmt, colindex);
 276                                         snprintf(intStr, sizeof(intStr), "%d", colintvalue);
 277                                         rowdata[colindex] = strdup(intStr);
 278                                         break;
 279
 280                                 case SQLITE_TEXT:
 281                                         textStr = sqlite3_column_text(sqlite_sock->pStmt, colindex);
 282                                         if (textStr != NULL)
 283                                                 rowdata[colindex] = strdup((const char *)textStr);
 284                                         break;
 285
 286                                 case SQLITE_BLOB:
 287                                         blob = sqlite3_column_blob(sqlite_sock->pStmt, colindex);
 288                                         if (blob != NULL) {
 289                                                 blobLen = sqlite3_column_bytes(sqlite_sock->pStmt, colindex);
 290                                                 rowdata[colindex] = (char *)rad_malloc(blobLen + 1);
 291                                                 if (rowdata[colindex] != NULL) {
 292                                                         memcpy(rowdata[colindex], blob, blobLen);
 293                                                 }
 294                                         }
 295                                         break;
 296
 297                                 default:
 298                                         break;
 299                         }
 300                 }
 301
 302                 returnCode = 0;
 303         }
 304
 305         return returnCode;
 306 }
 307
 308
 309 /*************************************************************************
 310  *
 311  *      Function: sql_free_result
 312  *
 313  *      Purpose: database specific free_result. Frees memory allocated
 314  *               for a result set
 315  *
 316  *************************************************************************/
 317 static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 318 {
 319         int status = 0;
 320         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 321
 322         if (sqlite_sock->pStmt != NULL) {
 323                 sql_free_rowdata(sqlsocket, sqlite_sock->columnCount);
 324                 status = sqlite3_finalize(sqlite_sock->pStmt);
 325                 sqlite_sock->pStmt = NULL;
 326                 radlog(L_DBG, "rlm_sql_sqlite: sqlite3_finalize() = %d\n", status);
 327         }
 328
 329         return status;
 330 }
 331
 332
 333 /*************************************************************************
 334  *
 335  *      Function: sql_error
 336  *
 337  *      Purpose: database specific error. Returns error associated with
 338  *               connection
 339  *
 340  *************************************************************************/
 341 static const char *sql_error(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 342 {
 343         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 344
 345         if (sqlite_sock->pDb != NULL) {
 346                 return sqlite3_errmsg(sqlite_sock->pDb);
 347         }
 348
 349         radlog(L_ERR, "rlm_sql_sqlite: Socket not connected");
 350         return NULL;
 351 }
 352
 353
 354 /*************************************************************************
 355  *
 356  *      Function: sql_close
 357  *
 358  *      Purpose: database specific close. Closes an open database
 359  *               connection
 360  *
 361  *************************************************************************/
 362 static int sql_close(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 363 {
 364         int status = 0;
 365         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 366
 367         if (sqlite_sock && sqlite_sock->pDb) {
 368                 status = sqlite3_close(sqlite_sock->pDb);
 369                 sqlite_sock->pDb = NULL;
 370         }
 371
 372         return status;
 373 }
 374
 375
 376 /*************************************************************************
 377  *
 378  *      Function: sql_finish_query
 379  *
 380  *      Purpose: End the query, such as freeing memory
 381  *
 382  *************************************************************************/
 383 static int sql_finish_query(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 384 {
 385         int status = 0;
 386         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 387
 388         if (sqlite_sock->pStmt) {
 389                 status = sqlite3_finalize(sqlite_sock->pStmt);
 390                 sqlite_sock->pStmt = NULL;
 391                 radlog(L_DBG, "rlm_sql_sqlite: sqlite3_finalize() = %d\n", status);
 392         }
 393
 394         return status;
 395 }
 396
 397
 398 /*************************************************************************
 399  *
 400  *      Function: sql_finish_select_query
 401  *
 402  *      Purpose: End the select query, such as freeing memory or result
 403  *
 404  *************************************************************************/
 405 static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 406 {
 407         return sql_finish_query(sqlsocket, config);
 408 }
 409
 410
 411 /*************************************************************************
 412  *
 413  *      Function: sql_affected_rows
 414  *
 415  *      Purpose: Requests the number of rows affected by the last executed
 416  *               statement
 417  *
 418  *************************************************************************/
 419 static int sql_affected_rows(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config)
 420 {
 421         int result = -1;
 422
 423         rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
 424
 425         if (sqlite_sock->pDb != NULL) {
 426                 result = sqlite3_changes(sqlite_sock->pDb);
 427                 DEBUG3("rlm_sql_sqlite: sql_affected_rows() = %i\n", result);
 428         }
 429
 430         return result;
 431 }
 432
 433
 434 /* Exported to rlm_sql */
 435 rlm_sql_module_t rlm_sql_sqlite = {
 436         "rlm_sql_sqlite",
 437         sql_init_socket,
 438         sql_destroy_socket,
 439         sql_query,
 440         sql_select_query,
 441         sql_store_result,
 442         sql_num_fields,
 443         sql_num_rows,
 444         sql_fetch_row,
 445         sql_free_result,
 446         sql_error,
 447         sql_close,
 448         sql_finish_query,
 449         sql_finish_select_query,
 450         sql_affected_rows
 451 };