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

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