Redundancy and Setup for Website Now Playing & Request Functionality

[djMot]

This is kind of a request for configuration guidance.  I want to make sure my stations have redundancy and I also want to implement the Now Playing and Request functions.

I'm coming to terms with allowing my website to access my local database for Now Playing and Requests, but I have security concerns...

Okay so this is what I have:

* Two computers on the local LAN:  192.168.1.11 and  192.168.1.12
* Both are running MySQL on port 3306 (via a WAMP installation on each.)
* Girls Rock Radio and Country Girl Radio are both on the .12 computer.
* The .11 computer is my regular work computer (and the stations' backup computer.)
* Girls Rock Radio still on SAM (want to move her to RadioDJ ASAP!)
* Country Girl Radio on RadioDJ.
* A website for each station that is not port blocked.
* Both station websites are running WordPress (and no, I don't know enough about the RDJ Plugin to talk intelligently about it.)
* The WAN side of my router is on a static IP.

What I've done so far:
* Created a new MySQL user on .12 for remote access for Country Girl Radio with access granted from the remote website.
* Opened port 3306 through the firewall. (*yikes!*)
* Following the info in this thread (and a little extra head scratching,) I've set up a working Now Playing widget.  It's outside WordPress at this time, but needs to ultimately be integrated.  I can handle that.   ( http://www.radiodj.ro/community/index.php?topic=7471.0 )

Concerns & Questions:
* I'm concerned that there may be a real issue with 2 computers running MySQL on the same port with identical (sort of) databases on each.  How would inbound queries to the database for Now Playing and Request services be routed?  Possible solutions?  My thoughts:
1) I could Use port forwarding in the router to get 3306 traffic to .12 (as I am now,) but if I have to take it down for maintenance and bring .11 up to stand in, I'd have to alter the port forwarding rules for the duration of the maintenance. -OR-
2) I could change MySQL port on each computer (.11:3304 and .12:3308 say,) but then the live and backup station configurations are out of sync, not to mention connect information in the PHP code up on the websites.
Is there an elegant solution to this situation?  Or am I overthinking or not looking at something right?

* Given some of the recent discussion of risk of SQL injections (Andy) due to outdated code, and request spam (Fuzzy,) I'm really concerned about opening things up for a potential attack.  I've read a lot in here, but it all still leaves me wanting for a good "best practices" guide to help make sure we don't just shoot ourselves in the foot.  And maybe even a guide, tools, etc., where and how to look for attack footprints (beyond the obvious request spam some have seen.)  Not sure there's a question in there, but any thoughts on staying safe are welcome.


[Aside] - Country Girl Radio sounds amazing.  Not just the music, yes there's that, but I mean the sound.  Way better!  So looking forward to getting Girls Rock Radio converted over!

[AndyDeGroo]

First, if you have a WordPress site, please install new WordPress plugin and not the one available from downloads on this site. I've rewritten it from ground up to be more secure. Don't use any of the demo scripts without modifications. They are good for starting out but not secure or easy to customise. But rewriting all the scripts is on my to do list.

As for your setup, to secure your MySQL server(s) from outside bruteforce attacks (they are way too common when you open 3306 on WAN), edit firewall rules to allow only the web servers to connect. Your router even may have option to limit remote IPs for a forward rule. Depends on router firmware but if it doesn't, configure Windows firewall.

On a side note, I highly recommend installing IPBan service, which monitors event logs for  failed login attempts to RDP, MSSQL and MySQL(!) and automagically blocks offending IPs in firewall.

If you want to keep two MySQL servers in sync, you'll have to get your hands dirty and set up master-master replication. It takes some work, but once set up, it allows for automatic failover either using mysqlfailover monitoring tool or DNS failover.

Best places to look for attack footprint is server log files. No matter if it is Apache/Nginx/IIS web server or MSSQL/MySQL/MariaDB database server. There are proactive defence solutions, like the aforementioned IPBan on Windows and fail2ban on various Linux distros.

I hope I've addressed at least some of your questions concerns.

[djMot]

Thank you Andy.

I will have questions.  Maybe not all at once.  That's a lot to digest right there.   

I have just installed your revised WordPress plugin and it is working fine right out of the box.  That said, I'm going to want to "skin" this thing differently.  That would mean doing things like adding album art, rearranging fields, conditionally displaying what's available and not displaying what's not, etc.  Is this something I can do?  I'm good with PHP, HTML, CSS, etc., but not experienced much within the context of a WordPress plugin.

I'm running an Asus RT-AC66U router here - a very capable router - so I think I should be able to set up appropriate blocking rules and will consider moving to a different port as soon as I can.  I have both RDJ and SAM to think about for a while yet, and I don't want to end up pissing either one off.  Once Girls Rock Radio is switched over to RDJ, that's when I'll look at a port change.  Country Girl Radio is the RDJ pilot-project.  Once I've got it purring like a kitten, Girls Rock Radio can begin the migration.

Not sure I really want to get as deep as an enterprise-level MySQL replication strategy.  With SAM, I just have everything installed on both computers here.  If I have to do some lengthy computer maintenance, I just switch to Manual and let the current song play out on the live computer, then switch into Auto on the other computer.  Switch back when everything's all done.  Preparation really only involves restoring a recent MySQL backup (previously nightly) from the live computer before the switch over to the other one so things would be reasonably equal.  BUT - for Girls Rock Radio, there is no remote database access going on.  As a matter of fact, for her request system, I literally copy the SAM Songs table into the Girls Rock Radio WordPress database and run queries for requests (and other song info) right on the db local to WordPress. 

But again, I'm not sure what happens (as in the case of RadioDJ) if I have remote db access going on and the remote website finds two instances of MySQL running over here and they both have the same database, usernames, passwords, etc...  Still looking for some guidance on that then.

You've given me a lot to think about and some excellent direction on the security issues.  THANK YOU for that.  I'll be all over that ASAP. 

[AndyDeGroo]

I have just installed your revised WordPress plugin and it is working fine right out of the box.  That said, I'm going to want to "skin" this thing differently.  That would mean doing things like adding album art, rearranging fields, conditionally displaying what's available and not displaying what's not, etc.  Is this something I can do?  I'm good with PHP, HTML, CSS, etc., but not experienced much within the context of a WordPress plugin.

Sure you can customize its look and layout. I've separated most of presentation in views and php files are located in, wait for it, views directory. If you are good with PHP and HTML, you should feel right at home editing those files.
As for WordPress context, all functions are well documented in WordPress codex. Just a handful of WordPress functions are used in the views files.
All CSS styles are in css/radiodj.css but the plugin look for a custom CSS radiodj.css in theme or child theme directory and load that file instead.

If you need additional fields fetched from database, you'll have to modify queries in lib/radiodj.class.php. Note that RadioDJ::nowplaying() method for now-playing shortcode is using WordPress transient to cache query results for 10 seconds. It reduces response times, since remote DB queries are slow but may interfere with your modifications.

I'm running an Asus RT-AC66U router here - a very capable router - so I think I should be able to set up appropriate blocking rules and will consider moving to a different port as soon as I can.  I have both RDJ and SAM to think about for a while yet, and I don't want to end up pissing either one off.  Once Girls Rock Radio is switched over to RDJ, that's when I'll look at a port change.  Country Girl Radio is the RDJ pilot-project.  Once I've got it purring like a kitten, Girls Rock Radio can begin the migration.

There is no need to change default ports of MySQL servers. You can forward a different external port to default 3306 on the inside.

Code Select Expand


[WAN IP] TCP 6033 --> 192.168.1.11:3306
[WAN IP] TCP 7033 --> 192.168.1.12:3306

For added security and peace of mind, allow only the web server IP(s) to connect to those ports. You can do that using either your router's firewall (Advanced Settings > Firewall > Network Service Filter tab) or "Windows Firewall with advanced security" interface. I'd use the latter, because it is more reliable and has more options than the one in your router (I checked it's manual). From my own experience, consumer grade router firewalls tend to be flimsy and can slow down all connections even when configuration looks correct.

Not sure I really want to get as deep as an enterprise-level MySQL replication strategy.  With SAM, I just have everything installed on both computers here.  If I have to do some lengthy computer maintenance, I just switch to Manual and let the current song play out on the live computer, then switch into Auto on the other computer.  Switch back when everything's all done.  Preparation really only involves restoring a recent MySQL backup (previously nightly) from the live computer before the switch over to the other one so things would be reasonably equal.  BUT - for Girls Rock Radio, there is no remote database access going on.  As a matter of fact, for her request system, I literally copy the SAM Songs table into the Girls Rock Radio WordPress database and run queries for requests (and other song info) right on the db local to WordPress.

But again, I'm not sure what happens (as in the case of RadioDJ) if I have remote db access going on and the remote website finds two instances of MySQL running over here and they both have the same database, usernames, passwords, etc...  Still looking for some guidance on that then.

Unlike SAM (IIRIC), RadioDJ will not function in manual mode if its database is not available. Even when AutoDJ is turned off, RadioDJ updates database upon track playback and will throw an exception if it can't. Similarly, with RadioDJ requests system the website has to have access to playout database if you want to automate request playback using rotations, events or requests plugin and it's not possible to configure a different database for requests.
If you are concerned about website being able to write to RadioDJ database, create a new MySQL user for use by web site and configure granular table permissions allowing write operations to requests table and only reading all other tables.

Remote website shouldn't get confused by two MySQL databases even if it connects to both at the same time. Since different external ports are forwarded to each database, the PHP code should be using those port numbers to connect to those databases. The only gotcha when using the old and deprecated mysql PHP extension with multiple connections is that mysql_error() returns errors from last successful connection, which in WordPress is connection to WordPress database. You can see an attempt to hack around this issue in radiodj_db.class.php file of WordPress plugin. Not elegant but at least it provides some indication to user.

[Chaos Radio!]

First, if you have a WordPress site, please install new WordPress plugin and not the one available from downloads on this site. I've rewritten it from ground up to be more secure. Don't use any of the demo scripts without modifications. They are good for starting out but not secure or easy to customise. But rewriting all the scripts is on my to do list.

As for your setup, to secure your MySQL server(s) from outside bruteforce attacks (they are way too common when you open 3306 on WAN), edit firewall rules to allow only the web servers to connect. Your router even may have option to limit remote IPs for a forward rule. Depends on router firmware but if it doesn't, configure Windows firewall.

On a side note, I highly recommend installing IPBan service, which monitors event logs for  failed login attempts to RDP, MSSQL and MySQL(!) and automagically blocks offending IPs in firewall.

If you want to keep two MySQL servers in sync, you'll have to get your hands dirty and set up master-master replication. It takes some work, but once set up, it allows for automatic failover either using mysqlfailover monitoring tool or DNS failover.

Best places to look for attack footprint is server log files. No matter if it is Apache/Nginx/IIS web server or MSSQL/MySQL/MariaDB database server. There are proactive defence solutions, like the aforementioned IPBan on Windows and fail2ban on various Linux distros.

I hope I've addressed at least some of your questions concerns.

Andy will you be making a new modified secure demoscript for php? just asking for the non-wordpress users.