wp_create_nonce("checker_ajax_nonce"),
"ajaxurl" => admin_url("admin-ajax.php"),
"i18n" => [
"refresh_page" => __("Refresh Page", "sheet-data-checker-pro"),
"session_expired" => __("Session expired. Please refresh the page and try again.", "sheet-data-checker-pro"),
"recaptcha_failed" => __("reCAPTCHA verification failed. Please try again.", "sheet-data-checker-pro"),
"turnstile_failed" => __("Turnstile verification failed. Please try again.", "sheet-data-checker-pro"),
"rate_limited" => __("Too many attempts. Please try again later.", "sheet-data-checker-pro"),
"security_error" => __("Security validation failed.", "sheet-data-checker-pro"),
"loading" => __("Loading...", "sheet-data-checker-pro"),
"searching" => __("Searching...", "sheet-data-checker-pro"),
"error_occurred" => __("An error occurred. Please try again.", "sheet-data-checker-pro"),
],
]);
}
public function content($atts, $content = null)
{
if (!isset($atts["id"])) {
return;
}
$post_id = $atts["id"];
$checker = get_post_meta($post_id, "checker", true);
// Load CAPTCHA scripts if enabled
if (class_exists('CHECKER_CAPTCHA_HELPER')) {
CHECKER_CAPTCHA_HELPER::load_captcha_scripts($post_id);
}
$checker = wp_parse_args($checker, [
"link" => "",
"description" => "",
"card" => [
"width" => 500,
"background" => "#cccccc",
"bg_opacity" => 50,
"border_radius" => 1,
"box_shadow" => "10px 5px 15px -5px",
"box_shadow_color" => "#333333",
"title" => "#333333",
"title_align" => "left",
"description" => "#333333",
"description_align" => "left",
"divider" => "#333333",
"divider_width" => 1,
],
"field" => [
"label" => "block",
"label-color" => "#333333",
],
"fields" => [],
"search_button" => [
"text" => "Search",
"bg_color" => "#cccccc",
"text_color" => "#333333",
"position" => "flex-end",
],
"back_button" => [
"text" => "Back",
"bg_color" => "#cccccc",
"text_color" => "#333333",
"position" => "flex-start",
],
"result" => [
"display" => "vertical-tabel",
"header" => "#333333",
"value" => "#333333",
"columns" => [],
"border_width" => 1,
],
]);
$url = isset($checker["link"]) ? (string)$checker["link"] : '';
$link_format = $url ? substr($url, -3) : 'csv';
// Set the delimiter based on the format
$delimiter = $link_format == "tsv" ? "\t" : ","; // Use tab for TSV, comma for CSV
// Validate allowed host
if (!$this->is_allowed_sheet_url($url)) {
wp_send_json_error([
"message" => __("Sheet URL is not allowed. Please use an approved domain.", "sheet-data-checker-pro"),
"type" => "error",
]);
return;
}
// Use WordPress HTTP API instead of fopen for better server compatibility
$data = $this->fetch_remote_csv_data($url, $delimiter);
$background_color = isset($checker["card"]["background"]) ? $checker["card"]["background"] : '#ffffff';
if ($checker["card"]["bg_opacity"] < 100) {
$background_color =
$checker["card"]["background"] .
"" .
$checker["card"]["bg_opacity"];
}
$render = "";
$render .=
'
';
$render .=
'
";
$render .=
'
';
$render .=
'
';
$render .=
'
';
$render .=
'
';
$render .= '
';
$render .=
'
';
$render .=
'
';
$render .=
'';
$render .= "
";
$render .= "
";
$render .= "
";
$render .= '';
// Pass settings to frontend as data attributes
$render .=
'";
return $render;
}
/**
* 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 = [];
// Build cache key
$cache_key = 'checker_csv_' . md5($url . $delimiter . $limit);
// Check cache first (unless force refresh)
if (!$force_refresh && !is_admin()) {
$cached_data = get_transient($cache_key);
if ($cached_data !== false) {
return $cached_data;
}
}
// Use WordPress HTTP API to fetch remote file
$response = wp_remote_get($url, [
'timeout' => 30,
'sslverify' => false // For local development
]);
if (is_wp_error($response)) {
error_log(
"Failed to fetch remote file: " .
$response->get_error_message(),
);
return $data;
}
$body = wp_remote_retrieve_body($response);
if (empty($body)) {
error_log("Empty response from remote file: " . $url);
return $data;
}
// Parse CSV/TSV data
$lines = explode("\n", $body);
if (empty($lines)) {
return $data;
}
// Get headers from first line
$keys = str_getcsv($lines[0], $delimiter);
// Process data rows
$count = 0;
for ($i = 1; $i < count($lines); $i++) {
if (empty(trim($lines[$i]))) {
continue; // Skip empty lines
}
$row = str_getcsv($lines[$i], $delimiter);
if (count($keys) === count($row)) {
$data[] = array_combine($keys, $row);
$count++;
// Apply limit if specified
if ($limit && $count >= $limit) {
break;
}
}
}
// Cache the data for 5 minutes (unless in admin)
if (!is_admin()) {
set_transient($cache_key, $data, 5 * MINUTE_IN_SECONDS);
}
return $data;
}
/**
* AJAX handler to clear cache for a specific checker (admin only)
*/
public function checker_clear_cache()
{
check_ajax_referer('checker_ajax_nonce', 'security');
if (!current_user_can('edit_posts')) {
wp_send_json_error(['message' => 'Unauthorized']);
return;
}
$post_id = isset($_REQUEST['checker_id']) ? intval($_REQUEST['checker_id']) : 0;
if (!$post_id) {
wp_send_json_error(['message' => 'Invalid checker ID']);
return;
}
$checker = get_post_meta($post_id, 'checker', true);
if (!$checker || !isset($checker['link'])) {
wp_send_json_error(['message' => 'Checker not found']);
return;
}
$url = (string)$checker['link'];
$link_format = $url ? substr($url, -3) : 'csv';
$delimiter = $link_format == 'tsv' ? "\t" : ",";
// Clear all possible cache variations for this checker
$limits = [null, 10, 100, 500, 1000];
$cleared = 0;
foreach ($limits as $limit) {
$cache_key = 'checker_csv_' . md5($url . $delimiter . $limit);
if (delete_transient($cache_key)) {
$cleared++;
}
}
wp_send_json_success([
'message' => sprintf(__('Cache cleared successfully (%d entries)', 'sheet-data-checker-pro'), $cleared),
'cleared' => $cleared
]);
}
/**
* AJAX handler to clear cache for frontend users (public)
*/
public function checker_clear_cache_public()
{
check_ajax_referer('checker_ajax_nonce', 'security');
$post_id = isset($_REQUEST['checker_id']) ? intval($_REQUEST['checker_id']) : 0;
if (!$post_id) {
wp_send_json_error(['message' => __('Invalid checker ID', 'sheet-data-checker-pro')]);
return;
}
$checker = get_post_meta($post_id, 'checker', true);
if (!$checker || !isset($checker['link'])) {
wp_send_json_error(['message' => __('Checker not found', 'sheet-data-checker-pro')]);
return;
}
$url = (string)$checker['link'];
$link_format = $url ? substr($url, -3) : 'csv';
$delimiter = $link_format == 'tsv' ? "\t" : ",";
// Clear all possible cache variations for this checker
$limits = [null, 10, 100, 500, 1000];
$cleared = 0;
foreach ($limits as $limit) {
$cache_key = 'checker_csv_' . md5($url . $delimiter . $limit);
if (delete_transient($cache_key)) {
$cleared++;
}
}
wp_send_json_success([
'message' => __('Data refreshed successfully!', 'sheet-data-checker-pro'),
'cleared' => $cleared
]);
}
public function checker_public_validation()
{
$post_id = isset($_REQUEST["checker_id"]) ? intval($_REQUEST["checker_id"]) : 0;
if (!$post_id) {
wp_send_json_error(["message" => "Invalid checker ID", "type" => "error"]);
return;
}
$checker = get_post_meta($post_id, "checker", true);
// Enforce nonce
if (!isset($_REQUEST['security']) || !check_ajax_referer('checker_ajax_nonce', 'security', false)) {
wp_send_json_error([
"message" => __("Security check failed. Please refresh the page.", "sheet-data-checker-pro"),
"type" => "nonce_expired",
]);
return;
}
// Unified security verification (rate limit, honeypot, reCAPTCHA, Turnstile)
$security_error = CHECKER_SECURITY::verify_all_security($post_id, $checker, $_REQUEST);
if ($security_error !== null) {
wp_send_json_error([
"message" => $security_error["message"],
"type" => $security_error["type"],
]);
return;
}
$url = isset($checker["link"]) ? (string)$checker["link"] : '';
$link_format = $url ? substr($url, -3) : 'csv';
// Set the delimiter based on the format
$delimiter = $link_format == "tsv" ? "\t" : ","; // Use tab for TSV, comma for CSV
// Use WordPress HTTP API instead of fopen for better server compatibility
$data = $this->fetch_remote_csv_data($url, $delimiter);
$validator = isset($_REQUEST["validate"]) && is_array($_REQUEST["validate"]) ? $_REQUEST["validate"] : [];
$validation = [];
foreach ($validator as $validate) {
$kolom = isset($validate["kolom"]) ? (string)$validate["kolom"] : '';
$val = isset($validate["value"]) ? (string)$validate["value"] : '';
if ($kolom !== '') {
$validation[$kolom] = $val;
}
}
$validator_count = count($validator);
$result = [];
if (!empty($data)) {
foreach ($data as $row) {
$valid = [];
foreach ($row as $header => $value) {
// Ensure string types to avoid null deprecation warnings
$header = ($header !== null) ? (string)$header : '';
$value = ($value !== null) ? (string)$value : '';
$id = "_" . strtolower(str_replace(" ", "_", $header));
$include = false;
if (isset($validation[$header])) {
$validation_value = isset($validation[$header]) ? (string)$validation[$header] : '';
if (
isset($checker["fields"][$id]["match"]) &&
$checker["fields"][$id]["match"] == "match" &&
strtolower($value) == strtolower($validation_value)
) {
$include = true;
}
if (
isset($checker["fields"][$id]["match"]) &&
$checker["fields"][$id]["match"] == "contain" &&
$validation_value !== '' &&
false !== strpos(strtolower($value), strtolower($validation_value))
) {
$include = true;
}
if ($include) {
$valid[$header] = $value;
}
}
}
if ($validator_count !== count($valid)) {
continue;
}
$result[] = $row;
}
}
$send = [
"count" => count($result),
"rows" => $result,
"settings" => $checker["result"],
"output" => $checker["output"],
];
wp_send_json($send);
}
/**
* Load all data from sheet (for show all mode)
*/
public function checker_load_all_data()
{
$post_id = isset($_REQUEST["checker_id"])
? intval($_REQUEST["checker_id"])
: 0;
$limit = isset($_REQUEST["limit"]) ? intval($_REQUEST["limit"]) : 100;
$is_initial_load = isset($_REQUEST["initial_load"]) && $_REQUEST["initial_load"] === "yes";
if (!$post_id) {
wp_send_json_error(["message" => "Invalid checker ID", "type" => "error"]);
return;
}
$checker = get_post_meta($post_id, "checker", true);
if (!$checker || !isset($checker["link"])) {
wp_send_json_error(["message" => "Checker not found", "type" => "error"]);
return;
}
// Enforce nonce
if (!isset($_REQUEST['security']) || !check_ajax_referer('checker_ajax_nonce', 'security', false)) {
wp_send_json_error([
"message" => __("Security check failed. Please refresh the page.", "sheet-data-checker-pro"),
"type" => "nonce_expired",
]);
return;
}
// For initial load in show-all mode, skip CAPTCHA but still check rate limit and honeypot
// This allows the page to load while CAPTCHA widget renders
$skip_captcha = $is_initial_load && CHECKER_SECURITY::get_setting($checker, 'result', 'skip_captcha_initial', 'yes') === 'yes';
// Unified security verification
$security_error = CHECKER_SECURITY::verify_all_security($post_id, $checker, $_REQUEST, $skip_captcha);
if ($security_error !== null) {
wp_send_json_error([
"message" => $security_error["message"],
"type" => $security_error["type"],
]);
return;
}
$url = isset($checker["link"]) ? (string)$checker["link"] : '';
$link_format = $url ? substr($url, -3) : 'csv';
$delimiter = $link_format == "tsv" ? "\t" : ",";
if (!$this->is_allowed_sheet_url($url)) {
wp_send_json_error([
"message" => __("Sheet URL is not allowed. Please use an approved domain.", "sheet-data-checker-pro"),
"type" => "error",
]);
return;
}
// Use WordPress HTTP API instead of fopen for better server compatibility
$data = $this->fetch_remote_csv_data($url, $delimiter, $limit);
wp_send_json([
"count" => count($data),
"rows" => $data,
"settings" => $checker["result"],
"output" => $checker["output"],
"url_params" => $checker["url_params"] ?? [],
"filter_mode" => $checker["result"]["filter_mode"] ?? "search",
]);
}
/**
* Allowlist check for sheet URL host
*/
private function is_allowed_sheet_url($url)
{
$host = parse_url($url, PHP_URL_HOST);
if (!$host) {
return false;
}
$allowed = apply_filters(
'sheet_checker_allowed_hosts',
[
'docs.google.com',
'drive.google.com',
'docs.googleusercontent.com',
wp_parse_url(home_url(), PHP_URL_HOST),
]
);
$allowed = array_filter(array_unique(array_map('strtolower', $allowed)));
return in_array(strtolower($host), $allowed, true);
}
}