Implement Blacklist in Asterisk using MySQL database

If you are not using a ready-made Asterisk distribution such as FreePBX and you do not have a web GUI for it, the task of adding numbers to the 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 distinguish between 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 (well, how can it be without it)
• DB on MySQL
• GUI phpmyadmin for more intuitive 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 Asterisk dialplan
3. Checking macro operation in dialplan

Using phpmyadmin create a database and test table with fields

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

Now we will write a macro to check in the dial plan for the presence of a number in our blacklist. To do this, in the general section, add a 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 connection commands to the database (we will use the Asterisk MySQL operator) and search the table for the required CALLERID value.
After sampling from the database, we check the found value with the callerid caller and terminate 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 reloading it in the console (dialplan reload) we check the operation.
')

 exten => 4445566,1,NoOp(-- to ${EXTEN} -- <from> -- ${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.