XOJO Linux to Mariadb with SSL Enabled

Databases
1

Hello,

Can someone that did this work guide me please on having this to work .

Apparently running

mysql -u user -p -h xxx.xxx.xxx.xxx --ssl

as command on the terminal it works straight while if I use the code in XOJO it does not work at all .

I did tried using

Var m1DB As New MySQLCommunityServer

m1DB.Host = “xxx.xxx.xxx.xxx”
m1DB.UserName = “user”
m1DB.Password = “password”
m1DB.SSLEnabled = True
m1DB.DatabaseName = “information_schema”

Var ca As FolderItem
Var cc As FolderItem
Var ck Aș FolderItem

ca = New FolderItem(“/etc/mysql/ssl/ca.pem”,FolderItem.PathModes.Shell)
cc = New FolderItem(“/etc/mysql/ssl/fs1-cert.pem”,FolderItem.PathModes.Shell)
ck = New FolderItem(“/etc/mysql/ssl/fs1-key.pem”,FolderItem.PathModes.Shell)

If ca.Exists Then
stdout.WriteLine("ca : " + ca.ShellPath)
End If

If cc.Exists Then
stdout.WriteLine("cc : " + cc.ShellPath)
End If

If ck.Exists Then
stdout.WriteLine("ck : " + ck.ShellPath)
End If

m1DB.SSLKey = ck
m1DB.SSLCertificate = cc
m1DB.SSLAuthority = ca

Var cipher As String
cipher = “DHE-RSA-AES256-SHA”
m1DB.SSLCipher = cipher

Try
m1DB.Connect

stdout.WriteLine("Connected ! ")

Catch e As DatabaseException
stdout.WriteLine("Error : " + e.Message)

End Try

m1DB.Close

With no luck, as error from XOJO side I get

ca : /etc/mysql/ssl/ca.pem

cc : /etc/mysql/ssl/fs1-cert.pem

ck : /etc/mysql/ssl/fs1-key.pem

Error : SSL connection error: error:00000001:lib(0):func(0):reason(1)

and on the server side I get :

[Warning] Aborted connection 287252 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘xxx.xxx.xxx.xxx’ (This connection closed normally without authentication)

Looking all over the forums mostly this is what it is used so no idea why it does not work .

OS : Debian 11.4
XOJO : 2022R2
Database : MariaDB - 10.8.4

Thanks

2

Could you quote code with </> instead of the double-quote button, then we get:

If ca.Exists Then
  stdout.WriteLine("ca : " + ca.ShellPath)
End If

instead which is much more readable - thanks.

Does the MariaDB/MySQL setup require a config file on the server side to allow your host and user to connect? Is that all in place?

3

Hello Tim,

All in place, we can connect from terminal, we can connect from php, java, only Xojo has this issue and unfortunately not to much error output from XOJO side except that weird string.

4

As a note , when using this

mysql -u user -p -h xxx.xxx.xxx.xxx --ssl

I get this on MariaDB side

MariaDB [(none)]> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
5

I’ve noticed two differences between your connection and the example in the documentation.

  1. You don’t specify a SSLAuthorityDirectory
  2. You don’t specify a SSLCipher

Does adding these properties kick things into gear? The old docs example code has comments the new docs do not, you may wish to refer to the older ones for now.

6

Hello Tim,

On my code post you do have ciphers setup but useless I guess

Var cipher As String
cipher = “DHE-RSA-AES256-SHA”
m1DB.SSLCipher = cipher

As for the SSLAuthorityDirectory is that mandatory ? and what should be there ? normally you get the CA and the user cert and key, what should I put in that folder ?

Thanks

7

Oops! Quite right, I’m not sure how I missed that.

The name of the files in the documentation example code are a bit obtuse, so I’m not quite sure. The names seem to imply the SSLAuthority is a property for MySQL to use and SSLAuthorityDirectory is a property for the SSL connection to use, so perhaps?

UPDATE: I found someone else has tried explaining these properties previously, try the information available in this thread: Setting up SSL in MySQL - #2 by Justin_Elliott

8

Well I did saw your post and did tried all those as well but no luck , as said I tried many options, even now python and it works, unfortunately XOJO does not work so I assume another mess on XOJO side as usual as I don’t see any other cause for this.

I guess I will do the app in another language as it will spare me a lot of time rather than going crazy here with debugging XOJO without any proper logs to see what Is the issue.

Thanks .

9

It doesn’t work because MariaDB has disabled the TLSv1.0 connection by default on the server side, and xojo tries to use that, but fails. There is an open feedback case for this 69197

10

well unfortunately I don’t have one more year to wait until this problem would be addressed, so far I find XOJO use for hobby case and private projects , for something comercial you need either a lot of plugins or something else , unfortunately cannot be used on modern platforms. To bad as It has potential.

11

In theory it is marked as milestone 2022r3. Maybe add a thumbs up on the feedback case. Try changing the server-side configuration of mariadb (tls_version = TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3) to verify that the problem is the same as the feedback case.

12

You haven’t specified a connection type, I’m not sure what it defaults to without. I do this in my constructor:

me.SSLConnectionType = SSLSocket.SSLConnectionTypes.SSLv23     // This will start at TLSv12 and work down.

.
See if that helps. The comment is correct, BTW - see the doc.

13

what is “me.”? On MySQLCommunityServer there is no SSLConnectionType property .

14

Hmmm strange, I see that the MySQLCommunityServer indeed has no such property. Odd.

Further, I see that the doc for SSLConnectionType no longer mentions the useful SSLv23. I wonder why that is.

1 Like
15

From what I remember no (xojo’s) database plugins have that property. In the case of feedback in fact I ask to add it but I don’t know if it will be implemented.

16

I use it for SSL sockets.

17

yes okay but they are two different things

18

@Xojo maybe someone that could calrify this , so far after 1 year still same issue and nobody could tell why and how this works .

Thanks

So far using the code above on MariaDB 10.8 i get this

Server error : SSL connection error: error:00000001:lib(0):func(0):reason(1)

If i don’t put the mDB.SSLAuthorityFolder i get this

SSL connection error: SSL_CTX_set_default_verify_paths failed

But so far still does not work
Thanks