WordPress

WordPress

Creating a clone of a WordPress site using UpdraftPlus

1. If there is an existing clone, delete the existing clone: files and database.

Set the PHP version to 5.5 - was this an error? Yes, it was! Put it to 5.3

1. Install WordPress.
2. Install the UpdraftPlus Backup and Restoration plugin.
3. Do a full back up the existing system to get the five .ZIP files (database, plugins, themes, others and uploads).
4. Go to the new site.
5. Upload the five .ZIP files
6. Restore the backups

The web site will now be broken as the links will be pointing at the old site.

1. https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ v3 ← did not work
2. https://github.com/daelan/WordPress-Migration-Script ← did not work
3. https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ v2 ← did work

Change the chmod for searchreplacedb2.php from 664 to 644 to prevent the error:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

1. I was moving conscienceonline.org.uk to 11112018.org.uk/conscience so I entered

conscienceonline.org.uk/

and

11112018.org.uk/conscience/

as the parameters and it made 53,293 changes. Without the trailing '/' it made 53,412 changes but that will have included email addresses too. Not allowing GUID fields to be changed (whatever that means) takes it down to 50,058.

That can't be all that is required because going to http://www.11112018.org.uk/conscience/ results in a page not found "This is somewhat embarrassing, isn’t it?" and links pointing to the likes of http://www.conscienceonline.org.uk/2015/10/last-day-at-conference/

Entering

conscienceonline.org.uk

and

11112018.org.uk/conscience

seems to have fixed it.

Also, it is necessary to go into the admin screen and change the permalink settings away from 'Month and name' and back again. That is under Settings, Permalinks. Apparently that fixes the .htaccess file somehow.

I use a custom favicon.ico - this is saved in among the images in the database. Change that with Appearance, Customise, Site Identity, Site Icon.

Purging old blocked IP addresses from IP Blacklist

The WordPress plugin IP Blacklist is very helpful for stopping spam. But the blacklist it creates needs clearing down from time-to-time. I suspect the provided function to delete IP addresses that have not attacked in the last 90 days may not be optimal. Instead I want something like this:

Any IP address in the range a.b.c.* that has done an attack in the last six months results in every address in a.b.c.* not being deleted.

The blacklisted IP addresses are stored in table wp_IPBLC_blacklist. The timestamp and lastvisit fields are in seconds, seemingly since the start of 1970. lastvisit is only set on records that already exist, it is zero if the IP address has not yet revisited.

If they have not revisited for 6 months, I'm happy to delete them.

This query returns the same list of records to be deleted as the IP Blacklist plugin:

SELECT   *
FROM     `wp_IPBLC_blacklist`
WHERE        ( UNIX_TIMESTAMP(NOW()) - lastvisit > 90 * 24 * 3600)
         AND ( UNIX_TIMESTAMP(NOW()) - timestamp > 90 * 24 * 3600)
ORDER BY `wp_IPBLC_blacklist`.`id` ASC

This returns anything that was blacklisted 100 days ago, and has not been back since:

SELECT   *
FROM     `wp_IPBLC_blacklist`
WHERE        ( UNIX_TIMESTAMP(NOW()) - TIMESTAMP > 100 * 24 * 3600 )
         AND ( visits = 0                                         )
ORDER BY `wp_IPBLC_blacklist`.`id`  ASC

and this deletes anything that was blacklisted 100 days ago, and has not been back since:

DELETE
FROM     `wp_IPBLC_blacklist`
WHERE        ( UNIX_TIMESTAMP(NOW()) - TIMESTAMP > 100 * 24 * 3600 )
         AND ( visits = 0                                         )

This returns anything where they attacked over 90 days ago and haven't been back, or if they have been back, it must be at least 180 days plus 10 days for every visit:

SELECT   *
FROM     `wp_IPBLC_blacklist`
WHERE        ( UNIX_TIMESTAMP(NOW()) - lastvisit > (180 + visits*10 ) * 24 * 3600)
         AND ( UNIX_TIMESTAMP(NOW()) - timestamp >   90               * 24 * 3600)
ORDER BY `wp_IPBLC_blacklist`.`id` ASC

Purging all binned spam where the IP address is blacklisted

The WordPress plugin IP Blacklist was helpful for stopping spam but no longer exists. However, the IP address blacklist function still works. I want to use it to do this:

For any comment in the Bin queue, if its IP address is already blacklisted, just delete the comment.

The blacklisted IP addresses are stored in table wp_IPBLC_blacklist. This query returns the blacklisted IP addresses:

SELECT IP FROM `wp_IPBLC_blacklist`; 

I also need to know the comments in the Bin which is these:

SELECT * FROM `wp_comments`  
WHERE  `comment_approved` = 'trash';

Now to join both queries:

SELECT	 *
FROM	 `wp_comments`
WHERE	 `comment_approved` = 'trash'
  AND	 `comment_author_IP` IN ( SELECT IP FROM `wp_IPBLC_blacklist` )
ORDER BY `comment_ID` DESC;

That seems to work OK. This is the delete:

DELETE
FROM	 `wp_comments`
WHERE	 `comment_approved` = 'trash'
  AND	 `comment_author_IP` IN ( SELECT IP FROM `wp_IPBLC_blacklist` );

Checking IP addresses using Simple Login Log

The plugin Simple Login Log gives a list of successful and failed logins. This query returns IP addresses of failed logins:

SELECT ip, MIN(time), MAX(time) , COUNT(1) FROM `wp_simple_login_log` WHERE login_result = 0 GROUP BY ip

And this returns successful logins:

SELECT ip, MIN(time), MAX(time) , COUNT(1) FROM `wp_simple_login_log` WHERE login_result = 1 GROUP BY ip

so a 'login_result' of 1 is successful and 0 is failed.

A list of IP addresses which have had successful logins:

SELECT DISTINCT ip FROM `wp_simple_login_log` WHERE login_result = 1

A list of IP addresses which have had failed logins:

SELECT DISTINCT ip FROM `wp_simple_login_log` WHERE login_result = 0

A list of IP addresses which have had successful logins but never any failed ones:

SELECT DISTINCT ip FROM `wp_simple_login_log` WHERE login_result = 1 AND ip NOT IN ( SELECT DISTINCT ip FROM `wp_simple_login_log` WHERE login_result = 0) ORDER BY ip

A list of IP addresses where someone has got their password wrong and also someone has logged in successfully:

SELECT ip                IP      ,
       user_login        Who     ,
       COUNT(1)          Attempts,
       SUM(login_result) Failed
FROM   `wp_simple_login_log`
WHERE  ip IN ( SELECT ip
               FROM   `wp_simple_login_log`
               WHERE  login_result = 0
                 AND  ip IN ( SELECT ip
                              FROM   `wp_simple_login_log`
                              WHERE  login_result = 1
                            )
             )
GROUP BY ip

If you recognise these IP addresses, you could whitelist them.

This is a list of IP addresses where there have been failed logins, no successful logins, listed by the number of login attempts and showing the date range over which they occurred.

SELECT   ip                    IP      ,
         MIN(time)             'From'  , 
         MAX(time)             'To'    , 
         MAX(time) - MIN(time) Duration, 
         COUNT(1)              Attacks
FROM     `wp_simple_login_log` 
WHERE    login_result = 0
  AND    ip NOT IN ( SELECT ip
                     FROM   `wp_simple_login_log` 
                     WHERE  login_result = 1
                   )
GROUP BY ip 
ORDER BY COUNT(1) DESC