[#27546] - mysqli ssl connection
- Closed
- 16 Jan 2020
- Medium
- Build: staging
- # 27546
I need joomla to connect to my mysql db via an SSL connection. I wasn't able to find where this was possible with existing extensions or framework code.
Steps to reproduce the issue
- Setup a MySQL database with SSL connections required.
- Begin to install a new Joomla site using mysqli option for database connection to the above db.
Expected result
- SSL would be detected and the database would work as expected through installation and production usage.
Actual result
- Standard "Database Connection Error".
System information (as much as possible)
- Latest joomla stable version as of January 15th 2020
- MySQL 5.7
- Separate VMs, both in azure.
Additional comments
I have crafted a work around and sharing it here for comment.
file: /var/www/website.com/libraries/joomla/database/driver/mysqli.php
line: 118
`/**
* Adds SQL functionality to mysqli
*
* @return MySQL_Connection Returns mysqli connection if the database connected successfully.
*
* @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_ssl_set($sslconn, NULL, NULL, '/var/www/JOOMLAMYSQLISSLDB_TrustRoot.crt.pem', NULL, NULL))
{
$this->ssldberror .= "\n".'SSL DB ERROR ['.mysqli_errno($sslconn).']:'.mysqli_error($sslconn);
}
if($sslconn && !mysqli_real_connect($sslconn, $host, $user, $password, $database, $port))
{
$this->ssldberror .= "\n".'SSL DB ERROR ['.mysqli_errno($sslconn).']:'.mysqli_error($sslconn);
}
return $sslconn;
}`
then on line (new after code above added): 196
$this->connection = $this->ssl_mysqli_connect( $this->options['host'], $this->options['user'], $this->options['password'], $this->options['database'], $this->options['port'], $this->options['socket'] );
then on line (new after code above added): 204
if (!$this->connection) { throw new JDatabaseExceptionConnecting('Could not connect to MySQL server.'."\n".$this->ssldberror); }
Votes
joomla-cms-bot
- | Labels |
Added:
?
|
||
A first quick reply: We won’t add new functionality to Joomla 3. In Joomla 4 it will be possible to set the SSL parameters at installation and also later in backend. Current 4.0-dev code has already all implemented. The code of the database drivers has also been extended by SSL support in the framework database package in the 2.0-dev branch. Can post links to that later tonight or tomorrow or latest on weekend when I have more time. Maybe it is possible for J3 to configure all what is necessary for SSL in the client configuration file (MySQLi or PDO, whatever used), i.e. outside of Joomla. I‘ll check that later if nobody else is faster.
Also for me checking the code providing above stay tuned until I have more time.
Thanks @richard67, I'm happy to hear it's in V4.
This work-around is working for me (didn't know about a client override config). I'll likely use this (re-updating the file on core upgrades) until V4 comes out or you know of a upgrade-safe way.
Cheers, and no rush!
Lance
Thanks for your workaround @lancedouglas1 I'm closing this because won't fix in j3 and is fixed in j4.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-01-16 09:05:31 |
| Closed_By | ⇒ | HLeithner |
lancedouglas1 first thanks for posting the work around. Sadly I cannot get mine to work. What is just above or below your line 118?
My file on line 118 looks like if (!empty($matches['port']))
{
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/27546.
@lancedouglas1 thank you for sharing the script I'm trying to set up Joomla as a Webapp on azure and this is very helpful ... did you run accross any issues with the version checker? I'm using MySql 8 and Azure MySql db which recommends enforce SSL
@richard67 you mentioned Client configuration file outside of joomla - what did you mean? were you thinking port forwarding or something?
Incase anyone else needs it, there are a few (small) updates to the script from @lancedouglas1 to get Joomla working in azure as a webapp with Azure MySQL (Not InApp MySQL)
@lancedouglas1 - updated so tries Non SSL first and then SSL Gist Here if anyone wants https://gist.github.com/kirancheema/590cc6d9cd81919cc741fe829740b155
:
Check you have the right certificates :
https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl gives you links to the downloads
If using PHP 7 you need to add MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT in the connection string.
E.g.
mysqli_real_connect($sslconn, $host, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);
If PHP <7 is having problems Try
mysqli_real_connect($sslconn, $host, $user, $password, $database, $port, MYSQLI_CLIENT_SSL);
tagging @richard67