1

My site has recently been compromised. If I restore the full database, google says there is hacked content on my site. The hacker has created many new garbage posts in my site - which already got listed on google SERP.

Is there a way how I can restore only my actual posts one by one on the freshly installed WordPress database or cleanup the database and restore fully?

I have the backup of the compromised database. I have to build the site again from scratch if I can't restore my posts.

9
  • If you are happy editing an SQL file, then you can extract the posts you need using a text editor, yes. The process will depend a little on the format of your backup. Jul 26 '16 at 9:07
  • My file is on SQL format. Can I edit it with Notepad++? Thanks!
    – Hasan
    Jul 26 '16 at 11:43
  • I've opened the SQL file with Notepad++ and found that it is full of garbage data alongside my post data. Is there any SQL editor which I can use in my local machine to clean the file? It is impossible to clean with a text editor.
    – Hasan
    Jul 26 '16 at 11:53
  • The "garbage data" is probably obscured hacker code. If you can see SQL commands of the form "INSERT INTO ..." followed by a number of lines of data then what you are trying to do is delete the lines with the hacked data in and keep lines with real posts in. There may be programs that read and edit SQL dump files but I don't know them (and I don't use Windows). Jul 26 '16 at 12:00
  • They are in the form of INSERT INTO followed by big chunks of code - some kind of encrypted.
    – Hasan
    Jul 26 '16 at 12:11
0

Take a copy of your data file and store it safely just in case you mess up - it's easily done. Make sure it is labelled as hacked so you can't accidentally use it anywhere.

Look in your SQL for a section starting with:

INSERT INTO `wp_posts` VALUES

and eventually ending with a semicolon.

This is all of your post data. Hopefully you have one line for each post otherwise this will be very hard. This does not include any categories, tags or custom fields.

You will hopefully see lines starting something like this:

INSERT INTO `wp_posts` VALUES (1,1,'2015-03-03 11:42:37','20
(2,1,'2015-03-03 11:42:37','2015-03-03 11:42:37','what is th
(3,1,'2015-03-03 11:51:21','0000-00-00 00:00:00','','Auto Dr
(5,1,'2015-03-03 11:54:41','2015-03-03 11:54:41','','Berry a
(6,1,'2015-03-03 11:55:16','2015-03-03 11:55:16','what is th
(7,1,'2015-03-03 11:55:21','0000-00-00 00:00:00','','Auto Dr
(8,1,'2015-03-03 12:00:01','2015-03-03 12:00:01','We are a n
(9,1,'2015-03-03 12:00:01','2015-03-03 12:00:01','We are a n
(10,1,'2015-03-03 12:01:30','2015-03-03 12:01:30','Due to th
(11,1,'2015-03-03 12:01:30','2015-03-03 12:01:30','Due to th
(12,1,'2015-03-03 12:01:43','2015-03-03 12:01:43','Applicati
(13,1,'2015-03-03 12:01:43','2015-03-03 12:01:43','Applicati
(14,1,'2015-03-03 12:02:06','2015-03-03 12:02:06','Participa
(15,1,'2015-03-03 12:02:06','2015-03-03 12:02:06','Participa
(16,1,'2015-03-03 12:03:16','2015-03-03 12:03:16','Walk for 
(17,1,'2015-03-03 12:03:13','2015-03-03 12:03:13','','461569

You "simply" (and this can be slow, careful work) want to delete the lines which have hacked data in. Any line that doesn't look like your original post can go.

Examining one of those lines in detail and formatting it to see what's what:

INSERT INTO `wp_posts` VALUES (
1, -- this is the Post ID
1, -- this is the author ID
'2015-03-03 11:42:37', -- post date
'2015-03-03 11:42:37', -- post date in GMT

and then:

'Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!',

That field is your post content. If that looks like you don't want it in your database then delete the whole line from ( up to ),

'Hello world!', -- Post title
'', -- Excerpt
'publish', -- status - all live posts will have "publish" here

-- and so on
'open','open','','hello-world','','',
'2015-03-03 11:42:37','2015-03-03 11:42:37',
'',0,'http://testsite.localhost/?p=1',0,'post','',1),

If any fields have something in that isn't plain English (if English your posts are) or clearly a simple value, date or URL related to your site then get rid of the line.

While doing this manually can be done, if there are many posts I would really consider getting someone who knows their way around WP & MySQL to clean the data for you. (Not me at the moment, I should add!!)

Backup your clean site before trying to import records just in case.

Good luck!

4
  • Thanks for the details answer @Andy Macaulay-Brook! I should follow it but regret not being able to do so. The values are so long in a hexa type code in very long lines, I can't find out one comma to another. This is a complete mess! I need a tool or have to do it somehow in MySql. I am downloading WAMP server in my local machine now. Will try to import the sql file and see if I can delete them record by record on the table. Really appreciate your answer! It might help others in similar case of mine.
    – Hasan
    Jul 26 '16 at 13:08
  • Yes deleting record by record in MySQL is what I would do. Then if your SQL is up to it, get a list of post IDs from the clean table and use it to filter the meta and the taxonomy relationships. Jul 26 '16 at 13:10
  • I just installed MySql in my local machine and tried to impoirt the SQL file on it, but failed. It says something about syntex. But I was able to import in on the MySQL server of my hosting. I am lost! Any help?
    – Hasan
    Jul 26 '16 at 16:47
  • What's the exact error? Is this the edited file? Jul 26 '16 at 17:04
-1

You can restore the full database and use Bulk Delete to delete the unwanted posts.

Use the plugin filters to detect the spam posts by age, category or author.

I would also recommend you temporarily install WordFence Security and run a scan to make sure you are all clear.

5
  • If you restore a compromised database, possibly with code to spread the hack within that data, you run the risk of spreading the problem on the new clean site. Jul 26 '16 at 9:41
  • I have installed the premium version of WordFence Security and scanned everything. All clear - no problem right now. All the files there are actually from the fresh install of WordPress, except a child theme and a plug-in written by myself which I checked manually and found no malicious code inside.
    – Hasan
    Jul 26 '16 at 11:42
  • I once uploaded the infected database (and later cleaned by WordFence premium) and google search console immediately sent me message that I have hacked content on my site. After that I've deleted the database and all the files on the account, and freshly installed WordPress. Now the database file from the infected site is in my hard disk. I want to upload it only after cleaning. Any more suggestions? Thanks for the comments!
    – Hasan
    Jul 26 '16 at 11:42
  • It's important to make a reconsideration request from Google after cleaning your site, and give them time to process it. Something else to try is to import your infected database to your local phpMyAdmin. Go to the wp_posts table and export just that table as a .csv file. Open that up in Excel or another spreadsheet application (e.g. Google Sheets), and delete every row apart from your original posts. Save the .csv. Then go to your clean, blank WordPress database, truncate the wp_posts table and import your cleaned csv.
    – johncarter
    Jul 26 '16 at 15:19
  • Thanks for the suggestion @johncarter! I've installed WAMP in local machineand tried to import the database, bust failed to do so, as phpMyAdmin is saying teh file is not from the latest version. I gave up regarding this and making everything from the scratch. Already submitted a review request to Google. Hope they remove the manual action soon. I know am feeling the pain of data loss! :(
    – Hasan
    Jul 28 '16 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.