FreePBX Guide How to Block or Prioritize calls with Dynamic Routes using sql database via ODBC

This post will attempt to cover everything necessary start to finish.

This guide assumes your using mariadb-server but if requested I can also cover mysql-server.

The beauty of this is a single database of contacts where you can block or prioritize calls.
You can have as many remote freepbx system as you want all using this single database.

If the database is going on the same machine as your freepbx then you already have one or the other.
On debian based systems you can check which is installed like this:

sudo apt list --installed | grep 'mariadb-server\|mysql-server'

In my case I actually run mariadb in a container separate from freepbx, so first I install mariadb-server:

sudo apt install mariadb-client mariadb-server

Next I secure the installation, I set a password for root and I answer Yes (default) to the other questions:

sudo mysql_secure_installation

Next I put my Let’s Encrypt Certs on my sql server.
I generate certs using pfsense ACME and a shell script to copy them to sql server via sftp in a chroot.

You could also just generate them directly on the sql server with certbot. (search letsencrypt certbot)
If your db is on the same host as freepbx and no remote systems will use the db then you can skip certs.

Most guides covering mysql/mariadb SSL certs use self signed certs. I do not recommend this because then every client needs certs in order to trust the self signed certs, but Let’s Encrypt is a trusted CA so certs are only needed on the sql server.

Make the directory where we will put our certs, and create the placeholder files:

sudo mkdir -p /etc/mysql/ssl
sudo touch /etc/mysql/ssl/{ca,server-cert,server-key}.pem
sudo chown -R mysql: /etc/mysql/ssl
sudo chmod 755 /etc/mysql/ssl
sudo chmod 400 /etc/mysql/ssl/*

Certbot can automatically renew certs, if you dont want to have to customize it to put the certs with the correct permissions in the correct location, then you can instead just setup a cron job.

You may have to adjust this a bit, its been years since I have used certbot, but what this does is copies the contents of the file from the certbot location to the mysql location, keeping the permissions in place that we already setup.

When you use crontab, it will ask how you want to edit, I suggest nano if your unsure:

sudo crontab -e

0 4 * * * cat /etc/letsencrypt/live/ | tee /etc/mysql/ssl/ca.pem >/dev/null
0 4 * * * cat /etc/letsencrypt/live/ | tee /etc/mysql/ssl/server-cert.pem >/dev/null
0 4 * * * cat /etc/letsencrypt/live/ | tee /etc/mysql/ssl/server-key.pem >/dev/null
1 4 * * * systemctl restart mysql

Configure the mariadb server to use SSL and open it to listen to remote connections:

sudo nano /etc/mysql/mariadb.conf.d/z-server.cnf

bind-address =
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
ssl-cipher = TLSv1.2,TLSv1.3
ssl = true

Now restart mariadb:

sudo systemctl restart mariadb

Now lets create a database, user, and table, and create a couple records:

sudo mysql
CREATE USER 'freepbx'@'%' IDENTIFIED BY 'ExampleSuperLongPassword';
GRANT SELECT ON contacts.* TO 'freepbx'@'%';
USE contacts;
CREATE TABLE `contacts` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `phone` BIGINT NOT NULL,
    `note` varchar(255),
    `block` boolean NOT NULL DEFAULT false,
    `vip` boolean NOT NULL DEFAULT false,
    PRIMARY KEY (`id`)
INSERT INTO contacts (name, phone, note) VALUES ('test', 5555555555, 'test');
INSERT INTO contacts (name, phone, note) VALUES ('Cust Survey', 5555555555, 'robo');
INSERT INTO contacts (name, phone, note, block) VALUES ('Vacuum Sales', 5555555555, 'robo', true);

Now on freepbx host configure it to use the contacts db via ODBC.

Next we will try connecting to the sql server we just setup.

Mariadb/odbc will not use SSL by default, meaning credentials/data are sent in plain text.
If your sql server is on the same network as FreePBX then you should go ahead and test connecting.
If sql server is on different network then you should probably jump down to where we enforce SSL.

Connect and check if SSL is supported, if setup properly it should show output like this:

mysql -u freepbx -p -h 192.168.0.x
mysql -u freepbx -p -h

|   have_openssl   |   YES
|   have_ssl       |   YES
|   ssl_ca         |   /etc/mysql/ssl/ca.pem
|   ssl_cert       |   /etc/mysql/ssl/client-cert.pem
|   ssl_key        |   /etc/mysql/ssl/client-key.pem

Above you can see that your able to connect via IP, but we will now enforce SSL:

sudo nano /etc/mysql/mariadb.conf.d/z-client.cnf


Now you should no longer be able to connect via IP.
You will need to use the domain name that matches your cert, try connecting again:

mysql -u freepbx -p -h

If this works then you have successfully setup a sql server and connected to it VIA SSL.

The next step is to setup ODBC so that you can use this database within FreePBX.
We will define another data source at the bottom of the odbc.ini file

sudo nano /etc/odbc.ini

Description=MySQL connection to contacts database

Modify res_odbc_custom.conf giving freepbx/asterisk access to the db we defined in odbc.ini
You need to use the same password here that you did above when you created the database and table:

sudo nano /etc/asterisk/res_odbc_custom.conf


Modify func_odbc.conf which gives freepbx/asterisk access to the db as a function:

sudo nano /etc/asterisk/func_odbc.conf


Check the odbc settings:

sudo ldconfig
sudo odbcinst -i -d -f /etc/odbcinst.ini
sudo odbcinst -i -s -l -f /etc/odbc.ini
sudo odbcinst -q -d

Now we will reboot, and asterisk/freepbx should have access to this database.

sudo reboot

If it worked then you should be able to do this and connect via ODBC:

isql -v contacts freepbx ExampleSuperLongPassword
SELECT * FROM contacts.contacts;

Now in FreePBX Web GUI configure dynroutes to use db via ODBC.

Create a Dynamic route and set the ODBC function name to:


Set the Dynamic route query to:

SELECT COUNT(*) FROM (SELECT phone,name FROM contacts WHERE (name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') LIMIT 1) AS myvar

The above query checks for a matching name or number in the database, you can route it accordingly.

Now say a couple contacts in the db are rude or downright keep calling even after being told not to.
Maybe you also have a very important person that you want to prioritize their calls.
Lets update our database to block the rude caller, and prioritize the important caller.
on your sql server connect to mysql using unix socket or you can also connect using the domain name.

sudo mysql
mysql -u root -p -h
USE contacts;
select * from contacts;
UPDATE contacts SET block = true WHERE name = 'Cust Survey';
UPDATE contacts SET vip = true WHERE name = 'test';
select * from contacts;

So now we are using the block and vip fields of the DB so instead of simply checking for a name in the db, lets check if block or vip is set to true and route accordingly:


SELECT COUNT(*) FROM (SELECT phone,name,VIP FROM contacts WHERE ((name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') AND VIP="1") LIMIT 1) AS myvar


SELECT COUNT(*) FROM (SELECT phone,name,block FROM contacts WHERE ((name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') AND block="1") LIMIT 1) AS myvar

I will cover more usage and example of dynamic routes soon. (including pictures)
I will show how to block callers or even prioritize callers, etc.
With small database changes you can do other things as well, dynroutes allows for very flexible routing.

This one is not SQL specific, but if you set the Source type to Asterisk variable instead of odbc for one of your routes. you can set the asterisk variable field to this:

${REGEX("^(800|888|877|866|855|844|833|822|880|881|882|883|884|885|886|887|889)" ${CALLERID(number)})}

That will catch all Toll Free numbers, you can route them to Terminate Call or if your woried it might be something you need, then you can just route it straight to voicemail. 99% of the toll free calls I get are marketing so I send them straight to voicemail so that I don’t get disturbed by the ringing phone.

Credits to these sources of information I used while working this out on my systems:


Reserved for mysql section if there is demand for it.

1 Like

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.