Double quoting in sql?
Hi! I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one small problem in the sql module: a Username seems to be quoted two times, first when setting sql_user_name, then when doing the xlat on the whole query. Am I just missing a config change? From the sample config I can see no difference. Fix: I use %{User-Name} in the queries instead of %{SQL-User-Name} Config: sql_user_name = "%{User-Name}" authorize_check_query = "SELECT id,name,attr,value,op FROM ${authcheck_table} WHERE name = '%{SQL-User-Name}' AND kind = 'user' AND type = 'check' ORDER BY id" Debug output: radius_xlat: 'test=23test' rlm_sql (sql): sql_set_user escaped user --> 'test=23test' radius_xlat: 'SELECT id,name,attr,value,op FROM radiususers WHERE name = 'test=3D23test' AND kind = 'user' AND type = 'check' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 9 rlm_sql_mysql: query: SELECT id,name,attr,value,op FROM radiususers WHERE name = 'test=3D23test' AND kind = 'user' AND type = 'check' ORDER BY id rlm_sql (sql): User test=23test not found in radcheck Oliver. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 09:39:07AM +0200, Oliver Graf wrote: > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > small problem in the sql module: a Username seems to be quoted two > times, first when setting sql_user_name, then when doing the xlat on > the whole query. > > Am I just missing a config change? From the sample config I can see no > difference. > > Fix: I use %{User-Name} in the queries instead of %{SQL-User-Name} Test Command: /usr/bin/radtest test#test test localhost 1 testing123 1 127.0.0.1 > Config: > sql_user_name = "%{User-Name}" > authorize_check_query = "SELECT id,name,attr,value,op FROM ${authcheck_table} WHERE > name = '%{SQL-User-Name}' AND kind = 'user' AND type = 'check' ORDER BY id" > > Debug output: > radius_xlat: 'test=23test' > rlm_sql (sql): sql_set_user escaped user --> 'test=23test' > radius_xlat: 'SELECT id,name,attr,value,op FROM radiususers WHERE name = > 'test=3D23test' AND kind = 'user' AND type = 'check' ORDER BY id' > rlm_sql (sql): Reserving sql socket id: 9 > rlm_sql_mysql: query: SELECT id,name,attr,value,op FROM radiususers WHERE name = > 'test=3D23test' AND kind = 'user' AND type = 'check' ORDER BY id > rlm_sql (sql): User test=23test not found in radcheck > > Oliver. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 09:39:07AM +0200, Oliver Graf wrote: > Hi! > > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > small problem in the sql module: a Username seems to be quoted two > times, first when setting sql_user_name, then when doing the xlat on > the whole query. IIRC this behavour is here since SQL-User-Name attribute is handled by rlm_sql, because it's being escaped twice. Two ways I see: 1. avoid using %{SQL-User-Name} in queries. 2. patch rlm_sql.c::sql_set_user to pass func=NULL to radius_xlat. However, in second case, radius_xlat uses own copy function (xlat_copy), which has "FIXME: Do escaping of bad stuff!" comment... -- Fduch M. Pravking - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 02:31:47PM +0400, Alexander M. Pravking wrote: > On Fri, Sep 24, 2004 at 09:39:07AM +0200, Oliver Graf wrote: > > Hi! > > > > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > > small problem in the sql module: a Username seems to be quoted two > > times, first when setting sql_user_name, then when doing the xlat on > > the whole query. > > IIRC this behavour is here since SQL-User-Name attribute is handled by > rlm_sql, because it's being escaped twice. Two ways I see: > 1. avoid using %{SQL-User-Name} in queries. > 2. patch rlm_sql.c::sql_set_user to pass func=NULL to radius_xlat. It does not seem that the change which causes this is in rlm_sql.c. I guess it is to search in variable expansion of main/xlat.c. But I currently fail to see the change between 0.9.3 and 1.0.1 where this happened... perhaps I will take a deeper look later. Oliver. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 12:39:09PM +0200, Oliver Graf wrote: > It does not seem that the change which causes this is in rlm_sql.c. I > guess it is to search in variable expansion of main/xlat.c. But I > currently fail to see the change between 0.9.3 and 1.0.1 where this > happened... perhaps I will take a deeper look later. Hmm... 0.9.3 did escaping for anything except: "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: =/", and the default setting of safe-characters is the same now, so the '#' char should have been escaped in 0.9.3 too. Didn't you patch rlm_sql.c of 0.9.3 to modify safe char list? ;-) -- Fduch M. Pravking - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 03:04:56PM +0400, Alexander M. Pravking wrote: > On Fri, Sep 24, 2004 at 12:39:09PM +0200, Oliver Graf wrote: > > It does not seem that the change which causes this is in rlm_sql.c. I > > guess it is to search in variable expansion of main/xlat.c. But I > > currently fail to see the change between 0.9.3 and 1.0.1 where this > > happened... perhaps I will take a deeper look later. > > Hmm... 0.9.3 did escaping for anything except: > "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: =/", > and the default setting of safe-characters is the same now, so the '#' > char should have been escaped in 0.9.3 too. Didn't you patch rlm_sql.c > of 0.9.3 to modify safe char list? ;-) Nope. I have a database with test=23test instead of test#test... :) Oliver. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
Oliver Graf <[EMAIL PROTECTED]> wrote: > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > small problem in the sql module: a Username seems to be quoted two > times, first when setting sql_user_name, then when doing the xlat on > the whole query. > > Am I just missing a config change? From the sample config I can see no > difference. I spent some time fixing xlat.c, so that it would only quote things ONCE. The issue I saw was escaping of backslashes, and doing: User-Name = "DOMAIN\\user" Filter-Id = "%{User-Name}" would get Filter-Id = "DOMAINuser", which is wrong. That *shouldn't* have affected anything else. > Debug output: > radius_xlat: 'test=23test' Something is escaping '#' to '=23', probably in the SQL module. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Fri, Sep 24, 2004 at 10:24:09AM -0400, Alan DeKok wrote: > Oliver Graf <[EMAIL PROTECTED]> wrote: > > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > > small problem in the sql module: a Username seems to be quoted two > > times, first when setting sql_user_name, then when doing the xlat on > > the whole query. > > > > Debug output: > > radius_xlat: 'test=23test' > > Something is escaping '#' to '=23', probably in the SQL module. Yeah. The Problem is that the allowed_chars string in 0.9.3 included '=', but the one in 1.0.1 does not. The pitty is that omitting '=' from allowed chars is obviously correct, cause its the char used to quote stuff. Like you need to use %% to get one %, an unescaped = should become a =3D. But cause radius_xlat (or whatever else...) does not know if a value of a pair is already escaped (as SQL-User-Name is), this creates some ugly double escaping. So the correct solution is to change the sql.conf and remove SQL-User-Name from it, cause freeradius 1.0.1 will escape pairs used inside queries always correctly, as it seems. Oliver. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Double quoting in sql?
On Wed, Sep 29, 2004 at 08:10:45AM +0200, Oliver Graf wrote: > On Fri, Sep 24, 2004 at 10:24:09AM -0400, Alan DeKok wrote: > > Oliver Graf <[EMAIL PROTECTED]> wrote: > > Something is escaping '#' to '=23', probably in the SQL module. > > Yeah. The Problem is that the allowed_chars string in 0.9.3 included > '=', but the one in 1.0.1 does not. > > But cause radius_xlat (or whatever else...) does not know if a value > of a pair is already escaped (as SQL-User-Name is), this creates some > ugly double escaping. > > So the correct solution is to change the sql.conf and remove > SQL-User-Name from it, cause freeradius 1.0.1 will escape pairs used > inside queries always correctly, as it seems. Wrong. Correct is: sql_set_user does NOT need to use sql_escape_func in radius_xlat. That way the SQL-User-Name pair is unescaped, as any other pair, and the radius_xlat (with sql_escape_func) that is run on the query will escape that pair correctly, as it does it for any other pair. Diff vs 1.0.1 attached. Oliver. --- freeradius-1.0.1/src/modules/rlm_sql/rlm_sql.c.orig 2004-09-29 08:15:55.0 +0200 +++ freeradius-1.0.1/src/modules/rlm_sql/rlm_sql.c 2004-09-29 08:16:37.0 +0200 @@ -459,7 +459,7 @@ if (username != NULL) { strNcpy(tmpuser, username, MAX_STRING_LEN); } else if (strlen(inst->config->query_user)) { - radius_xlat(tmpuser, sizeof(tmpuser), inst->config->query_user, request, sql_escape_func); + radius_xlat(tmpuser, sizeof(tmpuser), inst->config->query_user, request, NULL); } else { return 0; }
Re: Double quoting in sql?
On Wed, Sep 29, 2004 at 08:10:45AM +0200, Oliver Graf wrote: > On Fri, Sep 24, 2004 at 10:24:09AM -0400, Alan DeKok wrote: > > Oliver Graf <[EMAIL PROTECTED]> wrote: > > > I've upgraded recently from 0.9.3 to 1.0.1. There seems to be one > > > small problem in the sql module: a Username seems to be quoted two > > > times, first when setting sql_user_name, then when doing the xlat on > > > the whole query. > > > > > > Debug output: > > > radius_xlat: 'test=23test' > > > > Something is escaping '#' to '=23', probably in the SQL module. > > Yeah. The Problem is that the allowed_chars string in 0.9.3 included > '=', but the one in 1.0.1 does not. I'll take a risk to remind these threads... http://lists.cistron.nl/pipermail/freeradius-devel/2003-May/thread.html#4836 http://lists.cistron.nl/pipermail/freeradius-devel/2003-June/thread.html#4954 http://lists.cistron.nl/pipermail/freeradius-devel/2003-July/thread.html#5539 -- Fduch M. Pravking - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html