Chris BoardFeb 1, 2017 • 6 min read
Hopefully you know about our simple but useful light weight Android app that lets you view and edit your MySQL Databases on the move.
Hopefully, if you do, you also know that the app supports connecting to your MySQL Database via an SSH tunnel instead of connecting directly to the database.
Now unless you've read our website and documentation you might not necessarily know how the SSH tunnelling works. Before we get on to the exciting news, lets go over a brief description of how the plugin works.
There are 3 main parts, two of which are absolutely compulsory for the app to work, that is the mobile app itself, and the PHP API. The Android app talk to the PHP API to talk to your database, the reason for this is, there is no MySQL support for Android. If you don't want to do SSH tunnelling, then this is all you need to run the app. If however, you need/want SSH tunnelling then you need the MySQLManager-TunnelPlugin application.
Now you might be wondering, why is it separate app, why not do it within the PHP API, one reason you can't. PHP does support port forwarding/tunnelling, but the local and remote ports need to match, e.g. if you want to connect to a remote MySQL database on port 3306, the local port to tunnel through also has to be 3306. This doesn't work for us, for 2 reasons. The first reason, we already have a MySQL database on our server, so we can't create a socket on 3306 to forward from, plus we need to support multiple users connecting through SSH tunnels concurrently, so we need to be able to allocate a different local port, to tunnel to the users database port, which, is most likely going to be on port 3306.
Our server has the PHP API and the MySQL tunnel plugin installed so if you want to just go straight into using the app, you can, all you need is your own SSH server and you can then tunnel through it via our server to your database. Now we know security is important, and some users may not want our server accessing your database and returning the result. We promise we do not keep any record of the information retrieved, plus all the data is encrypted from end to end, but still it might be a security concern, so we provide the ability for you to install the PHP API and SSH Tunnel plugin on your own server.
The SSH tunnel plugin was developed using C# and can run on Windows and Linux under Mono. We run a Linux web server so we have the plugin running under Mono. Although its not causing our server any problem we have found that the plugin running under Mono causes 1 core of our server to permanently run at 100%, even if nobody is connected through a tunnel. Of all the investigations we've done we've not been able to find the cause, and this doesn't happen on Windows. Although we've had no complaints from users who might have installed the plugin on their own server, as we are seeing this on our own server, we are not happy, and are taking steps to avoid this issue.
We have completely re-written the MySQL Manager SSH Tunnel Plugin in C++ instead of C#. This should completely remove the CPU usage as its native code, and its not running inside a virtualised framework so we should have more control in the event that there are some odd issues crop up, although so far nothing has.
Not only that, we have also added a further security check when you connect to the SSH server. When you initiate an SSH tunnel connection, it will return you SSH servers fingerprint, you can then check if the fingerprint is what you expected, and if so accept it to complete the SSH tunnel (this will happen during the test connection on the connection creation). Then when you connect to your server in the future, if your servers been compromised, or the fingerprint changes, before you connect it will warn you that the fingerprint has changed, at which point you can either reject the fingerprint and stop connecting, or update the fingerprint in your app and confirm that it is correct. This will further ensure that you are connecting to the server you intended, and that you aren't unexpectedly connecting to a compromised server and potentially putting your data at risk.
Full disclosure, this is our first C++ app that we plan to release, although we have created some smaller apps for ourselves this is the first official C++ app that we are releasing as part of our services. Due to this it is currently going through some vigorous testing to ensure that there are no problems, at which point we will install it on our server. We won't release the new version to install yourself yet but we will ensure that the android app can support the old PHP/Tunnel Plugin and the new version. Once it has been tested in the wild we will then release it officially for you to install on your own server. This is where the exciting news comes in.
When we have completed are initial testing and ensured that there are no issues in production, we will then open source our PHP API and our SSH Tunnel Plugin.
We're open sourcing the project for a couple of reasons. One, we hope this will further the development of the code and make the service even better, and as we said, this is our first C++ project, so we would love to hear your feedback on how it can be improved (for either of the C++ or the PHP API) and we encourage anyone to do a pull request to fix any bugs you may find, any improvements you think could be made, or any features you wish to add. Plus, if its open source, you can check the code through yourself and confirm that we are not stealing or capturing any data from your database.
Once we have open sourced the code we will then do another blog post with details of where to get everything from and provide where to get the updated documentation to build and install it on your own web server.
If you have any questions, then please feel free to give us a call or send us an email at [email protected].