Using TLS when connecting to MySQL server

[MonkeybreadSoftware]

If you like to connect to MySQL or MariaDB from FileMaker with MBS FileMaker Plugin, you can set SSL options to connect through an encrypted connection. The options are named SSL, but nowadays it's all TLS v1.2 or newer.

 

Here is a sample script we build with a client to get this FileMaker solution to connect to a MySQL server to download data, e.g. from a web shop.

 

# Query parameters for connection

Set Variable [ $$SQL.DB ; Value: Test SSL::_DBName ] 

Set Variable [ $$SQL.Server ; Value: Test SSL::_Host ] 

Set Variable [ $$SQL.Account ; Value: Test SSL::_User ] 

Set Variable [ $$SQL.Pwd ; Value: Test SSL::_Passwort ] 

# 

# Start new connection

Set Variable [ $$Connection ; Value: MBS("SQL.NewConnection") ] 

# Query path to folder with database

Set Variable [ $appPath ; Value: MBS( "Path.RemoveLastPathComponent"; MBS( "Path.FileMakerPathToNativePath"; Get( FilePath ) )) ] 

# Build pass for mysql library in same folder as database

Set Variable [ $libPath ; Value: MBS( "Path.AddPathComponent"; $appPath ; Case( Abs( Get( SystemPlatform )) = 1 ; Test SSL::_Library_MAC ; Test SSL::_Library_WIN )) ] 

# Tell plugin where to find library

Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL.LIBS"; $libPath) ] 

# And SSL-Parameter

Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_SSL_CIPHER"; "DHE-RSA-AES256-SHA") ] 

Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_OPT_SSL_MODE"; "SSL_MODE_REQUIRED") ] 

Set Variable [ $sslPath ; Value: MBS( "Path.AddPathComponent"; $appPath ; "cacert.pem") ] 

Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_SSL_CA"; $sslPath) ] 

# Now connect it

Set Variable [ $result ; Value: MBS("SQL.SetClient"; $$Connection; "MySQL") ] 

Set Variable [ $DB ; Value: $$SQL.Server & "@" & $$SQL.DB ] 

Set Variable [ $result ; Value: MBS("SQL.Connect"; $$Connection; $db; $$SQL.Account; $$SQL.Pwd; "MySQL") ] 

# check result

If [ $result = "OK" ] 

# use $$Connection in calling script

Exit Script [ Text Result: "OK" ] 

Else

# cleanup and return error

Set Variable [ $error ; Value: $result ] 

Set Variable [ $result ; Value: MBS("SQL.FreeConnection"; $$Connection) ] 

Set Variable [ $$Connection ; Value: "" ] 

Exit Script [ Text Result: $error ] 

End If

 

If you have trouble or questions, please don't hesitate to contact us. If you need a cacert.pem file, you can go to the CURL website. As you see we pick the mysql library name based on the platform. While you can download MySQL or MariaDB servers and client software, we provide a convenient library download on our website: Libraries. The MySQL 8 files there include SSL libraries, so use the MySQL client libraries when you want to connect via SSL.