29 Jun
2010
29 Jun
'10
5:47 p.m.
Hi,
I have recently installed a kamailio server with release 3.0.2.
I would like to use the kamilio as a redirect server that would recieve REGISTER messages
from users and query a proprietary database that holds users information - if the users
are registered to the service it would redirect the user to the registrar. I need it for
security reasons which I can't fully explain.
Now, I am using the avpops module. I have been able to make it work using the following
configuration:
modparam("avpops","db_url","mysql://user:pass@localhost/mysql")
modparam("avpops","avp_table","proptable")
modparam("avpops","db_scheme","scheme0:username_col=sipUsername;value_col=sipDomainName;table=proptable")
----------
if (is_method("REGISTER"))
{
if
(avp_db_load("$fu/username","$avp(s:CONSTANT_DOMAIN)/$scheme0"))
{
append_branch("sip:registrarIP");
sl_send_reply("302","Test Redirect");
}
else
{
sl_send_reply("404","Test Not found");
}
--------------
The thing is that I don't need to match the string CENTREX against the domain- it is
something I added because I couldn't find another way to do it. I would like to match
only the username. I tried wildcards or leaving it empty but it didn't work. I tried
not to use the scheme and instead use the table it self but again it didn't work.
Is it possible? Do you know a better to do it?
Thanks,
Yaron.
29 Jun
29 Jun
5:57 p.m.
New subject: using avpops for querying an external db on kamailio 3.0.2
Hello,
look at sqlops module, it is more suitable for custom sql queries:
http://kamailio.org/docs/modules/stable/modules_k/sqlops.html
Cheers,
Daniel
On 6/29/10 5:47 PM, yaron nahcum wrote:
Hi,
I have recently installed a kamailio server with release 3.0.2.
I would like to use the kamilio as a redirect server that would
recieve REGISTER messages from users and query a proprietary database
that holds users information - if the users are registered to the
service it would redirect the user to the registrar. I need it for
security reasons which I can't fully explain.
Now, I am using the avpops module. I have been able to make it work
using the following configuration:
modparam("avpops","db_url","mysql://user:pass@localhost/mysql")
modparam("avpops","avp_table","proptable")
modparam("avpops","db_scheme","scheme0:username_col=sipUsername;value_col=sipDomainName;table=proptable")
----------
if (is_method("REGISTER"))
{
if
(avp_db_load("$fu/username","$avp(s:CONSTANT_DOMAIN)/$scheme0"))
{
append_branch("sip:registrarIP");
sl_send_reply("302","Test Redirect");
}
else
{
sl_send_reply("404","Test Not found");
}
--------------
The thing is that I don't need to match the string CENTREX against the
domain- it is something I added because I couldn't find another way to
do it. I would like to match only the username. I tried wildcards or
leaving it empty but it didn't work. I tried not to use the scheme and
instead use the table it self but again it didn't work.
Is it possible? Do you know a better to do it?
Thanks,
Yaron.
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla
http://www.asipto.com/
1 Jul
1 Jul
5:44 a.m.
Hi Daniel,
I looked at the module and it seems more suitable for my needs.
I have two questions regarding this module:
1. It says that it is possible to use psuedo variables in the query. How is it done? I
would like to make a query of the from username.
2. If I query the whole column how can I look for a specific value? The only way is to go
over all the values with a loop?
Yaron.
________________________________
From: Daniel-Constantin Mierla <miconda(a)gmail.com>
To: yaron nahcum <yaron_nachum(a)yahoo.com>
Cc: sr-users(a)lists.sip-router.org
Sent: Tue, June 29, 2010 6:57:53 PM
Subject: Re: [SR-Users] using avpops for querying an external db on kamailio 3.0.2
Hello,
look at sqlops module, it is more suitable for custom sql queries:
http://kamailio.org/docs/modules/stable/modules_k/sqlops.html
Cheers,
Daniel
On 6/29/10 5:47 PM, yaron nahcum wrote:
Hi,
I have recently installed a kamailio server with
release 3.0.2.
I would like to use the kamilio as a redirect server that would recieve REGISTER messages
from users and query a proprietary database that holds users information - if the users
are registered to the service it would redirect the user to the registrar. I need it for
security reasons which I can't fully explain.
Now, I am using the avpops module. I have been able to make it work using the following
configuration:
modparam("avpops","db_url","mysql://user:pass@localhost/mysql")
modparam("avpops","avp_table","proptable")
modparam("avpops","db_scheme","scheme0:username_col=sipUsername;value_col=sipDomainName;table=proptable")
----------
if (is_method("REGISTER"))
{
if
(avp_db_load("$fu/username","$avp(s:CONSTANT_DOMAIN)/$scheme0"))
{
append_branch("sip:registrarIP");
sl_send_reply("302","Test Redirect");
}
else
{
sl_send_reply("404","Test Not found");
}
--------------
The thing is that I don't need to match the string CENTREX against the domain- it is
something I added because I couldn't find another way to do it. I would like to match
only the username. I tried wildcards or leaving it empty but it didn't work. I tried
not to use the scheme and instead use the table it self but again it didn't work.
Is it possible? Do you know a better to do it?
Thanks,
Yaron.
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla
http://www.asipto.com/
6:04 a.m.
New subject: using avpops for querying an external db on kamailio 3.0.2
On 06/30/2010 11:44 PM, yaron nahcum wrote:
1. It says that it is possible to use psuedo variables
in the query. How
is it done? I would like to make a query of the from username.
Just include them straight in the literal query:
sql_query("ca", "SELECT * FROM users WHERE username =
'$fU'",
"ra");
2. If I query the whole column how can I look for a
specific value? The
only way is to go over all the values with a loop?
Generally, you should not be _searching_ for specific values in query
results; you should just design your query to constrain for the
criteria you want.
If you specifically need to _iterate_, rather than _search_, over the
returned values, yes, you will need to use an array index, e.g.
sql_query("ca", "SELECT ...", "ra");
$var(idx) = $dbr(ra=>rows) - 1;
while($var(idx) >= 0) {
$(avp(s:rowset)[$var(idx)]) = $dbr(ra=>row[0,N]);
...
$var(idx) = $var(idx) + 1;
}
--
Alex Balashov - Principal
Evariste Systems LLC
1170 Peachtree Street
12th Floor, Suite 1200
Atlanta, GA 30309
Tel: +1-678-954-0670
Fax: +1-404-961-1892
Web: http://www.evaristesys.com/
6:21 a.m.
New subject: using avpops for querying an external db on kamailio 3.0.2
Hi yaron,
On 06/30/10 20:44, yaron nahcum wrote:
Hi Daniel,
I looked at the module and it seems more suitable for my needs.
I have two questions regarding this module:
Let's say your proprietary database has a table with "username" as one
of the columns. For example, a table "Users" with the following columns:
username | acct_balance | acct_active | secret_code
1. It says that it is possible to use psuedo variables
in the query. How
is it done? I would like to make a query of the from username.
You just include it in the SQL query of the sql_query function:
sql_query ("ca", "select username from from Users where username =
'$fu'
and acct_active = 1", "rs");
You'll need some knowledge of how the SQL language works. But here's an
example based on your example code:
loadmodule "sqlops.so"
modparam ( "sqlops", "sqlcon",
ca=>dbdriver://username:password@dbhost/dbname")
# For example, to connect to a mysql database
# "ca=>mysql://yaron:Open_Sesame@192.168.6.1/mydatabase")
if (is_method("REGISTER"))
{
# Ask if $fu is in the database
sql_query ("ca", "select username from from Users where username =
'$fu' and acct_active = 1", "rs");
# If we get a row back, we know we had a match
if ($dbr(rs=>rows) == 1 ) {
append_branch("sip:registrarIP");
sl_send_reply("302","Test Redirect");
}
else
sl_send_reply("404","Test Not found");
}
}
2. If I query the whole column how can I look for a
specific value? The
only way is to go over all the values with a loop?
You have to use the SQL language to make sure you specify a query that
only gets the answer you want. Assuming the username is unique in the
table, the query above will return 1 or 0 rows. The key is knowing how
to use SQL. You should check the website for the SQL server you are
using for more information on the SQL language.
Hope this helps.
29 Jun
29 Jun
6 p.m.
On Tuesday 29 June 2010, yaron nahcum wrote:
Now, I am using the avpops module. I have been able to
make it work using
the following configuration:
[..]
The thing is that I don't need to match the string CENTREX against the
domain- it is something I added because I couldn't find another way to do
it. I would like to match only the username. I tried wildcards or leaving
it empty but it didn't work. I tried not to use the scheme and instead use
the table it self but again it didn't work. Is it possible? Do you know a
better to do it?
Hi yaron,
have you tried to use the avp_db_query function or the sqlops module? Here you
can just use arbitrary queries and then compare this to your required strings
or message header elements in the script, assign it to other AVPs and similar.
You'll get an AVP or another pseudo-variable as return value.
Cheers,
Henning
5054
days inactive
5056
days old
5 comments
5 participants
participants (5)
-
Alex Balashov -
Daniel-Constantin Mierla -
Henning Westerholt -
Nathan Angelacos -
yaron nahcum