[Devel] Single-quote escaping in unixodbc + now() vs. getdate() + openser crash in DB connection lost

[Klaus Darilion]

Jerome Martin wrote:
> Hello list,
> 
> I'm currently running a pre production Openser in order to test direct
> integration with our information system legacy DB (I am currently using
> a local mysql DB in production), which happens to be a MS SQLServer (I
> don't like it, I don't want it, I hope to get rid of it ASAP, but for
> now I am REQUIRED to use it).
> 
> In order to achieve this, I am using the recently introduced unixodbc
> module. However, there are three major issues I encountered, and which I
> would like to see fixed in devel branch.
> 
> 1) single-quote escaping
> 
> I use the acc module in order to account for relevant SIP request.
> However, the PSTN gateway I use (Audiocodes mediant 2000) sometimes
> generates in_uris that contain a single quote. As the acc module already
> uses single quotes to delimit the SQL query and does no escaping
> whatsoever, added to the fact that the unixodbc modules does not escape
> anything itself, all inserts containing a single-quote are failing. I
> temporarily worked around that in my very specific case by replacing
> single quotes by an other char (I don't use the in_uri for now, and it's
> the only case it fails for me right now), but doing proper escaping is a
> bit more complicated, due to the fixed size allocated in memory for
> storing the information, which prevents me from increasing the field
> length. IMHO, proper escaping should occur in the unixodbc module. What
> do you think ?

The escaping should be done either in the unixodbc library or in the 
unixodbc openser module.

E.g. the mysql module uses the mysql_real_escape_string function to 
escape the strings.

> 2) using getdate() instead of now()
> 
> usrloc uses the now() SQL function to filter out expired location
> entries (" %.*s from %s where %.*s > now() and %.*s & %d = %d").
> However, this function is called getdate in SQL Server, and there is no
> way of aliasing it so it can be called in the exact way the existing
> request does. Could this be a configurable parameter of the usrloc
> module ? The correponding file is modules/usrloc/dlist.c.

Feel free to add it and post it on the patches tracker at sourceforge ;-)

> 3) OpenSER crash on DB connection lost
> 
> since I am using the unixodbc module, whenever the usrloc module tries
> to fetch the location DB (I am using db mode 3 for now, so it's being
> reloaded entirely pretty often) and the SQL server is unreachable, the
> thread dies and then takes all of openser down with it. I suppose this
> is an issue in usrloc handling of DB mode 3.

It would be interesting how dbmode 3 works with mysql/postgres - if it 
is a dbmode 3 problem or a DB reconnect problem. You can take a look at 
the postgres and mysql modules which should handle reconnections.

regards
klaus

> What do you think of those issue ? Any pointers to help fix them ? I'm
> waiting for comments before opening tracker bugfixes/features requests.
> 
> Best Regards,
> Jérôme Martin
> 
> 
> 
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel


-- 
Klaus Darilion
nic.at