Re: [Users] Openser and Oracle

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