Secure CRUD Operations: Mastering $wpdb for WordPress Development

Previously in this course, we covered the database basics with wpdb, where you learned how to access the $wpdb object and perform simple SELECT queries. In this lesson, we move beyond reading data to writing it safely.

To maintain the integrity of our Knowledge Base plugin, we must ensure every "Create, Read, Update, and Delete" (CRUD) operation is immune to SQL injection. By leveraging built-in WordPress helper methods, we can abstract away the complexity of manual query construction and minimize human error.

The CRUD Mindset: Why Helper Methods Matter

When you write raw SQL queries, you are responsible for escaping every variable. If you forget even one, an attacker can manipulate your query to drop tables or leak sensitive user information. While preventing SQL injection in modern frameworks is standard practice, WordPress provides specific methods that handle this automatically.

We will focus on $wpdb->insert, $wpdb->update, and $wpdb->delete. These methods take arrays of data and handle the formatting and quoting for you, provided you use them correctly.

Performing Secure CRUD Operations

1. Creating Data with $wpdb->insert

To add a new entry to a custom table, we pass the table name, an associative array of data, and an optional array of formats.

PHP
global $wpdb;

$table_name = $wpdb->prefix . 'kb_articles';
$data = [
    'title'   => 'How to use hooks',
    'content' => 'Hooks allow you to modify WordPress...',
    'status'  => 'published'
];

$wpdb->insert($table_name, $data, ['%s', '%s', '%s']);

The third argument specifies the format: %s for strings, %d for integers, and %f for floats. Always define these to ensure the database engine treats your data as the correct type.

2. Updating Data with $wpdb->update

Updating follows a similar pattern, but requires a WHERE clause to ensure you don't accidentally update the entire table.

PHP
$wpdb->update(
    $table_name,
    ['status' => 'archived'], #6A9955">// Data to update
    ['id' => 5],              #6A9955">// WHERE clause
    ['%s'],                   #6A9955">// Format of data
    ['%d']                    #6A9955">// Format of WHERE
);

3. Secure Fetching with $wpdb->prepare

While insert and update handle their own security, SELECT queries (the "Read" in CRUD) require explicit protection. You must use $wpdb->prepare() to sanitize your input before it reaches the database.

PHP
$article_id = 5; #6A9955">// Suppose this came from $_GET['id']
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}kb_articles WHERE id = %d",
    $article_id
);

$article = $wpdb->get_row($query);

By using %d as a placeholder, prepare() ensures that $article_id is treated strictly as an integer, rendering SQL injection attempts harmless.

4. Deleting Data

Deletion is the most dangerous operation. Always ensure you have a specific WHERE clause to avoid catastrophic data loss.

PHP
$wpdb->delete(
    $table_name,
    ['id' => 5],
    ['%d']
);

Hands-on Exercise: Implementing a Delete Action

In your Knowledge Base plugin, create a method within your KnowledgeBaseModel class that deletes an article by its ID.

1. Ensure the method accepts $id as an argument.
2. Use $wpdb->delete to remove the row from your custom table.
3. Validate that the ID is cast to an integer before passing it to the method.
4. Return the result of the operation (the number of rows affected).

Common Pitfalls to Avoid

• Ignoring Formats: Never omit the format array. If you pass a string into a field expected to be an integer, you invite unexpected behavior.
• Mixing Raw SQL with variables: Never concatenate variables directly into a query string (e.g., "SELECT * FROM table WHERE id = $id"). This is the primary vector for SQL injection.
• Forgetting wpdb::prepare: Every time you use $wpdb->get_results, get_row, or get_var with variables, you must use prepare().

Recap

CRUD operations are the lifeblood of your plugin's data management. By standardizing your database interactions through $wpdb->insert, $wpdb->update, and $wpdb->prepare, you protect your plugin against common security vulnerabilities. Always treat user-supplied data as untrusted until it has been properly formatted and prepared for the database engine.

Up next: We will transition from raw SQL to the powerful WP_Query class, which simplifies complex data retrieval in WordPress.