Mysql error in Asterisk

General Help
1

Hello everyone. So, we have been using a script for CID Lookup and it has worked for years, but we have problems with the latest Sangoma iso.

This is the code which is included inside extensions_override:

[cidlookup]
include => cidlookup-custom
exten => cidlookup_1,1,MYSQL(Connect connid localhost *dbuser dbpass dbname*)
exten => cidlookup_1,n,Set(miocid=${CALLERID(num)})
exten => cidlookup_1,n,Set(miocid=${IF($[${ISNULL(${miocid})}]?"":${miocid})})
exten => cidlookup_1,n,MYSQL(Query resultid ${connid} SELECT name FROM personalpbook WHERE cell LIKE '${miocid}' OR tel LIKE '${miocid}' OR tel2 LIKE '${miocid}' OR tel3 LIKE '${miocid}' OR cell2 LIKE '${miocid}' OR cell3 LIKE '${miocid}')
exten => cidlookup_1,n,MYSQL(Fetch fetchid ${resultid} CALLERID(name))
exten => cidlookup_1,n,Noop(Nome chiamante: ${CALLERID(name)})
exten => cidlookup_1,n,MYSQL(Clear ${resultid})
exten => cidlookup_1,n,MYSQL(Disconnect ${connid})
exten => cidlookup_1,n,Return()

exten => cidlookup_return,1,ExecIf($["${DB(cidname/${CALLERID(num)})}" != ""]?Set(CALLERID(name)=${DB(cidname/${CALLERID(num)})}))
exten => cidlookup_return,n,Return()

There’s a variation we use in case of a pre-selection digit.

Anyway, lately we always get these errors when the script executes:

    -- Executing [cidlookup_1@cidlookup:1] MYSQL("SIP/REDACTED-0000002c", "Connect connid localhost *dbuser dbpass dbname*") in new stack
[2024-08-29 12:31:30] WARNING[22463][C-00000015]: app_mysql.c:383 aMYSQL_connect: mysql_real_connect(mysql,localhost,*dbuser,dbpass,dbname*,...) failed(1045): Access denied for user 'dbuser'@'localhost' (using password: YES)
    -- Executing [cidlookup_1@cidlookup:2] Set("SIP/REDACTED-0000002c", "miocid=REDACTED") in new stack
    -- Executing [cidlookup_1@cidlookup:3] Set("SIP/REDACTED-0000002c", "miocid=REDACTED") in new stack
    -- Executing [cidlookup_1@cidlookup:4] MYSQL("SIP/REDACTED-0000002c", "Query resultid  SELECT name FROM personalpbook WHERE cell LIKE 'REDACTED' OR tel LIKE 'REDACTED' OR tel2 LIKE 'REDACTED' OR tel3 LIKE 'REDACTED' OR cell2 LIKE 'REDACTED' OR cell3 LIKE 'REDACTED'") in new stack
[2024-08-29 12:31:30] WARNING[22463][C-00000015]: app_mysql.c:409 aMYSQL_query: missing some arguments
    -- Executing [cidlookup_1@cidlookup:5] MYSQL("SIP/REDACTED-0000002c", "Fetch fetchid  CALLERID(name)") in new stack
[2024-08-29 12:31:30] WARNING[22463][C-00000015]: app_mysql.c:521 aMYSQL_fetch: aMYSQL_fetch: missing some arguments

I think the authentication error is the cause of all the following errors.
But user and password are correct, I checked and I can connect with no problems to that db with said user and password.

So I was wondering if there’s another mistake, maybe the connect command has changed in recent versions of Asterisk?

Does anybody have ideas? Thanks.

2

Under the latest myslq/mariadb they changed the authentication scheme so that connections to mysql from localhost root user don’t need authentication. This is not the case for non-root. For example on FreePBX 17

tedm@phony:/$ whoami
tedm
tedm@phony:/$ su
Password:
root@phony:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18182
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
root@phony:/#

However FreePBX also changed it to assign a random password to the userID it uses, thus:

root@phony:/# sudo -u asterisk -s
asterisk@phony:/$ grep PASS /etc/freepbx.conf
$amp_conf["AMPDBPASS"] = "YB3k9ZgcJx41";
asterisk@phony:/$
asterisk@phony:/$ mysql -ufreepbxuser -pYB3k9ZgcJx41
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18200
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
asterisk@phony:/$

The error message you posted:

Access denied for user ‘dbuser’@‘localhost’

certainly seems to indicate that the variable dbuser does not exist anymore…

3

What is the current version of Asterisk?

Actually, scratch that. Since you’re not getting errors about app_mysql being removed you must be running Asterisk v18 or lower. As of Asterisk v19 app_mysql was removed from Asterisk. As well, v18 is going to Security Fixes Only in under 30 days.

At this point you’re going to need to rewrite this function to use something else, perhaps a System() or AGI() call to run the queries. But unless you stay on older versions of Asterisk that will get no real support for things like this, you’re going to new to update this to use current apps and functions.

4

no they didn’t, all access needs an acl account that mariadb itself will verify, this has never changed . As you have been informed app_mysql has been removed.

5

It amazes me that I post actual statements and their output that prove what I said and you still insist on contradicting the evidence of your own eyes. LOL

I was not talking about Asterisk when I said “they” I was talking about mysql/mariadb/whatever the heck they want to call themselves nowadays. And as I demonstrated the default install of mariadb permits the root user to access. What you are supposed to then do is run

mysql_secure_installation

The history of that database has been one of CONSTANTLY mucking around with the authentication mechanisms and BREAKING things. People write a script that does something useful and the sql developers are like “horrors they actually put a password in a script on a server that’s unreachable from the Internet - we just can’t have that!” Then the next version changes the authentication scheme that then breaks everyone’s stuff and they have to recode.

Just for example look at this rediculous gem from their own documentation:

MySQL :: MySQL 8.0 Reference Manual :: 2.9.4 Securing the Initial MySQL Account

“… If the root account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as root without a password and be granted all privileges…”

Well, the default install mysql bind is127.0.0.1 so let’s test this from the host the SQL server is running on, shall we:

tedm@phony:~$ mysql
ERROR 1698 (28000): Access denied for user 'tedm'@'localhost'
tedm@phony:~$ mysql -uroot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
tedm@phony:~$ su
Password:
root@phony:/home/tedm# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18738
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

MariaDB [mysql]> select * from user where User like 'root';
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | is_role | default_role | max_statement_time |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
| localhost | root | invalid  | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      | Y                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | invalid               | N                | N       |              |           0.000000 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
1 row in set (0.002 sec)

MariaDB [mysql]>exit
Bye
root@phony:/home/tedm#

Don’t see any password for “root” there yet my regular user ID cannot access it using the mysql root user ID, I have to escalate to root user.

But more to the point, if an attacker can get root on your mysql server your security is gone, anyway. How’s a nice rm -rf / sound, folks? That’s why they allow this “hole”

6

If an 'attacker ’ has gained root access on the lo interface, you have much bigger problems looming.