IVR Challenge

Directly modifying the asterisk database outside of asterisk is high unsupported and kind of dangerous. Just saying…

Yet FreePBX has been doing it for years :slight_smile:

I look for no support apart from sqlite3, both the embedded version in Asterisk and the generally available client , nowhere in the asterisk documentation can I find anything but agreement that it conforms in general to the underlying sqlite3 specs (confirmed humbly by myself) my only quibble as noted implicitly in the script that the asterisk implementation selfishly and unnecessarily locks the astdb table, but that need not be debated here in FreePBX

Given that then please explain the dangers you are fearful of and your authorities for so stating those fears.

Um no actually that is incorrect. FreePBX itself modifies the Astdb through either AMI or directly from the Asterisk CLI. In terms of backup the database is overwritten from the backup file. In no script do we shutdown asterisk and manipulate the database outside of Asterisk (notice that your script manipulates the db, in backup we replace the whole thing). So your statement is factually incorrect. I asked @tonyclewis and @plindheimer about this last week at ITEXPO where I was wondering if we could edit it from outside of Asterisk and the answer was “no we tried that and it was a disaster”

My explanation is simple. This database is/are the internal workings of Asterisk. If your script lazily inserts (as it can with an import) and somehow inserts an invalid character then you start Asterisk and you “may” have issues. It’s something to consider. For someone like yourself whom is so concerned about security it should be pointed out that your “outside of asterisk” method is experimental and dangerous for a new user to be doing. That is all I am trying to say. It is no different than trying to apply the Cisco patches to chan_sip, or shutting down mysqld and using a hex editor to modify the database files…they will work but they are experimental and dangerous. This is the same thing…

Of note: I am not prodding you. I am merely speaking my own opinion.

And I also was expressing my own opinion, I have had reason to be deep into the “internal workings” of asterisk many times, in this case

/asterisk/source/main/db.c

If you read it you will perhaps understand where I come from, the calls are all to well documented sqlite3 library functions and not really open to misinterpretation, the locking is a little brutal and doesn’t release the mutex lock on a read. The code is threadsafe but not multi connection safe yet, hence my choice to remove any other connections first.

Given that and understanding that that current sqlite3 astdb is a drop in replacement for the old berlkleydatabase and is defined simply by

sqlite3 /var/lib/asterisk/astdb.sqlite3 .schema

so there is a key and a value with the table indexed on the key for efficiency, just like the old berkley, no brain surgery involved here, its just a faster and more scale-able database with a journal for recovery, so injection of unlikely extra character would just make the desired call to asterisk database function not work as desired, further every call to those same functions are wrapped by the sqlite3 client within begin and commit statements so the journal will maintain its integrity, it is hard for me to discern any security risk without accidentally overwriting a value in your AMPUSER or DEVICE family, please notice that I am not going near those, if you have remaining concerns, please feel free to express them.

But as this thread was originally a challenge and new users are involved then you could replace the method I used, (which I personally don’t consider experimental or unsafe, more experiential as it was worked flawlessly for a couple of years) with a somewhat contrived but bash and asterisk safe by anyones standards, maybe even yours :slight_smile: :-

cat import.txt|sed 's:^/\(.*\)/\(.*\)|\(.*\):rasterisk -x |database put \1 \2  ?\3?|:'|sed 's/ ?/?/g'|tr '|' "'"|tr '?' '"'|/bin/bash

( hehe , I think I see boobs in that line :wink: )

It will run orders of magnitude slower, is definitely lazy, but will not require shutting down asterisk, and will I guarantee produce identical results, and as some of us charge $150/hr you have just spent $75.

I expect other solutions might or might not be published here, AMI/AGI or dialplan based, again there are many ways to skin a cat, my preference is always the fastest method with as little fat involved as possible.

ok, until I get my head in the books, I’m done… Thanks everyone for your input. I guess I’m just used to finding something that does kind of what I need, and just modifying it. I don’t have to start from scratch…