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

[Jerome Martin]

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 ?

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.


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.


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