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
>
>
Hello,everyone
I configure the openser with postgres.But I cann't start the openser .When I execute the command /usr/local/sbin/openser I occurs the following errors:
0(28102) ERROR: load_module: could not open module </usr/local/lib/openser/modules/postgres.so>:
/usr/local/lib/openser/modules/postgres.so: undefined symbol: PQescapeStringConn
0(28102) parse error (42,13-14): failed to load module
ERROR: bad config file (1 errors)
0(28102) INFO:mi_fifo:mi_destroy:memory for the child's mi_fifo_pid was not allocated -> nothing to destroy
And the file postgres.so exists and the path in the file openser.cfg is corrcet.Who can help me ,thank you!
Junjie Zhao
2007-03-15
Hi,
I'm using ser_ctl as follows:
ser_ctl -g -s http://localhost:2046 ps
but get the following error:
Traceback (most recent call last):
File "/usr/local/bin/ser_ctl", line 19, in ?
main(sys.argv)
File "./serctl/main.py", line 173, in main
return call(func, args, opts)
File "./serctl/main.py", line 115, in call
return apply(func, args, opts)
File "./serctl/ctlctl.py", line 235, in ps
ret = rpc.core_ps()
File "./serctl/ctlrpc.py", line 195, in core_ps
ps = self.ser.core.ps()
File "/usr/lib/python2.3/xmlrpclib.py", line 1029, in __call__
return self.__send(self.__name, args)
File "/usr/lib/python2.3/xmlrpclib.py", line 1316, in __request
verbose=self.__verbose
File "./serctl/serxmlrpc.py", line 132, in request
self._http_request(uripath, body, host)
File "./serctl/serxmlrpc.py", line 104, in _http_request
self.conn.request("POST", uripath, body, headers)
File "/usr/lib/python2.3/httplib.py", line 718, in request
self._send_request(method, url, body, headers)
File "/usr/lib/python2.3/httplib.py", line 739, in _send_request
self.endheaders()
File "/usr/lib/python2.3/httplib.py", line 712, in endheaders
self._send_output()
File "/usr/lib/python2.3/httplib.py", line 597, in _send_output
self.send(msg)
File "/usr/lib/python2.3/httplib.py", line 564, in send
self.connect()
File "/usr/lib/python2.3/httplib.py", line 548, in connect
raise socket.error, msg
socket.error: (111, 'Connection refused')
In my ser.cfg, I have:
# unix sockets and fifo creation parameters
modparam("ctl", "binrpc", "unix:/tmp/ser_ctl") # default
modparam("ctl", "binrpc", "udp:10.10.90.51:2046")
modparam("ctl", "binrpc", "tcp:10.10.90.51:2046")
modparam("ctl", "binrpc", "unixd:/tmp/unix_dgram") # unix datagram
modparam("ctl", "binrpc", "unixs:/tmp/unix_stream") # unix stream
modparam("ctl", "binrpc", "unix:/tmp/unix_default") # unix stream
modparam("ctl", "binrpc", "/tmp/unix_test") # unix stream
modparam("ctl", "binrpc", "10.10.90.51:3000") # udp
modparam("ctl", "binrpc", "tcp:3012") # tcp any , port 3012
modparam("ctl", "binrpc", "udp:*:3012") # udp any , port 3012
modparam("ctl", "fifo", "fifo:/tmp/ser_fifo")
modparam("ctl", "mode", 0666)
Any suggestions? Thanks.
Hi,
Thank you for your offer. I have already been able to compile the oracle
module from ser. However even this module will not solve the issue of so
many connections to DB.
best regards
g.
>
> Hi...
>
> I'm using an modified oracle module from ser dev implementation, it
> lacks raw_query, fetch_result and replace functions and needs more
> testing as well.
>
> I Was waiting to have time to finish it before to send to the tracker,
> but if you want to give it a try, i could send it to you.
>
> I havent tested it for memory leaks, so its possible to have.
>
> It's pretty stable in our test environment, but i never tested with a
> high load.
>
> Best regards
>
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.
Hi Christian,
Our DB already has more than 100 connections from various other systems.
Each connection takes about 5MB of memory. Since only 5 children are
taking the heavy load, the rest 23 connections are mostly idle. And
since we plan to have a second machine running Openser as a failover,
there will be another 28 iddle connections.
A single connection pool that would serve all children would be able to
create connections on demand. At peak traffic it would be possible that
the pool has as many connections as the number of children. But that
would be rare (at least in our case). Basically, the amount of traffic
would determine the amount of connections.
What if each module opened the connection to the DB in mod_init()
instead of child_init()? What implications could that have?
Best regards
George
> Papadopoulos Georgios wrote:
> ...
> > 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?
>
> I don't think that having 28 DB connections is a waste of
> resources, in
> fact I think you will get better DB query performance using one DB
> connection per openser worker process than using a DB
> connection pool.
> Most databases including Oracle DB are optimized for
> concurrent access
> using either a process or a thread pool, so why not take advantage of
> that. And most DBs can easily handle at least 100 concurrent
> DB connections.
>
> Christian
>
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.
於 四，2007-03-15 於 09:58 +0800，赵俊杰 提到：
> Hello ,erveryone
>
> I configure the openser under ipv6.I add listen ipv6_adress in the
> file openser.cfg,it cann't work.But under ipv4 it works perfectly.Who
> can tell me how to configure ipv6 in openser.Thank you
> very much!
>
OpenSER 1.2.0 can listen on IPv6 socket fine on both of our test
systems. How do you write the listen directive?
On my system, it looks like this:
listen=udp:[2002:xxxx:xxxx::1:3]:15060
Regards,
Bernard Chan.
Hello ,erveryone
I configure the openser under ipv6.I add listen ipv6_adress in the file openser.cfg,it cann't work.But under ipv4 it works perfectly.Who can tell me how to configure ipv6 in openser.Thank you
very much!
赵俊杰
2007-03-15
Hi,
I am going to put a pair of servers with OpenSER software on them
behind a BIG IP load balancer which will use NAT to provide static IP
addresses to each OpenSER server. In this case, when an incoming SIP
Request is received, the destination IP address will differ from the
host IP address defined in the Req URI (and To header). Is there any
special configuration or a module that should be utilized to deal with
this?
thanks,
Tim
Hi Everyone,
I am wondering if anyone here has experience with DBTEXT and can
comment on it's performance vs. MySQL.
I'm involved in a project where it makes sense to use DBTEXT at least
for the subscriber table, but I need to know what performance problems
I might face once the system is loaded with subscribers. For example,
how many calls per second or minute might I expect to see?
All comments are welcome. Thanks.
- Daryl
