Configuring SQL Queries

ClearBox Server uses special extensions to the syntax of SQL commands used by to authenticate a user, to select an authorization list items, accounting or state server queries. The actual SQL dialect depends on the database source used.

You may use the following special keys in a query string to substitute them by user name, realm and other values from the request packet (still the use of keys is not limited by SQL commands; say, they may be used in LDAP strings):

• $u - denotes user name after all transformations are made to it (e.g. domain name was stripped off or user name was rewritten).
• $r - denotes current realm name chosen for packet processing.
• $c - denotes IP address in dotted form (e.g. 192.168.2.5) of a client which sent the original request.
• $n - denotes IP address of a NAS which issued the accounting request. If the request has not been forwarded it's the same as $c.
• $s - denotes a number of connections currently opened by the user. Is valid if a valid state server is selected in the State server ID list on the 'Authentication' tab.
• $p - denotes a password sent in access request. Is available only when there's a PAP password in a request.
• $f - denotes an IP address of the host where a request has been forwarded to by the RADIUS proxy.
• $h - denotes a 'reject hint' number selected by an authentication SQL command rejecting a user connection. It may be used in a Reject list.
• $x - extended user name, may get its value from the extended user name rewriting.
  

For example, if you configure the authentication query command <SELECT Password FROM Users WHERE Username='$u' AND CurrentBallance>0 AND UserRealm='$r'> in the realm 'MyRealm', then on reception of an access request with user name 'john' it's executed as <SELECT Password FROM Users WHERE Username='john' AND CurrentBallance>0 AND UserRealm='MyRealm'>. If no password is returned by the query or it doesn't match the password in the request, then user authentication is rejected.

RADIUS Attributes Options

Besides these special keys, you may insert attributes from authentication/accounting request packets into a SQL command string:
{<$>Attribute Name<:tag><?Default value>} or {<$>Attribute type<:vendor ID:vendor type><?Default value>} (<> means that this element is optional).

For example, {Framed-IP-Address} is substituted by Framed-IP-Address attribute value from the request packet. If $ is placed before attribute name, its numeric value is used instead of its literal alias. Suppose, there's Framed-Protocol=PPP in the access request, then {Framed-Protocol} is replaced by PPP, while {$Framed-Protocol} is replaced by 1. If an attribute is not found in the request, it may be replaced by a default value specified after ? sign: {$Service-Type?2} is replaced with 2 if Service-Type is not found.

Besides, attributes may be specified by their numeric equivalents: {Service-Type} is the same as {$6}. Vendor-Specific attributes are written in the form 26:vendor ID:vendor type. MS-CHAP-Domain, for instance, may be represented both as {MS-CHAP-Domain} or {26:311:10}, where 311 is Microsoft vendor ID.

Sample SQL command: INSERT INTO Accounting(User, Duration, IPAddress) VALUES('$u', {Session-Time}, '{Framed-IP-Address?0.0.0.0}')

Tagged attributes. If an attribute is tagged, i.e. describes a tunnel, the attribute name string can be appended by the tag value which is used to provide a means of grouping attributes in the same packet which refer to the same tunnel. Valid values for this field are 1 through 31, inclusive. If the tag zero (0), it is ignored (unused). For example, {Tunnel-Private-Group-ID:1} will be replaced by actual attribute value with '1' tag value.

Cisco and Quintum Specific Attributes Options

ClearBox uses special syntax for specifying Cisco-AV-Pair attributes in SQL commands. This attribute value has the form "actual-attribute-name=actual value". For example, Cisco-AV-Pair = "vpdn:ip-addresses=10.1.1.1". This value itself contains an attribute name "ip-addresses", the service specificator "vpdn" and the value "10.1.1.1". In order to make ClearBox log this value correctly, you should specify {Cisco-AV-Pair:vpdn:ip-addresses}.

Other Cisco and Quintum attributes have similar syntax. For example, h323-call-type attribute may have "h323-call-type=Telephony" value. ClearBox handles such attributes automatically, so when it meets {h323-call-type} in the SQL query string, it cuts off the "h323-call-type=" part, logging only "Telephony".

ClearBox automatically handles h323-setup-time, h323-connect-time and h323-setup-time attributes. If they are used in a SQL command with the '$' sign, the server converts they string values to date/time values and puts into a command (in the format corresponding to the regional settings). Thus, when the attribute-value pair is 'h323-setup-time=22:39:48.353 MSD Mon May 17 2004', {$h323-setup-time} will be changed to '05.17.2004 22:39:48'.