Request for help with writing a MySQL Query
2 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: November 18, 2013 (RSS)
By JeffC - November 16, 2013
My cmsb has been hacked. My own fault - I have a form without recapture and I have received 87,000 spam entries.
I would like to export all of the entries so that I can sort the genuine from the spam using Apple's 'Pages' software
I have the csv export plugin but it can't handle 87,000 entries (Just gives up on the export after a while). I also have the MySQL console plugin which I am hoping will do the trick but would like some advice as to what the query should be.
The offending files are in a table called contacts.
To keep the export a manageable size (Pages wasn't designed for large databases it seems) I would like to export the files in batches of 5,000
thanks
By Dave - November 18, 2013
Hi Jeffncou,
First, backup the 'contacts' table under: Admin > General > Backup & Restore > Database Backup > "contacts" > then click "Backup". This will create a mysql backup file that you can restore or download as needed.
Next, the MySQL to output the entire table is "SELECT * FROM cms_contacts" (replace cms_ if you have a different table prefix). And you might be able to copy and paste from that page (or connect to your mysql directly with another program and export to CSV.
That said, if you see a pattern in the spam comments you might be able to erase a large batch of them with one mysql command. Just be careful is it's very easy to also erase "everything" with a single mysql command.
Here's an example of a command that erases all the comments where the first and last name match (a common spammer pattern):
DELETE FROM cms_comments WHERE first_name = last_name
If you'd like me to take a quick look I can see if I can erase a large block of the spam entries and save you some time. Just email me CMS login details and the URL of this forum post to dave@interactivetools.com (do not post login details to the forum).
Hope that helps!
interactivetools.com