rlm_sql sql_escape_func

classic Classic list List threaded Threaded
5 messages Options
| Threaded
Open this post in threaded view
|

rlm_sql sql_escape_func

Hagen Münch
Hello,

I met the problem that if there are string values in a data base that contain single-quotes, the radius_axlat function expands a "foo'bar" to "foo27bar" by using the sql_escape_func of the rlm_sql module.

I solved it by adding

---snip---
        case '\'':
            if (outlen <= 2) break;
            out[0] = '\'';
            out[1] = '\'';

            in++;
            out += 2;
            outlen -= 2;
            len += 2;
            break;
---snip---
To the sql_escape_func.
Do you think this approach is appropriate and would it be possible to add this single-quote escape case to the v3.x source? Thank you.

Take care,
Hagen


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
| Threaded
Open this post in threaded view
|

Re: rlm_sql sql_escape_func

Alan DeKok-2
On Jan 8, 2019, at 9:32 AM, Hagen Münch <[hidden email]> wrote:
>
>
> I met the problem that if there are string values in a data base that contain single-quotes, the radius_axlat function expands a "foo'bar" to "foo27bar" by using the sql_escape_func of the rlm_sql module.

  That's what the SQL escape function does.

> I solved it by adding
> ...
> Do you think this approach is appropriate and would it be possible to add this single-quote escape case to the v3.x source? Thank you.

  It's not correct.

  You can set "sql_safe_characters" in the SQL configuration.  See raddb/mods-config/sql/main/*/queries.conf for more information.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
| Threaded
Open this post in threaded view
|

Re: rlm_sql sql_escape_func

Herwin Weststrate-2
Alan DeKok wrote:

> On Jan 8, 2019, at 9:32 AM, Hagen Münch <[hidden email]> wrote:
>>
>>
>> I met the problem that if there are string values in a data base that contain single-quotes, the radius_axlat function expands a "foo'bar" to "foo27bar" by using the sql_escape_func of the rlm_sql module.
>
>    That's what the SQL escape function does.
>
>> I solved it by adding
>> ...
>> Do you think this approach is appropriate and would it be possible to add this single-quote escape case to the v3.x source? Thank you.
>
>    It's not correct.
>
>    You can set "sql_safe_characters" in the SQL configuration.  See raddb/mods-config/sql/main/*/queries.conf for more information.

It is (or will become) a bit more complicated: both 4.x and 3.0.x (the
next 3.0 release) have a possibility to get a more specific escape
function per driver. This has been implemented for MySQL and Postgres.
It could be implemented for other drivers as well, it's just that nobody
has done that yet.
So you might want to have a look at the git version of 3.0, it may
contain code that solves your problem.

--
Herwin Weststrate
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
| Threaded
Open this post in threaded view
|

RE: rlm_sql sql_escape_func

Hagen Münch
In reply to this post by Alan DeKok-2
Sure, but then e.g.
UPDATE users set username = '%{Stripped-User-Name}' is expanded to UPDATE users SET username = 'foo'bar', because the single quote is not escaped and the execution of the query will fail. The statement should rather be xlated to UPDATE users SET username = 'foo''bar'.


-----Original Message-----
From: Freeradius-Devel <freeradius-devel-bounces+hmuench=[hidden email]> On Behalf Of Alan DeKok
Sent: Dienstag, 8. Januar 2019 15:41
To: FreeRadius developers mailing list <[hidden email]>
Subject: Re: rlm_sql sql_escape_func

On Jan 8, 2019, at 9:32 AM, Hagen Münch <[hidden email]> wrote:
>
>
> I met the problem that if there are string values in a data base that contain single-quotes, the radius_axlat function expands a "foo'bar" to "foo27bar" by using the sql_escape_func of the rlm_sql module.

  That's what the SQL escape function does.

> I solved it by adding
> ...
> Do you think this approach is appropriate and would it be possible to add this single-quote escape case to the v3.x source? Thank you.

  It's not correct.

  You can set "sql_safe_characters" in the SQL configuration.  See raddb/mods-config/sql/main/*/queries.conf for more information.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
| Threaded
Open this post in threaded view
|

RE: rlm_sql sql_escape_func

Hagen Münch
In reply to this post by Herwin Weststrate-2
Ok, I'll have a look. Thank you.

-----Original Message-----
From: Freeradius-Devel <freeradius-devel-bounces+hmuench=[hidden email]> On Behalf Of Herwin Weststrate
Sent: Dienstag, 8. Januar 2019 17:50
To: [hidden email]
Subject: Re: rlm_sql sql_escape_func

Alan DeKok wrote:

> On Jan 8, 2019, at 9:32 AM, Hagen Münch <[hidden email]> wrote:
>>
>>
>> I met the problem that if there are string values in a data base that contain single-quotes, the radius_axlat function expands a "foo'bar" to "foo27bar" by using the sql_escape_func of the rlm_sql module.
>
>    That's what the SQL escape function does.
>
>> I solved it by adding
>> ...
>> Do you think this approach is appropriate and would it be possible to add this single-quote escape case to the v3.x source? Thank you.
>
>    It's not correct.
>
>    You can set "sql_safe_characters" in the SQL configuration.  See raddb/mods-config/sql/main/*/queries.conf for more information.

It is (or will become) a bit more complicated: both 4.x and 3.0.x (the next 3.0 release) have a possibility to get a more specific escape function per driver. This has been implemented for MySQL and Postgres.
It could be implemented for other drivers as well, it's just that nobody has done that yet.
So you might want to have a look at the git version of 3.0, it may contain code that solves your problem.

--
Herwin Weststrate
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html