[Users] Openser and Oracle

[Klaus Darilion]

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