FreePBX database (MySQL)

General Help
1

Using FreePBX 2.10.1.9.

FreePBX stores configuration information in a MySQL database and uses that database to generate a dial plan in /etc/asterisk, right?

As part of the cleanup I have been forced to do, after the hacking episode I described in other posts over the last week or so, and even though SIP isn’t how the hacker got in, I have had to shut down SIP in the firewall from everywhere except a few whitelisted IPs, like the main office of the non-profit I’m doing all this for. This is a very large problem for those volunteers for the non-profit who would like to access the PBX via their smartphones and X-Lite installations on their laptops, from wherever they might be.

I can’t open up general SIP access again until everyone has changed their authorization passwords, all of which were stolen in the hack attack. Since few have gotten around to doing that and it’ll be a long time before everyone gets around to it, the only alternative is to lock them out of the system until they do. Easiest way to do that is to bulk-change all of their passwords to something they don’t know, then they’ll have to come to me to regain access, and meanwhile I can open up SIP access again.

I can go in via FreePBX and individually change everyone’s password to something unknown, but that’ll take way more time than I have available. So, I’m thinking I can go into the MySQL database using some GUI tool like phpMyAdmin, and change the passwords in bulk, then tell FreePBX to reload the (new) dial plan.

Can someone tell me if there is documentation around that tells how to do all of this, like documentation of the MySQL database format? So far Google has not helped.

Yes, I am a MySQL noob. But if I can get info on the format I can probably figure it out.

Thanks…

2

Well you could start by reading up on mysql syntax because you can easily break things if you typo anything, but the core of your question’s answer will look something like:-

mysql -u youruser -pyourpassword -D asterisk -e "UPDATE sip SET data='yournewsecret' WHERE keyword='secret' AND id LIKE (3456 OR 1234 OR 987%. . . .)";amportal admin  reload

(I added a conditional clause for the extensions you want to change, season to taste)

3

What about the bulk extensions tool?

If you’re not comfortable with mysql …

4

I like the BUlk extension tool for this as well!

I was going to suggest bulk extensions as well - using a CSV file gives you a lot of flexibility and let’s you edit the file in a tool that a little easier to move around in than the CLI for MySQL.

If you’re comfortable with OpenOffice or Excel, you should be able to make short work of the password thing AND you can use the same file to mailmerge the passwords out to the users so that they don’t have to call you when their connections don’t work.

It also let’s you get creative with the passwords (instead of setting them all to the same thing). For example, you could use the dec2hex on a really big random number to get 0-9A-F digits and set each password to something different and “not as easy” to bulk crack. Throw in a random ‘hash’ to make the passwords even harder, or toss in a random three letter word. You can get pretty creative with passwords. Of course, some phones will only use numbers, others numbers and letters, others can use good passwords…