How to securely use a secondary database with JDatabase? Separate Host, DB, Username & Password? Topic is solved

[TruthSword]

I am learning how to do CRUD with Joomla & Modules using these two links:

https://docs.joomla.org/J4.x:Inserting_ ... _JDatabase

https://docs.joomla.org/J4.x:Selecting_ ... _JDatabase

How to securely use a secondary database with JDatabase? Separate Host, DB, Username & Password?

Does anyone have a working example of how to achieve this?

Thank you all!

[TruthSword]

I think I found the solution on stackoverflow; please correct this if it's incorrect!
https://stackoverflow.com/questions/35 ... -jdatabase

Answered by Lodder; Question asked by Dhaval Koradiya:

Code: Select all

You can add your own array of options to JDatabaseDriver, like so:
First database:
$db = JFactory::getDbo();

Code: Select all

Second database:

$option2 = array();
$option2['driver']   = 'mysqli';     // Database driver name
$option2['host']     = 'localhost';  // Database host name
$option2['user']     = 'DB_USER';    // User for database authentication
$option2['password'] = 'DB_PASS';    // Password for database authentication
$option2['database'] = 'DB_NAME';    // Database name
$option2['prefix']   = 'jos_';       // Database prefix (may be empty)

$db2 = JDatabaseDriver::getInstance($option2);

Code: Select all

Third database:

$option3 = array();
$option3['driver']   = 'mysqli';     // Database driver name
$option3['host']     = 'localhost';  // Database host name
$option3['user']     = 'DB_USER';    // User for database authentication
$option3['password'] = 'DB_PASS';    // Password for database authentication
$option3['database'] = 'DB_NAME';    // Database name
$option3['prefix']   = 'jos_';       // Database prefix (may be empty)

$db3 = JDatabaseDriver::getInstance($option3);

I hope this is compatible with Joomla 4x

[Webdongle]

Are you wanting to display data from an external database into Joomla's content area?

[TruthSword]

Are you wanting to display data from an external database into Joomla's content area?

Webdongle,

Exactly! I want it to go into article areas and modules.

[Webdongle]

https://extensions.joomla.org/extension ... base-lite/

[TruthSword]

https://extensions.joomla.org/extension ... base-lite/

Webdongle,

I am trying to learn. I have no intention of purchasing any 3rd party software/extensions. I'm learning so I can develop my extensions.

I thought this forum was for 4x Coding; I didn't know it was strictly for marketing 3rd party extensions?

Perhaps I am missing something.

[Webdongle]

It is free so you can download it without purchasing it. Then you can examine the code. It is for J3 and J4.

Is that what you missed

[TruthSword]

It is free so you can download it without purchasing it. Then you can examine the code. It is for J3 and J4.

Is that what you missed

Webdongle,

Oh okay. I appreciate it then. Maybe someone will join this thread who also is learning how to develop using Joomla 4x and MySQL/MariaDB.

Thank you for your time!

[gws]

Worth a read while you learn J4 development. https://blog.astrid-guenther.de/en/der- ... iterungen/

[TruthSword]

Worth a read while you learn J4 development. https://blog.astrid-guenther.de/en/der- ... iterungen/

gws,

Much appreciate it, Sir! Opened, Saved and ready for Reading!

[toivo]

How to securely use a secondary database with JDatabase? Separate Host, DB, Username & Password?

It is possible to use an instance of the DatabaseDriver class with separate parameters from the Options tab of the module to connect to a second database. Here is an example of the code that creates the connection and a working front end module that displays the number of users from the current database and a second database. Tested in Joomla 4.1.5 and 4.2.0-dev from Nightly Builds.

Code: Select all

<?php
/**
 * get an object of DatabaseDriver for a specific database
 * NB. this function does not support encrypted connection
 * @param $host (string) REQUIRED - the database host
 * @param $user (string) REQUIRED - the database user
 * @param $password (string) REQUIRED - the database password
 * @param $database (string) REQUIRED - the database name
 * @param $prefix (string) REQUIRED - the database table prefix
 */
defined('_JEXEC') or die;
use Joomla\Database\DatabaseDriver;

class ModDatabaseTestDatabase2
{
	public static function getConnection($host,$user,$password,$database,$prefix) 
	{
		$option = array();
		$option['driver'] 	= 'mysqli'; // Database driver name
		$option['host'] 	= $host; // Database host name
		$option['user'] 	= $user; // User for database authentication
		$option['password'] = $password; // Password for database authentication
		$option['database'] = $database; // Database name
		$option['prefix'] 	= $prefix; // Database prefix - includes underscore (abc_)

		try
		{
			$db = DatabaseDriver::getInstance($option);
		}
		catch (RuntimeException $e)
		{
			if (!headers_sent())
			{
				header('HTTP/1.1 500 Internal Server Error');
			}
			jexit('Database Error: ' . $e->getMessage());			
		}

		return $db;
	}
}

[TruthSword]

toivo,

Thank you very much for this share! What needs to be in the second database for this module to correctly function? Do you have a .sql I could study & test?

Much obliged! This is exactly what I was looking for. A working example of this caliber.

How to securely use a secondary database with JDatabase? Separate Host, DB, Username & Password?

It is possible to use an instance of the DatabaseDriver class with separate parameters from the Options tab of the module to connect to a second database. Here is an example of the code that creates the connection and a working front end module that displays the number of users from the current database and a second database. Tested in Joomla 4.1.5 and 4.2.0-dev from Nightly Builds.

Code: Select all

<?php
/**
 * get an object of DatabaseDriver for a specific database
 * NB. this function does not support encrypted connection
 * @param $host (string) REQUIRED - the database host
 * @param $user (string) REQUIRED - the database user
 * @param $password (string) REQUIRED - the database password
 * @param $database (string) REQUIRED - the database name
 * @param $prefix (string) REQUIRED - the database table prefix
 */
defined('_JEXEC') or die;
use Joomla\Database\DatabaseDriver;

class ModDatabaseTestDatabase2
{
	public static function getConnection($host,$user,$password,$database,$prefix) 
	{
		$option = array();
		$option['driver'] 	= 'mysqli'; // Database driver name
		$option['host'] 	= $host; // Database host name
		$option['user'] 	= $user; // User for database authentication
		$option['password'] = $password; // Password for database authentication
		$option['database'] = $database; // Database name
		$option['prefix'] 	= $prefix; // Database prefix - includes underscore (abc_)

		try
		{
			$db = DatabaseDriver::getInstance($option);
		}
		catch (RuntimeException $e)
		{
			if (!headers_sent())
			{
				header('HTTP/1.1 500 Internal Server Error');
			}
			jexit('Database Error: ' . $e->getMessage());			
		}

		return $db;
	}
}

[toivo]

What needs to be in the second database for this module to correctly function?

All you need is a second Joomla 4 site with a users table. The SQL statement is in the function getOnlineCount() of the file helper.php.

[TruthSword]

What needs to be in the second database for this module to correctly function?

All you need is a second Joomla 4 site with a users table. The SQL statement is in the function getOnlineCount() of the file helper.php.

toivo,

Oh, I see what you mean! 10-4. Appreciate this!

[SharkyKZ]

Some notes before you start using the posted code.

1) Joomla\Database\DatabaseDriver::getInstance() is deprecated and will be removed in 5.0. You might want to switch to Joomla\Database\DatabaseFactory::getDriver() now. Main difference is that it doesn't cache the driver instance so it's something you'll have to handle yourself.
2) J4 supports data in transit encryption which is essential when connecting to external databases. If the remote database supports this, you should configure the connection to use it.
3) Storing plaintext passwords in database is really insecure. If storing in a configuration file is not possible, it should at least be encrypted. The module form should also be altered to not leak the password.
4) When connection in a module fails you probably don't want to crash the entire site with a WSOD. The jexit() code makes sense for the main Joomla connection since even the error page requires a working database. But in all other cases you can either continue without a connection or render the regular error page.