Database Basics with wpdb: Secure Queries for WordPress

Previously in this course, we explored saving meta data, which provided a high-level way to interact with post attributes. In this lesson, we drop down a layer to interact directly with the WordPress database using the $wpdb object.

Accessing the $wpdb Object

In WordPress, the $wpdb object is a global instance of the wpdb class. It serves as the primary interface for executing SQL queries against your WordPress database. Because it is a global variable, you must explicitly declare it within your methods before you can use it.

PHP
function get_article_count() {
    global $wpdb;
    #6A9955">// Now you can access $wpdb methods
}

Think of $wpdb as your bridge to the database. It handles connection management, prefixing table names (so your queries work regardless of the user's table prefix), and providing helper methods for CRUD operations.

Prepared vs. Raw Queries

The most critical rule in WordPress development is: never trust user input. If you take a value from a URL parameter or a form field and inject it directly into an SQL string, you open your site to SQL injection attacks.

• Raw Queries: These are standard SQL strings. Using variables directly inside them is dangerous because a malicious user could craft an input that alters the structure of your query.
• Prepared Queries: These use placeholders (like %s for strings or %d for integers) to separate the SQL logic from the data. The wpdb::prepare() method then safely binds the values, ensuring they are escaped and treated strictly as data, not as executable code.

Performing a Simple SELECT Query

Let's look at a concrete example. Suppose we want to fetch the count of all published posts in our Knowledge Base plugin. We use $wpdb->get_var() to retrieve a single scalar value.

PHP
public function get_published_article_count() {
    global $wpdb;

    #6A9955">// Use the prepare method even for simple queries to stay in the habit
    $query = $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s",
        'knowledge-article',
        'publish'
    );

    return $wpdb->get_var($query);
}

Notice how we used {$wpdb->posts}. You should always use the table properties provided by the $wpdb object rather than hardcoding wp_posts, as site administrators often change their database prefix for security.

Hands-on Exercise

In your KnowledgeBaseModel class, create a method named get_latest_article_title().

1. Use global $wpdb;.
2. Write a query to select the post_title from the {$wpdb->posts} table.
3. Use a WHERE clause to filter by post_type = 'knowledge-article' and post_status = 'publish'.
4. Use ORDER BY post_date DESC and LIMIT 1.
5. Use $wpdb->prepare() to ensure the post_type is passed safely.
6. Return the result using $wpdb->get_var().

Common Pitfalls

• Hardcoding Prefixes: Never assume the table is named wp_posts. Always use $wpdb->posts, $wpdb->postmeta, or other built-in properties.
• Forgetting prepare(): It is tempting to skip prepare() for simple static queries. Don't. It's a defensive programming habit that prevents future bugs when you eventually decide to make that query dynamic.
• Over-querying: While $wpdb is powerful, it is often overkill. If you are retrieving post data, prefer using WP_Query or get_posts() first. We will cover those in later lessons, but for now, remember that direct SQL is for when the WordPress abstraction layer doesn't fit your needs.
• Security Risks: For a deeper look at why these practices matter, review WordPress Database Queries: Securely Using $wpdb and Preparing SQL.

Recap

We have successfully accessed the $wpdb object, learned the vital importance of prepare() to prevent SQL injection, and executed a simple SELECT query. Mastering these database basics is essential for building performant plugins that handle data structures beyond standard WordPress posts. As you grow more comfortable, you can eventually explore advanced topics like WP_Meta_Query to handle complex filtering.

Up next: Secure CRUD Operations with $wpdb.