Data Access Objects Pattern for Secure WordPress Development

Previously in this course, we explored Advanced Custom Database Tables to handle complex data storage. While those tables provide the storage layer, they often lead to "spaghetti code" if you execute $wpdb queries directly inside your controllers or service providers.

In this lesson, we introduce the Data Access Object (DAO) pattern—often implemented via the Repository pattern—to abstract database interactions. By centralizing SQL logic into dedicated classes, you shield your business logic from schema changes and ensure consistent security practices.

Why Use the DAO Pattern?

When you scatter global $wpdb; calls throughout your plugin, you create tight coupling. If you ever need to rename a table, change a column name, or switch to a different caching strategy, you are forced to hunt down every query in your codebase.

A DAO acts as a mediator. Your application logic requests data (e.g., "get the latest entry"), and the DAO handles the "how"—the SQL, the placeholders, and the result formatting.

Creating Your First Repository Class

Let's advance our Knowledge Base project by creating a KnowledgeEntryRepository. We will follow the Modern PHP Standards established earlier in this course.

First, define an interface to ensure your repositories remain interchangeable.

PHP
namespace KnowledgeBase\Repositories;

interface EntryRepositoryInterface {
    public function findById(int $id): ?array;
    public function create(array $data): int;
}

Now, implement the concrete class. We use Dependency Injection to pass the $wpdb instance, making the class easier to unit test later.

PHP
namespace KnowledgeBase\Repositories;

class KnowledgeEntryRepository implements EntryRepositoryInterface {
    private \wpdb $db;
    private string $table;

    public function __construct(\wpdb $db) {
        $this->db = $db;
        $this->table = $db->prefix . 'kb_entries';
    }

    public function findById(int $id): ?array {
        #6A9955">// Always use prepared statements for SQL security
        $query = $this->db->prepare(
            "SELECT * FROM {$this->table} WHERE id = %d LIMIT 1",
            $id
        );

        $result = $this->db->get_row($query, ARRAY_A);
        return $result ?: null;
    }

    public function create(array $data): int {
        $this->db->insert($this->table, [
            'title'   => sanitize_text_field($data['title']),
            'content' => wp_kses_post($data['content']),
        ], ['%s', '%s']);

        return (int) $this->db->insert_id;
    }
}

Key Principles for Implementation

1. Encapsulate SQL: No raw SQL should ever exist outside of your repository classes.
2. Type Safety: Use PHP type hints (int, array, ?array) to define clear contracts for your data.
3. Security by Default: Always use $wpdb->prepare() for dynamic values. While $wpdb->insert() handles escaping internally, manual SELECT or UPDATE queries must be sanitized via prepare().
4. Data Transfer: Consider returning objects or DTOs (Data Transfer Objects) rather than raw associative arrays to avoid "primitive obsession" in your business logic layer.

Hands-on Exercise

Refactor one of your existing admin pages that fetches data from the custom table we created in the Service Providers lesson.

1. Create a KnowledgeEntryRepository class.
2. Move the query logic into a findByCategory($category_id) method.
3. Inject the repository into your controller via the constructor.
4. Verify that the controller no longer references $wpdb directly.

Common Pitfalls

• Logic Leaking: Don't put business logic (like formatting dates or checking user permissions) inside the DAO. The DAO should only be responsible for persistence.
• Over-Engineering: For simple plugins, a full repository layer might be overkill. However, for a professional-grade product, it is essential for long-term maintenance.
• Ignoring $wpdb->prepare: Never concatenate variables directly into a SQL string. Even if a value comes from a "trusted" source, always use prepare() to prevent SQL injection vulnerabilities.

Recap

By implementing the DAO pattern, we’ve moved from scattered, hard-to-maintain database queries to a clean, injectable, and secure architecture. We’ve ensured that our data access is centralized, which sets the stage for our next challenge: implementing efficient caching.

Up next: Query Caching Strategies — we will learn how to wrap these DAO methods with the Transients API and Object Cache to minimize database load.