src/modules/rlm_sql/drivers/rlm_sql_iodbc/rlm_sql_iodbc.c

   1 /*
   2  * sql_iodbc.c  iODBC support for FreeRadius
   3  *
   4  * Version:     $Id$
   5  *
   6  *   This program is free software; you can redistribute it and/or modify
   7  *   it under the terms of the GNU General Public License as published by
   8  *   the Free Software Foundation; either version 2 of the License, or
   9  *   (at your option) any later version.
  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
  17  *   along with this program; if not, write to the Free Software
  18  *   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  19  *
  20  * Copyright 2000,2006  The FreeRADIUS server project
  21  * Copyright 2000  Jeff Carneal <jeff@apex.net>
  22  */
  23
  24 #include <freeradius-devel/ident.h>
  25 RCSID("$Id$")
  26
  27 #include <freeradius-devel/radiusd.h>
  28
  29 #include <sys/stat.h>
  30
  31 #include <isql.h>
  32 #include <isqlext.h>
  33 #include <sqltypes.h>
  34
  35 #include "rlm_sql.h"
  36
  37 typedef struct rlm_sql_iodbc_sock {
  38         HENV    env_handle;
  39         HDBC    dbc_handle;
  40         HSTMT   stmt_handle;
  41         int             id;
  42         rlm_sql_row_t row;
  43
  44         struct sql_socket *next;
  45
  46         void    *conn;
  47 } rlm_sql_iodbc_sock;;
  48
  49 static const char *sql_error(rlm_sql_handle_t *handle, rlm_sql_config_t *config);
  50 static int sql_num_fields(rlm_sql_handle_t *handle, rlm_sql_config_t *config);
  51
  52 /*************************************************************************
  53  *
  54  *      Function: sql_socket_init
  55  *
  56  *      Purpose: Establish connection to the db
  57  *
  58  *************************************************************************/
  59 static int sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
  60
  61         rlm_sql_iodbc_sock *iodbc_sock;
  62         SQLRETURN rcode;
  63
  64         if (!handle->conn) {
  65                 handle->conn = (rlm_sql_iodbc_sock *)rad_malloc(sizeof(rlm_sql_iodbc_sock));
  66                 if (!handle->conn) {
  67                         return -1;
  68                 }
  69         }
  70         iodbc_sock = handle->conn;
  71         memset(iodbc_sock, 0, sizeof(*iodbc_sock));
  72
  73         rcode = SQLAllocEnv(&iodbc_sock->env_handle);
  74         if (!SQL_SUCCEEDED(rcode)) {
  75                 radlog(L_ERR, "sql_create_socket: SQLAllocEnv failed:  %s",
  76                                 sql_error(handle, config));
  77                 return -1;
  78         }
  79
  80         rcode = SQLAllocConnect(iodbc_sock->env_handle,
  81                                 &iodbc_sock->dbc_handle);
  82         if (!SQL_SUCCEEDED(rcode)) {
  83                 radlog(L_ERR, "sql_create_socket: SQLAllocConnect failed:  %s",
  84                                 sql_error(handle, config));
  85                 return -1;
  86         }
  87
  88         rcode = SQLConnect(iodbc_sock->dbc_handle, config->sql_server,
  89                            SQL_NTS, config->sql_login, SQL_NTS,
  90                            config->sql_password, SQL_NTS);
  91         if (!SQL_SUCCEEDED(rcode)) {
  92                 radlog(L_ERR, "sql_create_socket: SQLConnectfailed:  %s",
  93                                 sql_error(handle, config));
  94                 return -1;
  95         }
  96
  97         return 0;
  98 }
  99
 100 /*************************************************************************
 101  *
 102  *      Function: sql_destroy_socket
 103  *
 104  *      Purpose: Free socket and private connection data
 105  *
 106  *************************************************************************/
 107 static int sql_destroy_socket(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 108 {
 109         free(handle->conn);
 110         handle->conn = NULL;
 111         return 0;
 112 }
 113
 114 /*************************************************************************
 115  *
 116  *      Function: sql_query
 117  *
 118  *      Purpose: Issue a non-SELECT query (ie: update/delete/insert) to
 119  *               the database.
 120  *
 121  *************************************************************************/
 122 static int sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char *querystr) {
 123
 124         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 125         SQLRETURN rcode;
 126
 127         rcode = SQLAllocStmt(iodbc_sock->dbc_handle,
 128                              &iodbc_sock->stmt_handle);
 129         if (!SQL_SUCCEEDED(rcode)) {
 130                 radlog(L_ERR, "sql_create_socket: SQLAllocStmt failed:  %s",
 131                                 sql_error(handle, config));
 132                 return -1;
 133         }
 134
 135         if (iodbc_sock->dbc_handle == NULL) {
 136                 radlog(L_ERR, "sql_query:  Socket not connected");
 137                 return -1;
 138         }
 139
 140         rcode = SQLExecDirect(iodbc_sock->stmt_handle, querystr, SQL_NTS);
 141         if (!SQL_SUCCEEDED(rcode)) {
 142                 radlog(L_ERR, "sql_query: failed:  %s",
 143                                 sql_error(handle, config));
 144                 return -1;
 145         }
 146
 147         return 0;
 148 }
 149
 150
 151 /*************************************************************************
 152  *
 153  *      Function: sql_select_query
 154  *
 155  *      Purpose: Issue a select query to the database
 156  *
 157  *************************************************************************/
 158 static int sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char *querystr) {
 159
 160         int numfields = 0;
 161         int i=0;
 162         char **row=NULL;
 163         SQLINTEGER len=0;
 164         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 165
 166         if(sql_query(handle, config, querystr) < 0) {
 167                 return -1;
 168         }
 169
 170         numfields = sql_num_fields(handle, config);
 171
 172         row = (char **) rad_malloc(sizeof(char *) * (numfields+1));
 173         memset(row, 0, (sizeof(char *) * (numfields)));
 174         row[numfields] = NULL;
 175
 176         for(i=1; i<=numfields; i++) {
 177                 SQLColAttributes(iodbc_sock->stmt_handle, ((SQLUSMALLINT) i), SQL_COLUMN_LENGTH,
 178                                                                                 NULL, 0, NULL, &len);
 179                 len++;
 180
 181                 /*
 182                  * Allocate space for each column
 183                  */
 184                 row[i-1] = (SQLCHAR*)rad_malloc((int)len);
 185
 186                 /*
 187                  * This makes me feel dirty, but, according to Microsoft, it works.
 188                  * Any ODBC datatype can be converted to a 'char *' according to
 189                  * the following:
 190                  *
 191                  * http://msdn.microsoft.com/library/psdk/dasdk/odap4o4z.htm
 192                  */
 193                 SQLBindCol(iodbc_sock->stmt_handle, i, SQL_C_CHAR, (SQLCHAR *)row[i-1], len, 0);
 194         }
 195
 196         iodbc_sock->row = row;
 197
 198         return 0;
 199 }
 200
 201
 202 /*************************************************************************
 203  *
 204  *      Function: sql_store_result
 205  *
 206  *      Purpose: database specific store_result function. Returns a result
 207  *               set for the query.
 208  *
 209  *************************************************************************/
 210 static int sql_store_result(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 211
 212         return 0;
 213 }
 214
 215
 216 /*************************************************************************
 217  *
 218  *      Function: sql_num_fields
 219  *
 220  *      Purpose: database specific num_fields function. Returns number
 221  *               of columns from query
 222  *
 223  *************************************************************************/
 224 static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 225
 226         SQLSMALLINT count=0;
 227         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 228
 229         SQLNumResultCols(iodbc_sock->stmt_handle, &count);
 230
 231         return (int)count;
 232 }
 233
 234 /*************************************************************************
 235  *
 236  *      Function: sql_num_rows
 237  *
 238  *      Purpose: database specific num_rows. Returns number of rows in
 239  *               query
 240  *
 241  *************************************************************************/
 242 static int sql_num_rows(UNUSED rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 243         /*
 244          * I presume this function is used to determine the number of
 245          * rows in a result set *before* fetching them.  I don't think
 246          * this is possible in ODBC 2.x, but I'd be happy to be proven
 247          * wrong.  If you know how to do this, email me at jeff@apex.net
 248          */
 249         return 0;
 250 }
 251
 252
 253 /*************************************************************************
 254  *
 255  *      Function: sql_fetch_row
 256  *
 257  *      Purpose: database specific fetch_row. Returns a rlm_sql_row_t struct
 258  *               with all the data for the query in 'handle->row'. Returns
 259  *               0 on success, -1 on failure, SQL_DOWN if 'database is down'
 260  *
 261  *************************************************************************/
 262 static int sql_fetch_row(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 263
 264         SQLRETURN rc;
 265         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 266
 267         handle->row = NULL;
 268
 269         if((rc = SQLFetch(iodbc_sock->stmt_handle)) == SQL_NO_DATA_FOUND) {
 270                 return 0;
 271         }
 272         /* XXX Check rc for database down, if so, return SQL_DOWN */
 273
 274         handle->row = iodbc_sock->row;
 275         return 0;
 276 }
 277
 278
 279
 280 /*************************************************************************
 281  *
 282  *      Function: sql_free_result
 283  *
 284  *      Purpose: database specific free_result. Frees memory allocated
 285  *               for a result set
 286  *
 287  *************************************************************************/
 288 static int sql_free_result(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
 289
 290         int i=0;
 291         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 292
 293         for(i=0; i<sql_num_fields(handle, config); i++) {
 294                 free(iodbc_sock->row[i]);
 295         }
 296         free(iodbc_sock->row);
 297         iodbc_sock->row=NULL;
 298
 299         SQLFreeStmt( iodbc_sock->stmt_handle, SQL_DROP );
 300
 301         return 0;
 302 }
 303
 304
 305 /*************************************************************************
 306  *
 307  *      Function: sql_error
 308  *
 309  *      Purpose: database specific error. Returns error associated with
 310  *               connection
 311  *
 312  *************************************************************************/
 313 static const char *sql_error(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 314
 315         SQLINTEGER errornum = 0;
 316         SQLSMALLINT length = 0;
 317         SQLCHAR state[256] = "";
 318         static SQLCHAR error[256] = "";
 319         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 320
 321         SQLError(iodbc_sock->env_handle, iodbc_sock->dbc_handle, iodbc_sock->stmt_handle,
 322                 state, &errornum, error, 256, &length);
 323         return error;
 324 }
 325
 326
 327 /*************************************************************************
 328  *
 329  *      Function: sql_close
 330  *
 331  *      Purpose: database specific close. Closes an open database
 332  *               connection and cleans up any open handles.
 333  *
 334  *************************************************************************/
 335 static int sql_close(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 336
 337         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 338
 339         SQLFreeStmt(iodbc_sock->stmt_handle, SQL_DROP);
 340         SQLDisconnect(iodbc_sock->dbc_handle);
 341         SQLFreeConnect(iodbc_sock->dbc_handle);
 342         SQLFreeEnv(iodbc_sock->env_handle);
 343
 344         iodbc_sock->stmt_handle = NULL;
 345         iodbc_sock->dbc_handle = NULL;
 346         iodbc_sock->env_handle = NULL;
 347
 348         return 0;
 349 }
 350
 351
 352 /*************************************************************************
 353  *
 354  *      Function: sql_finish_query
 355  *
 356  *      Purpose: End the query, such as freeing memory
 357  *
 358  *************************************************************************/
 359 static int sql_finish_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
 360
 361         return sql_free_result(handle, config);
 362 }
 363
 364
 365
 366 /*************************************************************************
 367  *
 368  *      Function: sql_finish_select_query
 369  *
 370  *      Purpose: End the select query, such as freeing memory or result
 371  *
 372  *************************************************************************/
 373 static int sql_finish_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
 374         return sql_free_result(handle, config);
 375 }
 376
 377
 378 /*************************************************************************
 379  *
 380  *      Function: sql_affected_rows
 381  *
 382  *      Purpose: Return the number of rows affected by the query (update,
 383  *               or insert)
 384  *
 385  *************************************************************************/
 386 static int sql_affected_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config) {
 387
 388         SQLINTEGER count;
 389         rlm_sql_iodbc_sock *iodbc_sock = handle->conn;
 390
 391         SQLRowCount(iodbc_sock->stmt_handle, &count);
 392         return (int)count;
 393 }
 394
 395 /* Exported to rlm_sql */
 396 rlm_sql_module_t rlm_sql_iodbc = {
 397         "rlm_sql_iodbc",
 398         NULL,
 399         sql_socket_init,
 400         sql_destroy_socket,
 401         sql_query,
 402         sql_select_query,
 403         sql_store_result,
 404         sql_num_fields,
 405         sql_num_rows,
 406         sql_fetch_row,
 407         sql_free_result,
 408         sql_error,
 409         sql_close,
 410         sql_finish_query,
 411         sql_finish_select_query,
 412         sql_affected_rows
 413 };