Purge Japanese/Chinese Keyword Spam From Your SQL Database

If search engines or customers are seeing walls of Japanese or Chinese keyword spam on your pages, the junk is almost always sitting in your SQL database—not just in a theme file. This guide walks homeowners and small-business owners through finding that spam, removing it safely, and confirming the site is clean again.

We assume a typical Linux LAMP/LEMP stack with MySQL or MariaDB and a WordPress (or similar CMS) database you can reach over SSH. Always snapshot first. If you are in Geelong or nearby and would rather not touch production SQL, Website support from Fixwebnode can handle the purge and hardening for you.

Why purging Japanese/Chinese keyword spam from SQL matters

Attackers inject CJK (Chinese/Japanese/Korean) keyword blocks into post_content, titles, excerpts, options, and post meta so Google indexes spam doorways while your real pages look fine in the admin. Deleting visible posts is not enough: residual rows keep ranking, forms stay abused, and reinfection is common if the write path is still open.

A proper cleanup means: full backup, read-only discovery queries, scoped UPDATEs/DELETEs, cache flush, and a second pass for tables you did not expect. Fixwebnode’s Website support work follows that same order when DIY stops being safe.

Common issues when cleaning CJK keyword spam from the database

• Issue 1 — Spam buried in published posts and pages: Public URLs show dense Japanese/Chinese keyword lists, random <a> farms, or hidden display:none blocks, but the Theme Editor looks clean. Root cause is usually altered wp_posts.post_content / post_title / post_excerpt.
• Issue 2 — Spam in options, widgets, and theme mods: Homepage or footer suddenly spews CJK text; Customizer shows garbled widget HTML. Root cause is often wp_options rows (widget_%, theme_mods_%, autoload=yes junk) rather than posts.
• Issue 3 — Spam in post meta, comments, and orphan revisions: You cleaned posts, yet Google still caches spam snippets, or “View source” shows hidden fields. Root cause: wp_postmeta, wp_comments, and old revisions/autosaves still holding payloads.
• Issue 4 — Wrong charset/collation or partial UTF-8 breakage: Searches for Japanese characters return nothing, or deletes corrupt neighboring Latin text. Root cause: connection charset not utf8mb4, or columns still on utf8/latin1 while spam used multi-byte sequences.

Before any DELETE: backup and connect safely

Never run write queries on production without a restore path. On the server (SSH), dump the full database, then open a read-only session first.

Step 1 — Dump the database

mkdir -p ~/db-backups && chmod 700 ~/db-backups
ms=$(date +%Y%m%d-%H%M%S)
# Replace DB_NAME, USER, HOST as in wp-config.php
mysqldump -u DB_USER -p -h localhost --single-transaction --routines --triggers DB_NAME \
 | gzip -c > ~/db-backups/DB_NAME-${ms}.sql.gz
ls -lh ~/db-backups/DB_NAME-${ms}.sql.gz

Step 2 — Confirm table prefix and charset

grep -E "table_prefix|DB_NAME|DB_USER|DB_HOST|DB_CHARSET" /var/www/html/wp-config.php
mysql -u DB_USER -p -h localhost -e "SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';"

Step 3 — Open mysql with utf8mb4

mysql -u DB_USER -p -h localhost --default-character-set=utf8mb4 DB_NAME

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW TABLES LIKE 'wp_%';

If your prefix is not wp_, substitute it everywhere below. Fixwebnode covers Geelong and other All service areas when you need remote hands on the same stack.

Fix issue 1: CJK spam inside posts and pages

Symptom: front-end paragraphs of Japanese/Chinese SEO keywords; admin “All Posts” may still show normal titles if only post_content was hit.

Step 1 — Discover matching rows (read-only)

-- Broad Unicode letter ranges often used in CJK spam blocks
SELECT ID, post_type, post_status, post_title,
 LEFT(post_content, 120) AS content_sample
FROM wp_posts
WHERE post_status IN ('publish','draft','pending','private','future')
 AND (
 post_content REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}\\x{f900}-\\x{faff}]'
 OR post_title REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 OR post_excerpt REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 )
ORDER BY post_modified DESC
LIMIT 200;

Also hunt Latin “bridge” spam phrases attackers mix in:

SELECT ID, post_title FROM wp_posts
WHERE post_content LIKE '%casino%'
 OR post_content LIKE '%viagra%'
 OR post_content LIKE '%crypto%'
 OR post_content LIKE '%貸金%'
 OR post_content LIKE '%色情%'
LIMIT 200;

Step 2 — Export IDs before changing anything

SELECT ID FROM wp_posts
WHERE post_content REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
INTO OUTFILE '/tmp/spam_post_ids.txt';

If INTO OUTFILE is disabled, run the SELECT in your client and save the result set locally.

Step 3 — DIY cleanup paths

If the whole post is spam (no legitimate content worth keeping):

-- Trash first (reversible in WP UI)
UPDATE wp_posts
SET post_status = 'trash',
 post_modified = NOW(),
 post_modified_gmt = UTC_TIMESTAMP()
WHERE ID IN (101,102,103); -- use IDs from discovery

If the post is real but a spam block was appended:

-- Example: strip a known closing spam marker; adjust the LIKE pattern after inspecting one row
UPDATE wp_posts
SET post_content = TRIM(
 SUBSTRING_INDEX(post_content, '<!--spam:cjk-->', 1)
 ),
 post_modified = NOW()
WHERE ID IN (101,102)
 AND post_content LIKE '%<!--spam:cjk-->%';

When there is no clean marker, restore the post body from a pre-infection backup for those IDs only rather than blind REGEXP replaces that can destroy UTF-8.

Step 4 — Purge revisions tied to infected parents

DELETE FROM wp_posts
WHERE post_type = 'revision'
 AND post_parent IN (101,102,103);

Step 5 — Verify

SELECT COUNT(*) AS still_hit FROM wp_posts
WHERE post_content REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 AND post_status = 'publish';

Then hard-refresh the public URL and check “view source.”

When to call Fixwebnode: hundreds of IDs, serialized content you cannot safely substring, or WooCommerce/product post types mixed in. Book via Website support before mass UPDATE.

Fix issue 2: spam in wp_options, widgets, and theme mods

Symptom: every page footer or a single widget outputs CJK keywords; wp_posts queries look clean.

Step 1 — Find dirty options

SELECT option_id, option_name, autoload, LEFT(option_value, 160) AS sample
FROM wp_options
WHERE option_value REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 OR option_name LIKE '%spam%'
 OR option_name LIKE 'widget_%'
ORDER BY autoload DESC, option_id DESC
LIMIT 300;

Step 2 — Inspect high-risk names

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE option_name IN (
 'siteurl','home','blogname','blogdescription',
 'active_plugins','template','stylesheet'
)
OR option_name LIKE 'theme_mods_%'
OR option_name LIKE 'widget_%'
OR option_name LIKE '%sidebars_widgets%';

Step 3 — Remove or reset safely

For a disposable spam-only option row:

DELETE FROM wp_options WHERE option_id IN (501,502);

For a corrupted widget option you can rebuild in wp-admin:

UPDATE wp_options
SET option_value = 'a:0:{}'
WHERE option_name = 'widget_text'
 AND option_value REGEXP '[\\x{3400}-\\x{9fff}]';

Warning: many option_value payloads are PHP-serialized. Changing string length without fixing serial counts breaks the site. If you see a:, s:, O: structures, prefer WP-CLI or a specialist restore of that row from backup.

# On the app server, if WP-CLI is installed
wp option get widget_text --format=json > /tmp/widget_text.json
wp option delete rogue_spam_option
wp cache flush
wp rewrite flush

Step 4 — Verify autoloaded bloat is gone

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload IN ('yes','on','1')
ORDER BY bytes DESC
LIMIT 30;

When to call Fixwebnode: serialized theme_mods, multisite sitemeta, or siteurl/home tampering. Related form-spam hardening is covered in services such as Collingwood WordPress Contact Form & SMTP Fixes | Stop Spam when the inbox path is the reinfection vector.

Fix issue 3: post meta, comments, and leftover junk tables

Symptom: posts look clean in the editor; cached SERPs or hidden HTML still show CJK blocks; comment floods with foreign anchors.

Step 1 — Post meta hunt

SELECT meta_id, post_id, meta_key, LEFT(meta_value, 120) AS sample
FROM wp_postmeta
WHERE meta_value REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 OR meta_key IN ('_yoast_wpseo_metadesc','_yoast_wpseo_title','_aioseo_description')
LIMIT 300;

Step 2 — Delete or null only confirmed spam meta

DELETE FROM wp_postmeta WHERE meta_id IN (9001,9002,9003);
-- Or clear SEO fields only
UPDATE wp_postmeta
SET meta_value = ''
WHERE meta_key IN ('_yoast_wpseo_metadesc','_yoast_wpseo_title')
 AND meta_value REGEXP '[\\x{3400}-\\x{9fff}]';

Step 3 — Comments

SELECT comment_ID, comment_post_ID, comment_author, LEFT(comment_content, 80)
FROM wp_comments
WHERE comment_content REGEXP '[\\x{3040}-\\x{30ff}\\x{3400}-\\x{9fff}]'
 OR comment_author_url REGEXP '[\\x{3400}-\\x{9fff}]'
LIMIT 200;

-- Moderate bulk spam
UPDATE wp_comments
SET comment_approved = 'spam'
WHERE comment_content REGEXP '[\\x{3400}-\\x{9fff}]';

-- Permanent purge after review
DELETE FROM wp_comments WHERE comment_approved = 'spam' AND comment_content REGEXP '[\\x{3400}-\\x{9fff}]';
DELETE FROM wp_commentmeta
WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

Step 4 — Drop obvious malware helper tables (name patterns vary—inspect first)

SHOW TABLES LIKE '%seo%';
SHOW TABLES LIKE '%spam%';
SHOW TABLES LIKE '%tmp%';
-- Only after you confirm a table is not required by a real plugin:
-- DROP TABLE wp_something_malware;

Step 5 — Application-level flush

wp cache flush
wp transient delete --all
# If using object cache / Nginx fastcgi cache, clear those too
redis-cli FLUSHDB # only if this Redis is site-dedicated

When to call Fixwebnode: unknown tables, infected page builders (Elementor data in meta), or builders’ brochure sites with heavy custom fields—see also Website Solutions for Canberra Builders & Construction Specialists if your stack is construction-marketing WordPress with complex meta.

Fix issue 4: charset, collation, and “search finds nothing”

Symptom: you paste Japanese text into LIKE and get zero rows though the browser clearly shows spam; or UPDATEs produce ? mojibake.

Step 1 — Check column definitions

SHOW FULL COLUMNS FROM wp_posts LIKE 'post_content';
SHOW FULL COLUMNS FROM wp_options LIKE 'option_value';
SHOW CREATE TABLE wp_posts\G

Step 2 — Force the session to utf8mb4

SET NAMES utf8mb4;
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

Step 3 — Prefer HEX probes when glyphs confuse the client

-- Find rows containing common CJK punctuation / dense multi-byte runs via HEX
SELECT ID, post_title
FROM wp_posts
WHERE HEX(post_content) LIKE '%E7%' -- crude multi-byte lead filter; review manually
LIMIT 50;

Step 4 — Convert tables only with a backup and maintenance window

-- Example conversion (test on a copy first)
ALTER TABLE wp_posts
 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_options
 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_postmeta
 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Update DB_CHARSET / DB_COLLATE in wp-config.php to match, then re-run discovery SELECTs.

When to call Fixwebnode: mixed collations across tables, replication errors after ALTER, or hosting panels that rewrite connection charset. DIY stops at “I might break checkout or memberships.”

Hardening so CJK spam does not return

1. Rotate all DB and WP admin passwords; revoke unused DB users.
2. Reinstall core and extensions from official packages; remove abandoned plugins.
3. Lock file permissions (find writable PHP under uploads is a red flag).
4. Add WAF / rate limits on xmlrpc.php and login; fix contact forms and SMTP so attackers cannot launder spam through you.
5. Schedule a weekly read-only REGEXP audit on wp_posts and wp_options.

# Quick writable-PHP check under uploads
find /var/www/html/wp-content/uploads -type f -name '*.php' -print
# Core checksums if WP-CLI present
wp core verify-checksums
wp plugin verify-checksums --all

When DIY is enough vs when to book Fixwebnode

DIY is enough when you have a fresh mysqldump, fewer than a few dozen clearly identified rows, no serialized blob edits, and you can verify with COUNT queries plus front-end view-source. Stay on SELECT until patterns are obvious; trash before permanent DELETE.

Book Fixwebnode when spam spans posts + options + meta, serialization is involved, WooCommerce or membership data sits in the same tables, malware drops extra tables/users, or reinfection happens within days. Specialists also re-check search-console indexed spam URLs and server logs—not only SQL.

Homeowners and small businesses around Geelong use Fixwebnode as a direct website-support specialist (not a bid marketplace) for this exact class of database cleanup and follow-up hardening. Browse geography coverage under All service areas if you are confirming whether remote SQL work is available for your host.

Talk to Fixwebnode about a clean database

If you want a guided purge, validation queries, and a short reinfection check rather than risking a blind DELETE on production, start a conversation through Website support. Bring hosting SSH details, approximate when the CJK text appeared, and whether you already have a mysqldump—those three items shorten the first pass dramatically.

Keep the focus on measurable outcomes: zero matching REGEXP hits on publish content, clean options autoload list, and public HTML free of Japanese/Chinese keyword blocks. That is the finish line for this job.