Join Us
-- =============================================================================
-- NYCRavers — Database Cleanup Script
-- Generated: 2026-06-07
-- Target prefix: nycr4_
--
-- SCOPE (database hygiene only — does NOT fix the file-level cloaking injection):
-- 1. Delete dormant throwaway-email user 568 (fengurelog1996@regmailproject.info)
-- 2. Purge spam-term rows from Rank Math's cached GSC analytics
-- 3. Drop leftover "Automatic" autoblog plugin tables (source of AI-gen thin content)
--
-- !!! READ BEFORE RUNNING !!!
-- * TAKE A FULL DATABASE BACKUP FIRST:
-- mysqldump -u USER -p nycr4_db > nycr4_backup_BEFORE_cleanup.sql
-- * This does NOT remove the cloaking payload in your THEME/.htaccess/mu-plugins.
-- Clean the files first (see the forensic report) or the site re-cloaks.
-- * Run SECTION 1 (verification) first and eyeball the output before SECTIONS 2-4.
-- * DROP TABLE (Section 4) is DDL and CANNOT be rolled back. The backup is your
-- only undo. Sections 2 & 3 are wrapped in a transaction you can ROLLBACK.
-- =============================================================================
-- =============================================================================
-- SECTION 1 — VERIFICATION (read-only). Run this first and review the results.
-- =============================================================================
-- 1a. Confirm user 568 is the throwaway account and has no role.
SELECT u.ID, u.user_login, u.user_email, u.user_registered,
(SELECT meta_value FROM nycr4_usermeta
WHERE user_id = u.ID AND meta_key = 'nycr4_capabilities') AS capabilities
FROM nycr4_users u
WHERE u.ID = 568;
-- 1b. Make sure user 568 does NOT own any content before deletion.
-- If this returns > 0, STOP and reassign authorship (see Section 2, step 2a).
SELECT post_status, COUNT(*) AS n
FROM nycr4_posts
WHERE post_author = 568
GROUP BY post_status;
-- 1c. Preview the spam GSC rows that will be purged (review before deleting).
SELECT id, created, query, page
FROM nycr4_rank_math_analytics_gsc
WHERE query REGEXP 'casibom|meritking|vdcasino|perabet|tipobet|amgbahis|jojobet|betturkey|deneme|streameast|viagra|cialis|\\bescort\\b|\\bporn\\b|\\bbahis\\b|\\bcasino\\b|\\bslot\\b'
ORDER BY created;
-- 1d. How many rows is that? (Expected ~61 based on forensic analysis.)
SELECT COUNT(*) AS spam_rows_to_delete
FROM nycr4_rank_math_analytics_gsc
WHERE query REGEXP 'casibom|meritking|vdcasino|perabet|tipobet|amgbahis|jojobet|betturkey|deneme|streameast|viagra|cialis|\\bescort\\b|\\bporn\\b|\\bbahis\\b|\\bcasino\\b|\\bslot\\b';
-- =============================================================================
-- SECTION 2 & 3 — DELETIONS (transaction-protected; ROLLBACK if anything looks off)
-- =============================================================================
START TRANSACTION;
-- ---- Section 2: Remove rogue/dormant user 568 -------------------------------
-- 2a. OPTIONAL safety net: if Section 1b showed posts owned by 568, reassign them
-- to the primary admin (ID 1 = aeryk2k) instead of orphaning them.
-- Leave commented unless 1b returned rows.
-- UPDATE nycr4_posts SET post_author = 1 WHERE post_author = 568;
-- UPDATE nycr4_comments SET user_id = 0 WHERE user_id = 568;
-- 2b. Delete the user's metadata, then the user row itself.
DELETE FROM nycr4_usermeta WHERE user_id = 568;
DELETE FROM nycr4_users WHERE ID = 568;
-- 2c. BuddyBoss/BuddyPress residue for this user (this is a BuddyBoss install).
-- Safe to run even if some tables are empty for this user.
DELETE FROM nycr4_bp_xprofile_data WHERE user_id = 568;
DELETE FROM nycr4_bp_activity WHERE user_id = 568;
DELETE FROM nycr4_bp_notifications WHERE user_id = 568 OR secondary_item_id = 568;
DELETE FROM nycr4_bp_friends WHERE initiator_user_id = 568 OR friend_user_id = 568;
DELETE FROM nycr4_bp_groups_members WHERE user_id = 568;
DELETE FROM nycr4_bp_messages_recipients WHERE user_id = 568;
-- ---- Section 3: Purge spam terms from Rank Math GSC analytics cache ----------
DELETE FROM nycr4_rank_math_analytics_gsc
WHERE query REGEXP 'casibom|meritking|vdcasino|perabet|tipobet|amgbahis|jojobet|betturkey|deneme|streameast|viagra|cialis|\\bescort\\b|\\bporn\\b|\\bbahis\\b|\\bcasino\\b|\\bslot\\b';
-- Review the row counts reported above. If correct:
COMMIT;
-- If anything looks wrong instead, run: ROLLBACK;
-- =============================================================================
-- SECTION 4 — DROP "Automatic" autoblog leftover tables
-- (deactivated plugin; tables hold the AI-generated thin-content footprint)
-- WARNING: DDL — not transactional, cannot be rolled back. Backup is your undo.
-- Skip this section if you might reactivate the Automatic plugin later.
-- =============================================================================
DROP TABLE IF EXISTS
nycr4_automatic_amazon_links,
nycr4_automatic_articles_keys,
nycr4_automatic_articles_links,
nycr4_automatic_cached,
nycr4_automatic_camps,
nycr4_automatic_categories,
nycr4_automatic_clickbank_links,
nycr4_automatic_feeds_links,
nycr4_automatic_general,
nycr4_automatic_keywords,
nycr4_automatic_links,
nycr4_automatic_log,
nycr4_automatic_youtube_links;
-- =============================================================================
-- SECTION 5 — POST-RUN VERIFICATION (read-only). Confirm everything is clean.
-- =============================================================================
-- 5a. User 568 should return 0 rows.
SELECT COUNT(*) AS user_568_remaining FROM nycr4_users WHERE ID = 568;
-- 5b. Spam GSC rows should return 0.
SELECT COUNT(*) AS spam_gsc_remaining
FROM nycr4_rank_math_analytics_gsc
WHERE query REGEXP 'casibom|meritking|vdcasino|perabet|tipobet|amgbahis|jojobet|betturkey|deneme|streameast|viagra|cialis|\\bescort\\b|\\bporn\\b|\\bbahis\\b|\\bcasino\\b|\\bslot\\b';
-- 5c. Automatic tables should be gone (returns only non-automatic tables, if any).
SHOW TABLES LIKE 'nycr4_automatic_%';
-- =============================================================================
-- REMINDER: This script cleans the DATABASE only. The actual gambling/piracy
-- spam was served by a FILE-LEVEL cloaking injection (theme / .htaccess /
-- mu-plugins / Duplicator backdoor). Clean the files and rotate all credentials,
-- then request a security review in Google Search Console.
-- =============================================================================
