Configuring SQL Queries

This article describes the syntax of SQL queries that are used by ClearBox Server 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:

• $u - denotes user name after all transformations are made to it (e.g. domain name was stripped off).
• $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 accounting 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 the password sent in the access request. Is available in 'check user password' authentication query for PAP passwords.
  

For example, if you configure the authentication query <SELECT Password FROM Users WHERE Username='$u' AND CurrentBallance>0 AND UserRealm='$r'> in the realm 'MySuperRealm' 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='MySuperRealm'>. 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 the authentication/accounting request packet into the query:
{<$>Attribute Name<:tag><?Default value>} or {<$>Attribute type<:vendor ID:vendor type><?Default value>}.

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. 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} and {26:311:10}, where 311 is Microsoft vendor ID.

Sample query: 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 the actual attribute value with '1' tag value.

Cisco Specific Attributes Options

ClearBox uses special syntax for specifyingCisco-AV-Pair attributes in SQL queries. 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 attributes have the similar syntax. For example, the attribute h323-call-type may have the value "h323-call-type=Telephony". 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 the value "Telephony".