I see what you mean. However, how is this avoided if you use query() instead of raw_query()? Function query() will also write the same string as the value of the User-Agent column. Both functions will construct the same query and at the end they will call submit_query(). No?
best regards
George
-----Original Message----- From: Klaus Darilion [mailto:klaus.mailinglists@pernau.at] Sent: Wednesday, March 14, 2007 2:28 PM To: Papadopoulos Georgios Cc: users@openser.org Subject: Re: [Users] Openser and Oracle
Papadopoulos Georgios wrote:
What do you mean by "SQL injection"? I know it is not a pretty solution (especially changing the code) but I cannot think
of an alternative.
Just an example. The save() function writes the User-Agent header into the DB. If the usrloc would use a raw SQL query, and the User-Agent header would be something like User-Agent: foobar'; delete from subscriber; '
then the delete query would be executed. This is an SQL injection.
regards klaus
I can look into SER's oracle module. But that would mean
that in the
where clauses all string values should be surounded with
"upper". Not
a pretty solution either but it would work I guess.
I briefly saw that Openser 1.2 has string transformations. So could uppercase and lowercase be included there? I am picturing something like avp_db_load("$ruri{s.lowercase}", ...) Of course this
would only
solve the issue with avpops. In order to solve the issue
with auth_db
for example, wouldn't we need new functions?
Any comments about the multiple connections to the DB?
-----Original Message----- From: Klaus Darilion [mailto:klaus.mailinglists@pernau.at] Sent: Wednesday, March 14, 2007 12:59 PM To: Papadopoulos Georgios Cc: users@openser.org Subject: Re: [Users] Openser and Oracle
Papadopoulos Georgios wrote:
Hello,
First of all congratulations to everyone involved in the
new release.
I haven't been able to get my hands on it yet, but just
reading about
it makes me very excited. Great job!
I would like to address the issue of Openser and Oracle working together. We are currently using Openser with a local
MySQL. Our main
database is Oracle and we are just copying data to MySQL so that Openser can work. This is a little difficult to maintain so
I thought
I would try to make Openser use directly our Oracle. Of
course I ran
into a number of issues.
- modules/acc and unixodbc. The acc_db_request() was not working
because inserting a string in a date column does not work
with Oracle.
So, I had to change functions time2str() and time2odbc() in
order to
make this work. Also had to change acc.c to treat column time as DB_TIME instead of DB_STR (I think this could be treated
as a bug).
- modules/lcr. The query uses char_length() and rand()
which I had to
replace with lengthc() and dbms_random.value. Should these
be modules
params?
Hi!
I guess oracle allows the defintion of new function. Then
you could
write the functions char_length() and rand() which would be just wrappers to lengthc() and dbms_random.value.
- modules/avpops. All issues were resolved by config changes and
replacing avp_db_load() with avp_db_query().
Be careful - raw queries are vulnerable to SQL injection!!!
- modules/auth_db, alias_db, uri_db, group. Since MySQL is case
insensitive and Oracle is not, I made changes in the code to use raw_query() instead of query(). All queries have to be in
the form
"select ... from ... where username=upper(...)"
Again: Be careful - raw queries are vulnerable to SQL injection!!!
I would be happy to provide patches and help in any way
in order to
make this migration easier in the future. However I am not sure whether my changes are general enough for everybody to use.
So, do you
have any suggestions about how to deal with these issues?
There is a oracle module in ser - you could port it to openser ;-)
regards klaus
Another issue that came up is the number of connections
from Openser
to the database. In our case, listening to five interfaces,
with tcp
disabled and children=5, we get 28 connections to DB which
is a great
waste of resources. From those five interfaces, one is
receiving the
bulk of traffic and the rest receive minimal traffic. Since
each child
has its own connection, then what is the purpose of
connection pooling?
How difficult would it be to have a common connection
pool for all
children?
sorry for the long email and thank you in advance for any answer.
George
Disclaimer The information in this e-mail and any attachments is
confidential. It is intended solely for the attention and
use of the
named addressee(s). If you are not the intended recipient,
or person
responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless
you are the
intended recipient or his/her representative you are not
authorized
to, and must not, read, copy, distribute, use or retain
this message
or any part of it. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,
corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses.
--
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
-- Klaus Darilion nic.at
-- Klaus Darilion nic.at
Papadopoulos Georgios wrote:
I see what you mean. However, how is this avoided if you use query() instead of raw_query()? Function query() will also write the same string
No. Using the query() escapes the string, e.g.:
in modules/mysql/val.c line 300: _s += mysql_real_escape_string(_c, _s, VAL_STRING(_v), l);
the same happens in postgres module using PQescapeStringConn() or in unixodbc using the openser function escape_common()
regards klaus
as the value of the User-Agent column. Both functions will construct the same query and at the end they will call submit_query(). No?
best regards
George
-----Original Message----- From: Klaus Darilion [mailto:klaus.mailinglists@pernau.at] Sent: Wednesday, March 14, 2007 2:28 PM To: Papadopoulos Georgios Cc: users@openser.org Subject: Re: [Users] Openser and Oracle
Papadopoulos Georgios wrote:
What do you mean by "SQL injection"? I know it is not a pretty solution (especially changing the code) but I cannot think
of an alternative.
Just an example. The save() function writes the User-Agent header into the DB. If the usrloc would use a raw SQL query, and the User-Agent header would be something like User-Agent: foobar'; delete from subscriber; '
then the delete query would be executed. This is an SQL injection.
regards klaus
I can look into SER's oracle module. But that would mean
that in the
where clauses all string values should be surounded with
"upper". Not
a pretty solution either but it would work I guess.
I briefly saw that Openser 1.2 has string transformations. So could uppercase and lowercase be included there? I am picturing something like avp_db_load("$ruri{s.lowercase}", ...) Of course this
would only
solve the issue with avpops. In order to solve the issue
with auth_db
for example, wouldn't we need new functions?
Any comments about the multiple connections to the DB?
-----Original Message----- From: Klaus Darilion [mailto:klaus.mailinglists@pernau.at] Sent: Wednesday, March 14, 2007 12:59 PM To: Papadopoulos Georgios Cc: users@openser.org Subject: Re: [Users] Openser and Oracle
Papadopoulos Georgios wrote:
Hello,
First of all congratulations to everyone involved in the
new release.
I haven't been able to get my hands on it yet, but just
reading about
it makes me very excited. Great job!
I would like to address the issue of Openser and Oracle working together. We are currently using Openser with a local
MySQL. Our main
database is Oracle and we are just copying data to MySQL so that Openser can work. This is a little difficult to maintain so
I thought
I would try to make Openser use directly our Oracle. Of
course I ran
into a number of issues.
- modules/acc and unixodbc. The acc_db_request() was not working
because inserting a string in a date column does not work
with Oracle.
So, I had to change functions time2str() and time2odbc() in
order to
make this work. Also had to change acc.c to treat column time as DB_TIME instead of DB_STR (I think this could be treated
as a bug).
- modules/lcr. The query uses char_length() and rand()
which I had to
replace with lengthc() and dbms_random.value. Should these
be modules
params?
Hi!
I guess oracle allows the defintion of new function. Then
you could
write the functions char_length() and rand() which would be just wrappers to lengthc() and dbms_random.value.
- modules/avpops. All issues were resolved by config changes and
replacing avp_db_load() with avp_db_query().
Be careful - raw queries are vulnerable to SQL injection!!!
- modules/auth_db, alias_db, uri_db, group. Since MySQL is case
insensitive and Oracle is not, I made changes in the code to use raw_query() instead of query(). All queries have to be in
the form
"select ... from ... where username=upper(...)"
Again: Be careful - raw queries are vulnerable to SQL injection!!!
I would be happy to provide patches and help in any way
in order to
make this migration easier in the future. However I am not sure whether my changes are general enough for everybody to use.
So, do you
have any suggestions about how to deal with these issues?
There is a oracle module in ser - you could port it to openser ;-)
regards klaus
Another issue that came up is the number of connections
from Openser
to the database. In our case, listening to five interfaces,
with tcp
disabled and children=5, we get 28 connections to DB which
is a great
waste of resources. From those five interfaces, one is
receiving the
bulk of traffic and the rest receive minimal traffic. Since
each child
has its own connection, then what is the purpose of
connection pooling?
How difficult would it be to have a common connection
pool for all
children?
sorry for the long email and thank you in advance for any answer.
George
Disclaimer The information in this e-mail and any attachments is
confidential. It is intended solely for the attention and
use of the
named addressee(s). If you are not the intended recipient,
or person
responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless
you are the
intended recipient or his/her representative you are not
authorized
to, and must not, read, copy, distribute, use or retain
this message
or any part of it. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,
corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses.
--
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
-- Klaus Darilion nic.at
-- Klaus Darilion nic.at