asterisk users - Jul 2008 - Realtime + SIP + MySQL: md5secret BROKEN

I'm quite sure there's a bug somewhere in SIP + realtime + MySQL.

To update, since last post we've integrated with our existing users
database  using MySQL views.  Our legacy database uses md5
password hashes, and does not store plaintext.

During testing this morning I could swear it was all working, however
for some reason, after going out to lunch today and coming back (no
config changes at all!) authentication would not succeed no matter
what I tried:
 - toggling rt* settings in sip.conf
 - re-creating MySQL view
 - reverting to static table
 - sip reload on command line
 - recompiling / re-installing asterisk and asterisk-addons
 - probably a bunch more

Most of these I tried multiple times in various combinations.

The issue appears in the debug log like this:

[Jul 24 17:16:43] DEBUG[8732] chan_sip.c: = No match Their Call ID:
803557069 at 192.168.0.25 Their Tag  Our tag: as2f38c31a
[Jul 24 17:16:43] DEBUG[8732] chan_sip.c: Allocating new SIP dialog
for 1499918050 at 192.168.0.25 - REGISTER (No RTP)
[Jul 24 17:16:43] DEBUG[8732] chan_sip.c: **** Received REGISTER (2) -
Command in SIP REGISTER
[Jul 24 17:16:43] DEBUG[8732] res_config_mysql.c: MySQL RealTime:
Everything is fine.
[Jul 24 17:16:43] DEBUG[8732] res_config_mysql.c: MySQL RealTime:
Retrieve SQL: SELECT * FROM sip_buddies WHERE name = 'walter' AND host
= 'dynamic'
[Jul 24 17:16:43] DEBUG[8732] db.c: Unable to find key 'walter' in
family 'SIP/Registry'

No matter what I tried I could not fix this.

Finally I found out that after dropping md5secret authentication instantly
began to succeed.

mysql> select * from sip_buddies;
+----+--------+---------+-----+--------+-------------+----------+------------+-----------+----------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+
| id | name   | host    | nat | type   | accountcode | amaflags |
call-limit | callgroup | callerid | cancallforward | canreinvite |
context | defaultip | dtmfmode | fromuser | fromdomain | insecure |
language | mailbox | md5secret                        | deny | permit
| mask | musiconhold | pickupgroup | qualify | regexten | restrictcid
| rtptimeout | rtpholdtimeout | secret | setvar | disallow | allow
              | fullcontact | ipaddr | port | regserver | regseconds |
username | defaultuser | subscribecontext |
+----+--------+---------+-----+--------+-------------+----------+------------+-----------+----------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+
|  1 | walter | dynamic | no  | friend | NULL        | NULL     |
 NULL | NULL      | NULL     | yes            | yes         | NULL
| NULL      | NULL     | NULL     | NULL       | NULL     | NULL     |
NULL    | 4d27b7677bd96f7ba00c4bd0541c9588 | NULL | NULL   | NULL |
NULL        | NULL        | NULL    | NULL     | NULL        | NULL
   | NULL           | qwedsa | NULL   | all      |
g729;ilbc;gsm;ulaw;alaw |             |        |    0 | NULL      |
      0 | walter   |             | NULL             |
+----+--------+---------+-----+--------+-------------+----------+------------+-----------+----------+----------------+-------------+---------+-----------+----------+----------+------------+----------+----------+---------+----------------------------------+------+--------+------+-------------+-------------+---------+----------+-------------+------------+----------------+--------+--------+----------+-------------------------+-------------+--------+------+-----------+------------+----------+-------------+------------------+
1 row in set (0.00 sec)

mysql> alter table sip_buddies drop regserver;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

(retry auth - no luck yet)

mysql> alter table sip_buddies drop regseconds;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

(retry auth - no luck yet)

mysql> alter table sip_buddies drop md5secret;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Suddenly, authentication works!

The md5secret used was the md5 of 'qwedsa', and the value was correct.

mysql> select md5('qwedsa');
+----------------------------------+
| md5('qwedsa')                    |
+----------------------------------+
| 4d27b7677bd96f7ba00c4bd0541c9588 |
+----------------------------------+
1 row in set (0.00 sec)

Now if I revert to md5secret ....

mysql> alter table sip_buddies drop secret;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table sip_buddies add md5secret varchar(32);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update sip_buddies set md5secret=md5('qwedsa');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Authentication fails again.

If I add back in a NULL secret column...

mysql> alter table sip_buddies add secret varchar(32);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Authentication fails again.

If I add the plaintext value in...

mysql> update sip_buddies set secret='qwedsa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Authentication fails again.

If I drop once more the md5secret...

mysql> alter table sip_buddies drop md5secret;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Authentication is working.

Now, this seems very strange to me since I could swear
we did have md5secret working for awhile earlier today.
I suppose there must be some kind of bug in config.c,
the res_mysql addon or someplace else in realtime.

The debug log of the final failed request:

[Jul 24 17:37:28] DEBUG[8732] chan_sip.c: = Found Their Call ID:
1179526910 at 192.168.0.25 Their Tag  Our tag: as302c86be
[Jul 24 17:37:28] DEBUG[8732] chan_sip.c: **** Received REGISTER (2) -
Command in SIP REGISTER
[Jul 24 17:37:28] DEBUG[8732] res_config_mysql.c: MySQL RealTime:
Everything is fine.
[Jul 24 17:37:28] DEBUG[8732] res_config_mysql.c: MySQL RealTime:
Retrieve SQL: SELECT * FROM sip_buddies WHERE name = 'walter' AND host
= 'dynamic'
[Jul 24 17:37:28] DEBUG[8732] db.c: Unable to find key 'walter' in
family 'SIP/Registry'
[Jul 24 17:37:28] DEBUG[8732] chan_sip.c: Bah, we're expired
(1216892248/0/1216892248)!
[Jul 24 17:37:28] DEBUG[8732] db.c: Unable to find key 'walter' in
family 'SIP/Registry'
[Jul 24 17:37:28] DEBUG[8732] chan_sip.c: Destroying SIP peer walter
[Jul 24 17:37:28] DEBUG[8732] chan_sip.c: SIP message could not be
handled, bad request: 1179526910 at 192.168.0.25

If someone could sort out this bug (or let me know if I'm missing
something 'obvious' - a hard call with realtime documentation this
sparse...) I'd be most grateful, since we require md5secret support
to integrate with our existing users database.

Regards,
Walter Stanish
Owner / Director
Occident Systems
(+86 15808 700 801)

On Thu, Jul 24, 2008 at 11:04 AM, Walter Stanish
<walter at occidentsystems.com> wrote:
> If someone could sort out this bug (or let me know if I'm missing
> something 'obvious' - a hard call with realtime documentation this
> sparse...) I'd be most grateful, since we require md5secret support
> to integrate with our existing users database.
>
Welcome to Asterisk!

It's highly unlikely you'll find anyone to find the bug for you unless
someone is experiencing the same thing. There's no guarantee the bug
is actually with Asterisk it could be with your database or somewhere
in between. That's not to say it's not with Asterisk but there are a
lot of people using realtime with MySQL so if it was a galring bug it
would have been seen and logged already.

If you do manage to track down the bug it will generally at lest get a
response in a short amount of time once it's on the bug tracker.

Regards,

Greyman.

> 
> mysql> alter table sip_buddies drop md5secret;
> Query OK, 1 row affected (0.00 sec)
> Records: 1  Duplicates: 0  Warnings: 0
> 
> Suddenly, authentication works!
> 
> The md5secret used was the md5 of 'qwedsa', and the value was
correct.
> 
> mysql> select md5('qwedsa');
> +----------------------------------+
> | md5('qwedsa')                    |
> +----------------------------------+
> | 4d27b7677bd96f7ba00c4bd0541c9588 |
> +----------------------------------+
> 1 row in set (0.00 sec)
> 
Walter,

Not sure, but the above might be your problem.

The md5secret is NOT a MD5 sum of the secret, but of the combination
"username:realm:secret". So in your case you should add this
md5secret:

mysql> select md5('walter:asterisk:qwedsa');
+----------------------------------+
| md5('walter:asterisk:qwedsa')    |
+----------------------------------+
| 577061918968e961153393ef87b43e4b | 
+----------------------------------+

This would explain why the tests with cleartext secrets work, and not the
ones with the md5secret. Not sure if you tried md5secrets with a static
sip.conf user definition, but the result should be a credential failure in
that case as well.

Best regards,
Torbj?rn