4 Mar
2006
4 Mar
'06
9:05 a.m.
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(a)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?
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
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(a)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(a)openser.org
> http://openser.org/cgi-bin/mailman/listinfo/users
>
_______________________________________________
Devel mailing list
Devel(a)openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel