I am working on a project for an app that allows Android to remotely connect to an MySQL Database using a PHP API.
Users have requested that the app should support SSH Tunneling which I have been looking into but I have a few concerns regarding this.
How the app works is the API runs on my web server (the user has the option to install on there own servers but most don't) so Android sends a post to the PHP API, and then PHP directly connect to a MySQL Host that was sent in the post message from android, this is all working fine.
However, if I want to support SSH tunneling, my understanding is that my Android app would still post to my PHP API, but instruct to open an SSH tunnel, for example forwarding local port 3307 to remote host example.com on port 3306. Then PHP would connect to MySQL via 127.0.0.1 on port 3307.
This is fine, except if another user then decides to use port 3307 at the same time another user is doing a tunnel the second user would clash as the first user already has 3307 open so they would need to pick a new port.
I thought that then the best work around for this would be the API has a pool of sockets that it can use and picks one that is free and connects via that local port via the specified remote port that android sent. E.g. the API will for example pick local port 5000 and forward to 3306.
My worry here is that although this would work, there is a potential risk that two users could make a request at the same time, and the API picks the same port number to use as a local port, e.g. 5000, as this port is technically free but then one user would successfully connect, and the second user would end up connecting to the first users servers, and hopefully fail to authenticate but still, potential risk here.
Am I thinking of this the correct way, or is there a better way, or should I only support the SSH tunneling option if the user is running the API from there own web server instead of mine so there would be no risk of clashing with another unrelated user.