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

For Joomla! 4.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 12:54 am

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!
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 1:31 am

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
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 41705
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Sat Jun 25, 2022 2:29 am

Are you wanting to display data from an external database into Joomla's content area?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"When I'm right no one remembers but when I'm wrong no one forgets".

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 2:32 am

Webdongle wrote:
Sat Jun 25, 2022 2:29 am
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.
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS


TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 2:49 am

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.
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 41705
Joined: Sat Apr 05, 2008 9:58 pm

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

Post by Webdongle » Sat Jun 25, 2022 2:53 am

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 ???
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"When I'm right no one remembers but when I'm wrong no one forgets".

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 3:32 am

Webdongle wrote:
Sat Jun 25, 2022 2:53 am
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!
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

gws
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4754
Joined: Tue Aug 23, 2005 1:56 pm
Location: South coast, UK
Contact:

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

Post by gws » Sat Jun 25, 2022 8:24 am

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

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sat Jun 25, 2022 10:17 pm

gws wrote:
Sat Jun 25, 2022 8:24 am
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! :)
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 14775
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

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

Post by toivo » Sun Jun 26, 2022 7:07 am

TruthSword wrote:
Sat Jun 25, 2022 12:54 am
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;
	}
}
You do not have the required permissions to view the files attached to this post.
Toivo Talikka, Global Moderator

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sun Jun 26, 2022 6:13 pm

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.


toivo wrote:
Sun Jun 26, 2022 7:07 am
TruthSword wrote:
Sat Jun 25, 2022 12:54 am
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;
	}
}
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 14775
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

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

Post by toivo » Sun Jun 26, 2022 10:20 pm

TruthSword wrote:
Sun Jun 26, 2022 6:13 pm
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 Talikka, Global Moderator

TruthSword
Joomla! Intern
Joomla! Intern
Posts: 70
Joined: Sat Feb 15, 2020 12:48 am

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

Post by TruthSword » Sun Jun 26, 2022 11:01 pm

toivo wrote:
Sun Jun 26, 2022 10:20 pm
TruthSword wrote:
Sun Jun 26, 2022 6:13 pm
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!
Best Regards,

Mr. Brandon Kastning
Washington State, USA
Glory to Constitutional American Republic Forever! ;)
#SICSEMPERTYRANNIS

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2238
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

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

Post by SharkyKZ » Mon Jun 27, 2022 6:05 am

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.


Post Reply

Return to “Joomla! 4.x Coding”