CSIM Technical Notes

When it comes to authentication with MySQL, it seems that PHP is pushing for an hardcore SSL authentication rather than a simple encryption of the connection. The documentation about simple encryption is scarce and overwhelmed by the documentation about SHA256 or X509 authentication.

This translates into Joomal and Horde offering only some authentication schemes that do not correspond to what I am looking for.

There is no configuration option neither in Joomal nor in Horde to enable simple SSL encryption of the connection, as provided by MySQL user's ssl_type="ANY".

Joomla and Horde have to be patched to work the way I want. The patches are listed bellow.

Patch for Joomla

This patch has to change the connection from mysqli_connect to mysqli_real_connect in order to accept the SSL options. This also mean that the connection has to be created with mysqli_initfirst.

The file to patch is /web/csim/htdocs/joomla3/libraries/joomla/database/driver/mysqli.php.

--- mysqli.php.orig	2021-09-08 13:45:56.087238000 +0700
+++ /web/csim/htdocs/joomla3/libraries/joomla/database/driver/mysqli.php	2021-09-08 14:04:00.270357000 +0700
@@ -86,7 +86,33 @@
 		// Finalize initialisation.
 		parent::__construct($options);
 	}
+	
+	/**	
+	* Adds SSL functionality to mysqli
+	*
+	* @return MySQL_Connection Returns mysqli connection if the database connected successfully.
+	* Based on https://github.com/joomla/joomla-cms/issues/27546
+	* @notice CUSTOM EDIT
+	*/
 
+    	public function ssl_mysqli_connect($host, $user, $password, $database, $port, $socket)
+	{
+	  $this->ssldberror = 'TRYING MYSQLI SSL CONNECTION';
+	  
+	  $sslconn=mysqli_init();
+	  
+	  if(!$sslconn){
+	    $this->ssldberror = "\n".'SSL DB ERROR ['.mysqli_errno($sslconn).']:'.mysqli_error($sslconn);
+	  }
+	  
+	  if($sslconn && !mysqli_real_connect($sslconn, $host, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT))
+	    {
+	      $this->ssldberror .= "\n".'SSL DB ERROR ['.mysqli_errno($sslconn).']:'.mysqli_error($sslconn);
+	    }
+	  
+	  return $sslconn;
+	}
+	
 	/**
 	 * Connects to the database if needed.
 	 *
@@ -163,14 +189,16 @@
 			throw new JDatabaseExceptionUnsupported('The MySQLi extension for PHP is not installed or enabled.');
 		}
 
-		$this->connection = @mysqli_connect(
-			$this->options['host'], $this->options['user'], $this->options['password'], null, $this->options['port'], $this->options['socket']
+		$this->connection = $this->ssl_mysqli_connect(
+			$this->options['host'], $this->options['user'], $this->options['password'],
+			$this->options['database'], $this->options['port'], $this->options['socket']
 		);
 
 		// Attempt to connect to the server.
 		if (!$this->connection)
 		{
-			throw new JDatabaseExceptionConnecting('Could not connect to MySQL server.');
+			throw new JDatabaseExceptionConnecting('Could not connect to MySQL server.'."\n".
+			$this->ssldberror);
 		}
 
 		// Set sql_mode to non_strict mode

Patch for Horde

This patch is more simple, as it only needs to add the proper SSL options. The file to patch is /usr/local/share/pear/Horde/Db/Adapter/Pdo/Base.php.

--- Base.php~	2021-01-16 00:06:57.292776000 +0700
+++ /usr/local/share/pear/Horde/Db/Adapter/Pdo/Base.php	2021-09-08 16:23:54.040976000 +0700
@@ -39,7 +39,12 @@
         list($dsn, $user, $pass) = $this->_parseConfig();
 
         try {
-            $pdo = @new PDO($dsn, $user, $pass);
+	    $options = array(
+	                 PDO::MYSQL_ATTR_SSL_CA => true,
+			 PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
+			);
+							   
+            $pdo = @new PDO($dsn, $user, $pass, $options);
         } catch (PDOException $e) {
             $msg = 'Could not instantiate PDO. PDOException: '
                 . $e->getMessage();