[Users] Openser and Oracle

[Papadopoulos Georgios]

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 at pernau.at] 
> Sent: Wednesday, March 14, 2007 2:28 PM
> To: Papadopoulos Georgios
> Cc: users at 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 at pernau.at]
> >> Sent: Wednesday, March 14, 2007 12:59 PM
> >> To: Papadopoulos Georgios
> >> Cc: users at 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.
> >>> 1. 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).
> >>> 2. 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.
> >>
> >>> 3. 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!!!
> >>
> >>> 4. 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 at openser.org
> >>> http://openser.org/cgi-bin/mailman/listinfo/users
> >>
> >> --
> >> Klaus Darilion
> >> nic.at
> >>
> >>
> 
> 
> --
> Klaus Darilion
> nic.at
> 
>