Stored procedures in rlm_sql_mysql
This patch allows me to use stored procedures in in sql.conf. For example instead authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = BINARY '%{SQL-User-Name}' \ ORDER BY id" I can use authorize_check_query = "CALL auth_check('%{SQL-User-Name}')" and store all code in the database. -------------------------------------- --- sql_mysql_orig.c 2007-04-05 14:52:38.000000000 +0400 +++ sql_mysql.c 2007-05-17 00:22:59.000000000 +0400 @@ -52,6 +52,9 @@ SQL_ROW row; } rlm_sql_mysql_sock; +/*static int sql_free_result(SQLSOCK*, SQL_CONFIG*); */ + + /************************************************************************* * * Function: sql_create_socket @@ -84,7 +87,7 @@ config->sql_db, atoi(config->sql_port), NULL, - CLIENT_FOUND_ROWS))) { + CLIENT_FOUND_ROWS| CLIENT_MULTI_RESULTS))) { radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server %s@%s:%s", config->sql_login, config->sql_server, config->sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -305,13 +308,35 @@ *************************************************************************/ static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config) { + int status; rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn; if (mysql_sock->result) { mysql_free_result(mysql_sock->result); mysql_sock->result = NULL; } + + /* + * http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html + * + * Multiple-result processing also is required if you execute CALL + * statements for stored procedures: A stored procedure returns a + * status result when it terminates, but it may also produce result + * sets as it runs (for example, if it executes SELECT statements). For + * any stored procedure that produces result sets in addition to the + * final status, you must be prepared to retrieve multiple results. + * */ + + status = mysql_next_result(mysql_sock); + while(status == 0) { + if ((status = mysql_next_result(mysql_sock)) > 0) + radlog(L_ERR, "rlm_sql_mysql: Error while fetching next result in result set."); + /* I do not need them - just discard it */ + radlog(L_DBG, "rlm_sql_mysql: Discarded an result from multiple result request"); + mysql_store_result(mysql_sock->sock); + } return 0; }
This code is specific to MySQL 4.1 and higher installations, so the MySQL version would have to be checked during build to insure its being linked against the appropriate library versions... --Mike On May 16, 2007, at 3:30 PM, Michael A Baikov wrote:
This patch allows me to use stored procedures in in sql.conf. For example instead
authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = BINARY '%{SQL-User-Name}' \ ORDER BY id"
I can use authorize_check_query = "CALL auth_check('%{SQL-User-Name}')" and store all code in the database.
--------------------------------------
--- sql_mysql_orig.c 2007-04-05 14:52:38.000000000 +0400 +++ sql_mysql.c 2007-05-17 00:22:59.000000000 +0400 @@ -52,6 +52,9 @@ SQL_ROW row; } rlm_sql_mysql_sock;
sql_port), NULL,
+/*static int sql_free_result(SQLSOCK*, SQL_CONFIG*); */ + + / ********************************************************************** *** * * Function: sql_create_socket @@ -84,7 +87,7 @@ config->sql_db, atoi(config- - CLIENT_FOUND_ROWS))) { + CLIENT_FOUND_ROWS| CLIENT_MULTI_RESULTS))) { radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server %s@%s:%s", config->sql_login, config->sql_server, config-
sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -305,13 +308,35 @@
********************************************************************** ***/ static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG *config) { + int status; rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
if (mysql_sock->result) { mysql_free_result(mysql_sock->result); mysql_sock->result = NULL; } + + /* + * http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple- queries.html + * + * Multiple-result processing also is required if you execute CALL + * statements for stored procedures: A stored procedure returns a + * status result when it terminates, but it may also produce result + * sets as it runs (for example, if it executes SELECT statements). For + * any stored procedure that produces result sets in addition to the + * final status, you must be prepared to retrieve multiple results. + * */ + + status = mysql_next_result(mysql_sock); + while(status == 0) { + if ((status = mysql_next_result(mysql_sock)) > 0) + radlog(L_ERR, "rlm_sql_mysql: Error while fetching next result in result set."); + /* I do not need them - just discard it */
+ radlog(L_DBG, "rlm_sql_mysql: Discarded an result from multiple result request"); + mysql_store_result(mysql_sock->sock); + } return 0; } - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/ devel.html
I think i am not very strong in usage of autotools. Can anybody add those checks? On Thursday 17 May 2007 00:42:30 Michael Griego wrote:
This code is specific to MySQL 4.1 and higher installations, so the MySQL version would have to be checked during build to insure its being linked against the appropriate library versions...
--Mike
On May 16, 2007, at 3:30 PM, Michael A Baikov wrote:
This patch allows me to use stored procedures in in sql.conf.
Michael A Baikov wrote:
This patch allows me to use stored procedures in in sql.conf.
Did you try the patch previously posted on this list? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
participants (3)
-
Alan DeKok -
Michael A Baikov -
Michael Griego