Catching errors in SQL xlat queries

[Alan DeKok]

On May 26, 2016, at 5:59 PM, Pshem Kowalczyk <pshem.k at gmail.com> wrote:
> We have a setup where a main frontend  cluster (3.0.11) proxies requests to
> various remote radius servers.
> We validate and adjust responses from those serves to match the
> requirements of a particular BNGs the session is on (this is for a
> DSL/fibre setup). For example if the remote radius system wants the speed
> of the connection limited to a given number they reply with our own
> 'SV-Speed-Down' attribute and a value. We do some SQL lookups:

  OK...

> update {
>        &control:Tmp-String-0 := "%{frontend_sql:SELECT prefix || '#' ||
> suffix FROM attrib_map, nas_type \
>                                                    WHERE
> nas_type.nasid='%{request:NAS-Identifier}' AND \
>                                                    attrib_map.nas_type =
> nas_type.type AND \
>                                                    attrib_map.attribute =
> 'SV-Speed-Down'}"
>    }
> 
> in order to figure out the actual syntax required for the particular BNG.
> Then we use a regex to parse out the details:
> 
> if (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/) {

  Which is fine.

> Recently we had some issues with the DB running out of steam (and handles).
> We've noticed that this didn't stop the queries from running and returning
> empty results (and not matching the regex).

  If the DB fails, the string expansion is empty.  You can do fail-over in string expansions.  See raddb/radiusd.conf, the "instantiate" section.

> With the regular rlm_sql queries the module returns 'fail' if there was a
> problem with the database or 'notfound' if there was nothing to return. Is
> there a way to detect the 'fail' state with the xlat queries?

  The simplest thing is to check for an empty string:

	update {
		&control:Tmp-String-0 := "%{front...
	}

	if ((&control:Tmp-String-0 != "") &&  (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/)) {
		...

  Alan DeKok.