Number pattern matching in database

Asterisk Asterisk Support
1

Good day,

I use an Asterisk-produced database to handle blacklisting of telemarketer and other nuisance numbers. I’m in the process of changing this over to a MySQL setup, but that’s not the important point at the moment.

My question: I know how pattern matching works in extensions.conf, but I’m wondering if entries to the blacklist can also work with pattern matching? Example: If a telemurketer is using a range of numbers like 4152223450 through 3459, can I put the following into the database (as opposed to multiple single entries):

415222345*

Or, if it’s a narrower range, would this work? 415222345[0-5]

Thanks much.

2

Hello.

As for me, I’d prefer to configure ODBC and then use it.

It’s really cool. You define SQL queries/updates/inserts in func_odbc.conf and then use them from dialplan.

If you’re interested if it’s possible to add data into MySQL column which looks like 12345[0-9], I think this forum is not the best place to discuss MySQL capabilities. But of course it’s possible, depending on a datatype.

But I’m not sure adding such data is a good design. You’d better create a table like shown here. And then add some extra columns.

range_start   range_stop   block
-----------------------------------------
12223330      12223339      1

So , the logic will be something like: if ${CALLERID(num)} belongs to range, select the value of ‘block’ column and do some interesting stuff in dialplan according to this value. The query itself must be written in func_odbc.conf .

3

Make a select between range example :

SELECT job FROM mytable WHERE id BETWEEN 10 AND 15

stackoverflow.com/questions/1058 … t-in-range

4

Another typical approach would be the “Best-Fit-Algorythm”. Let’s illustrate this:

MySQL-Table BLOCKED_NUMBERS

Number     Block
123           1
456100      1

Asterisk func_odbc.conf Select

[GET_BLOCKED]
readsql=SELECT Block from BLOCKED_NUMBERS WHERE number='${SQL_ESC(${ARG1})}'

Asterisk Dialplan (AEL-example)

for (x=${LEN(${CALLERID(num)})}; ${x}>=1;x=${x}-1) {
      Set(temp=${CALLERID(num):0:${x}});
      Set(BLOCKED=${ODBC_GET_BLOCKED(${temp})});
      if (! ${ISNULL(${BLOCKED})} ) {
            Set(x=0);
            Hangup(16); This is a blocked number calling us
      }
}

In this example every CALLERID starting with either 123 or 456100 will get blocked (e.g. 123; 1234; 4561000 will be blocked but 456101 won’t)
The minimum match-lentgth ist herby defined with 1 Digit (${x}>=1), this may be changed when convenient.

5

Thanks to all for the hints on SQL’ing things. However, no one has yet answered my original question.

Specifically: Does Asterisk’s native pattern matching, as used in the dialplan, also work with entries in my existing blacklist?

As I mentioned in my original post: SQL-converting things is the LOWEST of my priorities right now. I just want a yes/no answer to whether the pattern matching works.

Thank you.

6

Has anyone ever found an answer to this?

I am using AstDB blacklist implementation as per this thread: viewtopic.php?f=1&t=91389

And I would like to blacklist a range of numbers as OP but can’t figure out or find the syntax that works for this.

Thanks in advance!

[quote=“kyrrin”]Thanks to all for the hints on SQL’ing things. However, no one has yet answered my original question.

Specifically: Does Asterisk’s native pattern matching, as used in the dialplan, also work with entries in my existing blacklist?

As I mentioned in my original post: SQL-converting things is the LOWEST of my priorities right now. I just want a yes/no answer to whether the pattern matching works.

Thank you.[/quote]

7

There’s no native algorhythm for this, but You can just use the ODBC-example as basis with some assumptions:

Let’s assume the DB-Family is called “blocked”. Let’s further assume, that a blocked number is stored as the key and that - at least - the value for a blocked family/key-combination is 1 while a “unblocked” entry has the value 0. Then this could be implemented as in the following example:

Asterisk Dialplan (AEL-example)

for (x=${LEN(${CALLERID(num)})}; ${x}>=1;x=${x}-1) { Set(temp=${CALLERID(num):0:${x}}); Set(BLOCKED=${DB(blocked/${temp})}); if ("${BLOCKED}"="1" ) { Set(x=0); Hangup(16); This is a blocked number calling us } }