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:[EMAIL PROTECTED] > Sent: Wednesday, March 14, 2007 2:28 PM > To: Papadopoulos Georgios > Cc: [email protected] > 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:[EMAIL PROTECTED] > >> Sent: Wednesday, March 14, 2007 12:59 PM > >> To: Papadopoulos Georgios > >> Cc: [email protected] > >> 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 > >>> [email protected] > >>> http://openser.org/cgi-bin/mailman/listinfo/users > >> > >> -- > >> Klaus Darilion > >> nic.at > >> > >> > > > -- > Klaus Darilion > nic.at > > _______________________________________________ Users mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/users
