| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
trait ABJ_404_Solution_DataAccess_LogsTrait { |
| 8 |
|
| 9 |
/** |
| 10 |
* Populate logshits / logsid / last_used on each row from the pre-aggregated |
| 11 |
* wp_abj404_logs_hits rollup. Used by the captured/redirects table fallback |
| 12 |
* path when the main getRedirectsForView JOIN cannot include the rollup |
| 13 |
* (logs_hits race, sort by url/status/timestamp with queryAllRowsAtOnce=false). |
| 14 |
* |
| 15 |
* Reads only from logs_hits — never scans wp_abj404_logsv2. The previous |
| 16 |
* implementation aggregated logsv2 with GROUP BY in 50-URL chunks; on busy |
| 17 |
* sites with hot URLs (100K+ hits/URL) that meant 100K rows scanned per |
| 18 |
* chunk, routinely hitting the centralized 60s timeout. logs_hits is |
| 19 |
* O(distinct URLs), so the same lookup runs in milliseconds. |
| 20 |
* |
| 21 |
* Fallback: if logs_hits is missing or the lookup errors, schedule a |
| 22 |
* shutdown-time rebuild (mirrors getHighImpactCapturedCount, commit |
| 23 |
* 9133848d) and return rows with their original null/zero hit fields. |
| 24 |
* Never falls back to scanning logsv2 — the whole point is to never run |
| 25 |
* that query again. |
| 26 |
* |
| 27 |
* @param array<int, array<string, mixed>> $rows |
| 28 |
* @return array<int, array<string, mixed>> |
| 29 |
*/ |
| 30 |
function populateLogsData($rows) { |
| 31 |
if (empty($rows)) { |
| 32 |
return $rows; |
| 33 |
} |
| 34 |
|
| 35 |
// Extract all non-empty URLs from rows. |
| 36 |
// Keep lookup variants so legacy rows (e.g. missing leading slash) still map. |
| 37 |
$urls = array(); |
| 38 |
foreach ($rows as $row) { |
| 39 |
if ($row['url'] != null && !empty($row['url'])) { |
| 40 |
$variants = $this->buildHitsLookupUrlVariants($row['url']); |
| 41 |
foreach ($variants as $variant) { |
| 42 |
$urls[] = $variant; |
| 43 |
} |
| 44 |
} |
| 45 |
} |
| 46 |
|
| 47 |
if (empty($urls)) { |
| 48 |
return $rows; |
| 49 |
} |
| 50 |
|
| 51 |
$urls = array_values(array_unique($urls)); |
| 52 |
|
| 53 |
// If the rollup is not available, defer to a shutdown-time rebuild |
| 54 |
// rather than scan raw logsv2. Caller sees rows with null hits — same |
| 55 |
// contract as the main getRedirectsForView path when logs_hits is missing. |
| 56 |
if (!$this->logsHitsTableExists()) { |
| 57 |
$this->scheduleHitsTableRebuild(); |
| 58 |
return $rows; |
| 59 |
} |
| 60 |
|
| 61 |
// Chunk lookups so an absurdly large URL set doesn't build a multi-MB |
| 62 |
// IN() clause. logs_hits is small (O(distinct URLs)) so a generous batch |
| 63 |
// is fine — we cap at 200 to stay well within MySQL packet limits. |
| 64 |
$logsHitsTable = $this->doTableNameReplacements('{wp_abj404_logs_hits}'); |
| 65 |
$batchSize = 200; |
| 66 |
$logsResults = array(); |
| 67 |
$urlChunks = array_chunk($urls, $batchSize); |
| 68 |
|
| 69 |
foreach ($urlChunks as $urlChunk) { |
| 70 |
$placeholders = implode(',', array_fill(0, count($urlChunk), '%s')); |
| 71 |
// BINARY column equality matches getRedirectsForViewQuery() so case |
| 72 |
// and encoding edge cases behave identically across the main path |
| 73 |
// and this fallback. logs_hits stores rows by their exact logged |
| 74 |
// URL — variants like '/foo' and 'foo' may both exist as separate |
| 75 |
// rows; the PHP-side aggregation below merges them by canonical URL. |
| 76 |
$sql = "SELECT requested_url, logsid, last_used, logshits " |
| 77 |
. "FROM {$logsHitsTable} " |
| 78 |
. "WHERE BINARY requested_url IN ($placeholders)"; |
| 79 |
|
| 80 |
$chunkResult = $this->queryAndGetResults($sql, array( |
| 81 |
'query_params' => $urlChunk, |
| 82 |
'log_too_slow' => false, |
| 83 |
)); |
| 84 |
|
| 85 |
// logs_hits can be dropped between the existence check above and |
| 86 |
// this query (rebuild race). Treat any failure as "rollup |
| 87 |
// unavailable": schedule a rebuild and return rows untouched. |
| 88 |
// Never fall back to scanning logsv2. |
| 89 |
if (!empty($chunkResult['timed_out']) || |
| 90 |
(isset($chunkResult['last_error']) && $chunkResult['last_error'] != '')) { |
| 91 |
$errRaw = $chunkResult['last_error'] ?? ''; |
| 92 |
$err = is_string($errRaw) ? $errRaw : ''; |
| 93 |
if ($err !== '' && strpos($err, 'logs_hits') !== false) { |
| 94 |
$this->scheduleHitsTableRebuild(); |
| 95 |
} |
| 96 |
return $rows; |
| 97 |
} |
| 98 |
|
| 99 |
$chunkResults = is_array($chunkResult['rows'] ?? null) ? $chunkResult['rows'] : array(); |
| 100 |
if (!empty($chunkResults)) { |
| 101 |
$logsResults = array_merge($logsResults, $chunkResults); |
| 102 |
} |
| 103 |
} |
| 104 |
|
| 105 |
// Index logs data by canonical URL for fast lookup |
| 106 |
$logsDataByUrl = array(); |
| 107 |
foreach ($logsResults as $logRow) { |
| 108 |
$canonicalUrl = $this->canonicalizeUrlForHitsMatch($logRow['requested_url'] ?? ''); |
| 109 |
if ($canonicalUrl === '') { |
| 110 |
continue; |
| 111 |
} |
| 112 |
if (!isset($logsDataByUrl[$canonicalUrl])) { |
| 113 |
$logsDataByUrl[$canonicalUrl] = array( |
| 114 |
'logsid' => (int)($logRow['logsid'] ?? 0), |
| 115 |
'logshits' => (int)($logRow['logshits'] ?? 0), |
| 116 |
'last_used' => (int)($logRow['last_used'] ?? 0), |
| 117 |
); |
| 118 |
continue; |
| 119 |
} |
| 120 |
$existing = $logsDataByUrl[$canonicalUrl]; |
| 121 |
$currentLogsid = (int)($logRow['logsid'] ?? 0); |
| 122 |
$existingLogsid = (int)$existing['logsid']; |
| 123 |
$logsDataByUrl[$canonicalUrl]['logsid'] = ($existingLogsid > 0 && $currentLogsid > 0) |
| 124 |
? min($existingLogsid, $currentLogsid) |
| 125 |
: max($existingLogsid, $currentLogsid); |
| 126 |
$logsDataByUrl[$canonicalUrl]['logshits'] = (int)$existing['logshits'] + (int)($logRow['logshits'] ?? 0); |
| 127 |
$logsDataByUrl[$canonicalUrl]['last_used'] = max((int)$existing['last_used'], (int)($logRow['last_used'] ?? 0)); |
| 128 |
} |
| 129 |
|
| 130 |
// Populate rows with logs data using indexed lookup |
| 131 |
foreach ($rows as &$row) { |
| 132 |
if ($row['url'] != null && !empty($row['url'])) { |
| 133 |
$canonicalUrl = $this->canonicalizeUrlForHitsMatch($row['url']); |
| 134 |
if (isset($logsDataByUrl[$canonicalUrl])) { |
| 135 |
$logData = $logsDataByUrl[$canonicalUrl]; |
| 136 |
$row['logsid'] = $logData['logsid']; |
| 137 |
$row['logshits'] = $logData['logshits']; |
| 138 |
$row['last_used'] = $logData['last_used']; |
| 139 |
} |
| 140 |
} |
| 141 |
} |
| 142 |
|
| 143 |
return $rows; |
| 144 |
} |
| 145 |
|
| 146 |
/** |
| 147 |
* @param mixed $url |
| 148 |
* @return string |
| 149 |
*/ |
| 150 |
private function canonicalizeUrlForHitsMatch($url): string { |
| 151 |
if (!is_string($url)) { |
| 152 |
return ''; |
| 153 |
} |
| 154 |
|
| 155 |
$url = trim($url); |
| 156 |
if ($url === '') { |
| 157 |
return ''; |
| 158 |
} |
| 159 |
|
| 160 |
$fragment = ''; |
| 161 |
$fragmentPos = strpos($url, '#'); |
| 162 |
if ($fragmentPos !== false) { |
| 163 |
$fragment = substr($url, $fragmentPos); |
| 164 |
$url = substr($url, 0, $fragmentPos); |
| 165 |
} |
| 166 |
|
| 167 |
$query = ''; |
| 168 |
$queryPos = strpos($url, '?'); |
| 169 |
if ($queryPos !== false) { |
| 170 |
$query = substr($url, $queryPos); |
| 171 |
$url = substr($url, 0, $queryPos); |
| 172 |
} |
| 173 |
|
| 174 |
$path = trim($url, '/'); |
| 175 |
$normalizedPath = ($path === '') ? '/' : '/' . $path; |
| 176 |
|
| 177 |
return $normalizedPath . $query . $fragment; |
| 178 |
} |
| 179 |
|
| 180 |
/** |
| 181 |
* @param mixed $url |
| 182 |
* @return array<int, string> |
| 183 |
*/ |
| 184 |
private function buildHitsLookupUrlVariants($url) { |
| 185 |
$variants = array(); |
| 186 |
if (!is_string($url)) { |
| 187 |
return $variants; |
| 188 |
} |
| 189 |
|
| 190 |
$raw = trim($url); |
| 191 |
if ($raw !== '') { |
| 192 |
$variants[] = $raw; |
| 193 |
} |
| 194 |
|
| 195 |
$canonical = $this->canonicalizeUrlForHitsMatch($url); |
| 196 |
if ($canonical !== '') { |
| 197 |
$variants[] = $canonical; |
| 198 |
$parts = $this->splitCanonicalHitsUrl($canonical); |
| 199 |
$pathPart = $parts['path']; |
| 200 |
$suffixPart = $parts['suffix']; |
| 201 |
|
| 202 |
$pathVariants = array($pathPart); |
| 203 |
$noLeadingPath = ltrim($pathPart, '/'); |
| 204 |
if ($noLeadingPath !== '') { |
| 205 |
$pathVariants[] = $noLeadingPath; |
| 206 |
} |
| 207 |
|
| 208 |
if ($pathPart !== '/') { |
| 209 |
if (substr($pathPart, -1) === '/') { |
| 210 |
$toggleTrailingPath = rtrim($pathPart, '/'); |
| 211 |
} else { |
| 212 |
$toggleTrailingPath = $pathPart . '/'; |
| 213 |
} |
| 214 |
$pathVariants[] = $toggleTrailingPath; |
| 215 |
$toggleNoLeadingPath = ltrim($toggleTrailingPath, '/'); |
| 216 |
if ($toggleNoLeadingPath !== '') { |
| 217 |
$pathVariants[] = $toggleNoLeadingPath; |
| 218 |
} |
| 219 |
} |
| 220 |
|
| 221 |
foreach (array_unique($pathVariants) as $pathVariant) { |
| 222 |
$variants[] = $pathVariant . $suffixPart; |
| 223 |
} |
| 224 |
} |
| 225 |
|
| 226 |
return array_values(array_unique($variants)); |
| 227 |
} |
| 228 |
|
| 229 |
/** @return array{path: string, suffix: string} */ |
| 230 |
private function splitCanonicalHitsUrl(string $canonicalUrl): array { |
| 231 |
$firstQueryPos = strpos($canonicalUrl, '?'); |
| 232 |
$firstFragmentPos = strpos($canonicalUrl, '#'); |
| 233 |
|
| 234 |
if ($firstQueryPos === false && $firstFragmentPos === false) { |
| 235 |
return array('path' => $canonicalUrl, 'suffix' => ''); |
| 236 |
} |
| 237 |
|
| 238 |
if ($firstQueryPos === false) { |
| 239 |
$splitPos = $firstFragmentPos; |
| 240 |
} elseif ($firstFragmentPos === false) { |
| 241 |
$splitPos = $firstQueryPos; |
| 242 |
} else { |
| 243 |
$splitPos = min($firstQueryPos, $firstFragmentPos); |
| 244 |
} |
| 245 |
|
| 246 |
return array( |
| 247 |
'path' => substr($canonicalUrl, 0, $splitPos), |
| 248 |
'suffix' => substr($canonicalUrl, $splitPos), |
| 249 |
); |
| 250 |
} |
| 251 |
|
| 252 |
/** |
| 253 |
* Return up to 500 distinct requested URLs from the most recent log activity. |
| 254 |
* |
| 255 |
* Uses a reverse index scan on the timestamp index to fetch the 5 000 most |
| 256 |
* recent rows, then deduplicates. Much faster than GROUP BY on large tables |
| 257 |
* because it avoids a full table scan and aggregate computation. |
| 258 |
* |
| 259 |
* @return array<int, string> |
| 260 |
*/ |
| 261 |
function getDistinctLoggedUrls(): array { |
| 262 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/getDistinctLoggedUrls.sql"); |
| 263 |
|
| 264 |
$results = $this->queryAndGetResults($query); |
| 265 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 266 |
|
| 267 |
$urls = array(); |
| 268 |
foreach ($rows as $row) { |
| 269 |
$url = isset($row['requested_url']) && is_string($row['requested_url']) ? $row['requested_url'] : ''; |
| 270 |
if ($url !== '') { |
| 271 |
$urls[] = $url; |
| 272 |
} |
| 273 |
} |
| 274 |
return $urls; |
| 275 |
} |
| 276 |
|
| 277 |
/** |
| 278 |
* @param string $specificURL |
| 279 |
* @return array<int, array<string, mixed>> |
| 280 |
*/ |
| 281 |
function getLogsIDandURL($specificURL = '') { |
| 282 |
global $wpdb; |
| 283 |
$whereClause = ''; |
| 284 |
if ($specificURL != '') { |
| 285 |
// Strip invalid UTF-8 first — esc_sql does not validate UTF-8 and |
| 286 |
// bot-fed URLs deliver garbage bytes (Pattern 10). |
| 287 |
$specificURL = $this->f->sanitizeInvalidUTF8($specificURL); |
| 288 |
// Escape user input to prevent SQL injection |
| 289 |
$escapedURL = esc_sql($specificURL); |
| 290 |
$whereClause = "where requested_url = '" . $escapedURL . "'"; |
| 291 |
} |
| 292 |
|
| 293 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/getLogsIDandURL.sql"); |
| 294 |
$query = $this->f->str_replace('{where_clause_here}', $whereClause, $query); |
| 295 |
|
| 296 |
$results = $this->queryAndGetResults($query); |
| 297 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 298 |
|
| 299 |
return $rows; |
| 300 |
} |
| 301 |
|
| 302 |
/** |
| 303 |
* @param string $specificURL |
| 304 |
* @param string|int $limitResults |
| 305 |
* @return array<int, array<string, mixed>> |
| 306 |
*/ |
| 307 |
function getLogsIDandURLLike($specificURL, $limitResults) { |
| 308 |
global $wpdb; |
| 309 |
$whereClause = ''; |
| 310 |
if ($specificURL != '') { |
| 311 |
// Escape user input to prevent SQL injection |
| 312 |
// Use esc_like for LIKE queries, then add wildcards, then esc_sql for the full string. |
| 313 |
// esc_like escapes '%' and '_' so callers must pass the raw search term (no wildcards). |
| 314 |
$likePattern = '%' . $wpdb->esc_like($specificURL) . '%'; |
| 315 |
$escapedURL = esc_sql($likePattern); |
| 316 |
$whereClause = "where lower(requested_url) like lower('" . $escapedURL . "')\n"; |
| 317 |
$whereClause .= "and min_log_id = true"; |
| 318 |
} |
| 319 |
|
| 320 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/getLogsIDandURLForAjax.sql"); |
| 321 |
$query = $this->f->str_replace('{where_clause_here}', $whereClause, $query); |
| 322 |
$query = $this->f->str_replace('{limit-results}', 'limit ' . absint($limitResults), $query); |
| 323 |
|
| 324 |
$results = $this->queryAndGetResults($query); |
| 325 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 326 |
|
| 327 |
return $rows; |
| 328 |
} |
| 329 |
|
| 330 |
/** |
| 331 |
* @param array<string, mixed> $tableOptions orderby, paged, perpage, etc. |
| 332 |
* @return array<int, array<string, mixed>> rows from querying the logs table. |
| 333 |
*/ |
| 334 |
function getLogRecords($tableOptions) { |
| 335 |
$abj404logic = abj_service('plugin_logic'); |
| 336 |
|
| 337 |
$logsid_included = ''; |
| 338 |
$logsid = ''; |
| 339 |
$rawLogsId = $tableOptions['logsid']; |
| 340 |
if ($rawLogsId != 0) { |
| 341 |
$logsid_included = 'specific logs id included. */'; |
| 342 |
$logsid = esc_sql($abj404logic->sanitizeForSQL(is_scalar($rawLogsId) ? (string)$rawLogsId : '')); |
| 343 |
} |
| 344 |
|
| 345 |
// Perf audit F5: ORDER BY on logsv2 must use an indexed column, |
| 346 |
// otherwise large sites incur a full filesort that can exceed proxy |
| 347 |
// timeouts (logsv2 grows unbounded with 404 traffic). |
| 348 |
// Indexes on logsv2: PRIMARY (id), KEY timestamp, KEY requested_url, |
| 349 |
// KEY username, KEY min_log_id, KEY idx_requested_url_timestamp, |
| 350 |
// KEY idx_canonical_url. The `url` alias resolves to requested_url |
| 351 |
// in MySQL ORDER BY (SELECT aliases take precedence over JOIN columns). |
| 352 |
// Non-indexed columns previously accepted here (remote_host/user_ip, |
| 353 |
// referrer, dest_url/action, engine) and the bogus `logshits` (not |
| 354 |
// a column of logsv2) silently fall back to `timestamp`. |
| 355 |
// |
| 356 |
// Each entry maps the user-facing orderby name to the SQL expression |
| 357 |
// safe for ORDER BY in the assembled query. Qualify ambiguous columns |
| 358 |
// with the table placeholder so the LEFT JOIN on |
| 359 |
// {wp_abj404_lookup} (which also has `id`) cannot collide. `url` |
| 360 |
// resolves through the SELECT alias and stays unqualified. |
| 361 |
$orderbyExpressionByName = array( |
| 362 |
'timestamp' => '{wp_abj404_logsv2}.timestamp', |
| 363 |
'requested_url' => '{wp_abj404_logsv2}.requested_url', |
| 364 |
'url' => 'url', |
| 365 |
'id' => '{wp_abj404_logsv2}.id', |
| 366 |
'min_log_id' => '{wp_abj404_logsv2}.min_log_id', |
| 367 |
); |
| 368 |
$rawOrderByVal = $tableOptions['orderby']; |
| 369 |
$orderby = sanitize_text_field($abj404logic->sanitizeForSQL(is_string($rawOrderByVal) ? $rawOrderByVal : '')); |
| 370 |
$orderby = array_key_exists($orderby, $orderbyExpressionByName) ? $orderby : 'timestamp'; |
| 371 |
$orderbyExpression = $orderbyExpressionByName[$orderby]; |
| 372 |
|
| 373 |
// Whitelist allowed order directions |
| 374 |
$rawOrderVal2 = $tableOptions['order']; |
| 375 |
$order = strtoupper(sanitize_text_field($abj404logic->sanitizeForSQL(is_string($rawOrderVal2) ? $rawOrderVal2 : ''))); |
| 376 |
if (!in_array($order, array('ASC', 'DESC'), true)) { |
| 377 |
$order = 'DESC'; // Safe default |
| 378 |
} |
| 379 |
|
| 380 |
$paged = absint(is_scalar($tableOptions['paged'] ?? 1) ? ($tableOptions['paged'] ?? 1) : 1); |
| 381 |
if ($paged < 1) { |
| 382 |
$paged = 1; |
| 383 |
} |
| 384 |
$perpage = absint(is_scalar($tableOptions['perpage'] ?? ABJ404_OPTION_DEFAULT_PERPAGE) ? ($tableOptions['perpage'] ?? ABJ404_OPTION_DEFAULT_PERPAGE) : ABJ404_OPTION_DEFAULT_PERPAGE); |
| 385 |
if ($perpage < 1) { |
| 386 |
$perpage = ABJ404_OPTION_DEFAULT_PERPAGE; |
| 387 |
} |
| 388 |
$start = ($paged - 1) * $perpage; |
| 389 |
|
| 390 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/getLogRecords.sql"); |
| 391 |
$query = $this->f->str_replace('{logsid_included}', $logsid_included, $query); |
| 392 |
$query = $this->f->str_replace('{logsid}', $logsid, $query); |
| 393 |
$query = $this->f->str_replace('{orderby}', $orderbyExpression, $query); |
| 394 |
$query = $this->f->str_replace('{order}', $order, $query); |
| 395 |
$query = $this->f->str_replace('{start}', (string)$start, $query); |
| 396 |
$query = $this->f->str_replace('{perpage}', (string)$perpage, $query); |
| 397 |
|
| 398 |
$results = $this->queryAndGetResults($query); |
| 399 |
$rawRows = $results['rows']; |
| 400 |
return is_array($rawRows) ? $rawRows : array(); |
| 401 |
} |
| 402 |
|
| 403 |
/** |
| 404 |
* Privacy exporter/eraser support: fetch logsv2 IDs for a given lookup value (usually a username). |
| 405 |
* |
| 406 |
* @param string $lkupValue |
| 407 |
* @param int $page 1-based page |
| 408 |
* @param int $perPage |
| 409 |
* @return int[] |
| 410 |
*/ |
| 411 |
public function getLogsv2IdsForLookupValue($lkupValue, $page = 1, $perPage = 100) { |
| 412 |
global $wpdb; |
| 413 |
|
| 414 |
$lkupValue = trim($lkupValue); |
| 415 |
if ($lkupValue === '') { |
| 416 |
return array(); |
| 417 |
} |
| 418 |
|
| 419 |
$page = max(1, absint($page)); |
| 420 |
$perPage = max(1, min(500, absint($perPage))); |
| 421 |
$offset = ($page - 1) * $perPage; |
| 422 |
|
| 423 |
$logsTable = $this->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 424 |
$lookupTable = $this->doTableNameReplacements("{wp_abj404_lookup}"); |
| 425 |
|
| 426 |
$sql = "SELECT l.id |
| 427 |
FROM `{$logsTable}` l |
| 428 |
INNER JOIN `{$lookupTable}` u ON l.username = u.id |
| 429 |
WHERE u.lkup_value = %s |
| 430 |
ORDER BY l.id DESC |
| 431 |
LIMIT %d OFFSET %d"; |
| 432 |
|
| 433 |
// Route through queryAndGetResults() so this GDPR exporter join |
| 434 |
// inherits the centralized 60s timeout. The exporter runs in admin |
| 435 |
// request context (paginated by core), and an unbounded JOIN against |
| 436 |
// logsv2 could exceed reverse-proxy timeouts on large sites. |
| 437 |
$result = $this->queryAndGetResults($sql, array( |
| 438 |
'query_params' => array($lkupValue, $perPage, $offset), |
| 439 |
)); |
| 440 |
if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) { |
| 441 |
return array(); |
| 442 |
} |
| 443 |
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 444 |
|
| 445 |
$ids = array(); |
| 446 |
foreach ($rows as $row) { |
| 447 |
if (is_array($row) && isset($row['id'])) { |
| 448 |
$ids[] = absint($row['id']); |
| 449 |
} |
| 450 |
} |
| 451 |
return array_values(array_filter($ids)); |
| 452 |
} |
| 453 |
|
| 454 |
/** |
| 455 |
* Privacy exporter support: fetch logsv2 rows for a given lookup value (usually a username). |
| 456 |
* |
| 457 |
* @param string $lkupValue |
| 458 |
* @param int $page |
| 459 |
* @param int $perPage |
| 460 |
* @return array<int, array<string, mixed>> |
| 461 |
*/ |
| 462 |
public function getLogsv2RowsForLookupValue($lkupValue, $page = 1, $perPage = 50) { |
| 463 |
global $wpdb; |
| 464 |
|
| 465 |
$ids = $this->getLogsv2IdsForLookupValue($lkupValue, $page, $perPage); |
| 466 |
if (empty($ids)) { |
| 467 |
return array(); |
| 468 |
} |
| 469 |
|
| 470 |
$logsTable = $this->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 471 |
|
| 472 |
$placeholders = implode(',', array_fill(0, count($ids), '%d')); |
| 473 |
$sql = "SELECT id, timestamp, user_ip, referrer, requested_url, requested_url_detail, dest_url |
| 474 |
FROM `{$logsTable}` |
| 475 |
WHERE id IN ({$placeholders}) |
| 476 |
ORDER BY id DESC"; |
| 477 |
|
| 478 |
// Route through queryAndGetResults() so this exporter detail query |
| 479 |
// inherits the centralized 60s timeout. The IN(...) is bounded by |
| 480 |
// the page size from getLogsv2IdsForLookupValue, but a slow disk or |
| 481 |
// lock contention can still hang the request. |
| 482 |
$result = $this->queryAndGetResults($sql, array('query_params' => $ids)); |
| 483 |
if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) { |
| 484 |
return array(); |
| 485 |
} |
| 486 |
return is_array($result['rows'] ?? null) ? $result['rows'] : array(); |
| 487 |
} |
| 488 |
|
| 489 |
/** |
| 490 |
* Privacy eraser support: anonymize a set of logsv2 rows by IDs. |
| 491 |
* |
| 492 |
* We preserve non-user-identifying fields so site owners can still debug patterns, |
| 493 |
* while removing IP/username/referrer detail. |
| 494 |
* |
| 495 |
* @param int[] $ids |
| 496 |
* @return bool |
| 497 |
*/ |
| 498 |
public function anonymizeLogsv2RowsByIds($ids) { |
| 499 |
global $wpdb; |
| 500 |
|
| 501 |
if (!is_array($ids) || empty($ids)) { |
| 502 |
return true; |
| 503 |
} |
| 504 |
|
| 505 |
$ids = array_values(array_filter(array_map('absint', $ids))); |
| 506 |
if (empty($ids)) { |
| 507 |
return true; |
| 508 |
} |
| 509 |
|
| 510 |
$logsTable = $this->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 511 |
$placeholders = implode(',', array_fill(0, count($ids), '%d')); |
| 512 |
|
| 513 |
$sql = "UPDATE `{$logsTable}` |
| 514 |
SET user_ip = %s, |
| 515 |
referrer = NULL, |
| 516 |
requested_url_detail = NULL, |
| 517 |
username = NULL |
| 518 |
WHERE id IN ({$placeholders})"; |
| 519 |
|
| 520 |
$params = array_merge(array('(Anonymized)'), $ids); |
| 521 |
// Route through queryAndGetResults() so this GDPR eraser UPDATE |
| 522 |
// inherits the centralized timeout (MariaDB SET STATEMENT for non- |
| 523 |
// SELECT queries) and the standard retry/recovery handling. |
| 524 |
$result = $this->queryAndGetResults($sql, array('query_params' => $params)); |
| 525 |
if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) { |
| 526 |
return false; |
| 527 |
} |
| 528 |
return true; |
| 529 |
} |
| 530 |
|
| 531 |
/** |
| 532 |
* Log that a redirect was done. Insert into the logs table. |
| 533 |
* @param string $requested_url |
| 534 |
* @param string $action |
| 535 |
* @param string $matchReason |
| 536 |
* @param string|null $requestedURLDetail the exact URL that was requested, for cases when a regex URL was matched. |
| 537 |
* @param list<array{step: string, outcome: string, detail: string}>|null $pipelineTrace |
| 538 |
*/ |
| 539 |
function logRedirectHit(string $requested_url, string $action, string $matchReason, ?string $requestedURLDetail = null, ?array $pipelineTrace = null): void { |
| 540 |
global $wpdb; |
| 541 |
$abj404logic = abj_service('plugin_logic'); |
| 542 |
$logTableName = $this->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 543 |
|
| 544 |
$now = time(); |
| 545 |
|
| 546 |
// remove non-printable control characters while preserving valid multibyte Unicode |
| 547 |
$requested_url = preg_replace('/[\x00-\x1F\x7F]/u', '', $requested_url) ?? $requested_url; |
| 548 |
|
| 549 |
// Normalize to relative path before storing (Issue #24) |
| 550 |
$requested_url = $abj404logic->normalizeToRelativePath($requested_url); |
| 551 |
|
| 552 |
// If the database can't store utf8 URLs then URL-encode before saving (avoid insert errors). |
| 553 |
try { |
| 554 |
static $requestedUrlColumnMeta = null; |
| 555 |
|
| 556 |
if ($requestedUrlColumnMeta === null && function_exists('get_transient')) { |
| 557 |
$requestedUrlColumnMeta = get_transient('abj404_logs_requested_url_column_meta'); |
| 558 |
if ($requestedUrlColumnMeta === false) { |
| 559 |
$requestedUrlColumnMeta = null; |
| 560 |
} |
| 561 |
} |
| 562 |
|
| 563 |
// Backward compatibility: if only legacy charset transient exists, keep using it. |
| 564 |
if ($requestedUrlColumnMeta === null && function_exists('get_transient')) { |
| 565 |
$legacyCharset = get_transient('abj404_logs_requested_url_charset'); |
| 566 |
if (is_string($legacyCharset) && $legacyCharset !== '') { |
| 567 |
$requestedUrlColumnMeta = array( |
| 568 |
'charset_name' => $legacyCharset, |
| 569 |
'collation_name' => null, |
| 570 |
); |
| 571 |
} |
| 572 |
} |
| 573 |
|
| 574 |
$getCharsetQuery = $wpdb->prepare("SELECT character_set_name as charset_name, collation_name as collation_name \n " . |
| 575 |
"FROM information_schema.columns \n " . |
| 576 |
"WHERE lower(table_schema) = lower(%s) \n " . |
| 577 |
"AND lower(table_name) = lower(%s) \n " . |
| 578 |
"AND lower(column_name) = lower(%s) ", |
| 579 |
DB_NAME, $logTableName, 'requested_url'); |
| 580 |
|
| 581 |
if ($requestedUrlColumnMeta === null) { |
| 582 |
$resultArray = $wpdb->get_results($getCharsetQuery, ARRAY_A); |
| 583 |
// @cache-write-audit: opt-out — guarded by `!empty($resultArray)` below. |
| 584 |
// $wpdb->get_results returns null on error, and !empty(null) is false, |
| 585 |
// so a failed query never reaches the cache writes inside this block. |
| 586 |
if (!empty($resultArray)) { |
| 587 |
$requestedUrlColumnMeta = array( |
| 588 |
'charset_name' => $resultArray[0]['charset_name'] ?? $resultArray[0]['CHARSET_NAME'] ?? null, |
| 589 |
'collation_name' => $resultArray[0]['collation_name'] ?? $resultArray[0]['COLLATION_NAME'] ?? null, |
| 590 |
); |
| 591 |
if (function_exists('set_transient')) { |
| 592 |
$ttl = defined('WEEK_IN_SECONDS') ? WEEK_IN_SECONDS : 604800; |
| 593 |
set_transient('abj404_logs_requested_url_column_meta', $requestedUrlColumnMeta, $ttl); |
| 594 |
// Keep legacy key in sync for older code paths. |
| 595 |
if (!empty($requestedUrlColumnMeta['charset_name'])) { |
| 596 |
set_transient('abj404_logs_requested_url_charset', $requestedUrlColumnMeta['charset_name'], $ttl); |
| 597 |
} |
| 598 |
} |
| 599 |
} |
| 600 |
} |
| 601 |
|
| 602 |
$requestedUrlCharset = is_array($requestedUrlColumnMeta) ? ($requestedUrlColumnMeta['charset_name'] ?? null) : null; |
| 603 |
$requestedUrlCollation = is_array($requestedUrlColumnMeta) ? ($requestedUrlColumnMeta['collation_name'] ?? null) : null; |
| 604 |
|
| 605 |
if (!empty($requestedUrlCharset) && strpos(strtolower($requestedUrlCharset), 'utf8') === false) { |
| 606 |
$requested_url = $this->f->encodeUrlForLegacyMatch($requested_url); |
| 607 |
|
| 608 |
// Avoid spamming logs on every redirect hit. |
| 609 |
if (function_exists('get_transient') && function_exists('set_transient')) { |
| 610 |
$warnKey = 'abj404_warned_logs_charset_mismatch'; |
| 611 |
$warnVal = $logTableName . '|' . strtolower($requestedUrlCharset); |
| 612 |
$already = get_transient($warnKey); |
| 613 |
if ($already !== $warnVal) { |
| 614 |
$ttl = defined('WEEK_IN_SECONDS') ? WEEK_IN_SECONDS : 604800; |
| 615 |
// @cache-write-audit: opt-out — log-spam dedup marker, not a |
| 616 |
// query result. The cached value is the table+charset signature |
| 617 |
// we have already warned about; re-warning is harmless if the |
| 618 |
// transient is wrong. |
| 619 |
set_transient($warnKey, $warnVal, $ttl); |
| 620 |
$this->logger->warn("Logs table column charset is '{$requestedUrlCharset}' for {$logTableName}. URL-encoding stored requested URLs to avoid charset issues."); |
| 621 |
} |
| 622 |
} |
| 623 |
} |
| 624 |
} catch (Exception $e) { |
| 625 |
// not so important. |
| 626 |
$this->logger->debugMessage(__FUNCTION__ . |
| 627 |
" error. Issue getting character set for table: " . $logTableName . |
| 628 |
", column: requested_url. Error message: " . $e->getMessage()); |
| 629 |
} |
| 630 |
|
| 631 |
// no nonce here because redirects are not user generated. |
| 632 |
|
| 633 |
$options = $abj404logic->getOptions(true); |
| 634 |
$referer = wp_get_referer(); |
| 635 |
if ($referer !== null && $referer !== false) { |
| 636 |
$referer = esc_url_raw($referer); |
| 637 |
// this length matches the maximum length of the data field on the logs table. |
| 638 |
$referer = substr($referer, 0, 512); |
| 639 |
} else { |
| 640 |
$referer = ''; |
| 641 |
} |
| 642 |
$current_user = ABJ_404_Solution_UserRef::fromWpUser(wp_get_current_user()); |
| 643 |
$current_user_name = $current_user !== null ? $current_user->getLogin() : ''; |
| 644 |
$ipAddressToSave = is_string($_SERVER['REMOTE_ADDR'] ?? '') ? (string)$_SERVER['REMOTE_ADDR'] : ''; |
| 645 |
$ipAddressToSave = filter_var($ipAddressToSave, FILTER_VALIDATE_IP) ? |
| 646 |
esc_sql($ipAddressToSave) : ''; |
| 647 |
if (!array_key_exists('log_raw_ips', $options) || $options['log_raw_ips'] != '1') { |
| 648 |
$ipAddressToSave = $this->f->md5lastOctet($ipAddressToSave); |
| 649 |
} |
| 650 |
if (!empty($ipAddressToSave)) { |
| 651 |
$ipAddressToSave = substr($ipAddressToSave, 0, 512); |
| 652 |
} else { |
| 653 |
$ipAddressToSave = '(Unknown)'; |
| 654 |
} |
| 655 |
|
| 656 |
// we have to know what to set for the $minLogID value |
| 657 |
$minLogID = false; |
| 658 |
$comparisonCollation = $this->sanitizeCollationIdentifier(isset($requestedUrlCollation) ? (string)$requestedUrlCollation : ''); |
| 659 |
if ($comparisonCollation === '' || stripos($comparisonCollation, 'utf8mb4') === false) { |
| 660 |
$comparisonCollation = $this->getPreferredUtf8mb4Collation(); |
| 661 |
} |
| 662 |
$requestedUrlCharsetLower = isset($requestedUrlCharset) ? strtolower((string)$requestedUrlCharset) : ''; |
| 663 |
$canUseUtf8Cast = ($requestedUrlCharsetLower === '' || strpos($requestedUrlCharsetLower, 'utf8') !== false); |
| 664 |
// Route through queryAndGetResults() so this per-404-hit lookup |
| 665 |
// inherits the centralized 60s timeout. The CAST(... AS CHAR) form |
| 666 |
// can bypass the requested_url index on some schemas, turning into a |
| 667 |
// full table scan on huge logsv2 tables — a hot-path slow query |
| 668 |
// would block the 404 page render itself. |
| 669 |
if ($canUseUtf8Cast) { |
| 670 |
$checkMinIDSql = "SELECT id FROM `" . $logTableName . "` \n " . |
| 671 |
"WHERE CAST(requested_url AS CHAR CHARACTER SET utf8mb4) COLLATE " . $comparisonCollation . " = %s \n " . |
| 672 |
"LIMIT 1"; |
| 673 |
} else { |
| 674 |
$checkMinIDSql = "SELECT id FROM `" . $logTableName . "` \n " . |
| 675 |
"WHERE requested_url = %s \n " . |
| 676 |
"LIMIT 1"; |
| 677 |
} |
| 678 |
$primaryResult = $this->queryAndGetResults( |
| 679 |
$checkMinIDSql, |
| 680 |
array('query_params' => array($requested_url), 'log_errors' => false) |
| 681 |
); |
| 682 |
$checkMinIDQueryResults = is_array($primaryResult['rows'] ?? null) ? $primaryResult['rows'] : array(); |
| 683 |
$lastErrorRaw = $primaryResult['last_error'] ?? ''; |
| 684 |
$lastError = is_string($lastErrorRaw) ? $lastErrorRaw : ''; |
| 685 |
if ($lastError !== '' && $this->isInvalidDataError($lastError) && $canUseUtf8Cast) { |
| 686 |
$fallbackResult = $this->queryAndGetResults( |
| 687 |
"SELECT id FROM `" . $logTableName . "` \n WHERE requested_url = %s \n LIMIT 1", |
| 688 |
array('query_params' => array($requested_url), 'log_errors' => false) |
| 689 |
); |
| 690 |
$checkMinIDQueryResults = is_array($fallbackResult['rows'] ?? null) ? $fallbackResult['rows'] : array(); |
| 691 |
} |
| 692 |
|
| 693 |
if (empty($checkMinIDQueryResults)) { |
| 694 |
$minLogID = true; |
| 695 |
} |
| 696 |
|
| 697 |
// extra escaping suggestions from chatgpt |
| 698 |
// Don't escape "404" as a URL since it's not a URL, it's a status indicator |
| 699 |
if (trim($action) != "404") { |
| 700 |
$action = esc_url_raw($action); |
| 701 |
} |
| 702 |
|
| 703 |
// ------------ debug message begin |
| 704 |
$helperFunctions = abj_service('functions'); |
| 705 |
$reasonMessage = trim(implode(", ", |
| 706 |
array_filter( |
| 707 |
array(abj_service('request_context')->ignore_doprocess ?: '', |
| 708 |
abj_service('request_context')->ignore_donotprocess ?: '')))); |
| 709 |
$permalinksKept = '(not set)'; |
| 710 |
$ctx = abj_service('request_context'); |
| 711 |
if ($this->logger->isDebug() && !empty($ctx->permalinks_found)) { |
| 712 |
$permalinksKept = $ctx->permalinks_kept; |
| 713 |
} |
| 714 |
$this->logger->debugMessage("Logging redirect. Referer: " . esc_html($referer) . |
| 715 |
" | Current user: " . $current_user_name . " | From: " . $helperFunctions->normalizeUrlString($_SERVER['REQUEST_URI']) . |
| 716 |
esc_html(" to: ") . esc_html($action) . ', Reason: ' . $matchReason . ", Ignore msg(s): " . |
| 717 |
$reasonMessage . ', Execution time: ' . round((float)$helperFunctions->getExecutionTime(), 2) . |
| 718 |
' seconds, permalinks found: ' . $permalinksKept); |
| 719 |
// ------------ debug message end |
| 720 |
|
| 721 |
// insert the username into the lookup table and get the ID from the lookup table. |
| 722 |
$usernameLookupID = $this->insertLookupValueAndGetID($current_user_name); |
| 723 |
|
| 724 |
// Queue the log entry for batch INSERT at shutdown. |
| 725 |
// canonical_url is included so it's part of validatedColumns at flush |
| 726 |
// time (which is derived from the FIRST queued entry's keys, not from |
| 727 |
// sanitizeLogEntry's output — adding it later would still be sanitized |
| 728 |
// but never reach the INSERT column list). |
| 729 |
$reqUrlForLog = esc_url_raw($requested_url); |
| 730 |
$reqUrlForLogStr = is_string($reqUrlForLog) ? $reqUrlForLog : ''; |
| 731 |
$this->queueLogEntry([ |
| 732 |
'timestamp' => $now, |
| 733 |
'user_ip' => $ipAddressToSave, |
| 734 |
'referrer' => $referer, |
| 735 |
'dest_url' => $action, |
| 736 |
'requested_url' => $reqUrlForLogStr, |
| 737 |
'requested_url_detail' => $requestedURLDetail, |
| 738 |
'username' => $usernameLookupID, |
| 739 |
'min_log_id' => $minLogID, |
| 740 |
'engine' => substr($matchReason, 0, 64), |
| 741 |
'pipeline_trace' => $this->serializePipelineTrace($pipelineTrace), |
| 742 |
'canonical_url' => '/' . trim($reqUrlForLogStr, '/'), |
| 743 |
]); |
| 744 |
} |
| 745 |
|
| 746 |
/** |
| 747 |
* Queue a log entry for batch INSERT at shutdown. |
| 748 |
* Registers shutdown hook on first entry. |
| 749 |
* |
| 750 |
* @param array<string, mixed> $entry Log entry data |
| 751 |
*/ |
| 752 |
function queueLogEntry(array $entry): void { |
| 753 |
self::$logQueue[] = $entry; |
| 754 |
|
| 755 |
// Register shutdown hook on first entry only |
| 756 |
if (!self::$shutdownHookRegistered) { |
| 757 |
self::$shutdownHookRegistered = true; |
| 758 |
// Slightly earlier than default (10) to reduce chance other shutdown handlers poison the DB connection. |
| 759 |
add_action('shutdown', [$this, 'flushLogQueue'], 9); |
| 760 |
} |
| 761 |
} |
| 762 |
|
| 763 |
/** |
| 764 |
* Flush queued log entries with a batch INSERT. |
| 765 |
* Called automatically at shutdown. |
| 766 |
*/ |
| 767 |
function flushLogQueue(): void { |
| 768 |
if (self::$isFlushingLogQueue) { |
| 769 |
return; |
| 770 |
} |
| 771 |
self::$isFlushingLogQueue = true; |
| 772 |
if (empty(self::$logQueue)) { |
| 773 |
// Reset shutdown hook flag for next request (persistent hosting protection) |
| 774 |
self::$shutdownHookRegistered = false; |
| 775 |
self::$isFlushingLogQueue = false; |
| 776 |
return; |
| 777 |
} |
| 778 |
|
| 779 |
global $wpdb; |
| 780 |
$tableName = $this->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 781 |
|
| 782 |
// Get column names from first entry and validate as safe SQL identifiers |
| 783 |
$columns = array_keys(self::$logQueue[0]); |
| 784 |
$validatedColumns = []; |
| 785 |
foreach ($columns as $col) { |
| 786 |
// Validate column name is a safe SQL identifier (alphanumeric + underscore) |
| 787 |
if (preg_match('/^[a-z_][a-z0-9_]*$/i', $col)) { |
| 788 |
$validatedColumns[] = $col; |
| 789 |
} |
| 790 |
} |
| 791 |
|
| 792 |
// Schema drift tolerance: filter out columns that don't exist in the actual |
| 793 |
// table. Old installations may lack columns added in newer versions (e.g. 'engine'). |
| 794 |
$schemaColumns = $this->getTableColumnNames($tableName); |
| 795 |
if (!empty($schemaColumns)) { |
| 796 |
$validatedColumns = array_intersect($validatedColumns, $schemaColumns); |
| 797 |
} |
| 798 |
|
| 799 |
if (empty($validatedColumns)) { |
| 800 |
// No valid columns - clear queue and reset flag |
| 801 |
self::$logQueue = []; |
| 802 |
self::$shutdownHookRegistered = false; |
| 803 |
self::$isFlushingLogQueue = false; |
| 804 |
return; |
| 805 |
} |
| 806 |
|
| 807 |
$columnList = '`' . implode('`, `', $validatedColumns) . '`'; |
| 808 |
|
| 809 |
// Build VALUES for each entry with proper validation |
| 810 |
$valuesSets = []; |
| 811 |
$sanitizedEntries = []; |
| 812 |
foreach (self::$logQueue as $entry) { |
| 813 |
// Detect complex types early (kept for legacy test expectations). |
| 814 |
foreach ($entry as $val) { |
| 815 |
if (is_object($val) || is_array($val)) { |
| 816 |
// Handled in sanitizeLogEntry (converted to NULL) |
| 817 |
break; |
| 818 |
} |
| 819 |
} |
| 820 |
// Validate entry has same structure as first entry |
| 821 |
$entryColumns = array_keys($entry); |
| 822 |
$missingCols = array_diff($validatedColumns, $entryColumns); |
| 823 |
if (!empty($missingCols)) { |
| 824 |
// Skip entries with missing columns to prevent data corruption |
| 825 |
continue; |
| 826 |
} |
| 827 |
|
| 828 |
$sanitized = $this->sanitizeLogEntry($entry); |
| 829 |
if ($sanitized === null) { |
| 830 |
continue; |
| 831 |
} |
| 832 |
|
| 833 |
$sanitizedEntries[] = $sanitized; |
| 834 |
} |
| 835 |
|
| 836 |
if (empty($sanitizedEntries)) { |
| 837 |
// No valid entries - clear queue and reset flag |
| 838 |
self::$logQueue = []; |
| 839 |
self::$shutdownHookRegistered = false; |
| 840 |
self::$isFlushingLogQueue = false; |
| 841 |
return; |
| 842 |
} |
| 843 |
|
| 844 |
// Build placeholder-based batch insert with IGNORE to tolerate duplicates |
| 845 |
$formats = []; |
| 846 |
$flattenedValues = []; |
| 847 |
foreach ($sanitizedEntries as $entry) { |
| 848 |
$rowFormats = []; |
| 849 |
foreach ($validatedColumns as $col) { |
| 850 |
$value = $entry[$col]; |
| 851 |
if ($value === null) { |
| 852 |
$rowFormats[] = 'NULL'; |
| 853 |
continue; |
| 854 |
} |
| 855 |
if (is_int($value)) { |
| 856 |
$rowFormats[] = '%d'; |
| 857 |
} else { |
| 858 |
$rowFormats[] = '%s'; |
| 859 |
} |
| 860 |
$flattenedValues[] = $value; |
| 861 |
} |
| 862 |
$formats[] = '(' . implode(', ', $rowFormats) . ')'; |
| 863 |
} |
| 864 |
|
| 865 |
$sql = "INSERT IGNORE INTO `{$tableName}` ({$columnList}) VALUES " . implode(', ', $formats); |
| 866 |
$prepared = $wpdb->prepare($sql, $flattenedValues); |
| 867 |
|
| 868 |
// Execute batch INSERT |
| 869 |
$wpdb->flush(); |
| 870 |
$result = $wpdb->query($prepared); |
| 871 |
|
| 872 |
// Check for errors - if batch insert fails, try individual inserts |
| 873 |
if ($result === false && !empty($wpdb->last_error)) { |
| 874 |
$batchError = $wpdb->last_error; |
| 875 |
|
| 876 |
// Auto-trim oldest log entries when the log table is full (errno 1114 "table is full"). |
| 877 |
// Rate-limited to once per hour to avoid thrashing on a genuinely full disk. |
| 878 |
if ($this->isTableFullError($batchError)) { |
| 879 |
$trimmed = $this->autoTrimLogsv2IfNeeded($tableName, $batchError); |
| 880 |
if ($trimmed) { |
| 881 |
// Retry the INSERT after freeing space. |
| 882 |
/** @var \wpdb $wpdb */ |
| 883 |
$wpdb->flush(); |
| 884 |
$retryResult = $wpdb->query($prepared); |
| 885 |
if ($retryResult !== false) { |
| 886 |
self::$logQueue = []; |
| 887 |
self::$shutdownHookRegistered = false; |
| 888 |
self::$isFlushingLogQueue = false; |
| 889 |
return; |
| 890 |
} |
| 891 |
$batchError = $wpdb->last_error; |
| 892 |
} |
| 893 |
// Still failing after trim (or trim rate-limited): surface admin notice. |
| 894 |
$this->setLogsv2FullNotice($batchError); |
| 895 |
} |
| 896 |
|
| 897 |
// Attempt a one-time recovery for known connection-state issues (e.g., "Commands out of sync"). |
| 898 |
if ($this->isCommandsOutOfSyncError($batchError)) { |
| 899 |
$isolated = $this->getIsolatedWpdb(); |
| 900 |
if ($isolated !== null) { |
| 901 |
$isolated->flush(); |
| 902 |
/** @var literal-string $sql */ |
| 903 |
$isolatedPrepared = $isolated->prepare($sql, $flattenedValues); |
| 904 |
$isolatedResult = $isolated->query($isolatedPrepared !== null ? $isolatedPrepared : $sql); |
| 905 |
if ($isolatedResult !== false) { |
| 906 |
// Clear queue and reset flag for next request |
| 907 |
self::$logQueue = []; |
| 908 |
self::$shutdownHookRegistered = false; |
| 909 |
self::$isFlushingLogQueue = false; |
| 910 |
$context = $this->getWpdbRecentQueryContextForLogs(); |
| 911 |
$suffix = ($context !== '') ? " | savequeries_context={$context}" : ''; |
| 912 |
$this->logger->warn("flushLogQueue batch INSERT succeeded using isolated DB connection (commands out of sync on shared connection).{$suffix}"); |
| 913 |
return; |
| 914 |
} |
| 915 |
$batchError .= " | isolated_error=" . $isolated->last_error; |
| 916 |
} else { |
| 917 |
$batchError .= " | isolated_error=no_isolated_connection"; |
| 918 |
} |
| 919 |
} |
| 920 |
|
| 921 |
// Retry each entry individually to salvage what we can |
| 922 |
$successCount = 0; |
| 923 |
$failCount = 0; |
| 924 |
$failureDetails = []; |
| 925 |
foreach ($sanitizedEntries as $index => $entry) { |
| 926 |
$rowFormats = []; |
| 927 |
$rowValues = []; |
| 928 |
foreach ($validatedColumns as $col) { |
| 929 |
$value = $entry[$col]; |
| 930 |
if ($value === null) { |
| 931 |
$rowFormats[] = 'NULL'; |
| 932 |
} else { |
| 933 |
$rowFormats[] = is_int($value) ? '%d' : '%s'; |
| 934 |
$rowValues[] = $value; |
| 935 |
} |
| 936 |
} |
| 937 |
$rowPlaceholder = '(' . implode(', ', $rowFormats) . ')'; |
| 938 |
/** @var literal-string $singleSqlTemplate */ |
| 939 |
$singleSqlTemplate = "INSERT IGNORE INTO `{$tableName}` ({$columnList}) VALUES {$rowPlaceholder}"; |
| 940 |
/** @var wpdb $wpdb */ |
| 941 |
$singleSql = $wpdb->prepare($singleSqlTemplate, $rowValues); |
| 942 |
$wpdb->flush(); |
| 943 |
$singleResult = $wpdb->query((string)$singleSql); |
| 944 |
|
| 945 |
if ($singleResult === false && !empty($wpdb->last_error)) { |
| 946 |
$lastError = $wpdb->last_error; |
| 947 |
|
| 948 |
// One retry on known connection-state errors. |
| 949 |
if ($this->isCommandsOutOfSyncError($wpdb->last_error)) { |
| 950 |
/** @var wpdb|null $isolated */ |
| 951 |
$isolated = $this->getIsolatedWpdb(); |
| 952 |
if ($isolated !== null) { |
| 953 |
$isolated->flush(); |
| 954 |
/** @var literal-string $singleSqlTemplate */ |
| 955 |
$isolatedSingleSql = $isolated->prepare($singleSqlTemplate, $rowValues); |
| 956 |
$isolatedSingleResult = $isolated->query((string)$isolatedSingleSql); |
| 957 |
if ($isolatedSingleResult !== false) { |
| 958 |
$successCount++; |
| 959 |
continue; |
| 960 |
} |
| 961 |
$lastError = $lastError . " | isolated_error=" . $isolated->last_error; |
| 962 |
} else { |
| 963 |
$lastError = $lastError . " | isolated_error=no_isolated_connection"; |
| 964 |
} |
| 965 |
} |
| 966 |
|
| 967 |
$failCount++; |
| 968 |
$payload = function_exists('wp_json_encode') ? wp_json_encode($entry) : json_encode($entry); |
| 969 |
if (is_string($payload) && strlen($payload) > 1024) { |
| 970 |
$payload = substr($payload, 0, 1024) . '...'; |
| 971 |
} |
| 972 |
$failureDetails[] = [ |
| 973 |
'index' => $index, |
| 974 |
'error' => $lastError, |
| 975 |
'payload' => $payload, |
| 976 |
]; |
| 977 |
} else { |
| 978 |
$successCount++; |
| 979 |
} |
| 980 |
} |
| 981 |
|
| 982 |
if ($failCount > 0) { |
| 983 |
$detailsParts = []; |
| 984 |
$maxDetails = 3; |
| 985 |
foreach (array_slice($failureDetails, 0, $maxDetails) as $detail) { |
| 986 |
$detailsParts[] = "entry {$detail['index']}: {$detail['error']} | payload={$detail['payload']}"; |
| 987 |
} |
| 988 |
$detailsSuffix = ''; |
| 989 |
if (count($failureDetails) > $maxDetails) { |
| 990 |
$detailsSuffix = ' | (additional failures omitted)'; |
| 991 |
} |
| 992 |
|
| 993 |
// Use a single ERROR line so email summaries include the actual DB error(s). |
| 994 |
$context = $this->getWpdbRecentQueryContextForLogs(); |
| 995 |
$contextSuffix = ($context !== '') ? (" | savequeries_context=" . $context) : ''; |
| 996 |
// Pattern 7 (defense-in-depth): the bespoke recovery above |
| 997 |
// handles "table is full" + "commands out of sync" only. If |
| 998 |
// $batchError is a different infra cause (disk full, read-only, |
| 999 |
// crashed table, lock timeout, ...) classify it so the user |
| 1000 |
// gets a plugin-page admin notice rather than a dev email |
| 1001 |
// alone. classifyAndHandleInfrastructureError logs at WARN |
| 1002 |
// and returns true for matched infra causes; in that case we |
| 1003 |
// skip the errorMessage to avoid double-reporting (and to |
| 1004 |
// honor rule 8: hosting issues never trigger email reports). |
| 1005 |
if ($this->classifyAndHandleInfrastructureError($batchError)) { |
| 1006 |
$this->logger->warn( |
| 1007 |
"flushLogQueue recovery incomplete: {$successCount} inserted, {$failCount} failed." . |
| 1008 |
" | batch_error=" . $batchError . |
| 1009 |
" | failures=" . implode(' || ', $detailsParts) . $detailsSuffix . |
| 1010 |
$contextSuffix |
| 1011 |
); |
| 1012 |
} else { |
| 1013 |
$this->logger->errorMessage( |
| 1014 |
"flushLogQueue recovery incomplete: {$successCount} inserted, {$failCount} failed." . |
| 1015 |
" | batch_error=" . $batchError . |
| 1016 |
" | failures=" . implode(' || ', $detailsParts) . $detailsSuffix . |
| 1017 |
$contextSuffix |
| 1018 |
); |
| 1019 |
} |
| 1020 |
} else { |
| 1021 |
// Batch insert failure was recovered; don't escalate as an error. |
| 1022 |
$this->logger->warn("flushLogQueue batch INSERT failed but recovered: all {$successCount} entries inserted individually. | batch_error=" . $batchError); |
| 1023 |
} |
| 1024 |
} |
| 1025 |
|
| 1026 |
// Clear queue and reset flag for next request |
| 1027 |
self::$logQueue = []; |
| 1028 |
self::$shutdownHookRegistered = false; |
| 1029 |
self::$isFlushingLogQueue = false; |
| 1030 |
} |
| 1031 |
|
| 1032 |
private function isCommandsOutOfSyncError(string $error): bool { |
| 1033 |
return stripos($error, 'commands out of sync') !== false; |
| 1034 |
} |
| 1035 |
|
| 1036 |
/** @param string $error @return bool */ |
| 1037 |
private function isTableFullError(string $error): bool { |
| 1038 |
$lower = strtolower($error); |
| 1039 |
return stripos($lower, 'is full') !== false || stripos($lower, 'table full') !== false; |
| 1040 |
} |
| 1041 |
|
| 1042 |
/** |
| 1043 |
* Delete the oldest 1000 rows from logsv2 to free space, rate-limited to once per hour. |
| 1044 |
* |
| 1045 |
* @param string $tableName Fully-resolved logsv2 table name. |
| 1046 |
* @param string $errorMessage The error that triggered the call. |
| 1047 |
* @return bool True if a trim was attempted (regardless of success), false if rate-limited. |
| 1048 |
*/ |
| 1049 |
private function autoTrimLogsv2IfNeeded(string $tableName, string $errorMessage): bool { |
| 1050 |
// Defense-in-depth: verify the table name is valid before using in SQL |
| 1051 |
if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName) || strpos($tableName, 'abj404_logsv2') === false) { |
| 1052 |
$this->logger->warn("autoTrimLogsv2IfNeeded: rejected unexpected table name: " . substr($tableName, 0, 100)); |
| 1053 |
return false; |
| 1054 |
} |
| 1055 |
|
| 1056 |
$cooldownKey = 'abj404_logsv2_trim_cooldown_until'; |
| 1057 |
$alreadyTrimmed = function_exists('get_transient') ? get_transient($cooldownKey) : false; |
| 1058 |
if ($alreadyTrimmed) { |
| 1059 |
return false; |
| 1060 |
} |
| 1061 |
|
| 1062 |
global $wpdb; |
| 1063 |
// ORDER BY ensures we delete oldest first. LIMIT keeps the DELETE bounded. |
| 1064 |
$trimSql = "DELETE FROM `{$tableName}` ORDER BY timestamp ASC LIMIT 1000"; |
| 1065 |
$wpdb->query($trimSql); |
| 1066 |
|
| 1067 |
$ttl = defined('HOUR_IN_SECONDS') ? (int) HOUR_IN_SECONDS : 3600; |
| 1068 |
if (function_exists('set_transient')) { |
| 1069 |
// @cache-write-audit: opt-out — rate-limit cooldown timestamp, not a |
| 1070 |
// query result. The cached value (1) is a sentinel meaning "auto-trim |
| 1071 |
// attempted within the last hour"; we want it written even if the |
| 1072 |
// DELETE failed so we do not retry immediately and pile on the disk. |
| 1073 |
set_transient($cooldownKey, 1, $ttl); |
| 1074 |
} |
| 1075 |
|
| 1076 |
if (!empty($wpdb->last_error)) { |
| 1077 |
$this->logger->warn("Log table full — auto-trim failed: " . $wpdb->last_error); |
| 1078 |
} else { |
| 1079 |
$this->logger->warn("Log table full — auto-trimmed 1000 oldest entries to free space."); |
| 1080 |
} |
| 1081 |
return true; |
| 1082 |
} |
| 1083 |
|
| 1084 |
/** @param string $errorMessage @return void */ |
| 1085 |
private function setLogsv2FullNotice(string $errorMessage): void { |
| 1086 |
$message = $this->localizeOrDefault( |
| 1087 |
'The 404 Solution log table is full and cannot accept new entries. This is usually caused by a full disk. Please contact your host or manually prune the logs table.'); |
| 1088 |
$this->setPluginDbNotice('log_table_full', $message, $errorMessage); |
| 1089 |
} |
| 1090 |
|
| 1091 |
/** |
| 1092 |
* Create an isolated DB connection (separate from the shared $wpdb connection). |
| 1093 |
* This avoids failures caused by other code leaving the shared mysqli connection in a bad state. |
| 1094 |
*/ |
| 1095 |
private function getIsolatedWpdb(): ?wpdb { |
| 1096 |
static $isolated = null; |
| 1097 |
|
| 1098 |
if ($isolated !== null) { |
| 1099 |
return $isolated; |
| 1100 |
} |
| 1101 |
if (!class_exists('wpdb')) { |
| 1102 |
return null; |
| 1103 |
} |
| 1104 |
if (!defined('DB_USER') || !defined('DB_PASSWORD') || !defined('DB_NAME') || !defined('DB_HOST')) { |
| 1105 |
// Per-request warn once: silently returning null here is the |
| 1106 |
// exact pattern the error-swallow audit flagged as Smell 1 — |
| 1107 |
// tests that don't set the constants exercise this fallback |
| 1108 |
// branch and never the real one. |
| 1109 |
static $warnedNoDbConsts = false; |
| 1110 |
if (!$warnedNoDbConsts) { |
| 1111 |
$warnedNoDbConsts = true; |
| 1112 |
$this->logger->warn(__METHOD__ . ': DB_USER/DB_PASSWORD/DB_NAME/DB_HOST undefined; isolated wpdb unavailable'); |
| 1113 |
} |
| 1114 |
return null; |
| 1115 |
} |
| 1116 |
|
| 1117 |
// phpcs:ignore WordPress.DB.RestrictedClasses.mysql__wpdb |
| 1118 |
$isolated = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST); |
| 1119 |
$isolated->show_errors(false); |
| 1120 |
$isolated->suppress_errors(true); |
| 1121 |
|
| 1122 |
return $isolated; |
| 1123 |
} |
| 1124 |
|
| 1125 |
/** |
| 1126 |
* If WordPress query recording is already enabled (SAVEQUERIES), return a safe summary |
| 1127 |
* of recent DB callers to help identify the component that poisoned the shared connection. |
| 1128 |
* |
| 1129 |
* Returns empty string when SAVEQUERIES isn't enabled. |
| 1130 |
*/ |
| 1131 |
private function getWpdbRecentQueryContextForLogs(): string { |
| 1132 |
global $wpdb; |
| 1133 |
if (!isset($wpdb) || !is_object($wpdb)) { |
| 1134 |
return ''; |
| 1135 |
} |
| 1136 |
if (!defined('SAVEQUERIES') || SAVEQUERIES !== true) { |
| 1137 |
return ''; |
| 1138 |
} |
| 1139 |
if (empty($wpdb->queries) || !is_array($wpdb->queries)) { |
| 1140 |
return ''; |
| 1141 |
} |
| 1142 |
|
| 1143 |
$recent = array_slice($wpdb->queries, -5); |
| 1144 |
$parts = []; |
| 1145 |
foreach ($recent as $q) { |
| 1146 |
$sql = $q[0] ?? ''; |
| 1147 |
$time = $q[1] ?? null; |
| 1148 |
$caller = $q[2] ?? ''; |
| 1149 |
$hash = is_string($sql) ? substr(sha1($sql), 0, 10) : 'n/a'; |
| 1150 |
$who = $this->extractWpComponentFromString(is_string($caller) ? $caller : ''); |
| 1151 |
$t = is_numeric($time) ? round((float)$time, 3) : 'n/a'; |
| 1152 |
$parts[] = "{$who}:{$hash}@{$t}"; |
| 1153 |
} |
| 1154 |
return implode(', ', $parts); |
| 1155 |
} |
| 1156 |
|
| 1157 |
private function extractWpComponentFromString(string $text): string { |
| 1158 |
$normalized = str_replace('\\', '/', $text); |
| 1159 |
|
| 1160 |
$pos = strpos($normalized, '/wp-content/mu-plugins/'); |
| 1161 |
if ($pos !== false) { |
| 1162 |
$rest = substr($normalized, $pos + strlen('/wp-content/mu-plugins/')); |
| 1163 |
$name = explode('/', ltrim($rest, '/'))[0] ?? ''; |
| 1164 |
return $name !== '' ? "mu-plugin:{$name}" : 'mu-plugin:unknown'; |
| 1165 |
} |
| 1166 |
|
| 1167 |
$pos = strpos($normalized, '/wp-content/plugins/'); |
| 1168 |
if ($pos !== false) { |
| 1169 |
$rest = substr($normalized, $pos + strlen('/wp-content/plugins/')); |
| 1170 |
$name = explode('/', ltrim($rest, '/'))[0] ?? ''; |
| 1171 |
return $name !== '' ? "plugin:{$name}" : 'plugin:unknown'; |
| 1172 |
} |
| 1173 |
|
| 1174 |
$pos = strpos($normalized, '/wp-content/themes/'); |
| 1175 |
if ($pos !== false) { |
| 1176 |
$rest = substr($normalized, $pos + strlen('/wp-content/themes/')); |
| 1177 |
$name = explode('/', ltrim($rest, '/'))[0] ?? ''; |
| 1178 |
return $name !== '' ? "theme:{$name}" : 'theme:unknown'; |
| 1179 |
} |
| 1180 |
|
| 1181 |
// Caller strings are often like "require_once('...')" or "SomeClass->method", so we keep it generic. |
| 1182 |
return 'unknown'; |
| 1183 |
} |
| 1184 |
|
| 1185 |
/** |
| 1186 |
* Validate and sanitize a log entry before insertion. |
| 1187 |
* Returns sanitized array or null if invalid. |
| 1188 |
* @param array<string, mixed> $entry |
| 1189 |
* @return array<string, mixed>|null |
| 1190 |
*/ |
| 1191 |
private function sanitizeLogEntry(array $entry): ?array { |
| 1192 |
// Required fields |
| 1193 |
$required = array('timestamp', 'user_ip', 'referrer', 'dest_url', 'requested_url', 'requested_url_detail', 'username', 'min_log_id', 'engine'); |
| 1194 |
foreach ($required as $key) { |
| 1195 |
if (!array_key_exists($key, $entry)) { |
| 1196 |
return null; |
| 1197 |
} |
| 1198 |
} |
| 1199 |
|
| 1200 |
$normalizeString = function($value, $maxLen) { |
| 1201 |
if (is_object($value) || is_array($value)) { |
| 1202 |
return null; |
| 1203 |
} |
| 1204 |
$str = (string)$value; |
| 1205 |
// Strip invalid UTF-8 sequences that cause "invalid data" SQL errors. |
| 1206 |
if (function_exists('mb_convert_encoding')) { |
| 1207 |
$str = mb_convert_encoding($str, 'UTF-8', 'UTF-8'); |
| 1208 |
} |
| 1209 |
return substr($str, 0, $maxLen); |
| 1210 |
}; |
| 1211 |
|
| 1212 |
$sanitized = array(); |
| 1213 |
|
| 1214 |
$tsVal = $entry['timestamp'] ?? time(); |
| 1215 |
$sanitized['timestamp'] = absint(is_scalar($tsVal) ? $tsVal : time()); |
| 1216 |
$sanitized['user_ip'] = $normalizeString($entry['user_ip'], 512); |
| 1217 |
$sanitized['referrer'] = $normalizeString($entry['referrer'], 512); |
| 1218 |
$sanitized['dest_url'] = $normalizeString($entry['dest_url'], 512); |
| 1219 |
|
| 1220 |
// Enforce lengths on URL fields (match schema) |
| 1221 |
$sanitized['requested_url'] = $normalizeString($entry['requested_url'], 2048); |
| 1222 |
$sanitized['requested_url_detail'] = $normalizeString($entry['requested_url_detail'], 2048); |
| 1223 |
|
| 1224 |
// canonical_url: PHP equivalent of CONCAT('/', TRIM(BOTH '/' FROM requested_url)). |
| 1225 |
// Populated at insert time so every new logsv2 row is born with the |
| 1226 |
// indexed canonical form set — the read-side JOIN (logsv2.canonical_url |
| 1227 |
// = redirects.canonical_url) hits idx_canonical_url instead of |
| 1228 |
// recomputing CONCAT/TRIM per row. Why insert-time and not |
| 1229 |
// backfill-only: logsv2 logs every 404 hit including bot traffic, so a |
| 1230 |
// busy site can produce 10K+ rows/day — the NULL backlog would grow |
| 1231 |
// faster than any chunked backfill could clear it. The matching |
| 1232 |
// idx_canonical_url + the COALESCE fallback in |
| 1233 |
// getRedirectsForViewTempTable.sql together keep reads correct |
| 1234 |
// regardless of whether legacy backfill is complete. |
| 1235 |
// |
| 1236 |
// Derived if missing from $entry (legacy callers / tests) so the |
| 1237 |
// invariant "every sanitized entry has canonical_url" holds. If the |
| 1238 |
// INSERT-time table doesn't have the column yet (pre-upgrade install), |
| 1239 |
// flushLogQueue's schema-drift filter at validatedColumns drops the |
| 1240 |
// key from the column list — the value is computed but harmless. |
| 1241 |
$reqUrlSafe = is_string($sanitized['requested_url']) ? $sanitized['requested_url'] : ''; |
| 1242 |
if (array_key_exists('canonical_url', $entry) && is_string($entry['canonical_url'])) { |
| 1243 |
$canonical = $entry['canonical_url']; |
| 1244 |
} else { |
| 1245 |
$canonical = '/' . trim($reqUrlSafe, '/'); |
| 1246 |
} |
| 1247 |
$sanitized['canonical_url'] = substr($canonical, 0, 2048); |
| 1248 |
|
| 1249 |
$usernameVal = $entry['username'] ?? null; |
| 1250 |
$sanitized['username'] = ($usernameVal === null || !is_scalar($usernameVal)) |
| 1251 |
? null : absint($usernameVal); |
| 1252 |
$minLogIdVal = $entry['min_log_id'] ?? null; |
| 1253 |
$sanitized['min_log_id'] = ($minLogIdVal === null || !is_scalar($minLogIdVal)) |
| 1254 |
? null : absint($minLogIdVal); |
| 1255 |
$sanitized['engine'] = $normalizeString($entry['engine'], 64); |
| 1256 |
|
| 1257 |
// pipeline_trace: pass through as-is (base64-encoded gzip string or null) |
| 1258 |
if (array_key_exists('pipeline_trace', $entry)) { |
| 1259 |
$traceVal = $entry['pipeline_trace']; |
| 1260 |
$sanitized['pipeline_trace'] = ($traceVal === null || is_string($traceVal)) ? $traceVal : null; |
| 1261 |
} else { |
| 1262 |
$sanitized['pipeline_trace'] = null; |
| 1263 |
} |
| 1264 |
|
| 1265 |
// Drop rows without required URL data |
| 1266 |
if ($sanitized['requested_url'] === '' || $sanitized['dest_url'] === '') { |
| 1267 |
return null; |
| 1268 |
} |
| 1269 |
|
| 1270 |
return $sanitized; |
| 1271 |
} |
| 1272 |
|
| 1273 |
/** |
| 1274 |
* Serialize a pipeline trace array for storage as a BLOB. |
| 1275 |
* Returns base64(gzip(json)) so the value is safe ASCII for SQL. |
| 1276 |
* |
| 1277 |
* @param array<int, array{step: string, outcome: string, detail: string}>|null $trace |
| 1278 |
* @return string|null |
| 1279 |
*/ |
| 1280 |
private function serializePipelineTrace(?array $trace): ?string { |
| 1281 |
if ($trace === null || empty($trace)) { |
| 1282 |
return null; |
| 1283 |
} |
| 1284 |
$json = json_encode($trace); |
| 1285 |
if ($json === false) { |
| 1286 |
return null; |
| 1287 |
} |
| 1288 |
$compressed = gzcompress($json, 6); |
| 1289 |
if ($compressed === false) { |
| 1290 |
return null; |
| 1291 |
} |
| 1292 |
return base64_encode($compressed); |
| 1293 |
} |
| 1294 |
|
| 1295 |
/** |
| 1296 |
* Decompress and decode a stored pipeline trace blob. |
| 1297 |
* |
| 1298 |
* @param string|null $raw Base64-encoded gzip-compressed JSON string |
| 1299 |
* @return array<int, array{step: string, outcome: string, detail: string}>|null |
| 1300 |
*/ |
| 1301 |
public static function decompressPipelineTrace(?string $raw): ?array { |
| 1302 |
if ($raw === null || $raw === '') { |
| 1303 |
return null; |
| 1304 |
} |
| 1305 |
$decoded = base64_decode($raw, true); |
| 1306 |
if ($decoded === false) { |
| 1307 |
return null; |
| 1308 |
} |
| 1309 |
$json = @gzuncompress($decoded); |
| 1310 |
if ($json === false) { |
| 1311 |
return null; |
| 1312 |
} |
| 1313 |
$result = json_decode($json, true); |
| 1314 |
return is_array($result) ? $result : null; |
| 1315 |
} |
| 1316 |
|
| 1317 |
/** Insert a value into the lookup table and return the ID of the value. |
| 1318 |
* Uses upsert pattern (INSERT ... ON DUPLICATE KEY UPDATE) for atomic operation. |
| 1319 |
* @param string $valueToInsert |
| 1320 |
* @return int |
| 1321 |
*/ |
| 1322 |
function insertLookupValueAndGetID($valueToInsert) { |
| 1323 |
global $wpdb; |
| 1324 |
|
| 1325 |
// Use upsert pattern: single atomic query that handles both insert and duplicate cases |
| 1326 |
// ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id) ensures insert_id is set even for existing rows |
| 1327 |
$query = "INSERT INTO {wp_abj404_lookup} (lkup_value) VALUES (%s) |
| 1328 |
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)"; |
| 1329 |
$this->queryAndGetResults($query, array( |
| 1330 |
'query_params' => array($valueToInsert) |
| 1331 |
)); |
| 1332 |
|
| 1333 |
return intval($wpdb->insert_id); |
| 1334 |
} |
| 1335 |
|
| 1336 |
/** |
| 1337 |
* Get daily 404/redirect activity for the last N days. |
| 1338 |
* |
| 1339 |
* Returns array of rows, one per day (including days with zero activity), |
| 1340 |
* sorted ascending by date. Each row has: |
| 1341 |
* 'date' => 'YYYY-MM-DD' |
| 1342 |
* 'hits_404' => int (rows where dest_url equals the 404 sentinel) |
| 1343 |
* 'hits_redirect' => int (rows where dest_url is not the 404 sentinel) |
| 1344 |
* 'new_captures' => int (same as hits_404 for trend purposes) |
| 1345 |
* |
| 1346 |
* Result is cached in a transient keyed on (blog_id, days, max_log_id) |
| 1347 |
* with TTL TREND_DATA_CACHE_TTL_SECONDS. New log inserts increase |
| 1348 |
* max_log_id which moves the cache key, so fresh data appears |
| 1349 |
* automatically as soon as a new request arrives. |
| 1350 |
* |
| 1351 |
* @param int $days Number of days (default 30, clamped to 1-90) |
| 1352 |
* @return array<int, array<string, mixed>> |
| 1353 |
*/ |
| 1354 |
public function getDailyActivityTrend(int $days = 30): array { |
| 1355 |
$days = max(1, min(90, $days)); |
| 1356 |
|
| 1357 |
$blogId = 1; |
| 1358 |
if (function_exists('get_current_blog_id')) { |
| 1359 |
$blogId = function_exists('absint') |
| 1360 |
? absint(get_current_blog_id()) |
| 1361 |
: abs(intval(get_current_blog_id())); |
| 1362 |
if ($blogId <= 0) { |
| 1363 |
$blogId = 1; |
| 1364 |
} |
| 1365 |
} |
| 1366 |
|
| 1367 |
$maxLogId = 0; |
| 1368 |
try { |
| 1369 |
$maxLogId = intval($this->getMaxLogId()); |
| 1370 |
if ($maxLogId < 0) { |
| 1371 |
$maxLogId = 0; |
| 1372 |
} |
| 1373 |
} catch (Throwable $e) { |
| 1374 |
// getMaxLogId() failed (table missing, query timeout). Fall back to |
| 1375 |
// 0 so the cache key still varies; the trend transient will recompute |
| 1376 |
// until the underlying query recovers. Log at debug level: this is a |
| 1377 |
// transient cache-key derivation, not a load-bearing failure. |
| 1378 |
$this->logger->debugMessage(__FUNCTION__ . ' getMaxLogId() failed: ' . |
| 1379 |
$e->getMessage() . '. Falling back to maxLogId=0 (cache key uses 0).'); |
| 1380 |
$maxLogId = 0; |
| 1381 |
} |
| 1382 |
|
| 1383 |
$cacheKey = 'abj404_trend_v1_' . $blogId . '_' . $days . '_' . $maxLogId; |
| 1384 |
if (function_exists('get_transient')) { |
| 1385 |
$cached = get_transient($cacheKey); |
| 1386 |
if (is_array($cached)) { |
| 1387 |
return $cached; |
| 1388 |
} |
| 1389 |
} |
| 1390 |
|
| 1391 |
$logsTable = $this->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 1392 |
$cutoff = time() - ($days * 86400); |
| 1393 |
|
| 1394 |
$notFoundDest = '404'; |
| 1395 |
$query = "SELECT |
| 1396 |
DATE(FROM_UNIXTIME(`timestamp`)) AS `date`, |
| 1397 |
SUM(CASE WHEN `dest_url` = %s THEN 1 ELSE 0 END) AS `hits_404`, |
| 1398 |
SUM(CASE WHEN `dest_url` <> %s THEN 1 ELSE 0 END) AS `hits_redirect` |
| 1399 |
FROM " . $logsTable . " |
| 1400 |
WHERE `timestamp` >= " . intval($cutoff) . " |
| 1401 |
GROUP BY DATE(FROM_UNIXTIME(`timestamp`)) |
| 1402 |
ORDER BY `date` ASC"; |
| 1403 |
|
| 1404 |
$result = $this->queryAndGetResults($query, array( |
| 1405 |
'query_params' => array($notFoundDest, $notFoundDest), |
| 1406 |
)); |
| 1407 |
$hadError = !empty($result['timed_out']) |
| 1408 |
|| (isset($result['last_error']) && $result['last_error'] !== ''); |
| 1409 |
$rows = (isset($result['rows']) && is_array($result['rows'])) ? $result['rows'] : array(); |
| 1410 |
|
| 1411 |
// Build a date-keyed map from query results. |
| 1412 |
$byDate = array(); |
| 1413 |
foreach ($rows as $row) { |
| 1414 |
if (!is_array($row)) { |
| 1415 |
continue; |
| 1416 |
} |
| 1417 |
$d = isset($row['date']) ? (string)$row['date'] : ''; |
| 1418 |
if ($d === '') { |
| 1419 |
continue; |
| 1420 |
} |
| 1421 |
$byDate[$d] = array( |
| 1422 |
'date' => $d, |
| 1423 |
'hits_404' => intval($row['hits_404'] ?? 0), |
| 1424 |
'hits_redirect' => intval($row['hits_redirect'] ?? 0), |
| 1425 |
'new_captures' => intval($row['hits_404'] ?? 0), |
| 1426 |
); |
| 1427 |
} |
| 1428 |
|
| 1429 |
// Fill in days with zero activity so the chart always shows N points. |
| 1430 |
$output = array(); |
| 1431 |
for ($i = $days - 1; $i >= 0; $i--) { |
| 1432 |
$d = date('Y-m-d', time() - ($i * 86400)); |
| 1433 |
if (isset($byDate[$d])) { |
| 1434 |
$output[] = $byDate[$d]; |
| 1435 |
} else { |
| 1436 |
$output[] = array( |
| 1437 |
'date' => $d, |
| 1438 |
'hits_404' => 0, |
| 1439 |
'hits_redirect' => 0, |
| 1440 |
'new_captures' => 0, |
| 1441 |
); |
| 1442 |
} |
| 1443 |
} |
| 1444 |
|
| 1445 |
// Only cache the result on success. A transient DB error/timeout |
| 1446 |
// would otherwise pin a zero-filled chart for TREND_DATA_CACHE_TTL_SECONDS |
| 1447 |
// (15 min) so the admin sees "no activity" until the cache expires — |
| 1448 |
// misleading and harder to diagnose than letting the next request retry. |
| 1449 |
if (!$hadError && function_exists('set_transient')) { |
| 1450 |
set_transient($cacheKey, $output, self::TREND_DATA_CACHE_TTL_SECONDS); |
| 1451 |
} |
| 1452 |
|
| 1453 |
return $output; |
| 1454 |
} |
| 1455 |
|
| 1456 |
/** |
| 1457 |
* @param string $userName |
| 1458 |
* @return int |
| 1459 |
*/ |
| 1460 |
function getLookupIDForUser($userName) { |
| 1461 |
// Use prepared statement to prevent SQL injection |
| 1462 |
$query = "select id from {wp_abj404_lookup} where lkup_value = %s"; |
| 1463 |
$results = $this->queryAndGetResults($query, array( |
| 1464 |
'query_params' => array($userName) |
| 1465 |
)); |
| 1466 |
|
| 1467 |
$lookupRows = is_array($results['rows']) ? $results['rows'] : array(); |
| 1468 |
if (count($lookupRows) > 0) { |
| 1469 |
// the value already exists so we only need to return the ID. |
| 1470 |
$rows = $lookupRows; |
| 1471 |
$row1 = is_array($rows[0]) ? $rows[0] : array(); |
| 1472 |
$id = isset($row1['id']) ? $row1['id'] : 0; |
| 1473 |
return is_scalar($id) ? intval($id) : 0; |
| 1474 |
} |
| 1475 |
return -1; |
| 1476 |
} |
| 1477 |
} |
| 1478 |
|
