Catalog Search Database Issues - Cleaning Out Useless Data

Question

We recently encountered an issue on our Magento site a believe we encountered some type of security or SPAM attack on the search feature of our site, which was overpopulating our mage_catalogsearch database with useless data.

Below is the data for the following tables in phpMyAdmin:

phpMyAdmin (database teamss5_mage1)

mage_catalogsearch_query       1,782,506        InnoDB utf8_general_ci       215.5 MiB     mage_catalogsearch_result       700,538           InnoDB utf8_general_ci         47.6 MiB      

You can see that these rows are storing a considerable amount of data. We are trying to keep our database size down as low as possible to allow for optimal site performance.

When logging into the Magento admin, we are also noticing that when navigating to "Catalog > Search Terms" there are 89,126 pages and a total of 1,782,506 records found. Which seems to be an incredibly unusual large amount of data.

Does anyone have any idea what could have caused this and how we can clean out these pages and records so we can keep our Magento site optimized. Is it possible to truncate or delete these records without affecting our website and causing it to crash? And if so how?

Please let us know what can be done to resolve this issue, any assistance is greatly appreciated!

You can also see the below image link screenshots to better understand the issue:

http://www.elevatemediastudio.com/support/ssc/catalog-search-terms-page.jpg

http://www.elevatemediastudio.com/support/ssc/admin-search-terms-menu.jpg

http://www.elevatemediastudio.com/support/ssc/php-admin-view.jpg

Answer 1

It is not uncommon for the search form to be spammed. Other than random junk, you will often seen SQLi and XSS attempts. I believe that these are from clumsy bots. I don't think that this is a legitimate attack vector (on an up-to-date Magento install).

It is generally safe to edit the catalogsearch_query table in the database. catalogsearch_result is configured to delete rows related to catalogsearch_query by query_id.

Truncating catalogsearch_query will wipe out the unwanted data, but also any legitimate search terms, which appear as hints when users interact with the search form.

More importantly, it will delete any custom search term redirects that may have been configured to return specific pages for certain searches, rather than search results. These contain a value in catalogsearch_query.redirect.

I think that a safe approach is to back-up catalogsearch_query, and then delete unwanted rows based on any patterns that you can find to identify them. E.g. rows with catalogsearch_query.num_results = 0 may be mostly junk. catalogsearch_result should decrease in size accordingly.

Safer still, first do all this on a development site after importing your live database.

FWIW, I tested the following query on a development site:

mysql> SELECT COUNT(*) FROM catalogsearch_query WHERE redirect IS NOT NULL;
+----------+
| count(*) |
+----------+
|      923 |
+----------+
1 row in set (0.02 sec)

mysql> DELTE FROM catalogsearch_query WHERE redirect IS NULL;
Query OK, 44367 rows affected (5.45 sec)

mysql> SELECT COUNT(*) FROM catalogsearch_query;
+----------+
| COUNT(*) |
+----------+
|      923 |
+----------+
1 row in set (0.00 sec)

The reindex process works as normal, and the redirect search terms appear as hints and work as expected.