Dealing with WordPress blog spam

Off-topic, but as a minor “giving back” to everyone who helps WordPress bloggers fight spammers.

I discovered this afternoon that a few of my posts had 200 or so links each to sites selling various pain medications. You probably missed it, as did I, because they were embedded between tags styled display:none, so they would show only to search engines. That’s still bad, because it increases my bandwidth load and could lead to Google deciding that this was a spam site and dropping it from their index.

So it had to be fixed.

I read up here:

• http://ocaoimh.ie/2008/06/08/did-your-wordpress-site-get-hacked/

• http://thenondesigner.com/wordpress/hidden-spam-in-wordpress-displaynone/
• http://wordpress.org/support/topic/221431

and installed the Bad Behavior plugin.

Then I went to work in the database. If any of the following puzzles you, PLEASE STOP READING HERE. I’m not guaranteeing that this will work for you. It may blow up your blog, translate your categories into French (or perhaps English), or cause your hair (or mine) to fall out.

1. I backed up the entire database via the phpMyAdmin interface, and also copied the table containing my posts, citizentools_posts in this case.
2. I was able to determine that the infected posts had the spam text right at the end, i.e. those posts ended with <u style=display:none> 100′s of bad links </u>
3. so I used this criterion to find them:
   
SELECT * from citizentools_posts
WHERE
REPLACE( `post_content` , "</u>", CHAR( 10 ) )
REGEXP
CONCAT( '^.*<u style=display:none>.+', CHAR( 10 ) , '$' )
and

4. then ran this SQL to fix them, tagging each fixed post with <–nospam–> so I could backtrack if needed
   
UPDATE citizentools_posts
SET `post_content` =
CONCAT('<!--nospam-->',SUBSTRING_INDEX(post_content,'<u style=display:none>',1))
WHERE
REPLACE( `post_content` , "</u>", CHAR( 10 ) )
REGEXP
CONCAT( '^.*<u style=display:none>.+', CHAR( 10 ) , '$' )


Thanks to the posters before me – at the links above and elsewhere – who dealt with this and left careful notes.