SQL Logging quirks with cisco-avpair

[Nick Porter]

Hi Mike

What you are seeing there is the escaping of the attributes in your SQL 
query.  The "=" in the attribute values has been encoded as =3D.

rlm_sql defines a set of safe characters, and all other characters get 
escaped in this manner to protect from SQL injection attacks.

Logging out to files doesn't require the same level of caution, hence 
the attribute appearing without any escaping there.

I would suggest that you are better off getting FreeRADIUS to interpret 
the Cisco-AVPair attributes by enabling the "with_cisco_vsa_hack" option 
of the preprocess module.

Presuming you are calling preprocess module early in the packet 
handling, then the Cisco-AVPair attributes will be turned into 
individual attributes correctly named.

Nick

On 04/04/2022 06:37, Mike Manning wrote:
> Hi all,
>
> I have a strange issue that I have seen pop up on a thread years ago but cannot for the life of me find the answer to it anymore.  I have a freeradius 3.0 server set up handling authentication requests from Cisco ASR's for our internet users.  We're sending through extended attributes in our AAA data from our ASR's and can see the details in our radacct files (details removed for privacy):
>
> Mon Apr  4 00:01:09 2022
>          Acct-Session-Id = "0003E6A0"
>          Framed-Protocol = PPP
>          Framed-IP-Address = x.x.x.x
>          User-Name = "username at realm"
>          X-Ascend-Connect-Progress = LAN-Session-Up
>          Cisco-AVPair = "connect-progress=LAN Ses Up"
>          X-Ascend-PreSession-Time = 1
>          X-Ascend-Xmit-Rate = 1410065408
>          Cisco-AVPair = "nas-tx-speed=1410065408"
>          Cisco-AVPair = "nas-tx-speed-kbps=1410065"
>          X-Ascend-Data-Rate = 1410065408
>          Cisco-AVPair = "nas-rx-speed=1410065408"
>          Cisco-AVPair = "nas-rx-speed-kbps=1410065"
>          Acct-Session-Time = 888770
>          Acct-Input-Gigawords = 151
>          Acct-Output-Gigawords = 18
>          Acct-Input-Octets = 1877053998
>          Acct-Output-Octets = 3577481241
>          WLAN-RF-Band = 83
>          X-Ascend-Pre-Output-Octets = 53
>          Acct-Input-Packets = 494627556
>          Acct-Output-Packets = 336289465
>          X-Ascend-Pre-Input-Packets = 4
>          X-Ascend-Pre-Output-Packets = 4
>          Acct-Authentic = RADIUS
>          Acct-Status-Type = Interim-Update
>          NAS-Port-Type = Virtual
>          NAS-Port = 0
>          NAS-Port-Id = "0/1/0/2066.123"
>          Cisco-AVPair = "client-mac-address=7c5a.xxxx.xxxx"
>          Cisco-AVPair = "circuit-id-tag=AVC000xxxxxxxx"
>          Service-Type = Framed-User
>          NAS-IP-Address = y.y.y.y
>          Acct-Delay-Time = 0
>          Event-Timestamp = "Apr  4 2022 00:01:09 AEST"
>          Tmp-String-9 = "ai:"
>
> As you can see in the radacct files, the Cisco-AVPair is being written correctly for the client-mac-address and circuit-id tag.  This also appears correctly in the /var/log/freeradius/radius.log file as I have the following 2 lines in radiusd.conf
>
>          msg_goodpass = "Mac: %{Cisco-AVPair[0]}, Circuit ID: %{Cisco-AVPair[1]}"
>          msg_badpass = "Mac: %{Cisco-AVPair[0]}, Circuit ID: %{Cisco-AVPair[1]}"
>
> Mon Apr  4 15:23:43 2022 : Auth: (3463054) Login OK: [username at realm] (from client CLIENTNAME port 0) Mac: client-mac-address=7483.xxxx.yyyy, Circuit ID: circuit-id-tag=AVC00xxxxxxx
>
> Here's the strange part.  I am logging the circuit-id and also mac address to the postauth table as follows in the SQL db.  Here is the extract from /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf:
>
> post-auth {
>          # Write SQL queries to a logfile. This is potentially useful for bulk inserts
>          # when used with the rlm_sql_null driver.
> #       logfile = ${logdir}/post-auth.sql
>
>          query = "\
>                  INSERT IGNORE INTO ${..postauth_table} \
>                          (username, pass, reply, authdate, macaddress, circuitid) \
>                  VALUES ( \
>                          '%{SQL-User-Name}', \
>                          '%{%{User-Password}:-%{Chap-Password}}', \
>                          '%{reply:Packet-Type}', \
>                          '%S', \
>                          '%{Cisco-AVPair[0]}', \
>                          '%{Cisco-AVPair[1]}')"
>
> This proceeds to log the details in the SQL DB however both cisco-avpair entries are prepended with a "3D" entry:
>
> INSERT INTO `radpostauth` (`id`, `username`, `reply`, `authdate`, `macaddress`, `circuitid`) VALUES
> (131788, 'username at realm', 'Access-Accept', '2022-04-04 05:08:41', 'client-mac-address=3D7c5a.xxxx.yyyy', 'circuit-id-tag=3DAVC0000xxxxxx');
>
> This does not appear in any other log files, is there something to remove the "3D" header that these entries have when logging?
>
> Cheers, Mike
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-- 
Nick Porter

Porter Computing Ltd
Registered in England No 12659380

-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_signature
Type: application/pgp-signature
Size: 665 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20220404/9a1a5fcc/attachment.sig>