Re: [Users] Openser and Oracle

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(a)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(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 >>> >>> > > -- > Klaus Darilion > nic.at > >