Accessing external database using the wpdb class in WordPress

Question

I have a custom page template in WordPress that is relying on an external database, and which is using the wpdb class for this purpose.

This is my code:

<article id="post-<?php the_ID(); ?>" <?php post_class(); ?>>
    <header class="entry-header">
        <?php the_title( '<h1 class="entry-title">', '</h1>' ); ?>
    </header><!-- .entry-header -->

<?php

class StudentsDatabase
{
    private $db;
    public function __construct() {
        try {
            $this->db = new wpdb(DB_USER, DB_PASSWORD, 'students_db', DB_HOST);
            $this->db->show_errors();
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }
    public function getStudentById($student_id)
    {
        return $this->db->get_results("SELECT * FROM `students` WHERE id=$student_id");
    }
    public function getSchoolByAreaCode($area_code)
    {
        return $this->db->get_results("SELECT * FROM `schools` WHERE area_code=$area_code;--");
    }

}
$Students_DB = new StudentsDatabase();
$student_one = $Students_DB->getStudentById(1);
$school_one = $Students_DB->getSchoolByAreaCode(1);

?>
<div class="entry-content">
    <?php

    //do something with $student_one and $school_one ...

    the_content();

    ?>
</div><!-- .entry-content -->

Well, I was wondering if this is the right way to do it. Security-wise or any 'other'-wise actually.

It feels kinda sketchy to make external db calls from within the page's template itself. Should I register these functions on some external file and then just use them inside the template?

Answer 1

I think the most "clean" way is to implement a plugin that would be an API for your theme. Of course, it depends if it's a theme only for your own purposes beause Wordpress (so far) lacks from an dependency manager.

To sum up - in the theme use then this API.

Answer 2

Put the class declarations, etc. in the functions.php file of the theme. Or, even better, require_once them there, and put them in an assets or includes folder of the theme.

-/theme/
   -/includes/classes/class-studentsDatabase.php
   -functions.php

In functions.php

define('TEMPLATE_PATH', get_template_directory());
require_once(TEMPLATE_PATH . '/includes/classes/class-studentsDatabase.php');

You can instantiate the class(es) for the theme as a whole, or as needed on the template page(s) as you're doing now.

---

As far as security goes, I would avoid putting DB connections that need to be secure within a theme that is going to be sent out into the wild.

I'm not sure I follow what you're doing to that end, but as presented, I would handle that bit outside of the theme environment.

Again, not knowing your use case, the theme could leverage an external api, and that api could be a wordpress wp-json api managing that DB connection at a central site.

That would allow the theme to GET / POST to an endpoint(s) that handle(s) authentication and any CRUD, and mitigate a lot of potential security issues. The theme on the external site would then just be parsing the returned json, and wouldn't have any DB access beyond that.