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.

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.

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'.