How can I get a single entry from a custom database table in WordPress?

by

Summary

The code attempts to retrieve a single value (authority) from a custom WordPress database table (tbl_industries) based on a user-provided ID (via $_REQUEST['industry']) and immediately prints it. The primary failure is SQL Injection due to unsanitized user input directly concatenated into the query, compounded by the likely absence of the custom table in the WordPress database schema, causing the query to fail silently or throw an error depending on configuration.

Root Cause

The root cause is improper input sanitization and lack of database schema verification.

  1. SQL Injection Vulnerability: The variable $industry is sourced directly from $_REQUEST and inserted into the SQL string without validation, escaping, or binding.
  2. Table Existence: The code assumes $wpdb->tbl_industries is defined and the table exists. In a standard WordPress environment, custom tables are not automatically registered in the $wpdb object unless explicitly defined. If this property is null or empty, the resulting SQL query becomes SELECT authority FROM WHERE id=..., which is syntactically invalid.
  3. Error Handling: get_var() returns null on database errors or empty results. Without checking $wpdb->last_error, the code fails silently, leading to the assumption that the variable is empty rather than the query failing.

Why This Happens in Real Systems

This is a classic “Quick and Dirty” development pattern often seen in legacy PHP codebases ported to WordPress.

  • Direct Access to Superglobals: Developers often access $_GET, $_POST, or $_REQUEST directly to save time, bypassing WordPress security abstractions (like nonce verification or sanitization functions).
  • Implicit Trust of Input: The developer assumes the input will always be a valid integer, ignoring the possibility of malicious input or malformed data.
  • Dynamic Table Name Assumptions: Developers often create custom tables but forget that $wpdb does not natively know about them. They expect $wpdb->tbl_name to work automatically, which it does not unless manually assigned: $wpdb->tbl_name = 'my_table';.
  • Silent Failures: PHP and MySQL are often configured to suppress warnings in production, causing get_var() to return false or null without visible errors, leading to “ghost” data issues.

Real-World Impact

  • Security Breach (Data Loss/Exposure): This code allows SQL Injection. An attacker could manipulate the industry parameter to extract sensitive data (e.g., UNION SELECT user_pass FROM wp_users), modify data, or delete the table.
  • Site Crashes (White Screen of Death): If WP_DEBUG is enabled, invalid SQL syntax (due to an undefined table name) triggers a fatal error, crashing the page load.
  • Data Integrity Issues: If the query fails silently, $authority remains empty. The user sees nothing, or worse, the data renders incorrectly, leading to bad business decisions based on missing information.
  • Performance Degradation: While less likely with a single query, repeated injection attempts can flood the database with complex queries, exhausting resources.

Example or Code

The following code demonstrates the secure way to handle this request. It uses $wpdb->prepare() to sanitize inputs and ensures the table name is correctly referenced.

prefix . 'industries'; 

    // 2. Sanitize and Validate Input
    // Ensure we have an integer to prevent injection and logic errors.
    $industry_id = isset($_REQUEST['industry']) ? intval($_REQUEST['industry']) : 0;

    if (empty($industry_id)) {
        echo '
Please select a valid industry.
';
        return;
    }

    // 3. Secure SQL Query using prepare()
    // This escapes the integer and prevents SQL injection.
    // Note: For integers, %d is used. For strings, %s.
    $query = $wpdb->prepare(
        "SELECT authority FROM $table_name WHERE id = %d",
        $industry_id
    );

    $authority = $wpdb->get_var($query);

    // 4. Error Handling
    if ($wpdb->last_error) {
        // Log error in production, display debug info in development
        error_log('DB Error: ' . $wpdb->last_error);
        echo '
Data currently unavailable.
';
        return;
    }

    // 5. Output Sanitization
    // Always escape output when printing data from the database.
    if (!empty($authority)) {
        echo '
' . esc_html($authority) . '
';
    } else {
        echo '
No authority found for this selection.
';
    }
}
?>

How Senior Engineers Fix It

Senior engineers approach this by prioritizing security and robustness over speed.

  1. Implement the Table Registry: They ensure custom tables are registered in the plugin activation hook and verified on load.

    // In activation hook
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql_create_table);

// In global scope or class
$wpdb->tbl_industries = $wpdb->prefix . 'industries';

  2. Strict Typing: They never trust string inputs. They immediately cast integers using intval() or use strict type checks.

  3. Use Prepared Statements: They strictly use $wpdb->prepare() for all variable interpolations in SQL queries. This is the standard WordPress method for securing queries.

  4. Graceful Degradation: They implement checks for $wpdb->last_error and handle the “no results” case explicitly so the UI doesn’t break or show empty data without context.

  5. Output Escaping: They never echo database content directly. They use esc_html(), esc_attr(), or wp_kses() depending on the context to prevent Cross-Site Scripting (XSS).

Why Juniors Miss It

  • Lack of Security Awareness: Juniors often focus on functionality (“Does it display the text?”) rather than vulnerabilities (“Can I inject SQL?”). They may not understand how SQL injection works technically.
  • Global Variable Confusion: The $wpdb object is powerful but magical. Juniors often assume $wpdb->custom_table works automatically without knowing they must define that property or use $wpdb->prefix.
  • Direct Input Usage: They are used to standard PHP scripts where $_GET is the primary way to retrieve data. They are unaware of or forget WordPress wrappers like sanitize_text_field() or isset() checks.
  • Testing Bias: They test with valid data. They rarely test with malicious payloads (e.g., 1 OR 1=1) or edge cases (empty strings, special characters), missing the failure points.