New subject: [Users] Openser and Oracle

14 Mar 2007

      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

RE: [Users] Openser and Oracle