[sr-dev] sql column type check

[Jasmin Schnatterbeck]

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