12

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?

Improve this question
2
  • can i input this as a value for area_code or student_id? "null or 1=1; drop table users; --" Make sure that inputs are 'sanitised' before bobby tables gets his hands on your page. Commented May 30, 2017 at 23:56
  • @thenaglecode they're all sanitized well, I just posted part of the code (: but thanks for the concern though! Commented May 31, 2017 at 12:27

3 Answers 3

Reset to default
10
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Didn't really get your sum up part. Anyway, this method is gonna be used around hundreds of different websites - Each of them has a different theme and specific own customizations. So a plugin isn't quite a solution in this case.. it'd require a lot of time and work to cover all these cases.
This isn't the most elegant answer, but it's 100 correct. If the question is how do I access an external DB, the answer is via an API. The OP's code sample is gibberish, so this is a great answer!
@GonrasKarols - has a different theme and specific own customizations - this is why it should be a plugin; plugin shares a common logic in abstract way - when you fix a bug, you just upgrade a plugin, not replace an each instance of code.
@eRIZ What do you mean by "a plugin that would be an API for your theme" - Does my custom-page.php template need to make API calls to the plugin or the opposite way? The plugin will deliver database information into the right sections in custom-page.php? I feel like I'm missing something..
Your plugin provides - for example - a method called my_vendor_get_poll_data returning an array. You use this function within your theme and only wrap data there. Leave any processing to your plugin.
|
6

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.

Improve this answer

6 Comments

This is a common misconception amoungst beginner WP devs. If you are adding functionality, it should go in a plugin, not a theme. If you are modifying the "look" of a site, it should go in your theme's functions.php . This type of thing absolutely should not be put in a theme.
@JimMaguire If you build functionality that related to a specific theme, why not put it inside the theme functions.php? A theme in wordpress is a lot more then just a "theme" that defines the look of the site. (At least this is what it turned to be in the last 2-3 years)
It's hard to think of an example where what you're suggesting makes sense. Yes.. of course theme sellers put functionality in a theme, they want to lock you in and sell you something. I'd ask you why put it in a theme? It limits your options and gains nothing.
Re-reading it, I have to side with Jim on this. I was commenting on separating class declarations and their methods away from the template files, and wasn't direct enough in my comments on OP's database connection bit. The approach is an error, and should be redesigned. My "the theme could leverage an external api" should have "via a plugin" appended to it. Whatever functionality the theme may require should be relegated to the delivering of the template files. If OP is trying to create & manage a Server->Client relationship via a theme, he is asking for nothing but headaches.
I wasn't talking about managing server-client relationships via a theme - I just need a few simple get_results() statements that would link to an external db and the results would affect the page's content. Nothing too complicated and only a one-way communication is required (from db into the template). So what you are saying is that I should build a plugin which handles the db connection and the queries, and sends out data to the template itself?
|
0

There is also more radical way to work with multiple DBs in Wordpress. As follows from wpdb Codex page:

The $wpdb object can talk to any number of tables, but only to one database at a time; by default the WordPress database. In the rare case you need to connect to another database, you will need to instantiate your own object from the wpdb class with your own database connection information. For extremely complicated setups with many databases, consider using hyperdb instead.

Hyperdb is a plugin that helps with multiple DBs and replaces your overthinking about code organization.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.