dw-sheet-data-checker/CACHE_AND_TURNSTILE_FIXES.md

Cache and Turnstile Fixes for Sheet Data Checker Pro

Implementation Date: December 17, 2024
Version: 1.5.0
Status: Complete Implementation

Overview

This document outlines the fixes implemented to address two critical issues reported for Sheet Data Checker Pro:

1. Cache Issue in Admin Area: Cache was interfering with real data retrieval in the admin interface
2. Turnstile Tracking Issue: Turnstile CAPTCHA wasn't being properly tracked in the security dashboard

1. Cache Issue in Admin Area

Problem

Administrators were experiencing issues with cached data when setting up checkers in the WordPress admin area. This prevented them from seeing real-time changes in their Google Sheets data.

Root Cause

The fetch_remote_csv_data() method was using cached responses for both frontend and admin requests, which is inappropriate for admin operations where fresh data is needed for configuration.

Solution Implemented

Modified includes/class-Shortcode.php

/**
 * Fetch remote CSV/TSV data using WordPress HTTP API
 * Replaces fopen() for better server compatibility
 */
private function fetch_remote_csv_data($url, $delimiter, $limit = null, $force_refresh = false) {
    $data = [];

    // Add cache-busting parameter for admin area to ensure fresh data
    $fetch_url = $url;
    if ($force_refresh || is_admin()) {
        $separator = (strpos($url, '?') !== false) ? '&' : '?';
        $fetch_url = $url . $separator . 'nocache=' . time();
    }

    // Use WordPress HTTP API to fetch remote file
    $response = wp_remote_get($fetch_url);

    // ... rest of the method
}

Updated Method Calls

Updated all calls to fetch_remote_csv_data() in admin contexts to force refresh:

// In content() method
$data = $this->fetch_remote_csv_data($url, $delimiter, null, is_admin());

// In checker_public_validation() method
$data = $this->fetch_remote_csv_data($url, $delimiter, null, is_admin());

// In checker_load_all_data() method
$data = $this->fetch_remote_csv_data($url, $delimiter, $limit, is_admin());

Benefits

1. Admin Gets Fresh Data: Admin requests always fetch fresh data from the source
2. Frontend Still Caches: Frontend requests continue to benefit from caching for performance
3. Minimal Performance Impact: Only admin requests bypass cache
4. Backward Compatible: No breaking changes to existing functionality

2. Turnstile Tracking Issue

Problem

Turnstile CAPTCHA wasn't being properly tracked in the security dashboard, making it appear as if no checkers were using Turnstile protection.

Root Cause

1. The security dashboard was using outdated syntax for checking array values
2. Debugging information wasn't available to identify the issue
3. No logging was in place to track Turnstile verification attempts

Solutions Implemented

A. Enhanced Security Dashboard

File: admin/class-Security-Dashboard.php

1. Improved Array Checking:

// Before
$has_turnstile = ($checker_data['security']['turnstile']['enabled'] ?? 'no') === 'yes';

// After
$has_turnstile = isset($checker_data['security']['turnstile']['enabled']) && $checker_data['security']['turnstile']['enabled'] === 'yes';

2. Added Debug Information:

// Added separate counters for reCAPTCHA and Turnstile
$recaptcha_count = 0;
$turnstile_count = 0;

// Added detailed logging
error_log('Checker ID: ' . $checker->ID . ' - Rate Limit: ' . ($has_rate_limit ? 'yes' : 'no') .
          ', reCAPTCHA: ' . ($has_recaptcha ? 'yes' : 'no') .
          ', Turnstile: ' . ($has_turnstile ? 'yes' : 'no'));

3. Enhanced UI Display:

// Show separate counts in dashboard
<small class="text-muted">
    reCAPTCHA: <?php echo $security_status['recaptcha_count']; ?> |
    Turnstile: <?php echo $security_status['turnstile_count']; ?>
</small>

B. Turnstile Test Page

File: admin/test-turnstile.php

Created a comprehensive test page at /wp-admin/admin.php?page=test-turnstile that provides:

1. Configuration Check:

   • Verifies Turnstile is properly configured
   • Validates site key and secret key formats
   • Checks if keys are properly stored

2. CAPTCHA Helper Testing:

   • Tests get_captcha_config() method
   • Tests validate_captcha_config() method
   • Shows detailed validation results

3. Debug Information:

   • WordPress and PHP versions
   • Plugin version
   • Debug log entries
   • Step-by-step troubleshooting guide

C. Security Logging System

File: includes/logs/class-Security-Logger.php

Implemented a comprehensive logging system to track security events:

1. Rate Limit Logging:

public static function log_rate_limit_block($checker_id, $ip, $limit_config) {
    return self::log_event(
        'rate_limit',
        $checker_id,
        [
            'ip' => $ip,
            'max_attempts' => $limit_config['max_attempts'] ?? 5,
            'time_window' => $limit_config['time_window'] ?? 15,
            'block_duration' => $limit_config['block_duration'] ?? 60
        ],
        'warning'
    );
}

2. CAPTCHA Failure Logging:

public static function log_captcha_failure($checker_id, $captcha_type, $verification_data) {
    return self::log_event(
        $captcha_type,
        $checker_id,
        [
            'success' => false,
            'score' => $verification_data['score'] ?? null,
            'error_codes' => $verification_data['error_codes'] ?? []
        ],
        'warning'
    );
}

3. Database Table for Logs:

CREATE TABLE IF NOT EXISTS wp_checker_security_logs (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    event_type varchar(50) NOT NULL,
    checker_id bigint(20) unsigned NOT NULL,
    ip_address varchar(45) NOT NULL,
    user_agent varchar(255) DEFAULT NULL,
    event_data longtext DEFAULT NULL,
    level varchar(10) NOT NULL DEFAULT 'info',
    created_at datetime NOT NULL,
    PRIMARY KEY (id),
    KEY event_type (event_type),
    KEY checker_id (checker_id),
    KEY created_at (created_at),
    KEY level (level)
);

D. Integrated Logging with Security Class

File: includes/class-Security.php

Added logging calls throughout the security verification process:

1. Rate Limit Verification:

// Log the rate limit block
if (class_exists('CHECKER_SECURITY_LOGGER')) {
    CHECKER_SECURITY_LOGGER::log_rate_limit_block($checker_id, $ip, [
        'max_attempts' => $max_attempts,
        'time_window' => $time_window,
        'block_duration' => $block_duration
    ]);
}

2. reCAPTCHA Verification:

// Log the CAPTCHA failure
if (class_exists('CHECKER_SECURITY_LOGGER')) {
    CHECKER_SECURITY_LOGGER::log_captcha_failure($checker_id, 'recaptcha', [
        'success' => false,
        'score' => $score,
        'error_codes' => is_array($body['error-codes']) ? $body['error-codes'] : []
    ]);
}

3. Turnstile Verification:

// Log the CAPTCHA failure
if (class_exists('CHECKER_SECURITY_LOGGER')) {
    CHECKER_SECURITY_LOGGER::log_captcha_failure($checker_id, 'turnstile', [
        'success' => false,
        'error_codes' => is_array($body['error-codes']) ? $body['error-codes'] : []
    ]);
}

3. Additional Improvements

A. Automated Log Cleanup

File: includes/class-Sheet-Data-Checker-Pro.php

Added scheduled task to automatically clean up old security logs:

/**
 * Schedule cleanup of old security logs
 */
public function schedule_log_cleanup() {
    // Schedule cleanup if not already scheduled
    if (!wp_next_scheduled('checker_security_log_cleanup')) {
        wp_schedule_event(time(), 'daily', 'checker_security_log_cleanup');
    }
}

/**
 * Cleanup old security logs
 */
public static function cleanup_security_logs() {
    if (class_exists('CHECKER_SECURITY_LOGGER')) {
        CHECKER_SECURITY_LOGGER::cleanup_old_logs(90); // Keep logs for 90 days
    }
}

B. Enhanced Nonce Verification

File: includes/class-Security.php

Enhanced nonce verification to include logging:

public static function verify_nonce($nonce, $action, $checker_id = 0) {
    if (!$nonce) {
        return false;
    }

    $is_valid = wp_verify_nonce($nonce, $action) !== false;

    // Log nonce failure if checker_id is provided
    if (!$is_valid && $checker_id && class_exists('CHECKER_SECURITY_LOGGER')) {
        CHECKER_SECURITY_LOGGER::log_nonce_failure($checker_id, $nonce);
    }

    return $is_valid;
}

4. How to Use the New Features

A. Testing Cache Fix

1. Go to any checker in the WordPress admin
2. Modify the Google Sheet URL
3. Save changes
4. Verify that the updated data is immediately reflected

B. Testing Turnstile Fix

1. Enable Turnstile on a checker
2. Go to Security Dashboard → Checkers
3. Verify Turnstile appears as "Enabled"
4. Use the test page at /wp-admin/admin.php?page=test-turnstile

C. Viewing Security Logs

1. In WordPress admin, go to Checkers → Security
2. View the "Recent Rate Limit Blocks" section
3. Click "Refresh" to see the latest logs

5. Troubleshooting Guide

Cache Issues

• Problem: Still seeing old data in admin
• Solution: Check browser cache or use incognito mode
• Debug: Look for nocache parameter in network requests

Turnstile Issues

• Problem: Turnstile not showing in security dashboard
• Solution: Use test page to verify configuration
• Debug: Check WordPress error logs for CAPTCHA errors

Logging Issues

• Problem: No security events being logged
• Solution: Verify database table was created
• Debug: Check if WP_DEBUG_LOG is enabled

6. Future Enhancements

1. Cache Control UI: Add option to manually clear cache for specific checkers
2. Advanced Log Filtering: More granular filtering options in security dashboard
3. Log Export: Ability to export security logs for analysis
4. Real-time Monitoring: WebSocket integration for real-time security event monitoring

Conclusion

These fixes address the critical cache and Turnstile tracking issues while providing additional security visibility through comprehensive logging. The implementation maintains backward compatibility and follows WordPress best practices.