Set up a secure connection to mysql

Targets Web
1

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

2

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.

3

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?

4

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).

5

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
6

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.

7

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
8

Thank you all, I understand it.

1 Like
9

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
10

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.

11

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.

12

This link is break :face_holding_back_tears:

13

this link is break :face_holding_back_tears:

14

This can help (and it’s up to date)

https://documentation.xojo.com/topics/xojo_cloud/the_xojo_cloud_control_panel.html#topics-xojo-cloud-the-xojo-cloud-control-panel-setting-up-a-tunnel

15

Page not Found error, seeing that a lot of people struggle with this issue , it would be nice if XOJO would do a detailed project on connecting to a MySQL server using Certificates

16

this as well comes with the message “The author deleted this Medium story.”

17

Usually on Linux side it is enough to add on the client.config of the DB server the following :

ssl-ca = /path to/ca-cert.pem
ssl-cert=/path to/clientCert.pem
ssl-key=/path to/clientKey.pem

then with any other app , including go, php, python you just simply connect and it works but apparently not with XOJO .

I guess i asked many times and in many places but no answer so far, so i guess either they don’t know what to reply or don’t want to clarify this issue.

Usually what is provided in many cases are the Client Certificate db.SSLCertificate ; client Key db.SSLKey ; SSL CA which i assume it is db.SSLAuthority, now can someone could clarify for everyone what in the world you would put in db.SSLAuthorityFolder and what is the format of that data in this folder ? as i see it first time here for XOJO only where it requires that much config to connect securely to a Mysql server which should be simple and as standard considering the current security standards .

Thanks in advance for clarifying .

18

Xojo chose to only allow databases to be connected to from localhost. That allows for easier connection from your apps and removes the possibility risk of a bad actor connecting from the outside world and exploiting a vulnerability in the database engine itself. The way you connect securely to the database is through an ssh tunnel.

Now yes, Xojo could (and should) update that to use rsa keys, but the mechanism is quite secure already.

19

while this is true, you don’t always control the DB end so in my case i do need to work what the customer provides, in this case the client certs, the CA cert and the remote ip and port, so how can i explain this to the customer ? sorry, XOJO cannot do that , not nice i guess .I’ll see if they can provide a tunnel with regular connection but this will be declined for sure as that means allowing additional setup and access to the server and so on while the current setup they just generate the client certs when needed and done.

Thanks Greg.

20

Your customer uses Xojo Cloud? I think Greg is talking about Xojo Cloud.