[Users] Openser and Oracle
Klaus Darilion
klaus.mailinglists at pernau.at
Thu Mar 15 11:02:07 CET 2007
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
More information about the Users
mailing list