SQL to remove spam entries from Gravity Forms entries

extended-spam-query.sql

-- 1) Create a temporary table to store rows to delete
CREATE TEMPORARY TABLE `gf_spam_entries` AS
SELECT `entry_id`
FROM `wp_gf_entry_meta`
WHERE ( 
       LENGTH(meta_value) > 700
       OR LOWER(`meta_value`) REGEXP '^(http|\\<a href)'
       OR `meta_value` REGEXP '[\\p{Cyrillic}]'
       OR `meta_value` REGEXP 'http.+\\.(ru|cz|de|jp|bz|bx|php|asp|xyz|ua|tk|icu|bit\\.ly)'
       OR LOWER(`meta_value`) REGEXP '([a-z0-9._%+-]+)@(.*\\.)?(ru|xy|xx|xv|cz|cc|de|xyz|bagat|[a-z]maill)'
       OR LOWER(`meta_value`) REGEXP 'eric[\\W._]*jones|quick\\Wloans|top\\Wsecret|\\Wdating|\\%\\Woff'
       OR LOWER(`meta_value`) REGEXP '>>>>>|<<<<<|altcoin|bitcoin|crypto|payday|claim your|casino|freesite|free shipping|serp|adsense|analytics|webtraffic|seo report|seo strategy|promo |bad credit|kamagra|bitch|big ass|big dick|a dick|cunt|orgasm|fuck|lesbian|milf|oral|pussy|porn|sex|webcam|hidden cam|locker room|viagra|cialis|free trial|free demo|free download|win|prize|limited time|act now|no cost|sba loan|drunk|dui'
);

-- 2) Delete rows from entry table with the same id
DELETE FROM `wp_gf_entry`
WHERE `id` IN (SELECT `entry_id` FROM `gf_spam_entries`);

-- 2) Delete rows from meta table with the same id
DELETE FROM `wp_gf_entry_meta`
WHERE `entry_id` IN (SELECT `entry_id` FROM `gf_spam_entries`);

-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS gf_spam_entries;

remove-spam-gf.sql

-- 1) Example: Select rows with Cyrillic characters to double check the REGEXP
SELECT *
FROM `wp_gf_entry_meta`
WHERE `meta_value` REGEXP '[\\p{Cyrillic}]'

/*
-- 1) Create a temporary table to store rows to delete
CREATE TEMPORARY TABLE spam_entries AS
SELECT entry_id
FROM wp_gf_entry_meta
WHERE meta_value REGEXP '[\\p{Cyrillic}]';

-- 2) Delete rows from entry table with the same id
DELETE FROM wp_gf_entry
WHERE id IN (SELECT entry_id FROM spam_entries);


-- 2) Delete rows from meta table with the same id
DELETE FROM wp_gf_entry_meta
WHERE entry_id IN (SELECT entry_id FROM spam_entries);

-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS spam_entries;
*/