[Users] Openser and Oracle

[Klaus Darilion]

Papadopoulos Georgios wrote:
> 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

No. Using the query() escapes the string, e.g.:

in modules/mysql/val.c line 300:
_s += mysql_real_escape_string(_c, _s, VAL_STRING(_v), l);

the same happens in postgres module using PQescapeStringConn()
or in unixodbc using the openser function escape_common()

regards
klaus


> 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 at pernau.at] 
>> Sent: Wednesday, March 14, 2007 2:28 PM
>> To: Papadopoulos Georgios
>> Cc: users at 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 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
>>
>>


-- 
Klaus Darilion
nic.at