Yate Community Forum

Yate server => Other Yate server issues => Topic started by: iKenny on April 29, 2013, 06:43:56 AM

Title: extensions from mysql
Post by: iKenny on April 29, 2013, 06:43:56 AM

Hi, i am trying to register users through mysql database.

simple sip registration, i have a database and a table of 2 columns, login and password.
The thing is, that password is correct, but i get unauthorized message:

Sniffed 'user.auth' time=1367249962.688858
  thread=0x1d728f50 'YSIP Register'
  data=(nil)
  retval='(null)'
  param['protocol'] = 'sip'
  param['username'] = '300'
  param['realm'] = 'Yate'
  param['nonce'] = '1bb7d309d9a6051807788e3d3459281c.1367249962'
  param['response'] = '723f3537d790f66962a227c18ff7521c'
  param['method'] = 'REGISTER'
  param['uri'] = 'sip:83.218.223.50:5060'
  param['ip_host'] = '89.149.80.221'
  param['ip_port'] = '5060'
  param['ip_transport'] = 'UDP'
  param['address'] = '89.149.80.221:5060'
  param['newcall'] = 'false'
  param['domain'] = '83.218.223.50'
  param['device'] = 'SJphone/1.60.299a/L (SJ Labs)'
  param['number'] = ''
  param['expires'] = '600'
<register:INFO> On account 'yate' performing query 'SELECT password FROM users WHERE login='300' AND password IS NOT NULL AND password='';' expects results
Sniffed 'database' time=1367249962.689206
  thread=0x1d728f50 'YSIP Register'
  data=(nil)
  retval='(null)'
  param['account'] = 'yate'
  param['query'] = 'SELECT password FROM users WHERE login='300' AND password IS NOT NULL AND password='';'
  param['results'] = 'true'
Returned true 'database' delay=0.000926
  thread=0x1d728f50 'YSIP Register'
  data=0x1d72ab90
  retval='(null)'
  param['account'] = 'yate'
  param['query'] = 'SELECT password FROM users WHERE login='300' AND password IS NOT NULL AND password='';'
  param['results'] = 'true'
  param['handlers'] = 'mysqldb:100'
  param['columns'] = '1'
  param['rows'] = '0'
  param['affected'] = '0'
  param['dbtype'] = 'mysqldb'
Returned false 'user.auth' delay=0.001489


My register.conf:
user.auth=yes
account=yate
query=SELECT password FROM users WHERE login='${username}' AND password IS NOT NULL AND password='${password}';
result=password;

And i can see, that there is a login in message, but no password:
 param['query'] = 'SELECT password FROM users WHERE login='300' AND password IS NOT NULL AND password='';'

Could someone tell where the problem is?

Title: Re: extensions from mysql
Post by: iKenny on April 30, 2013, 12:38:15 AM

I can see that CDRs were written successfully. But users are not registering. Only from regfile.conf.
I tryed to disable regfile from yate.conf (regfile.yate=no) But no result

Title: Re: extensions from mysql
Post by: iKenny on April 30, 2013, 02:18:35 AM

I have found the problem, in register.conf section accounts must be enabled
accounts=yes

Title: Re: extensions from mysql
Post by: iKenny on April 30, 2013, 02:27:20 AM

For those, who want to know,
register.conf:

user.auth=yes
user.register=yes
user.unregister=yes
accounts=yes



[user.auth]
;in my case i use as context, but you can remove "as context" and use withount it, in that case you must set in result password: result=password
account=yate
query=SELECT password as context FROM users WHERE username="${username}";   
result=context

[user.register]
;here we put in table field "location" and 'expires' data
account=yate
query=UPDATE users SET location='${data}',expires='CURRENT_TIMESTAMP + INTERVAL ${expires}' WHERE username='${username}';

[user.unregister]
;Here we remove user.register data from table, if sip account unregisters
account=yate
query=UPDATE users SET location=NULL,expires=NULL WHERE expires IS NOT NULL AND username='${username}';

And mysql table: username varchar(20), password varchar(20), location varchar(50), expires varchar(50)

Insert some clients with password and try.

Here's my config for registering users through mysql.

And one more thing, you may have problem with regfile module, it can cause problems, so i disable it from yate.conf:
[modules]
regfile.yate=no