SQL-User-Name should be removed after rlm_sql finishes processing a request

[philmayers]

If you do this:

authorize {
  update request {
    Tmp-String-0 := "%{sql:select '%{SQL-User-Name}'}"
  }
}

...it doesn't work because the %{SQL-User-Name} seems to have been expanded by the xlat machinery before calling sql_xlat. This code:

https://github.com/FreeRADIUS/freeradius-server/blob/master/src/modules/rlm_sql/rlm_sql.c#L152

....is therefore not doing what it should be.

This works:

authorize {
  sql
  update request {
    Tmp-String-0 := "%{sql:select '%{SQL-User-Name}'}"
  }
}

...because the SQL-User-Name is left lying around on the request from the sql.authorize call for the xlat to find.

[arr2036]

There's no good fix for this. The best we can do is guarantee consistent behaviour by removing Sql-User-Name from the request before returning from any of rlm_sql's mod_* functions.

The reason why SQL-User-Name is needed, is to allow for querying with alternative usernames to allow the 'profile' functionality to work. There's no reason to use it in an xlat string, User-Name will still be expanded and escaped correctly.

[philmayers]

I don't mind if it's left lying around (though IIRC the old code did indeed remove it before returning). What I noticed was it no longer working in xlats, unless you'd already called one which is weirdly inconsistent.

If it shouldn't be used in an XLAT then a) that's a change in behaviour and b) it should definitely be removed before returning to avoid people doing that and seeing the inconsistent behaviour above.

FWIW the reason we use it is to avoid cluttering up the SQL xlat queries with %{%{%{%{%{%{%{%{% into infinity when doing complex fallback of various values. It's also faster to do this once before entering the query. Obviously I can (and now will) just define a local attribute and set it with a policy at the top of all of our virtual servers. Yay DRY oh wait no.

[arr2036]

Or if you really want to confuse people:

# outside server
sql_username = "%{%{Stripped-User-Name}:-%{User-Name}}"

"%{sql:SELECT * FROM BLAH WHERE user='${sql_username}'}"

[arr2036]

Expansion of ${} has gotten better to the point that they're almost like macros when used in value string and conditions.

[philmayers]

Hey neat. That works for me.

[alandekok]

Can we close this?

also, if SQL-User-Name is confusing, we should look at replacing it with something better.

[arr2036]

no, we need to remove SQL-User-name before returning from module functions