[asterisk-users] Realtime + SIP + MySQL: md5secret BROKEN

[Walter Stanish]

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)