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

JF jfkavaka at gmail.com
Thu Mar 2 16:22:23 CET 2006


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.
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