Implementing Blacklist in Asterisk with MySQL Database

If you don’t use the ready-made Asterisk distribution such as FreePBX and you don’t have a web GUI for it, then the task of adding numbers to Blacklist comes down to working with AstDB. With this, in principle, everything is simple. But the Blacklist Asterisk database is common and if we need to differentiate the lists of blocked numbers by subscribers, then in this case it is better to resort to using an external database.

So for this we need:

• Asterisk itself (how could it be without it)
• MySQL database
• Phpmyadmin GUI for more visual database management

Let's get started. We have the following action plan:

1. Creating a base for numbers and tables in it
2. Create a macro in dialplan Asterisk
3. Verifying macro operation in dialplan

Using phpmyadmin, create a database and a test table with fields

• id (key field)
• callerid (the blocked number itself)
• blockenabled (field for setting whether blocking is enabled for this number)
• notes

Now we’ll write a macro to check in terms of dialing for the availability of numbers in our blacklist. To do this, in the general section, add the globals block where we declare all our variables necessary to connect to our database.
In the macro itself, and we will use its updated and more stable version of GoSub (), we will write the commands for connecting to the database (we will use the Asterisk MySQL operator) and search the table for the desired CALLERID value.
After fetching from the database, we check the found value with the CALLERID of the caller and end the call in case of a match.

[general]
[globals]
DBCurrentHost=localhost
DBuser=myuser
DBpass=mysomepassword
DBname=asterisk
[SubMysqlblacklist]
exten => s,1,NoOp(--- MACRO --- BLACKLIST ---)
same => n,MySql(connect connid ${DBCurrentHost} ${DBuser} ${DBpass} ${DBname})
same => n,MySql(query resultid ${connid} select callerid from own_blacklist where callerid=${CALLERID(num)} and blockenabled = 1)
same => n,MySql(Fetch fetchid ${resultid} blacklistid)
same => n,NoOp(FetchID: ${fetchid} Var1: ${blacklistid} ConnID: ${connid} ResultID: ${resultid})
same => n,GotoIf($["${CALLERID(num)}" = "${blacklistid}"]?blacklisted)
same => n,MySql(clear ${resultid})
same => n,MySql(disconnect ${connid})
same => n,Goto(end)
same => n(blacklisted),NoOp(Cannot dial - ${CALLERID(num)} is blacklisted !)
same => n,MYSQL(clear ${resultid})
same => n,MYSQL(disconnect ${connid})
same => n,Hangup()
same => n(end),NoOp(-- Clear --)
same => n,Return()

Next, we call our macro in terms of sets and after it is rebooted in the console (dialplan reload) we check the operability.

exten => 4445566,1,NoOp(-- to ${EXTEN} --  -- ${CALLERID(num)} --)
same => n,GoSub(SubMysqlblacklist,s,1())
same => n,Dial(SIP/to_user1/555,40)
same => n,Hangup()

As a result, we got the following functionality:

• All malicious telephone pests are blocked and stored in one database. And you can add numbers not through the console but through the Web interface;
• We can create several tables for each subscriber and block the callers individually (if necessary).

Everything is prepared on materials from open sources.