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@gmail.com To: yaron nahcum yaron_nachum@yahoo.com Cc: sr-users@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@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

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.