safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
sql_user_name = "%{User-Name}"
client_query = "\
	SELECT id, nasname, shortname, type, secret, server \
	FROM ${client_table}"
authorize_check_query = "\
	SELECT id, username, attribute, value, op \
	FROM ${authcheck_table} \
	WHERE username = '%{SQL-User-Name}' \
	ORDER BY id"
authorize_reply_query = "\
	SELECT id, username, attribute, value, op \
	FROM ${authreply_table} \
	WHERE username = '%{SQL-User-Name}' \
	ORDER BY id"
group_membership_query = "\
	SELECT groupname \
	FROM ${usergroup_table} \
	WHERE username = '%{SQL-User-Name}' \
	ORDER BY priority"
authorize_group_check_query = "\
	SELECT id, groupname, attribute, \
	Value, op \
	FROM ${groupcheck_table} \
	WHERE groupname = '%{${group_attribute}}' \
	ORDER BY id"
authorize_group_reply_query = "\
	SELECT id, groupname, attribute, \
	value, op \
	FROM ${groupreply_table} \
	WHERE groupname = '%{${group_attribute}}' \
	ORDER BY id"
simul_count_query = "\
	SELECT COUNT(*) \
	FROM ${acct_table1} \
	WHERE username = '%{SQL-User-Name}' \
	AND acctstoptime IS NULL"
simul_verify_query = "\
	SELECT \
		radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
		callingstationid, framedprotocol \
	FROM ${acct_table1} \
	WHERE username = '%{SQL-User-Name}' \
	AND acctstoptime IS NULL"
accounting {
	reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
	# Write SQL queries to a logfile. This is potentially useful for bulk inserts
	# when used with the rlm_sql_null driver.
	column_list = "\
		acctsessionid,		acctuniqueid,		username, \
		realm,			nasipaddress,		nasportid, \
		nasporttype,		acctstarttime,		acctupdatetime, \
		acctstoptime,		acctsessiontime, 	acctauthentic, \
		connectinfo_start,	connectinfo_stop, 	acctinputoctets, \
		acctoutputoctets,	calledstationid, 	callingstationid, \
		acctterminatecause,	servicetype,		framedprotocol, \
		framedipaddress"
	type {
		accounting-on {
			#
			#  Bulk terminate all sessions associated with a given NAS
			#
			query = "\
				UPDATE ${....acct_table1} \
				SET \
					acctstoptime = FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctsessiontime	= '%{integer:Event-Timestamp}' \
						- UNIX_TIMESTAMP(acctstarttime), \
					acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
				WHERE acctstoptime IS NULL \
				AND nasipaddress   = '%{NAS-IP-Address}' \
				AND acctstarttime <= FROM_UNIXTIME(\
					%{integer:Event-Timestamp})"
		}
		accounting-off {
			query = "${..accounting-on.query}"
		}
		#
		#  Implement the "sql_session_start" policy.
		#  See raddb/policy.d/accounting for more details.
		#
		#  You also need to fix the other queries as
		#  documented below.  Look for "sql_session_start".
		#
		post-auth {
			query = "\
			INSERT INTO ${....acct_table1} \
				(${...column_list}) \
			VALUES(\
				'%{Acct-Session-Id}', \
				'%{Acct-Unique-Session-Id}', \
				'%{SQL-User-Name}', \
				'%{Realm}', \
				'%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
				NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
				'%{NAS-Port-Type}', \
				FROM_UNIXTIME(%{integer:Event-Timestamp}), \
				NULL, \
				NULL, \
				0, \
				'', \
				'%{Connect-Info}', \
				NULL, \
				0, \
				0, \
				'%{Called-Station-Id}', \
				'%{Calling-Station-Id}', \
				NULL, \
				'%{Service-Type}', \
				NULL, \
				'')"
			query = "\
				UPDATE ${....acct_table1} SET \
					AcctStartTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					ConnectInfo_start = '%{Connect-Info}', \
					AcctSessionId = '%{Acct-Session-Id}' \
				WHERE UserName = '%{SQL-User-Name}' \
					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
					AND NASPortType = '%{NAS-Port-Type}' \
					AND AcctStopTime IS NULL"
		}
		start {
			#
			#  Insert a new record into the sessions table
			#
			query = "\
				INSERT INTO ${....acct_table1} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					NULL, \
					'0', \
					'%{Acct-Authentic}', \
					'%{Connect-Info}', \
					'', \
					'0', \
					'0', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
			#
			#  When using "sql_session_start", you should comment out
			#  the previous query, and enable this one.
			#
			#  Just change the previous query to "-query",
			#  and this one to "query".  The previous one
			#  will be ignored, and this one will be
			#  enabled.
			#
			-query = "\
				UPDATE ${....acct_table1} \
				SET \
					AcctSessionId = '%{Acct-Session-Id}', \
					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
					AcctAuthentic = '%{Acct-Authentic}', \
					ConnectInfo_start = '%{Connect-Info}', \
					ServiceType = '%{Service-Type}', \
					FramedProtocol = '%{Framed-Protocol}', \
					framedipaddress = '%{Framed-IP-Address}', \
					AcctStartTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}) \
				WHERE UserName = '%{SQL-User-Name}' \
					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
					AND NASPortType = '%{NAS-Port-Type}' \
					AND AcctStopTime IS NULL"
			#
			#  Key constraints prevented us from inserting a new session,
			#  use the alternate query to update an existing session.
			#
			query = "\
				UPDATE ${....acct_table1} SET \
					acctstarttime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					acctupdatetime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					connectinfo_start = '%{Connect-Info}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
		}
		interim-update {
			#
			#  Update an existing session and calculate the interval
			#  between the last data we received for the session and this
			#  update. This can be used to find stale sessions.
			#
			query = "\
				UPDATE ${....acct_table1} \
				SET \
					acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), \
					acctupdatetime  = FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctinterval    = %{integer:Event-Timestamp} - \
						UNIX_TIMESTAMP(@acctupdatetime_old), \
					framedipaddress = '%{Framed-IP-Address}', \
					acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
					acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
			#
			#  The update condition matched no existing sessions. Use
			#  the values provided in the update to create a new session.
			#
			query = "\
				INSERT INTO ${....acct_table1} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					NULL, \
					%{%{Acct-Session-Time}:-NULL}, \
					'%{Acct-Authentic}', \
					'%{Connect-Info}', \
					'', \
					'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
					'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
			#
			#  When using "sql_session_start", you should comment out
			#  the previous query, and enable this one.
			#
			#  Just change the previous query to "-query",
			#  and this one to "query".  The previous one
			#  will be ignored, and this one will be
			#  enabled.
			#
			-query = "\
				UPDATE ${....acct_table1} \
				SET \
					AcctSessionId = '%{Acct-Session-Id}', \
					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
					AcctAuthentic = '%{Acct-Authentic}', \
					ConnectInfo_start = '%{Connect-Info}', \
					ServiceType = '%{Service-Type}', \
					FramedProtocol = '%{Framed-Protocol}', \
					framedipaddress = '%{Framed-IP-Address}', \
					AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \
					AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					AcctOutputOctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}' \
				WHERE UserName = '%{SQL-User-Name}' \
					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
					AND NASPortType = '%{NAS-Port-Type}' \
					AND AcctStopTime IS NULL"
		}
		stop {
			#
			#  Session has terminated, update the stop time and statistics.
			#
			query = "\
				UPDATE ${....acct_table2} SET \
					acctstoptime	= FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctsessiontime	= %{%{Acct-Session-Time}:-NULL}, \
					acctinputoctets	= '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}', \
					acctterminatecause = '%{Acct-Terminate-Cause}', \
					connectinfo_stop = '%{Connect-Info}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
			#
			#  The update condition matched no existing sessions. Use
			#  the values provided in the update to create a new session.
			#
			query = "\
				INSERT INTO ${....acct_table2} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					%{%{Acct-Session-Time}:-NULL}, \
					'%{Acct-Authentic}', \
					'', \
					'%{Connect-Info}', \
					'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
					'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'%{Acct-Terminate-Cause}', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
			#
			#  When using "sql_session_start", you should comment out
			#  the previous query, and enable this one.
			#
			#  Just change the previous query to "-query",
			#  and this one to "query".  The previous one
			#  will be ignored, and this one will be
			#  enabled.
			#
			-query = "\
				UPDATE ${....acct_table1} \
				SET \
					AcctSessionId = '%{Acct-Session-Id}', \
					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
					AcctAuthentic = '%{Acct-Authentic}', \
					ConnectInfo_start = '%{Connect-Info}', \
					ServiceType = '%{Service-Type}', \
					FramedProtocol = '%{Framed-Protocol}', \
					framedipaddress = '%{Framed-IP-Address}', \
					AcctStopTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					AcctSessionTime = %{Acct-Session-Time}, \
					AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					AcctOutputOctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}', \
					AcctTerminateCause = '%{Acct-Terminate-Cause}', \
					ConnectInfo_stop = '%{Connect-Info}' \
				WHERE UserName = '%{SQL-User-Name}' \
				AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
				AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
				AND NASPortType = '%{NAS-Port-Type}' \
				AND AcctStopTime IS NULL"
		}
		#
		#  No Acct-Status-Type == ignore the packet
		#
		accounting {
			query = "SELECT true"
		}
	}
}
post-auth {
	# Write SQL queries to a logfile. This is potentially useful for bulk inserts
	# when used with the rlm_sql_null driver.
	query =	"\
		INSERT INTO ${..postauth_table} \
			(username, pass, reply, authdate) \
		VALUES ( \
			'%{SQL-User-Name}', \
			'%{%{User-Password}:-%{Chap-Password}}', \
			'%{reply:Packet-Type}', \
			'%S')"
}