Hi,
Just starting to try and help out on a small forum that has been blitzed by Russian Spam. Know SQL but not a lot else.
The regular administrator is presumably applying the necessary software updates to make it more bullet proof. She is also new to this.
EXPORTed Members MYSQL D/B to my PC via a .xml file hoping to sort and massage and tease out all the suspicious members. Unless available hope to cobble some Excel functions to sort by email domain name and by IP address, which is not possible in SMF or Excel because of the variable length strings. Would be a helpful feature to have.
Two questions -
1. Dates such as DateRegistered show and sort correctly in SMF but export to .xml as stored in MYSQL as 10 digit integer. How to I convert this number to a correct date format? (Also tried to export to Excel Format - .xls and .csv but file produced errors when opened in Excel)
2. Having identified the spammers, can I safely block delete them directly in MYSQL Members database or will I create problems elsewhere doing this (assuming they have no posts)? There is a large number of them.
Thanks
you are best to not play with the database unless you know exactly what you are doing, it is very easy to break the forum if you dont know what you are doing.
Why don't you export the DB as an .sql file? It will be in plain text that you can manipulate with a text editor.
The long integer you see for timestamps is a Unix-style count of seconds since 1/1/1970 (GMT). There are some random tools floating around the Web for converting to and from date/time representation. Search for "unix timestamp converter".
I agree that you should not directly edit the database outside of phpMyAdmin, as you risk doing great damage to it. However, it's fine to use an editor to search for suspicious names and email addresses, get the member name from it, and use SMF's admin functions to delete that user and all their posts. When SMF is back up, you can put it in maintenance mode ($maintenance = 1) and keep everyone other than the administrator locked out while you clean out spammers.
Thanks for your Helpful Posts Illori and MrPhil, especially for the Epoch dates. This was a very useful site http://www.epochconverter.com/ It has date conversions to all possible formats. I now have a spreadsheet with columns of legible DatesRegistered and LastAccessed.
Am totally comfortable cruising a native SQL database but took your advice and stayed with the forum functions since I don't want to compromise referential integrity and break it.
No Point in Exporting .sql unless I want to set it up on my PC and anyway the dates are still in Epoch format. The export worked a treat straight into Excel with no errors. I applied the date formula to the Epoch dates. Was easily able to manipulate these rows to get my hit list of spammers. Deleted a few hundred and hope things will now improve with the upgraded registration.
Thanks for coming back to me so quickly.