(RADIATOR) Problem with quoted variable substitution and AuthSQL

Tue Apr 4 20:33:01 CDT 2006

Hugh:

	I'm currently using AuthSQL to store data from accounting
requests.  However, I'm looking to switch from the AcctColumnDef
to the use of AcctSQLStatement to launch a stored procedure to
manage this data.  The reason for the switch is that my stored
procedure will call other stored procedures that presently are
stored in a different database so as to map out some of the
string types to an integer index.

	However, while I was experimenting with this -- I ran into
the problem that I noticed that my request variable substitutions
towards the end of the AcctSQLStatement were not being translated,
specifically in this example for Called-Station-Id and 
Calling-Station-Id which I verified were in every accounting request
that I tested with.
	Turning Trace to 4 also showed these av-pairs in the packet,
but they were not being found as the variable lookup.  After looking
through this, I'm wondering if I'm possibly running into a problem
with quotes as I'm noticing that the attributes I'm not finding in
my formatted AcctSQLStatement are the string types which have their
own quotes.
	If there's a way to have the substitutions return single
rather than double quotes, that would work as well -- since would
prefer single quoted data to be in line with SQL.

Below is the AuthBy clause and a cut from the trace 4 logfile 
demonstrating this on Radiator 3.14, ActiveState 5.8.0, Win2K:

<AuthBy SQL>

	Identifier	SitestarAcct2

         DBSource        	%{GlobalVar:ACCOUNTING_DB}
	DBUsername		%{GlobalVar:databaseuser}
         DBAuth          	%{GlobalVar:databasepass}
	FailureBackoffTime	%{GlobalVar:databackofftime}

	# Temporary strip realms.
	# RewriteUsername		s/^([^\@]+).*/$1/

	AuthSelect
	IgnoreAuthentication

	HandleAcctStatusTypes	Start,Stop
	AccountingTable	Calls

	AcctSQLStatement	CallInsert @UserName='%U',\
		@Realm='%R',\
		@CallDate='%A',\
		@AcctStatusType=%{IntegerVal:Acct-Status-Type},\
		@AcctDelayTime=0%{Acct-Delay-Time},\
		@AcctInputOctets=0%{Acct-Input-Octets},\
		@AcctOutputOctets=0%{Acct-Output-Octets},\
		@AcctSessionID='%{Acct-Session-Id}',\
		@AcctSessionTime=0%{Acct-Session-Time},\

@AcctTerminateCause=%{IntegerVal:Acct-Terminate-Cause}%{IntegerVal:Ascend-Disconnect-Cause},\
		@NASIdentifier='%N',\
		@NASPort=0%{IntegerVal:NAS-Port},\
		@UserService=0%{Service-Type},\
		@FramedProtocol=0%{IntegerVal:Framed-Protocol},\
		@FramedAddress='%{Framed-IP-Address}',\
		@NASPortType=0%{IntegerVal:NAS-Port-Type},\
		@NASPortDNIS='%{Called-Station-Id}',\
		@CallerID='%{Calling-Station-Id}',\
		@ConnectInfo='%{Connect-Info}'

</AuthBy SQL>

*** Received from 72.236.200.5 port 1646 ....
Code:       Accounting-Request
Identifier: 82
Authentic:  X} <185>=<137><164><204><142>-<204><4><159><201><131><194>
Attributes:
	User-Name = "mdwhite"
	NAS-IP-Address = 67.63.128.5
	NAS-Identifier = "67.63.128.5"
	Acct-Status-Type = Stop
	Acct-Session-Id = "16975425"
	Acct-Delay-Time = 240
	Acct-Authentic = RADIUS
	Service-Type = Framed-User
	NAS-Port-Type = 0
	NAS-Port = 260
	USR-Modem-Training-Time = 27
	USR-Interface-Index = 1516
	USR-Chassis-Call-Slot = 2
	USR-Chassis-Call-Span = 1
	USR-Chassis-Call-Channel = 4
	USR-Unauthenticated-Time = 3
	Calling-Station-Id = "2766944917"
	Called-Station-Id = "2766936013"
	USR-VPN-ID = 0
	USR-Modulation-Type = v90Digital
	USR-Simplified-MNP-Levels = ccittV42
	USR-Simplified-V42bis-Usage = ccittV42bis
	USR-Connect-Speed = 38666_BPS
	Framed-Protocol = PPP
	Framed-IP-Address = 72.236.201.242
	USR-VTS-Session-Key = 
"<148><178><233><236>h<230>P<233><249><162><221><16><130><18>w`"
	USR-Call-Arrived-Time = 323720333
	USR-Call-Lost-Time = 323723045
	USR-Bogus-39087 = ""
	USR-Connect-Term-Reason = 61
	USR-Failure-to-Connect-Reason = 31
	USR-Blocks-Sent = 23896
	USR-Blocks-Resent = 33
	USR-Number-of-Blers = 3
	USR-Initial-Tx-Link-Data-Rate = 19
	USR-Final-Tx-Link-Data-Rate = 19
	USR-Bogus-39079 = "<0><0><0><8>"
	USR-Bogus-39080 = "<0><0><31><219>"
	USR-Initial-Rx-Link-Data-Rate = 18
	USR-Final-Rx-Link-Data-Rate = 18
	USR-Retrains-Requested = 0
	USR-Retrains-Granted = 2
	Acct-Session-Time = 2685
	Acct-Terminate-Cause = User-Request
	USR-Disconnect-Reason = 8
	USR-Speed-of-Connection = 38666
	Acct-Input-Octets = 120750
	Acct-Output-Octets = 987605
	Acct-Input-Packets = 1952
	Acct-Output-Packets = 2288

Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Rewrote user name to mdwhite
Tue Apr  4 20:18:43 2006: DEBUG: Handling request with Handler ''
Tue Apr  4 20:18:43 2006: DEBUG:  Deleting session for mdwhite, 
67.63.128.5, 260
Tue Apr  4 20:18:43 2006: DEBUG: Handling with Radius::AuthSQL
Tue Apr  4 20:18:43 2006: DEBUG: Handling accounting with Radius::AuthSQL
Tue Apr  4 20:18:43 2006: ERR: There is no value named  for attribute 
Ascend-Disconnect-Cause. Using 0.
Tue Apr  4 20:18:43 2006: DEBUG: do query is: 'CallInsert 
@UserName='mdwhite', at Realm='', at CallDate='Apr  4, 2006 
20:14', at AcctStatusType=2, at AcctDelayTime=0240, at AcctInputOctets=0120750, at AcctOutputOctets=0987605, at AcctSessionID='16975425', at AcctSessionTime=02685, at AcctTerminateCause=10, at NASIdentifier='67.63.128.5', at NASPort=00, at UserService=0, at FramedProtocol=00, at FramedAddress='', at NASPortType=00, at NASPortDNIS='', at CallerID='', at ConnectInfo=''': 

Tue Apr  4 20:18:43 2006: DEBUG: AuthBy SQL result: ACCEPT,
Tue Apr  4 20:18:43 2006: DEBUG: Handling with Radius::AuthSITESTAR
Tue Apr  4 20:18:43 2006: DEBUG: AuthBy SITESTAR result: IGNORE, Ignored 
due to IgnoreAccounting
Tue Apr  4 20:18:43 2006: DEBUG: Packet dump:

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.