[Devel] Re: [Users] avpops: new function avp_db_query()

JF jfkavaka at gmail.com
Sat Mar 4 15:05:18 CET 2006


Thanks. Having a default non-NULL value could work (although not
pretty), as long as it's not empty, because of the following lines in
avpops_db.c, function db_query_avp:

if(avp_val.s.len<=0)
     goto next_avp;

JF

On 3/4/06, Daniel-Constantin Mierla <daniel at voice-system.ro> wrote:
> On 03/02/06 17:22, JF wrote:
> > How can I check for SQL NULLs returned in some of the returned rows?
> > >From what I could understand of the code, these are not saved into AVPs.
> >
> yes, null values are not stored in AVPs. There is no way to mark an avp
> as being NULL. Cant you use some default value (empty or "NULL") you can
> check.
>
> Cheers,
> Daniel
> > Could this be changed to set somekind of "NULL" AVP value?
> > Thanks in advance.
> >
> > JF
> >
> > On 2/17/06, Daniel-Constantin Mierla <daniel at voice-system.ro> wrote:
> >
> >> Hello Klaus,
> >>
> >> On 02/17/06 14:59, Klaus Darilion wrote:
> >>
> >>> Hi Daniel!
> >>>
> >>> cool new feature, some questions inline:
> >>>
> >>> Daniel-Constantin Mierla wrote:
> >>>
> >>>> Hello,
> >>>>
> >>>> avpops module has a new function which allow to execute raw SQL
> >>>> queries and store the result in AVPs.
> >>>>
> >>>> avp_db_query(query, dest);
> >>>>
> >>>> The query given as parameter can contain pseudo-variables. Using this
> >>>> function you can benefit of full database system features, being able
> >>>> to do joins, unions, etc. Old db-related functions are in place since
> >>>> they are faster for their usage case.
> >>>>
> >>>> The documentation of the of avpops module was updated and posted at:
> >>>>
> >>>> http://openser.org/docs/modules/1.1.x/avpops.html
> >>>>
> >>>> A small example of usage: limit the number of calls done in the last
> >>>> day:
> >>>>
> >>>> if(is_method("INVITE") && !has_totag())
> >>>> {
> >>>>     if(avp_db_query("select count(*) from acc where username='$fU'
> >>>> and domain='$fd' and method='INVITE' and timestamp>=$Ts-24*3600",
> >>>> "$avp(i:234)"))
> >>>>
> >>> I guess the SQL query returns the result as string. Is the conversion
> >>> to int done when copying into the AVP?
> >>>
> >> the mysql module does the conversion, based on returned columns' types.
> >>
> >>> What happens if the query returns multiple rows? Will the AVP be
> >>> defined multiple times?
> >>>
> >> Yes, the first AVP will correspond to the first row in result.
> >>
> >>> Is it possible to retrieve multiple columns? e.g.
> >>>  avp_db_query("select user,domain from ....", "$avp(user)$avp(domain)")
> >>>
> >> Yes, the destination list has to be separated by ';' =>
> >> "$avp(user);$avp(domain)"
> >>
> >>> Is the query SQL-injection save?
> >>>
> >> Depending of what you do and how :-). Authenticating the user should
> >> prevent bad values in From header and credentials, some character
> >> sequences are not allowed to be part of user or domain names. Using
> >> values from custom headers is quite risky, you have to use other
> >> technics to ensure a trusted value. So, I am sure that someone can get
> >> some examples of doing sql-injections even without using avp_db_query()
> >> , there are many other modules doing SQL queries using parts of SIP
> >> message, but these situations can be avoided if you know what you are
> >> doing in the script. I do not know a technique to prevent 100%
> >> SQL-injections, are you aware of?
> >>
> >> Cheers,
> >> Daniel
> >>
> >>
> >>> regards
> >>> klaus
> >>>
> >>>
> >>>>    {
> >>>>       if(avp_chech("$avp(i:234)", "ge/i:10"))
> >>>>      {
> >>>>          sl_send_reply("403", "too many calls in the last day");
> >>>>          exit();
> >>>>     }
> >>>>   }
> >>>> }
> >>>>
> >>>> Cheers,
> >>>> Daniel
> >>>>
> >>>>
> >>>> _______________________________________________
> >>>> Users mailing list
> >>>> Users at openser.org
> >>>> http://openser.org/cgi-bin/mailman/listinfo/users
> >>>>
> >>>
> >> _______________________________________________
> >> Devel mailing list
> >> Devel at openser.org
> >> http://openser.org/cgi-bin/mailman/listinfo/devel
> >>
> >>
> >
> >
>




More information about the Users mailing list