[Users] Openser and Oracle

[Papadopoulos Georgios]

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. 

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
> 
>