| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
require_once __DIR__ . '/../database/DatabaseCollationHelper.php'; |
| 8 |
|
| 9 |
/** |
| 10 |
* Query policy for admin redirect and captured-url table reads. |
| 11 |
* |
| 12 |
* Owns safe status-filter resolution, score-range fragments, text-search |
| 13 |
* fragments, order-by allowlists, collation selection, and view-build labels. |
| 14 |
*/ |
| 15 |
class ABJ_404_Solution_ViewQueryPolicy { |
| 16 |
|
| 17 |
/** |
| 18 |
* @param array<string, mixed> $tableOptions |
| 19 |
* @param string $columnPrefix |
| 20 |
* @return string |
| 21 |
*/ |
| 22 |
public function buildScoreRangeClause(array $tableOptions, string $columnPrefix): string { |
| 23 |
$rawScoreRange = $tableOptions['score_range'] ?? 'all'; |
| 24 |
$scoreRange = is_string($rawScoreRange) ? $rawScoreRange : 'all'; |
| 25 |
$col = $columnPrefix . 'score'; |
| 26 |
$high = (int) ABJ_404_Solution_ScoreThresholds::HIGH; |
| 27 |
$medium = (int) ABJ_404_Solution_ScoreThresholds::MEDIUM; |
| 28 |
switch ($scoreRange) { |
| 29 |
case ABJ_404_Solution_ScoreThresholds::RANGE_HIGH: |
| 30 |
return 'AND ' . $col . ' >= ' . $high; |
| 31 |
case ABJ_404_Solution_ScoreThresholds::RANGE_MEDIUM: |
| 32 |
return 'AND ' . $col . ' >= ' . $medium . ' AND ' . $col . ' < ' . $high; |
| 33 |
case ABJ_404_Solution_ScoreThresholds::RANGE_LOW: |
| 34 |
return 'AND ' . $col . ' IS NOT NULL AND ' . $col . ' < ' . $medium; |
| 35 |
case ABJ_404_Solution_ScoreThresholds::MANUAL: |
| 36 |
return 'AND ' . $col . ' IS NULL'; |
| 37 |
default: |
| 38 |
return ''; |
| 39 |
} |
| 40 |
} |
| 41 |
|
| 42 |
/** |
| 43 |
* @param string $sub |
| 44 |
* @param array<string, mixed> $tableOptions |
| 45 |
* @return string |
| 46 |
*/ |
| 47 |
public function resolveStatusTypeList(string $sub, array $tableOptions): string { |
| 48 |
global $abj404_redirect_types, $abj404_captured_types; |
| 49 |
$filter = $tableOptions['filter'] ?? 0; |
| 50 |
|
| 51 |
if ($filter == 0 || $filter == ABJ404_TRASH_FILTER) { |
| 52 |
if ($sub === 'abj404_redirects') { |
| 53 |
return $this->statusArrayToList(is_array($abj404_redirect_types) ? $abj404_redirect_types : array()); |
| 54 |
} |
| 55 |
if ($sub === 'abj404_captured') { |
| 56 |
return $this->statusArrayToList(is_array($abj404_captured_types) ? $abj404_captured_types : array()); |
| 57 |
} |
| 58 |
return '0'; |
| 59 |
} |
| 60 |
if ($filter == ABJ404_STATUS_MANUAL) { |
| 61 |
return implode(', ', array(ABJ404_STATUS_MANUAL, ABJ404_STATUS_REGEX)); |
| 62 |
} |
| 63 |
if ($filter == ABJ404_HANDLED_FILTER) { |
| 64 |
return implode(', ', array(ABJ404_STATUS_IGNORED, ABJ404_STATUS_LATER)); |
| 65 |
} |
| 66 |
|
| 67 |
return $this->singleStatusFilter($filter); |
| 68 |
} |
| 69 |
|
| 70 |
/** |
| 71 |
* @param mixed $filter |
| 72 |
* @return string |
| 73 |
*/ |
| 74 |
private function singleStatusFilter($filter): string { |
| 75 |
if (!is_scalar($filter)) { |
| 76 |
return '0'; |
| 77 |
} |
| 78 |
$raw = trim((string)$filter); |
| 79 |
if (!preg_match('/^\d+$/', $raw)) { |
| 80 |
return '0'; |
| 81 |
} |
| 82 |
return (string)intval($raw); |
| 83 |
} |
| 84 |
|
| 85 |
/** |
| 86 |
* @param array<int, mixed> $types |
| 87 |
* @return string |
| 88 |
*/ |
| 89 |
private function statusArrayToList(array $types): string { |
| 90 |
$clean = array(); |
| 91 |
foreach ($types as $type) { |
| 92 |
if (is_scalar($type)) { |
| 93 |
$clean[] = intval($type); |
| 94 |
} |
| 95 |
} |
| 96 |
return count($clean) > 0 ? implode(', ', $clean) : '0'; |
| 97 |
} |
| 98 |
|
| 99 |
/** @param array<string, mixed> $tableOptions @return int */ |
| 100 |
public function resolveTrashValue(array $tableOptions): int { |
| 101 |
return ($tableOptions['filter'] ?? 0) == ABJ404_TRASH_FILTER ? 1 : 0; |
| 102 |
} |
| 103 |
|
| 104 |
/** |
| 105 |
* @param array<string, mixed> $tableOptions |
| 106 |
* @return string |
| 107 |
*/ |
| 108 |
public function resolveOrderByColumn(array $tableOptions): string { |
| 109 |
$rawOrderBy = $tableOptions['orderby'] ?? ''; |
| 110 |
$orderBy = strtolower(is_string($rawOrderBy) ? $rawOrderBy : ''); |
| 111 |
$allowed = array('url', 'status', 'type', 'code', 'score', 'timestamp', |
| 112 |
'logshits', 'last_used', 'final_dest', 'dest', 'id'); |
| 113 |
if ($orderBy === 'dest' || $orderBy === 'final_dest') { |
| 114 |
return "CASE WHEN dest_for_view IS NULL OR dest_for_view = '' THEN 1 ELSE 0 END ASC, dest_for_view"; |
| 115 |
} |
| 116 |
if (!in_array($orderBy, $allowed, true)) { |
| 117 |
return 'url'; |
| 118 |
} |
| 119 |
return $orderBy; |
| 120 |
} |
| 121 |
|
| 122 |
/** |
| 123 |
* @param array<string, mixed> $tableOptions |
| 124 |
* @return string |
| 125 |
*/ |
| 126 |
public function resolveOrderDirection(array $tableOptions): string { |
| 127 |
$rawOrderVal = $tableOptions['order'] ?? ''; |
| 128 |
$rawOrderValStr = is_string($rawOrderVal) ? $rawOrderVal : ''; |
| 129 |
$order = strtoupper((string)preg_replace('/[^a-zA-Z]/', '', trim($rawOrderValStr))); |
| 130 |
return $order === 'DESC' ? 'DESC' : 'ASC'; |
| 131 |
} |
| 132 |
|
| 133 |
/** |
| 134 |
* @param string $sub |
| 135 |
* @param array<string, mixed> $tableOptions |
| 136 |
* @param bool $singleTable When true, the post-type label predicate resolves |
| 137 |
* the matching destination posts via a {wp_posts} subquery on final_dest |
| 138 |
* rather than a wp_post_type column reference, because the single-table |
| 139 |
* redirects read (Denorm Step 3b) has no denormalized wp_post_type column. |
| 140 |
* @param bool $destColumnAvailable When false, the destination-title match |
| 141 |
* drops the dest_for_view column from the search expression (schema-drift |
| 142 |
* tolerance: an old redirects table may lack it). The search then matches |
| 143 |
* url/code/labels only. |
| 144 |
* @return string |
| 145 |
*/ |
| 146 |
public function buildFilterTextClause(string $sub, array $tableOptions, bool $singleTable = false, bool $destColumnAvailable = true): string { |
| 147 |
$rawFilterText = $tableOptions['filterText'] ?? ''; |
| 148 |
$rawFilterText = is_string($rawFilterText) ? $rawFilterText : ''; |
| 149 |
if ($rawFilterText === '') { |
| 150 |
return ''; |
| 151 |
} |
| 152 |
|
| 153 |
$filterText = $this->sanitizeFilterText($rawFilterText); |
| 154 |
$collation = $this->resolveCollation($tableOptions); |
| 155 |
$needle = $this->normalizedSearchExpression("'%" . $filterText . "%'", $collation); |
| 156 |
if ($sub === 'abj404_redirects') { |
| 157 |
$predicates = $this->labelPredicatesForFilterText($filterText, $singleTable); |
| 158 |
$searchConcat = $destColumnAvailable |
| 159 |
? "CONCAT(url, '////', dest_for_view, '////', code)" |
| 160 |
: "CONCAT(url, '////', code)"; |
| 161 |
$predicates[] = $this->normalizedSearchExpression($searchConcat, $collation) . " LIKE " . $needle; |
| 162 |
return 'AND (' . implode(' OR ', $predicates) . ')'; |
| 163 |
} |
| 164 |
if ($sub === 'abj404_captured') { |
| 165 |
return "AND " . $this->normalizedSearchExpression('url', $collation) . " LIKE " . $needle; |
| 166 |
} |
| 167 |
return 'AND 0 = 1'; |
| 168 |
} |
| 169 |
|
| 170 |
/** |
| 171 |
* @param string $sqlExpression |
| 172 |
* @param string $collation |
| 173 |
* @return string |
| 174 |
*/ |
| 175 |
private function normalizedSearchExpression(string $sqlExpression, string $collation): string { |
| 176 |
return "REPLACE(LOWER(CONVERT(" . $sqlExpression . " USING utf8mb4) COLLATE " |
| 177 |
. $collation . "), ' ', '')"; |
| 178 |
} |
| 179 |
|
| 180 |
/** |
| 181 |
* @param string $rawFilterText |
| 182 |
* @return string |
| 183 |
*/ |
| 184 |
public function sanitizeFilterText(string $rawFilterText): string { |
| 185 |
global $wpdb; |
| 186 |
$sanitized = str_replace(array('*', '/', '$'), '', $rawFilterText); |
| 187 |
if (isset($wpdb) && is_object($wpdb) && method_exists($wpdb, 'esc_like')) { |
| 188 |
/** @var wpdb $wpdb */ |
| 189 |
$sanitized = $wpdb->esc_like($sanitized); |
| 190 |
} else { |
| 191 |
$sanitized = addcslashes($sanitized, '_%\\'); |
| 192 |
} |
| 193 |
return esc_sql($sanitized); |
| 194 |
} |
| 195 |
|
| 196 |
/** |
| 197 |
* @param array<string, mixed> $tableOptions |
| 198 |
* @return string |
| 199 |
*/ |
| 200 |
public function resolveCollation(array $tableOptions): string { |
| 201 |
global $wpdb; |
| 202 |
$rawCollate = ''; |
| 203 |
if (array_key_exists('forceCollate', $tableOptions) && !empty($tableOptions['forceCollate'])) { |
| 204 |
$rawForceCollateVal = $tableOptions['forceCollate']; |
| 205 |
$rawCollate = is_string($rawForceCollateVal) ? $rawForceCollateVal : ''; |
| 206 |
} |
| 207 |
if ($rawCollate === '' && isset($wpdb) && isset($wpdb->collate) && is_scalar($wpdb->collate)) { |
| 208 |
$rawCollate = (string)$wpdb->collate; |
| 209 |
} |
| 210 |
|
| 211 |
// normalizedSearchExpression() CONVERTs to a hard-coded utf8mb4, so the |
| 212 |
// collation it pairs with must belong to that charset whatever the site |
| 213 |
// (or an explicit forceCollate) asked for. A latin1 site collation reached |
| 214 |
// the engine verbatim here and every filtered admin table read died with |
| 215 |
// "COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8mb4'". |
| 216 |
return ABJ_404_Solution_DatabaseCollationHelper::utf8mb4CollationOrFallback($rawCollate); |
| 217 |
} |
| 218 |
|
| 219 |
/** |
| 220 |
* @param string $filterText |
| 221 |
* @param bool $singleTable |
| 222 |
* @return array<int, string> |
| 223 |
*/ |
| 224 |
private function labelPredicatesForFilterText(string $filterText, bool $singleTable = false): array { |
| 225 |
$normalized = $this->normalizeSearchLabel($filterText); |
| 226 |
if ($normalized === '') { |
| 227 |
return array(); |
| 228 |
} |
| 229 |
|
| 230 |
$statusMatches = $this->matchingLabelCodes($normalized, $this->statusSearchLabels()); |
| 231 |
$typeMatches = $this->matchingLabelCodes($normalized, $this->typeSearchLabels()); |
| 232 |
$predicates = array(); |
| 233 |
if (count($statusMatches) > 0) { |
| 234 |
$predicates[] = 'status IN (' . implode(', ', $statusMatches) . ')'; |
| 235 |
} |
| 236 |
if (count($typeMatches) > 0) { |
| 237 |
$predicates[] = 'type IN (' . implode(', ', $typeMatches) . ')'; |
| 238 |
} |
| 239 |
$predicates = array_merge($predicates, $this->postTypeLabelPredicates($normalized, $singleTable)); |
| 240 |
return $predicates; |
| 241 |
} |
| 242 |
|
| 243 |
/** |
| 244 |
* @param array<int, array<int, string>> $labelsByCode |
| 245 |
* @return array<int, int> |
| 246 |
*/ |
| 247 |
private function matchingLabelCodes(string $normalized, array $labelsByCode): array { |
| 248 |
$matches = array(); |
| 249 |
foreach ($labelsByCode as $code => $labels) { |
| 250 |
foreach ($labels as $label) { |
| 251 |
if ($this->normalizeSearchLabel($label) === $normalized) { |
| 252 |
$matches[] = (int)$code; |
| 253 |
break; |
| 254 |
} |
| 255 |
} |
| 256 |
} |
| 257 |
return $matches; |
| 258 |
} |
| 259 |
|
| 260 |
/** @return array<int, array<int, string>> */ |
| 261 |
private function statusSearchLabels(): array { |
| 262 |
return array( |
| 263 |
ABJ404_STATUS_MANUAL => array(__('Manual', '404-solution')), |
| 264 |
ABJ404_STATUS_AUTO => array(__('Auto', '404-solution'), __('Automatic', '404-solution')), |
| 265 |
ABJ404_STATUS_REGEX => array(__('Regex', '404-solution')), |
| 266 |
); |
| 267 |
} |
| 268 |
|
| 269 |
/** @return array<int, array<int, string>> */ |
| 270 |
private function typeSearchLabels(): array { |
| 271 |
return array( |
| 272 |
ABJ404_TYPE_EXTERNAL => array(__('External', '404-solution')), |
| 273 |
ABJ404_TYPE_CAT => array(__('Category', '404-solution')), |
| 274 |
ABJ404_TYPE_TAG => array(__('Tag', '404-solution')), |
| 275 |
ABJ404_TYPE_HOME => array(__('Home', '404-solution')), |
| 276 |
ABJ404_TYPE_404_DISPLAYED => array(__('(404 page)', '404-solution')), |
| 277 |
); |
| 278 |
} |
| 279 |
|
| 280 |
/** |
| 281 |
* @param string $normalized |
| 282 |
* @param bool $singleTable When true, match the destination post type via a |
| 283 |
* {wp_posts} subquery on final_dest instead of the denormalized |
| 284 |
* wp_post_type column (which the single-table redirects read lacks). |
| 285 |
* @return array<int, string> |
| 286 |
*/ |
| 287 |
private function postTypeLabelPredicates(string $normalized, bool $singleTable = false): array { |
| 288 |
$matchingSlugs = $this->matchingPostTypeSlugs($normalized); |
| 289 |
if (count($matchingSlugs) === 0) { |
| 290 |
return array(); |
| 291 |
} |
| 292 |
|
| 293 |
$quotedSlugs = array(); |
| 294 |
foreach ($matchingSlugs as $slug) { |
| 295 |
// Post-type slugs are validated by register_post_type() to be |
| 296 |
// lowercase ASCII word/dash characters. Strip anything outside |
| 297 |
// that whitelist defensively before esc_sql() so a misregistered |
| 298 |
// (or scanner-injected) slug with invalid UTF-8 cannot reach SQL. |
| 299 |
$safeSlug = preg_replace('/[^a-zA-Z0-9_-]/', '', $slug); |
| 300 |
if ($safeSlug === null || $safeSlug === '') { |
| 301 |
continue; |
| 302 |
} |
| 303 |
$quotedSlugs[] = "'" . esc_sql($safeSlug) . "'"; |
| 304 |
} |
| 305 |
if (count($quotedSlugs) === 0) { |
| 306 |
return array(); |
| 307 |
} |
| 308 |
if ($singleTable) { |
| 309 |
// No wp_post_type column on wp_abj404_redirects: resolve the matching |
| 310 |
// destination posts by id. final_dest holds the numeric post id as a |
| 311 |
// string for POST-typed rows. The subquery only appears for a (rare) |
| 312 |
// post-type-label search, so it never affects the no-filter |
| 313 |
// single-table EXPLAIN plan asserted by the scale test. |
| 314 |
// allow-unbounded-select: wp_posts SELECT is a subquery consumed DB-side inside a final_dest IN (...) predicate; never read into PHP |
| 315 |
return array('(type = ' . (int)ABJ404_TYPE_POST |
| 316 |
. ' AND final_dest IN (SELECT ID FROM {wp_posts} WHERE post_type IN (' |
| 317 |
. implode(', ', $quotedSlugs) . ')))'); |
| 318 |
} |
| 319 |
return array('(type = ' . (int)ABJ404_TYPE_POST . ' AND wp_post_type IN (' . implode(', ', $quotedSlugs) . '))'); |
| 320 |
} |
| 321 |
|
| 322 |
/** @return array<int, string> */ |
| 323 |
private function matchingPostTypeSlugs(string $normalized): array { |
| 324 |
$postTypes = $this->currentPostTypeSearchLabels(); |
| 325 |
$matchingSlugs = array(); |
| 326 |
foreach ($postTypes as $slug => $labels) { |
| 327 |
foreach ($labels as $label) { |
| 328 |
if ($this->normalizeSearchLabel($label) === $normalized) { |
| 329 |
$matchingSlugs[] = (string)$slug; |
| 330 |
break; |
| 331 |
} |
| 332 |
} |
| 333 |
} |
| 334 |
return array_values(array_unique($matchingSlugs)); |
| 335 |
} |
| 336 |
|
| 337 |
/** @return array<string, array<int, string>> */ |
| 338 |
private function currentPostTypeSearchLabels(): array { |
| 339 |
$labelsBySlug = array( |
| 340 |
'post' => array(__('Post', '404-solution'), 'Post'), |
| 341 |
'page' => array(__('Page', '404-solution'), 'Page'), |
| 342 |
); |
| 343 |
if (!function_exists('get_post_types')) { |
| 344 |
return $labelsBySlug; |
| 345 |
} |
| 346 |
|
| 347 |
$postTypes = get_post_types(array(), 'objects'); |
| 348 |
if (!is_array($postTypes)) { |
| 349 |
return $labelsBySlug; |
| 350 |
} |
| 351 |
foreach ($postTypes as $slug => $postType) { |
| 352 |
$slugString = (string)$slug; |
| 353 |
if ($slugString === '' && is_object($postType) && property_exists($postType, 'name') |
| 354 |
&& is_scalar($postType->name)) { |
| 355 |
$slugString = (string)$postType->name; |
| 356 |
} |
| 357 |
if ($slugString === '') { |
| 358 |
continue; |
| 359 |
} |
| 360 |
$labels = array(ucfirst(strtolower($slugString))); |
| 361 |
if (is_object($postType) && property_exists($postType, 'labels') && is_object($postType->labels) |
| 362 |
&& property_exists($postType->labels, 'singular_name') && is_scalar($postType->labels->singular_name)) { |
| 363 |
$singular = trim((string)$postType->labels->singular_name); |
| 364 |
if ($singular !== '') { |
| 365 |
$labels[] = $singular; |
| 366 |
} |
| 367 |
} |
| 368 |
$labelsBySlug[$slugString] = array_values(array_unique(array_merge( |
| 369 |
$labelsBySlug[$slugString] ?? array(), |
| 370 |
$labels |
| 371 |
))); |
| 372 |
} |
| 373 |
return $labelsBySlug; |
| 374 |
} |
| 375 |
|
| 376 |
private function normalizeSearchLabel(string $label): string { |
| 377 |
$clean = str_replace(array('*', '/', '$'), '', $label); |
| 378 |
return strtolower(str_replace(' ', '', trim($clean))); |
| 379 |
} |
| 380 |
} |
| 381 |
|
