All Data Structures Files Functions Variables Typedefs Enumerations Enumerator Macros Groups Pages
rlm_sql_unixodbc.c
Go to the documentation of this file.
1 /*
2  * sql_unixodbc.c unixODBC rlm_sql driver
3  *
4  * This program is free software; you can redistribute it and/or modify
5  * it under the terms of the GNU General Public License as published by
6  * the Free Software Foundation; either version 2 of the License, or
7  * (at your option) any later version.
8  *
9  * This program is distributed in the hope that it will be useful,
10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12  * GNU General Public License for more details.
13  *
14  * You should have received a copy of the GNU General Public License
15  * along with this program; if not, write to the Free Software
16  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
17  *
18  * Copyright 2000,2006 The FreeRADIUS server project
19  * Copyright 2000 Dmitri Ageev <d_ageev@ortcc.ru>
20  */
21 
22 RCSID("$Id: 82a8c87ec3396c315c584c923f3b213bc88f534e $")
24 
25 #include <freeradius-devel/radiusd.h>
26 #include <freeradius-devel/rad_assert.h>
27 
28 #include <sqltypes.h>
29 #include "rlm_sql.h"
30 
31 typedef struct rlm_sql_unixodbc_conn {
32  SQLHENV env;
33  SQLHDBC dbc;
34  SQLHSTMT stmt;
36  void *conn;
38 
40 #include <sql.h>
41 #include <sqlext.h>
42 
43 /* Forward declarations */
44 static int sql_check_error(long err_handle, rlm_sql_handle_t *handle, rlm_sql_config_t *config);
46 static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config);
47 static int sql_num_fields(rlm_sql_handle_t *handle, rlm_sql_config_t *config);
48 
50 {
51  DEBUG2("rlm_sql_unixodbc: Socket destructor called, closing socket");
52 
53  if (conn->stmt) SQLFreeStmt(conn->stmt, SQL_DROP);
54 
55  if (conn->dbc) {
56  SQLDisconnect(conn->dbc);
57  SQLFreeConnect(conn->dbc);
58  }
59 
60  if (conn->env) SQLFreeEnv(conn->env);
61 
62  return 0;
63 }
64 
66  struct timeval const *timeout)
67 {
69  long err_handle;
70  uint32_t timeout_ms = FR_TIMEVAL_TO_MS(timeout);
71 
72  MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_unixodbc_conn_t));
73  talloc_set_destructor(conn, _sql_socket_destructor);
74 
75  /* 1. Allocate environment handle and register version */
76  err_handle = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &conn->env);
77  if (sql_check_error(err_handle, handle, config)) {
78  ERROR("rlm_sql_unixodbc: Can't allocate environment handle");
79  return RLM_SQL_ERROR;
80  }
81 
82  err_handle = SQLSetEnvAttr(conn->env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
83  if (sql_check_error(err_handle, handle, config)) {
84  ERROR("rlm_sql_unixodbc: Can't register ODBC version");
85  return RLM_SQL_ERROR;
86  }
87 
88  /* 2. Allocate connection handle */
89  err_handle = SQLAllocHandle(SQL_HANDLE_DBC, conn->env, &conn->dbc);
90  if (sql_check_error(err_handle, handle, config)) {
91  ERROR("rlm_sql_unixodbc: Can't allocate connection handle");
92  return RLM_SQL_ERROR;
93  }
94 
95  /* Set the connection timeout */
96  SQLSetConnectAttr(conn->dbc, SQL_ATTR_LOGIN_TIMEOUT, &timeout_ms, SQL_IS_UINTEGER);
97 
98  /* 3. Connect to the datasource */
99  {
100  SQLCHAR *odbc_server, *odbc_login, *odbc_password;
101 
102  memcpy(&odbc_server, &config->sql_server, sizeof(odbc_server));
103  memcpy(&odbc_login, &config->sql_login, sizeof(odbc_login));
104  memcpy(&odbc_password, &config->sql_password, sizeof(odbc_password));
105  err_handle = SQLConnect(conn->dbc,
106  odbc_server, strlen(config->sql_server),
107  odbc_login, strlen(config->sql_login),
108  odbc_password, strlen(config->sql_password));
109  }
110 
111  if (sql_check_error(err_handle, handle, config)) {
112  ERROR("rlm_sql_unixodbc: Connection failed");
113  return RLM_SQL_ERROR;
114  }
115 
116  /* 4. Allocate the stmt */
117  err_handle = SQLAllocHandle(SQL_HANDLE_STMT, conn->dbc, &conn->stmt);
118  if (sql_check_error(err_handle, handle, config)) {
119  ERROR("rlm_sql_unixodbc: Can't allocate the stmt");
120  return RLM_SQL_ERROR;
121  }
122 
123  return RLM_SQL_OK;
124 }
125 
126 static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
127 {
128  rlm_sql_unixodbc_conn_t *conn = handle->conn;
129  long err_handle;
130  int state;
131 
132  /* Executing query */
133  {
134  SQLCHAR *odbc_query;
135 
136  memcpy(&odbc_query, &query, sizeof(odbc_query));
137  err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query));
138  }
139  if ((state = sql_check_error(err_handle, handle, config))) {
140  if(state == RLM_SQL_RECONNECT) {
141  DEBUG("rlm_sql_unixodbc: rlm_sql will attempt to reconnect");
142  }
143  return state;
144  }
145  return 0;
146 }
147 
148 static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
149 {
150  rlm_sql_unixodbc_conn_t *conn = handle->conn;
151  SQLINTEGER i;
152  SQLLEN len;
153  int colcount;
154  int state;
155 
156  /* Only state = 0 means success */
157  if ((state = sql_query(handle, config, query))) {
158  return state;
159  }
160 
161  colcount = sql_num_fields(handle, config);
162  if (colcount < 0) {
163  return RLM_SQL_ERROR;
164  }
165 
166  /* Reserving memory for result */
167  conn->row = talloc_zero_array(conn, char *, colcount + 1); /* Space for pointers */
168 
169  for (i = 1; i <= colcount; i++) {
170  SQLColAttributes(conn->stmt, ((SQLUSMALLINT) i), SQL_COLUMN_LENGTH, NULL, 0, NULL, &len);
171  conn->row[i - 1] = talloc_array(conn->row, char, ++len);
172  SQLBindCol(conn->stmt, i, SQL_C_CHAR, (SQLCHAR *)conn->row[i - 1], len, NULL);
173  }
174 
175  return RLM_SQL_OK;
176 }
177 
179 {
180  rlm_sql_unixodbc_conn_t *conn = handle->conn;
181  long err_handle;
182  SQLSMALLINT num_fields = 0;
183 
184  err_handle = SQLNumResultCols(conn->stmt,&num_fields);
185  if (sql_check_error(err_handle, handle, config)) return -1;
186 
187  return num_fields;
188 }
189 
190 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
191 {
192  rlm_sql_unixodbc_conn_t *conn = handle->conn;
193 
194  SQLSMALLINT fields, len, i;
195 
196  char const **names;
197  char field[128];
198 
199  SQLNumResultCols(conn->stmt, &fields);
200  if (fields == 0) return RLM_SQL_ERROR;
201 
202  MEM(names = talloc_array(handle, char const *, fields));
203 
204  for (i = 0; i < fields; i++) {
205  char *p;
206 
207  switch (SQLColAttribute(conn->stmt, i, SQL_DESC_BASE_COLUMN_NAME,
208  field, sizeof(field), &len, NULL)) {
209  case SQL_INVALID_HANDLE:
210  case SQL_ERROR:
211  ERROR("Failed retrieving field name at index %i", i);
212  talloc_free(names);
213  return RLM_SQL_ERROR;
214 
215  default:
216  break;
217  }
218 
219  MEM(p = talloc_array(names, char, (size_t)len + 1));
220  strlcpy(p, field, (size_t)len + 1);
221  names[i] = p;
222  }
223  *out = names;
224 
225  return RLM_SQL_OK;
226 }
227 
229 {
230  rlm_sql_unixodbc_conn_t *conn = handle->conn;
231  long err_handle;
232  int state;
233 
234  *out = NULL;
235 
236  handle->row = NULL;
237 
238  err_handle = SQLFetch(conn->stmt);
239  if (err_handle == SQL_NO_DATA_FOUND) return RLM_SQL_OK;
240 
241  if ((state = sql_check_error(err_handle, handle, config))) return state;
242 
243  *out = handle->row = conn->row;
244 
245  return RLM_SQL_OK;
246 }
247 
249 {
250  rlm_sql_unixodbc_conn_t *conn = handle->conn;
251 
252  sql_free_result(handle, config);
253 
254  /*
255  * SQL_CLOSE - The cursor (if any) associated with the statement
256  * handle (StatementHandle) is closed and all pending results are
257  * discarded. The application can reopen the cursor by calling
258  * SQLExecute() with the same or different values in the
259  * application variables (if any) that are bound to StatementHandle.
260  * If no cursor has been associated with the statement handle,
261  * this option has no effect (no warning or error is generated).
262  *
263  * So, this call does NOT free the statement at all, it merely
264  * resets it for the next call. This is terrible terrible naming.
265  */
266  SQLFreeStmt(conn->stmt, SQL_CLOSE);
267 
268  return 0;
269 }
270 
272 {
273  rlm_sql_unixodbc_conn_t *conn = handle->conn;
274 
275  SQLFreeStmt(conn->stmt, SQL_CLOSE);
276 
277  return 0;
278 }
279 
281 {
282  rlm_sql_unixodbc_conn_t *conn = handle->conn;
283 
284  TALLOC_FREE(conn->row);
285 
286  return 0;
287 }
288 
289 /** Retrieves any errors associated with the connection handle
290  *
291  * @note Caller will free any memory allocated in ctx.
292  *
293  * @param ctx to allocate temporary error buffers in.
294  * @param out Array of #sql_log_entry_t to fill.
295  * @param outlen Length of out array.
296  * @param handle rlm_sql connection handle.
297  * @param config rlm_sql config.
298  * @return number of errors written to the #sql_log_entry_t array.
299  */
300 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
301  rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
302 {
303  rlm_sql_unixodbc_conn_t *conn = handle->conn;
304  SQLCHAR state[256];
305  SQLCHAR errbuff[256];
306  SQLINTEGER errnum = 0;
307  SQLSMALLINT length = 255;
308 
309  rad_assert(outlen > 0);
310 
311  errbuff[0] = state[0] = '\0';
312  SQLError(conn->env, conn->dbc, conn->stmt, state, &errnum,
313  errbuff, sizeof(errbuff), &length);
314  if (errnum == 0) return 0;
315 
316  out[0].type = L_ERR;
317  out[0].msg = talloc_asprintf(ctx, "%s: %s", state, errbuff);
318 
319  return 1;
320 }
321 
322 /** Checks the error code to determine if the connection needs to be re-esttablished
323  *
324  * @param error_handle Return code from a failed unixodbc call.
325  * @param handle rlm_sql connection handle.
326  * @param config rlm_sql config.
327  * @return
328  * - #RLM_SQL_OK on success.
329  * - #RLM_SQL_RECONNECT if reconnect is needed.
330  * - #RLM_SQL_ERROR on error.
331  */
332 static sql_rcode_t sql_check_error(long error_handle, rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
333 {
334  SQLCHAR state[256];
335  SQLCHAR error[256];
336  SQLINTEGER errornum = 0;
337  SQLSMALLINT length = 255;
338  int res = -1;
339 
340  rlm_sql_unixodbc_conn_t *conn = handle->conn;
341 
342  if (SQL_SUCCEEDED(error_handle)) return 0; /* on success, just return 0 */
343 
344  error[0] = state[0] = '\0';
345 
346  SQLError(conn->env, conn->dbc, conn->stmt, state, &errornum,
347  error, sizeof(error), &length);
348 
349  if (state[0] == '0') {
350  switch (state[1]) {
351  /* SQLSTATE 01 class contains info and warning messages */
352  case '1':
353  INFO("rlm_sql_unixodbc: %s %s", state, error);
354  /* FALL-THROUGH */
355  case '0': /* SQLSTATE 00 class means success */
356  res = RLM_SQL_OK;
357  break;
358 
359  /* SQLSTATE 08 class describes various connection errors */
360  case '8':
361  ERROR("rlm_sql_unixodbc: SQL down %s %s", state, error);
362  res = RLM_SQL_RECONNECT;
363  break;
364 
365  /* any other SQLSTATE means error */
366  default:
367  ERROR("rlm_sql_unixodbc: %s %s", state, error);
368  res = RLM_SQL_ERROR;
369  break;
370  }
371  } else {
372  ERROR("rlm_sql_unixodbc: %s %s", state, error);
373  }
374 
375  return res;
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  *************************************************************************/
387 {
388  rlm_sql_unixodbc_conn_t *conn = handle->conn;
389  long error_handle;
390  SQLLEN affected_rows;
391 
392  error_handle = SQLRowCount(conn->stmt, &affected_rows);
393  if (sql_check_error(error_handle, handle, config)) return -1;
394 
395  return affected_rows;
396 }
397 
398 
399 /* Exported to rlm_sql */
401 rlm_sql_module_t rlm_sql_unixodbc = {
402  .name = "rlm_sql_unixodbc",
403  .sql_socket_init = sql_socket_init,
404  .sql_query = sql_query,
405  .sql_select_query = sql_select_query,
406  .sql_num_fields = sql_num_fields,
407  .sql_affected_rows = sql_affected_rows,
408  .sql_fields = sql_fields,
409  .sql_fetch_row = sql_fetch_row,
410  .sql_free_result = sql_free_result,
411  .sql_error = sql_error,
412  .sql_finish_query = sql_finish_query,
413  .sql_finish_select_query = sql_finish_select_query
414 };
USES_APPLE_DEPRECATED_API struct rlm_sql_unixodbc_conn rlm_sql_unixodbc_conn_t
General connection/server error.
Definition: rlm_sql.h:46
static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen, rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
Retrieves any errors associated with the connection handle.
Prototypes and functions for the SQL module.
char ** rlm_sql_row_t
Definition: rlm_sql.h:59
log_type_t type
Type of log entry L_ERR, L_WARN, L_INFO, L_DBG etc..
Definition: rlm_sql.h:62
#define MEM(x)
Definition: radiusd.h:396
static sql_rcode_t sql_fetch_row(rlm_sql_row_t *out, rlm_sql_handle_t *handle, rlm_sql_config_t *config)
#define INFO(fmt,...)
Definition: log.h:143
char const * msg
Log message.
Definition: rlm_sql.h:63
#define UNUSED
Definition: libradius.h:134
Error message.
Definition: log.h:36
char const * sql_server
Server to connect to.
Definition: rlm_sql.h:85
uint8_t length
Definition: proto_bfd.c:203
static float timeout
Definition: radclient.c:43
static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
static int _sql_socket_destructor(rlm_sql_unixodbc_conn_t *conn)
static USES_APPLE_DEPRECATED_API int sql_check_error(long err_handle, rlm_sql_handle_t *handle, rlm_sql_config_t *config)
#define rad_assert(expr)
Definition: rad_assert.h:38
Stale connection, should reconnect.
Definition: rlm_sql.h:48
char const * name
Definition: rlm_sql.h:191
#define DEBUG(fmt,...)
Definition: log.h:175
sql_rcode_t
Definition: rlm_sql.h:44
#define DEBUG2(fmt,...)
Definition: log.h:176
Definition: rlm_sql.h:61
static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
Success.
Definition: rlm_sql.h:47
char const * sql_password
Login password to use.
Definition: rlm_sql.h:88
void * conn
Database specific connection handle.
Definition: rlm_sql.h:153
rlm_sql_module_t rlm_sql_unixodbc
static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config, struct timeval const *timeout)
rlm_sql_row_t row
Row data from the last query.
Definition: rlm_sql.h:154
unsigned int state
Definition: proto_bfd.c:200
static int sql_num_fields(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
char const * sql_login
Login credentials to use.
Definition: rlm_sql.h:87
static sql_rcode_t sql_finish_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
static int affected_rows(PGresult *result)
Return the number of affected rows of the result as an int instead of the string that postgresql prov...
static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
size_t strlcpy(char *dst, char const *src, size_t siz)
Definition: strlcpy.c:38
#define FR_TIMEVAL_TO_MS(_x)
Definition: conffile.h:235
#define RCSID(id)
Definition: build.h:135
static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
#define ERROR(fmt,...)
Definition: log.h:145
static sql_rcode_t sql_finish_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
#define USES_APPLE_DEPRECATED_API
Definition: build.h:122