Selectively restoring original posts from a compromised site to a freshly installed WordPress database

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!

Related Posts:

  1. Sub-domain or 2nd, temporary different domain?
  2. Cloning and syncing a WordPress website
  3. WordPress showing install screen after max_questions error
  4. Installation with sql server express edition
  5. Export all content from wordpress
  6. Automate dir and DB stack creation with WP-CLI
  7. Error establishing a database connection on Installation
  8. “Database Connection Error” upon install in Apache VirtualHost document root.. Why did this happen? [closed]
  9. Migrating from PDO using SQLite to clean new install using MySQL
  10. Moving to another server database connection error [closed]
  11. Is it possible to have more than 1 MySQL database in my WordPress blog?
  12. Create database on installation
  13. Reinstalling wordpress from database breaks the site
  14. Choosing http or https during manual installation of WordPress and database
  15. Installation question
  16. Multiple WordPress sites installs sharing the same database but with different content and domains
  17. Image link issues after importing a database backup to my local web server
  18. Database create or redirect
  19. New installation fatal error in 5.9.3 at edit or create entry
  20. WordPress credentials not matched
  21. unable to use my wordpress site after moving
  22. I have a WordPress 4.6 database dump – how do I use it in a new install?
  23. Copy WordPress to new directory
  24. Does WordPress Support Using Named Pipes on the Windows Operating System?
  25. Can’t Install WordPress (local) Failed to open file wp-includes/wp-db.php
  26. Why is wordpress trying to re-install itself?
  27. Install WordPress with SQL database
  28. Getting an error when trying to migrate to DV server from Grid with Media Temple [closed]
  29. WordPress can’t fetch the posts from the database after moving to new server [closed]
  30. Is it fine to have foreign key as primary key?
  31. Detecting errors generated by $wpdb->get_results()
  32. wpdb update add current timestamp not working
  33. Custom form that store input in database
  34. Restoring WordPress posts from database only
  35. Displaying content from one WP site on separate WP site
  36. Lost the `wp_options` table: what’s the best way to restore the site?
  37. Performance Gains of Relational DB Setup
  38. How to move existing WordPress wp-content folder along with database to new server and new domain name?
  39. store simple data in get_option()
  40. How might I retrieve a featured post image from an external WP site and display it as a link back?
  41. What are conventions about the schema of the $table_prefix
  42. Migrating non-WordPress CMS to WordPress, lots of data to move — possible solutions?
  43. BuddyPress: What’s the use of wp_bp_xprofile_data table and how does it get updated? [closed]
  44. storing database in version control
  45. Is deleting orphaned wp_options entries safe?
  46. How to build a movie database with wordpress?
  47. How do i get user data from a custom table in the wordpress database by user ID?
  48. Converting WordPress into Android or iPhone app
  49. Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin
  50. Database slowdown after update to 3.4.1
  51. WooCommerce sku location in DB
  52. How to insert data into MySQL database from the form created in WordPress site
  53. Emoji support and MariaDB 10.0.30-
  54. Can I have multiple database users within WordPress?
  55. How do I copy a wordpress database?
  56. Modify Database in Multi-Site wp_usermeta table
  57. Form Data not getting stored in WordPress database
  58. Hook directly into query execution
  59. $wpdb doesn’t like to store arrays
  60. Connect to a remote database for wordpress in my own hosting server
  61. WordPress ‘repairing database’ in a constant error loop?
  62. What Is the Proper Way to Merge Usermeta Table to a an existing WP Database?
  63. Importing CSV into database table not working?
  64. Why user_pass column in wp_users table is varchar(64)
  65. Best practice to limit results in get_row()?
  66. Which table (and column) has the content of configured get_post_meta?
  67. Serialize data before inserting into the DB
  68. Pages from custom table
  69. Trouble running $wpdb->query() with last_insert_id
  70. Using $wpdb | checking entered email against existing emails in db
  71. wp-cli: Error establishing a database connection: undefined constant DB_USER
  72. Can I customised default WP table
  73. Woocommerce – Check product stock availability from external database
  74. Get results from wordpress data custom table
  75. WordPress where clauses in db select
  76. How to display data from db in select list [closed]
  77. Will adding & modifying default WordPress table index break future WordPress update
  78. Database migration issues – Error #1046 No database selected
  79. echo site_url() returns a different value than what is in the database siteurl field
  80. Search Character Set Problem
  81. Native timestamp on wp_options option
  82. SELECT rows between two datetimes when the range is dynamic [closed]
  83. How to get the full stack trace for WordPress “table doesn’t exist” error in debug.log?
  84. How to fix Uploaded to this post option to see group of images uploaded to a post?
  85. How to access a table in a wordpress database using REST API?
  86. error establishing a database connection 3
  87. Correct and secure way to access a custom SQL database in a custom PHP template file
  88. Missing latest posts and options after DB migration via phpMyAdmin
  89. Custom metabox does not store data
  90. How do i get value from wp_postmeta?
  91. Local host to server import problem
  92. Restoring .sql backup results in “Error establishing a database connection”
  93. “MySQL server has gone away” since update to 3.8
  94. featured image not showing up with mysql_connect
  95. Getting deleted users in database
  96. Options of accessing wordpress data from mobile app
  97. Getting data from a table using a query
  98. Database interaction (private-public) [closed]
  99. wp_usermeta key problem
  100. Database Queries are crashing the server