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
