Re: How to execute TWO OR MORE Sql statement?

[Dan O'Neill]

Alan Lumb wrote:

Create a stored procedure in the database that contains all of the SQL
queries necessary. Then call that stored procedure via sql.conf. This
works fine with Postgres.



MySQL 5 supports stored proceedures and functions, however I know that
mysql proceedures can cause problems as they can/will return multiple data
sets that can cause some apps problems (not sure about freeradius and its
support for mysql).


Stored procedures have to be designed to return one or more rows.

Here's an example. I have heartbeat monitor running against Freeradius
to monitor authorization on a regular basis. I do not want these
heartbeat authentications written into the 'radpostauth' table.  The
username 'TylerDurden' is filtered out prior to inserting the postauth
record.

Here is the Postgres stored procedure in the RADIUS database:

CREATE FUNCTION filtered_insert_radpostauth(text, text, text) RETURNS
integer
AS $_$
DECLARE _new_filtered_insert_radpostauth_id  integer;
BEGIN
IF ( $1 = 'TylerDurden')
THEN
RETURN 0;
END IF;

INSERT INTO radpostauth (username, pass, reply, authdate)
VALUES ( $1, $2, $3, NOW() );
IF FOUND
THEN
SELECT INTO  _new_filtered_insert_radpostauth_id  MAX(id)
  FROM radpostauth;
RETURN   _new_filtered_insert_radpostauth_id;
END IF;

return -1;
END
$_$
LANGUAGE plpgsql;


ALTER FUNCTION public.filtered_insert_radpostauth(text, text, text)
OWNER TO postgres;

and the line in postgresql.conf looks like (it's wrapped here):

postauth_query = "SELECT filtered_insert_radpostauth( '%{User-Name}',
'%{User-Password:-Chap-Password}', '%{reply:Packet-Type}' )"

Everyone but TylerDurden gets logged because he doesn't really exist anyway.

Good luck!

Dan



- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: How to execute TWO OR MORE Sql statement?

[Dan O'Neill]

王世彦 wrote:
> Oh, my have another question about Freeradius.
> 
> I see when the Freeradius receives whatever a request, Freeradius will
> execute ONE SQL statement which is defined in the sql.conf.
> 
> My question is how to make Freeradius to execute TOW OR MORE SQL statement
> when Freeradius receives a request.
> 
> I am new to Freeradius and apologize for asking simple question.

Create a stored procedure in the database that contains all of the SQL
queries necessary. Then call that stored procedure via sql.conf. This
works fine with Postgres.

Dan

- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html