Database Explorer

Security

Comprehensive security implementation details for the Database Explorer feature.

Security Overview

The Database Explorer implements multiple layers of security to protect against common web vulnerabilities and ensure safe database operations.

Authentication & Authorization

User Authentication

Requirement: User must be logged into WordPress
Capability Check: Requires manage_options capability (administrator only)
Verification: Checked on every REST API request
Implementation: check_permissions() method in DatabaseExplorer class

Permission Checks

Admin Page Access

if (!current_user_can("manage_options")) {
    return; // Don't add menu item
}

REST API Access

public static function check_permissions($request) {
    if (!current_user_can("manage_options")) {
        return new \WP_Error("rest_forbidden", ...);
    }
    // Nonce verification...
    return true;
}

Password Verification

Required For: Table deletion operations
Method: WordPress wp_authenticate() function
Validation: Verifies password against current user account
User Matching: Ensures password matches current logged-in user

CSRF Protection

Nonce Verification

Implementation: WordPress nonce system
Nonce Name: wp_rest
Header: X-WP-Nonce (preferred)
Fallback: _wpnonce parameter
Verification: wp_verify_nonce() function

Frontend Implementation

Nonce Source: Retrieved from script tag data attribute
Header Inclusion: Automatically included in all API requests
Automatic Handling: Managed by lmnFetch utility function

Input Validation

Table Name Validation

Format Validation

Pattern: /^[a-zA-Z0-9_]+$/ (alphanumeric and underscores only)
Length Check: 1-64 characters
Implementation: validate_table_name() method

Existence Validation

Check: Verifies table exists before operations
Query: SHOW TABLES LIKE %s
Error: Returns 404 if table not found

SQL Query Validation

Query Type Restriction

Allowed: SELECT queries only
Blocked: DROP, DELETE, UPDATE, INSERT, ALTER, CREATE, TRUNCATE, EXEC, EXECUTE, CALL
Check: Performed after comment removal

Comment Removal

Single-line: Removes -- comments
Multi-line: Removes /* */ comments
Purpose: Prevents comment-based bypass attempts

Whitespace Normalization

Process: Normalizes whitespace before validation
Purpose: Prevents whitespace-based bypass attempts

Multiple Statement Prevention

Check: Detects semicolons in query
Validation: Ensures no content after semicolon
Error: Returns error if multiple statements detected

Parameter Validation

Pagination Parameters

Page: Validated with absint(), must be > 0
Per Page: Validated with absint(), must be > 0 and <= 500
Default: Page 1, Per Page 50

Sort Parameters

Orderby: Validated against actual table columns
Order: Must be "ASC" or "DESC"
Sanitization: sanitize_text_field()

Search Parameters

Sanitization: sanitize_text_field()
Escape: $wpdb->esc_like() for LIKE queries
Usage: Used in prepared statements

SQL Injection Prevention

Prepared Statements

Method: $wpdb->prepare()
Usage: All user input in SQL queries
Parameters: Table names validated before use
Search Terms: Properly escaped with esc_like()

Table Name Handling

Validation First: Table names validated before use
Escape After Validation: esc_sql() used after validation
No Direct Interpolation: Never directly interpolated into queries

Column Name Validation

Source: Retrieved from DESCRIBE query
Validation: Checked against actual table columns
Usage: Only validated columns used in queries

Example Safe Query

// Validate table name
$validated = self::validate_table_name($table_name);
if (!$validated) return error;

// Escape after validation
$escaped = esc_sql($table_name);

// Use in query
$query = "SELECT * FROM `{$escaped}`";

XSS Protection

Output Escaping

Backend Escaping

Table Names: esc_html() before JSON encoding
Error Messages: Generic messages, no sensitive data
JSON Encoding: WordPress REST API handles encoding

Frontend Escaping

Function: escapeHtml() utility function
Method: Uses DOM textContent to escape HTML
Usage: All database values before v-html rendering

HTML Rendering

Limited Use: Only for formatted NULL values
Escaped Content: All actual data properly escaped
Safe HTML: Only trusted HTML rendered

Table Deletion Safety

WordPress Core Table Protection

Detection Method

Standard Tables: List of WordPress core table names
Prefix Detection: Uses $wpdb->prefix dynamically
Name Matching: Compares table name (without prefix) to standard list
Multisite Support: Recognizes numbered tables

Protected Tables

Core Tables (12):

commentmeta, comments, links, options
postmeta, posts, terms, termmeta
term_relationships, term_taxonomy
usermeta, users

Multisite Tables (6, if enabled):

blogs, blog_versions, registration_log
signups, site, sitemeta

Implementation

private static function is_wordpress_table($table_name) {
    $table_prefix = $wpdb->prefix;
    $standard_tables = self::get_standard_wp_tables();
    
    if (strpos($table_name, $table_prefix) !== 0) {
        return false;
    }
    
    $base_table_name = substr($table_name, strlen($table_prefix));
    
    if (in_array($base_table_name, $standard_tables)) {
        return true;
    }
    
    // Multisite numbered tables...
    return false;
}

System Table Protection

Protected Tables: information_schema, mysql, performance_schema, sys
Check: Performed before deletion
Error: Returns 403 error if system table

Deletion Process Security

Password Verification: Required before deletion
Table Validation: Checks if safe to delete
Existence Check: Verifies table exists
Audit Logging: Logs all deletions
Error Handling: Generic error messages

Information Disclosure Prevention

Error Messages

Generic Messages: No detailed SQL errors exposed
User-Friendly: Clear, actionable messages
No Stack Traces: No technical details in errors
Status Codes: Appropriate HTTP status codes

Query Errors

Generic Response: "Query execution failed. Please check your SQL syntax."
No Details: Doesn't expose database structure
No SQL Errors: Doesn't show actual SQL error messages

Audit Logging

Deletion Logging

Format: [Database Explorer] User {username} ({id}) deleted table: {table_name}
Location: WordPress error log
Information: User login, user ID, table name, timestamp
Purpose: Accountability and audit trail