Make change to the freepbx database using php

I have two incoming routes of which one adds a “zero” to the incoming callerid so when I call back it’s automatically routed through another line. When adding a number to the blacklist (*32), this additional zero is also added and I would like to strip that automatically. That is why I wrote the following script:

<?php
$db=new SQLite3('/var/lib/asterisk/astdb.sqlite3');

//get
$results=$db->query('SELECT * FROM astdb WHERE key like "%/blacklist/00%";');

//check
if($results!==false) {
  //update
  $results=$db->query('UPDATE astdb SET key=replace( key, "/00", "/0" ) WHERE key like "%/blacklist/00%";');
  exec('fwconsole r');
}
?>

This script sometimes works, and sometimes it doesn’t and I can’t seem to figure out why. I have tested it numerous times in my browser and also ran it from the command line (php blacklist.php). Mostly it returns nothing, sometimes it returns “PHP Warning: SQLite3::query(): Unable to execute statement: database is locked in /var/www/html/blacklist.php on line 10”.

First of all, is this a smart way to go about this or is there a better way?
Second: any idea why it sometimes does and other times doesn’t work?

Modifying the asterisk internal database outside of asterisk is asking for trouble. You need to modify the asterisk internal database through either ami or through database commands in the asterisk cli. When you edit it directly using SQLite you can really mess up asterisk when it’s trying to ask for a lock and instead you have it locked.

Also you are updating the asterisk internal db. There is no need to run fwconsole r.

The asterisk internal database is different from the freepbx asterisk database.

Can you give me examples of or point me to AMI or database commands in the asterisk cli?

freepbx*CLI> database show blacklist
/blacklist/5554446666                             : old name
1 results found.
freepbx*CLI> database get blacklist 5554446666
Value: old name
freepbx*CLI> database put blacklist 5554446666 "new name"
Updated database successfully
freepbx*CLI> database get blacklist 5554446666
Value: new name

Ok, but can you also tell me how to update the number? Or is it only possible to get/put/del items?

freepbx*CLI> database put blacklist 123456789 "second number"
Updated database successfully
freepbx*CLI> database show blacklist
/blacklist/123456789                              : second number
/blacklist/5554446666                             : new name
2 results found.
freepbx*CLI> database del blacklist 5554446666
Database entry removed.
freepbx*CLI> database show blacklist
/blacklist/123456789                              : second number
1 results found.

Clear, in my case that would mean first deleting the old value and inserting the new value. I will give that a try.

1 Like

The following works for me, hope it helps somebody in the future, probably not this specific case, but in any case the PHP/CLI part.

<?php
include '/etc/freepbx.conf';

if($astman->connected()) {
    //get numbers currently in blacklist
    $numbers=$astman->Command('database show blacklist');
    $numbers=explode(PHP_EOL, $numbers['data']);

    //loop
    foreach($numbers as $number) {
	//find number with 00
	if(strpos($number, '/blacklist/00')!==false) {
	    //filter number from string
	    $number=str_replace(array(' ', ':1'), '', $number);
	    $number=filter_var($number, FILTER_SANITIZE_NUMBER_INT);

	    //delete number with 00			
	    $astman->Command('database del blacklist '.$number);

	    //add number without 00
            $astman->Command('database put blacklist '.substr($number,1,10).' "Updated '.date('YmdHis').'"');
	}
    }
}else echo 'no asterisk manager connection';
?>

Thanks for the tips and pointers. Have a Merry Christmas!

1 Like

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