LC Server and SSL connection to MySQL database

[giovanni_c]

Hello everyone,
I am trying to connect to a local MySQL database through a LC script.
The connection is made properly with the following function call:

put revOpenDatabase("mysql", (tDataHost & ":" & tDataPort), tDataSet, tUsername, tPassword,,,288000) into dbID

but I need to connect to the server via SSL and if I set to true the "useSSL" parameter (as per documentation) I get an internal server error from the webserver and I am not able to understand why.
Is not the SSL connection to MySQL allowed in livecode server scripts?
FYI I tried both on a Windows and MacOS and the function call should be the following:

put revOpenDatabase("mysql", (tDataHost & ":" & tDataPort), tDataSet, tUsername, tPassword, true,,288000) into dbID

Is there something wrong in it?
Is there a workaround to make it working?
Thank you in advance

[FourthWorld]

Are both your LC Server and MySQL server behind the same firewall?

[stam]

Is there a workaround to make it working?
Thank you in advance

Could it be an issue with the port used?

[giovanni_c]

Hello everyone and thanks for the feedback.
LC server and MySQL server are on the same workstation, so the firewall should not be a problem; it's not a production environment but it's only (at the moment) for developing and testing purposes.
The MySQL server uses the standard 3306 port and there are no problems in connecting with other applications or with Livecode app.
I need to setup the SSL connection to the DBMS because the prod environment will not accept unencrypted connections.

It's quite strange because, as I said, I am experiencing this both on Windows and MacOS environment.

Is this a bug that must be filed in the QA center ? (I already had a look at it and there are no mentions about this issue)
Is it working for you? Have you ever tried to connect to a MySQL DB via SSL with Livecode Server?

Unfortunately I am not able to find any error in the Apache log file and it seems that there is no way to diagnose the problem (at least, I was not able to do it).

[giovanni_c]

FYI and to keep it as simpler as possible, here follow a code sample of the connection that I'm trying to setup:

Code: Select all

<?lc
put "test" into dbUsername
put "test" into dbPassword
put "information_schema" into dbName

# The following is working properly
put false into SSLConnection

# The following is causing the internal server error
#put true into SSLConnection

put revOpenDatabase("mysql", "127.0.0.1", dbName, dbUsername, dbPassword, SSLConnection,,28800, true) into dbConnId

put "<p>Connected to database</p><p>DB Connection ID:" && dbConnId & "</p>"

revCloseDatabase dbConnId
?>

Uncommenting the line "put false into SSLConnection" causes the internal server error; it cannot be caught and no errors are fired in the apache logs.
Thanks again for your help and hints.

[mwieder]

Interesting. Does the server product support the SSL Encryption library?
When building a standalone application you need to explicitly select it for inclusion.

[giovanni_c]

It's what I am asking here!
I think that the server supports the SSL encryption library, as other encryption functions that should be related to it are working.
Is there someone else that can confirm what I am writing?
I think I should file a bug on the QA center to get some feedback from the devs.

Thank you again

[FourthWorld]

It's so unusual to use MySQL exposed to the open Internet that its ability to accept connections remotely is disabled by default.

I wonder if there's a similar need to change that MySQL config to allow SSL, normally not needed when the DB is only accessed from a local process.

[mwieder]

Possibly, but in this case it looks like the MySQL database itself isn't exposed to the internet but is on a separate backend server.
I see somewhat conflicting information on these posts:

https://scalegrid.io/blog/configuring-a ... ql-server/
https://docs.cpanel.net/knowledge-base/ ... nnections/

Maybe issuing the following command would help

Code: Select all

mysql> SHOW VARIABLES LIKE 'have_ssl';

If the value of the "have_ssl" variable is YES then the MySQL server supports encrypted connections.

[SparkOut]

If it's a separate backend server then the connection to IP 127.0.0.1 would always fail, yet the OP mentions that it works if the SSL parameter is false.
What if the hostname is specifically used, rather than 127.0.0.1, or "localhost"?

[giovanni_c]

I think that the main focus of the problem is not fully clear.
The problem is that the SSL connection to the DB from a LC server script is causing the internal server error.
Trying to connect to the same server with an application built in Livecode that uses the same code that I pasted above is properly working (you can easily try this).
The MySQL server is accepting the connection via SSL without problems (that means that the "SHOW VARIABLES LIKE 'have_ssl';" returns YES, as expected) so the problem is not on the MySQL server side nor in the Livecode app side, but it's on the Livecode server side.
I tried both "127.0.0.1" and "localhost" as MySQL server address with the same result.

[mwieder]

Again, does the mySql server itself accept encrypted connections?
Without LiveCode... from a command prompt try

Code: Select all

mysql> SHOW VARIABLES LIKE 'have_ssl';

[giovanni_c]

Hi mwieder, I already answered to this question but yes. It works properly from any MySQL client (command line, workbench, sequelpro, using PHP, Java etc.)
As already said, it's not a problem on MySQL side...

[giovanni_c]

I can add that this sounds to have never worked...
I went back to lc-server 8.0.0 and the SSL connection to the MySQL server doesn't work either even with a different behavior: I don't have the "Internal server error" from the webserver but the execution stops at the "revOpenDatabase" without any notice; even the "try... catch" statement is not able to catch any error, simply it stops the execution.

It seems that the SSL connection to MySQL server has never worked (at least from the lc8 on)

I filed a bug report for this; waiting for a feedback from the devs

[mwieder]

Ah. Yes, I missed that part.
What about copying the revsecurity library from the IDE over to the server?