Hello list,
last week I switched to a new mysql table structure for the LCR module.
Because of proper permission management and mapping of our internal structures to kamailio's structures we are using mysql views.
When I tried to run kamailio with the new settings the column type check failed, even if the view worked with the mysql "CONVERT" function to convert the output to what kamailio expects.
So I thought about a module-specific or server-wide setting to skip/ignore the column-type check...
Jasmin
we discussed this recently, IMO it would be nice to have to options to disable table checks completely.
regards klaus
Am 19.09.2011 10:39, schrieb Jasmin Schnatterbeck:
Hello list,
last week I switched to a new mysql table structure for the LCR module.
Because of proper permission management and mapping of our internal structures to kamailio's structures we are using mysql views.
When I tried to run kamailio with the new settings the column type check failed, even if the view worked with the mysql "CONVERT" function to convert the output to what kamailio expects.
So I thought about a module-specific or server-wide setting to skip/ignore the column-type check...
Jasmin
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
Klaus Darilion writes:
we discussed this recently, IMO it would be nice to have to options to
So I thought about a module-specific or server-wide setting to skip/ignore the column-type check...
jasmin,
what do you mean by column-type checks? i took a quick look at lcr module and didn't find any checks on column types. there are checks that values of columns in query results are of correct type, which should not cause any trouble.
-- juha
Am Montag, den 19.09.2011, 22:15 +0300 schrieb Juha Heinanen:
Klaus Darilion writes:
we discussed this recently, IMO it would be nice to have to options to
So I thought about a module-specific or server-wide setting to skip/ignore the column-type check...
jasmin,
what do you mean by column-type checks? i took a quick look at lcr module and didn't find any checks on column types. there are checks that values of columns in query results are of correct type, which should not cause any trouble.
hello juha,
in lcr_mod.c, line 980-1050 there are the checks I've had problems with... they seem to depend on the column itself. e.g.:
IF(`fallback`='N',`callrouting_prefixes`.`fakeint1`,`callrouting_prefixes`.`fakeint0`) AS `stopper`, `callrouting_prefixes`.`fakeint1` AS `enabled`
works (fakeint0, fakeint1 are int columns filled with 0 or 1), but:
IF(`fallback`='N',1,0) AS `stopper`, 1 AS `enabled`
OR
IF(`fallback`='N','1','0') AS `stopper`, '1' AS `enabled`
OR (with CONVERT(...) function)
does not work ("stopper is NULL or not int" "lcr rule <%u> enabled is NULL or not int)
but: '1' AS `lcr_id`,
works...
A similar problem with auth_db and the load_credentials parameter - e.g. a view with a column created through GROUP_CONCAT(....) is not accepted (error: int or string supported only) - even if CONVERT(...) is being used.
I think VAL_TYPE does not work as expected...
jasmin
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
jasmin,
are you suggesting that all VAL_TYPE tests that test type of value received from db column are removed?
if so, what if column really has wrong type in database? are you sure that no bad things will happen?
-- juha
juha,
well, bad things will happen, if there are really wrong values in the database...
kamailio functions (even from different modules) "trust" each other, they don't always carefully check every input argument. That would add complexity (and therefore possible bugs) as well as it would cost cpu time.
So why don't we trust values in database?
I think it's much more efficient if values are checked before they are written to database. That would mean one "check" for each value - opposing to many more checks that would occur, if kamailio checks values every time it reads them from the database...
But that's my perspective only. I think there are also really valid reasons to check every time a value is being fetched from DB - it adds a layer of security and stability.
So if a server-wide parameter "skip db value type checks" exists, the kamailio user will be able to choose the preferred model....
Or a int parameter for different levels of security, for different type of data (auth_db data changes frequently/is modified by end-users (lower security level required to bypass checks), lcr data is changed by employees only (higher security level sufficient to bypass checks)).
jasmin
Am Mittwoch, den 21.09.2011, 11:04 +0300 schrieb Juha Heinanen:
jasmin,
are you suggesting that all VAL_TYPE tests that test type of value received from db column are removed?
if so, what if column really has wrong type in database? are you sure that no bad things will happen?
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
On Wednesday 21 September 2011, Jasmin Schnatterbeck wrote:
well, bad things will happen, if there are really wrong values in the database...
Hello Jasmin,
kamailio functions (even from different modules) "trust" each other, they don't always carefully check every input argument. That would add complexity (and therefore possible bugs) as well as it would cost cpu time.
So why don't we trust values in database?
There is a difference in the internal representation of a value in the DB API if the result is returned as DB_STRING or DB_INT. So a module which expect a integer value but gets a string instead will probably not work correctly. Not every module does this additional checks, if they would be not in the LCR module, it would maybe just crash.
I think it's much more efficient if values are checked before they are written to database. That would mean one "check" for each value - opposing to many more checks that would occur, if kamailio checks values every time it reads them from the database...
But that's my perspective only. I think there are also really valid reasons to check every time a value is being fetched from DB - it adds a layer of security and stability.
So if a server-wide parameter "skip db value type checks" exists, the kamailio user will be able to choose the preferred model....
Such a parameter would probably work, but i wonder if this is really necessary. From a kamailio POV we support our table structure, and if you don't want to use it, you need to be compatible to it. Have you thought about fixing your view that it returns the value that are expected? ;-)
Or a int parameter for different levels of security, for different type of data (auth_db data changes frequently/is modified by end-users (lower security level required to bypass checks), lcr data is changed by employees only (higher security level sufficient to bypass checks)).
Best regards,
Henning
On Wednesday 21 September 2011, Henning Westerholt wrote:
[..]
Hello,
So if a server-wide parameter "skip db value type checks" exists, the kamailio user will be able to choose the preferred model....
Such a parameter would probably work, but i wonder if this is really
s/probably/maybe
necessary. From a kamailio POV we support our table structure, and if you don't want to use it, you need to be compatible to it. Have you thought about fixing your view that it returns the value that are expected? ;-)
Or a int parameter for different levels of security, for different type of data (auth_db data changes frequently/is modified by end-users (lower security level required to bypass checks), lcr data is changed by employees only (higher security level sufficient to bypass checks)).
Have you already tried to debug this issue further? If you enable debugging, you get plenty of output from the database modules which shows you the type conversion done, values from the client library etc..
Regards,
Henning
Hello Henning,
Am Mittwoch, den 21.09.2011, 10:52 +0200 schrieb Henning Westerholt:
On Wednesday 21 September 2011, Jasmin Schnatterbeck wrote:
well, bad things will happen, if there are really wrong values in the database...
Hello Jasmin,
kamailio functions (even from different modules) "trust" each other, they don't always carefully check every input argument. That would add complexity (and therefore possible bugs) as well as it would cost cpu time.
So why don't we trust values in database?
There is a difference in the internal representation of a value in the DB API if the result is returned as DB_STRING or DB_INT. So a module which expect a integer value but gets a string instead will probably not work correctly. Not every module does this additional checks, if they would be not in the LCR module, it would maybe just crash.
Of course that could be the result of missing checks and wrong values...without any doubt it's reasonable to check to prevent crashes, but there are several possibilities to do that - with different advantages and disadvantages....
I think it's much more efficient if values are checked before they are written to database. That would mean one "check" for each value - opposing to many more checks that would occur, if kamailio checks values every time it reads them from the database...
But that's my perspective only. I think there are also really valid reasons to check every time a value is being fetched from DB - it adds a layer of security and stability.
So if a server-wide parameter "skip db value type checks" exists, the kamailio user will be able to choose the preferred model....
Such a parameter would probably work, but i wonder if this is really necessary. From a kamailio POV we support our table structure, and if you don't want to use it, you need to be compatible to it. Have you thought about fixing your view that it returns the value that are expected? ;-)
That was the first idea :-)
I tried the following: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_convert
but unfortunately it didn't change anything... so either the mysql function doesn't work properly or VAL_TYPE does not detect the value type properly...
Or a int parameter for different levels of security, for different type of data (auth_db data changes frequently/is modified by end-users (lower security level required to bypass checks), lcr data is changed by employees only (higher security level sufficient to bypass checks)).
Best regards,
Henning
Kind Regards Jasmin
Jasmin Schnatterbeck writes:
I think it's much more efficient if values are checked before they are written to database. That would mean one "check" for each value - opposing to many more checks that would occur, if kamailio checks values every time it reads them from the database...
in case of lcr module, db is not accessed for each message, but only during lcr reload and thus checking types of values is not a performance issue.
So if a server-wide parameter "skip db value type checks" exists, the kamailio user will be able to choose the preferred model....
it would be hard to do, because VAL_TYPE returns type of value that is then checked in the module, e.g.
if (VAL_TYPE(ROW_VALUES(row) + 1) != DB1_STRING) ...
so it would not work just by turning VAL_TYPE into a no-op.
-- juha
-
Henning Westerholt -
Jasmin Schnatterbeck -
Juha Heinanen -
Klaus Darilion