Set up a secure connection to mysql

If you have ssl and mysql enabled in xojo cloud, is this script enough to ensure the security of the connection to the database?:

Var db AsNew MySQLCommunityServer

db.Host = “192.168.1.172”
db.Port = 3306
db.DatabaseName = “BaseballLeague”
db.UserName = “broberts”
db.Password = “streborb”
db.SSLEnabled = True

Try
db.Connect
Catch error As DatabaseException
MessageBox("Connection failed. Error: " + error.Message)
End Try

Or are these additional properties also necessary?:

// Specify SSL key file
db.SSLKey = NewFolderItem(“MySQLKeyFile”)

// Specify SSL certificate file
db.SSLCertificate = New FolderItem(“MySQLCertificateFile”)

// Specify SSL authority file
db.SSLAuthority = New FolderItem(“MySQLAuthFileFile”)

// Specify SSL authority directory
db.SSLAuthorityFolder = New FolderItem(“SSLCACertFile”)

// Specify SSL cipher
Var cipher As String
cipher = “DHE-RSA-AES256-SHA”
db.SSLCipher = cipher

Hi Francisco! I don’t have the answer but I’m eager to see what others have to say - welcome to the Forum by the way.

Hi Francisco
Need a little more information to answer your question. You reference Xojocloud. Are you connecting to a MySQL database on your XojoCloud server or is your MySQL database on another server?

In this case, I will host the final version of the database on XojoCloud, but I am also interested in knowing how to implement a secure connection on a separate server (mysql).

Only enabling SSL is NOT sufficient, you need to specify the certs to make it secure. Not sure about mySQL as I stopped using it long ago, but I would say that only enabling but not specifying the certs will in the “best” case properly fail and cause an error, in the “worst” case it might be that it is just falling back to a standard connection, w/o a beep. Worst case, because you might feel secure, but you are not.

Generally speaking it is usually better not to expose your database “outside” of the localhost at first place. For testing purposes I would open an SSL Tunnel to your Xojo Cloud server. so that Xojo Cloud databases will be seen in the debugger as any other local instance, so you don’t need to open any ports even not for debugging on production and no need for certificates.

For connections between two server on the Internet (not talking about a local network) it is, in my opinion, best to develop some APIs for full (or better saying maximum) control) still without exposing your whole database to the bad, dark internet. Xojo Cloud Server has implemented a lot of measures to protect you, but exposing a database to the internet is something to still be very careful about Of course it all depends on the content in your database. Grandma’s best apple pie recipes versus credit card record of your customer etc. :wink:

1 Like

When your application that is hosted on Xojocloud is talking to the MySQL database on the same server you are actually behind a firewall and communicating via a local interface so your xojocloud MySQL server is not visible to the internet world. They do allow you to setup an SSL tunnel so you can connect from your local machine to your Xojocloud MySQL server. I would contact @Paul_Lefebvre about current instructions on how to set these things up since I cannot seem to locate the videos that I used when we got started.

I recommend what @Jeannot_Muller suggested above.

Information on setting up a tunnel to MySQL on Xojo Cloud is here:

https://documentation.xojo.com/topics/xojo_cloud/introduction_to_xojo_cloud.html_Control_Panel

2 Likes

Thank you all, I understand it.

1 Like

With input from Greg O’Lone (I think?) I build the following a long while ago to automate the process of building an SSH tunnel while debugging. Perhaps it might help you too:

1 Like

Thanks Jeannot, automating the SSH tunnel saves time and effort.

My intention was to work with two mirror databases, one on xojocloud for production and other on an external server for testing. Depending on the url of the session, I connect with one or the other.

That should be possible by just adapting the script accordingly. And you could even SSH between the 2 servers. All better than opening database ports. In the latter case I would probably use a VPN between both servers though. Again depending if we are talking about grandma’s recipes or more sensitive data :wink:

I am usually just mirroring the databases on my local machine. It is quite easy to install postgres on macOS for instance and via SSH you can easily pull or push files, for instance a database dump.

My SSH script is meanwhile far more sophisticated. I usually use a global property to set to which server/database I want to connect before starting to debug.