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)")) { if(avp_chech("$avp(i:234)", "ge/i:10")) { sl_send_reply("403", "too many calls in the last day"); exit(); } } }
Cheers, Daniel
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?
What happens if the query returns multiple rows? Will the AVP be defined multiple times?
Is it possible to retrieve multiple columns? e.g. avp_db_query("select user,domain from ....", "$avp(user)$avp(domain)")
Is the query SQL-injection save?
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@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
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@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/17/06 14:59, Klaus Darilion wrote:
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?
AFAIK there are 2 ways to prevent SQL injection. 1. quoting and escaping 2. Do not provide the user input in the SQL query, but explicit as parameter. This way, the DB client library prevents SQL injection.
I've checked the postgresql module, which supports both version. If "params" are defined, the safe version is used. But, when raw queries are used, there is no protection through the API, thus, checks must done before. Does this query work?
if (avp_subst("s:foo","/"//")) { sl_send_reply("403","bad syntax"); }
regards klaus
Hello Klaus,
On 02/20/06 12:31, Klaus Darilion wrote:
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/17/06 14:59, Klaus Darilion wrote:
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?
AFAIK there are 2 ways to prevent SQL injection.
- quoting and escaping
- Do not provide the user input in the SQL query, but explicit as
parameter. This way, the DB client library prevents SQL injection.
I've checked the postgresql module, which supports both version. If "params" are defined, the safe version is used. But, when raw queries are used, there is no protection through the API, thus, checks must done before. Does this query work?
if (avp_subst("s:foo","/"//")) { sl_send_reply("403","bad syntax"); }
I am not sure I got what you want to achieve with this statement. Do you want to forbid messages which have quotes or some other "dangerous" characters in some pseudo-variables? Or you want to escape the quotes?
You can do quoting and escaping from the script, as you already mentioned, using avp_subst(). Checks for special characters like quotes or double dash can be done via avp_check().
Cheers, Daniel
regards klaus
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/20/06 12:31, Klaus Darilion wrote:
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/17/06 14:59, Klaus Darilion wrote:
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?
AFAIK there are 2 ways to prevent SQL injection.
- quoting and escaping
- Do not provide the user input in the SQL query, but explicit as
parameter. This way, the DB client library prevents SQL injection.
I've checked the postgresql module, which supports both version. If "params" are defined, the safe version is used. But, when raw queries are used, there is no protection through the API, thus, checks must done before. Does this query work?
if (avp_subst("s:foo","/"//")) { sl_send_reply("403","bad syntax"); }
I am not sure I got what you want to achieve with this statement. Do you want to forbid messages which have quotes or some other "dangerous" characters in some pseudo-variables? Or you want to escape the quotes?
You can do quoting and escaping from the script, as you already mentioned, using avp_subst(). Checks for special characters like quotes or double dash can be done via avp_check().
I didn't found out how to use avp_check thus I used avp_subst.
regards klaus
Cheers, Daniel
regards klaus
On 02/20/06 15:19, Klaus Darilion wrote:
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/20/06 12:31, Klaus Darilion wrote:
Daniel-Constantin Mierla wrote:
Hello Klaus,
On 02/17/06 14:59, Klaus Darilion wrote:
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?
AFAIK there are 2 ways to prevent SQL injection.
- quoting and escaping
- Do not provide the user input in the SQL query, but explicit as
parameter. This way, the DB client library prevents SQL injection.
I've checked the postgresql module, which supports both version. If "params" are defined, the safe version is used. But, when raw queries are used, there is no protection through the API, thus, checks must done before. Does this query work?
if (avp_subst("s:foo","/"//")) { sl_send_reply("403","bad syntax"); }
I am not sure I got what you want to achieve with this statement. Do you want to forbid messages which have quotes or some other "dangerous" characters in some pseudo-variables? Or you want to escape the quotes?
You can do quoting and escaping from the script, as you already mentioned, using avp_subst(). Checks for special characters like quotes or double dash can be done via avp_check().
I didn't found out how to use avp_check thus I used avp_subst.
To perform regular expression matching you have to use the 're' operation.
avp_check("$avp(s:foo)", "re/"/g");
Cheers, Daniel
regards klaus
Cheers, Daniel
regards klaus
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@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@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
Devel mailing list Devel@openser.org http://openser.org/cgi-bin/mailman/listinfo/devel
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@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@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
Devel mailing list Devel@openser.org http://openser.org/cgi-bin/mailman/listinfo/devel
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@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@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@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
Devel mailing list Devel@openser.org http://openser.org/cgi-bin/mailman/listinfo/devel
-
Daniel-Constantin Mierla -
JF -
Klaus Darilion