MySQL commands for replace domains and server paths

Useful MySQL commands for replacing domain URL and file path.

UPDATE wp_options SET option_value = replace(option_value, 'http://www.awesome.org','http://www.awesome.dev/cwsa.org');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.awesome.org','http://www.awesome.dev/cwsa.org');
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.awesome.org', 'http://www.awesome.dev/cwsa.org'), guid = replace(guid, 'http://www.awesome.org','http://www.awesome.dev/cwsa.org');


UPDATE wp_options SET option_value = replace(option_value, '/public_html/awesome','/var/www/awesome');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'/public_html/awesome','/var/www/awesome');
UPDATE wp_posts SET post_content = replace(post_content, '/public_html/awesome','/var/www/awesome'), guid = replace(guid, '/public_html/awesome','/var/www/awesome');

WP-CLI Guide: Install WordPress via SSH

WP-CLI is a very useful utility if you have the luxury of getting a server that comes with it plus ssh. This is very useful for those who need to constantly launch new WordPress sites.

Create a new directory then execute
wp core download

if you get an error "Error: WordPress files seem to already be present here." Add the force flag.
wp core download --path=.

Downloading WordPress 4.8.1 (en_US)...
md5 hash verified: f3dd0e033519aa363eb07e13c6676e3c
Success: WordPress downloaded.

Now configure your config file
wp core config --dbhost=localhost --dbname=forum --dbuser=wordpress --dbpass=wordpress --path=.
Again here –path=. for those who have wordpress install in the root directory.

Success: Generated 'wp-config.php' file.

Install WordPress
wp core install --url=example.com --title=Example --admin_user=supervisor --admin_password=strongpassword --admin_email=info@example.com --path=.
Error: Can’t select database. We were able to connect to the database server (which means your username and password is okay) but not able to select the `forum` database.

Database ‘forum’ is missing. You need to create the database in MySQL.

After creating the database run the command again.

Success: WordPress installed successfully.

Done!

Automated Multiple WordPress Version Setup

Automated WordPress Setup
Automated WordPress Setup

For plugin development, you need to maintain a number of WordPress versions for testing. I need to at least install 5 version of WordPress if I start with 4.7.
The setup of a normal setup is:
1. Create database, user and password
2. Download WordPress, unzip, move to expanded folder
3. Copy and rename wp-config-sample.php to wp-config.php and add-in the credentials
4. WordPress core is updated automatically, you need to remove auto update feature by adding a line in wp-config.php

The Problem
Repeating the task everything you need a clean testing environment can be tedious.

Of course, there is WP-CLI the command line interface for WordPress but many web-hosts do not have WP-CLI installed, sometimes they have a strip down version, others they simply don’t work. If your host is provided with ssh, we could write a script for these, if not a good option will be to create all the files and then send it up by FTP.

The Solutions
For now, we will work with SSH. The solution will consist of 2 parts, a script to install the files and setup the database and another to open up the browser and complete the setups.

Part 1: The Server Script
We need a script that can be executed on the server that will download WordPress version 4.7.1 – 4.8. Since the archive version URL naming is quite standard
https://wordpress.org/wordpress-4.7.1.tar.gz
https://wordpress.org/wordpress-4.7.2.tar.gz
https://wordpress.org/wordpress-4.7.3.tar.gz
https://wordpress.org/wordpress-4.7.4.tar.gz
https://wordpress.org/wordpress-4.7.5.tar.gz
https://wordpress.org/wordpress-4.8.tar.gz

We can easily use a bash script to loop through and grab them with wget or curl.

#!/bin/bash
arr=( "4.8" "4.7.5" "4.7.4" "4.7.3" "4.7.2" "4.7.1" )
## now loop through the above array
for i in "${arr[@]}"
do
curl -O https://wordpress.org/wordpress-$i.tar.gz
done

To complete the code, we need to unzip, rename the folder, add and insert our credentials to the wp-config.php
I modified a code by bgallagh3r.

We still need to add the code for creating the database. Since most web-host do not allow the use of root for MySQL, you need a user id that has the privilege of creating databases. If not, you can only create it manually through your web interface.

function createdatabase () {
MAINDB=$1
ROOTPASSWD=$3
mysql -u$2 -p${ROOTPASSWD} -e "CREATE DATABASE IF NOT EXISTS ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;"
}

During my research phase I came across some solution that generates different username and password for each site, but I prefer to keep it simple with just one username and password for all sites. I anticipate that these sites will be torn down once the testing is done.

Part 2: The Client-Side Script
We need a script to open up the browser and input, select and click through the steps. I choose Python and selenium. You can find out more here

First, download Python bindings for Selenium.
sudo pip install selenium

We need ChromeDriver that can be download here and read the ChromeDrive documentation here

from selenium import webdriver
driver = webdriver.Chrome('/path/to/chromedriver') # Optional argument, if not specified will search path.
driver.get('http://www.google.com/xhtml');

Test and make sure that the path to ChromeDrive is correct and Selenium is installed correctly. This should launch google on a new browser window.
Now let’s work on our script.

Automated WordPress Setup Part 2
Automated WordPress Setup Part 2

Now let see how the whole thing runs on the client side.

See how easy it is to automate everything with a few lines of code.

A tool to convert PSD to PNG

There is a Command-line (Terminal) tool from Telegraphics called PSDParse that parse Photoshop PSD format files and dump layers to PNG files.

psd2png.exe -w Filename.psd

usage: psd2png.exe [options] psdfile…
-h, –help show this help
-v, –verbose print more information
-q, –quiet work silently
-w, –writepng write PNG files of each raster layer (and merged composite)
-d, –pngdir dir put PNGs in directory (implies –writepng)
-m, –makedirs create subdirectory for PNG if layer name contains \’s
-l, –list write an ‘asset list’ of layer sizes and positions
-s, –split write each composite channel to individual (grey scale) PNG

Plugin for a productized service

If you are looking to launched a productized service like wpcurve.com. There are currently no plugins that support this fully.

I am cobbler together a few plugin and duct tape one for proof of concept.

The Required Plugins:
Responsive Pricing Table
WP Full Stripe Free
Contact Form 7

The Main Setup
First Setup your 3 tier offer using Responsive Pricing Table

Create all the payment type in WP Full Stripe Free.

In each payment type create a Thank You page with in contact from 7. Here you collect all the necessary information from your customer e.g. email address, web URL.

The duct tape Part
Under the 3-tier offer that you have setup in Responsive Pricing Table. On each button link, link to the payment type you have created.

On the payment form that you created in WP Full Stripe Free, add the page under “Redirect to” select the correct thank you  page.

Basically we are just creating 1 offer, 1 payment and 1 contact from for each tier that we offer.

Missing?

First recurring payment is not setup, this can be easily fix by upgrading  WP Full Stripe Free to Pro, which include recurring payment. However, I would like to allow clients to create an account, purchase a subscription, change subscription type of cancel subscription. How about support/ job tickets tracking, allow clients to upload files such as backups. Ideally I could create reports and send notifications for clients, admin and maybe staffs.

Would you be interested in a plugin that have all these features?

Deleting Woocommerce products using Mysql commands

mysql> DELETE relations.*, taxes.*, terms.*
-> FROM wp_term_relationships AS relations
-> INNER JOIN wp_term_taxonomy AS taxes
-> ON relations.term_taxonomy_id=taxes.term_taxonomy_id
-> INNER JOIN wp_terms AS terms
-> ON taxes.term_id=terms.term_id
-> WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');
Query OK, 497631 rows affected (4 min 10.74 sec)

mysql>
mysql> DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');

Query OK, 1419875 rows affected (13 min 16.68 sec)

mysql> DELETE FROM wp_posts WHERE post_type = 'product';
Query OK, 66618 rows affected (51.91 sec)

Very useful commands especially when you need to purge large amount of products.

Replacing WordPress Cron With A Real Cron Job

In many situations, the WP-Cron may not working well or work at all. By default WordPress is setup to call wp-cron.php only when someone visits your site.

A better way to use the cron job that is available on your hosting server. In your cpanel, look for your cron job icon, click on it and select Add New Cron Job, then add the following line.

wget -q -O - http://myurl.com/wp-cron.php?doing_wp_cron >/dev/null 2>&1

Remember to replace “myurl.com” with your actual url of your site.

If you have access to ssh, simply type

crontab -e

This will open a file for you where you will be able to add the line above.

Useful Bash Script for Malware Removal

If you have access to your wordpress server via ssh, there some bash command that you can run to check if your files are infected with malware. This can be very helpful if you host a whole bund of sites on the same server.

Using Obfuscalp.

Obfuscalp is an open source php tool to finds and removes obfuscated suspicious/malicious code planted inside PHP and other scripts.
git clone https://github.com/Orbixx/Obfuscalp.git
cd Obfuscalp
php find.php /path/to/a/bunch/of/php/sites > infected.txt
...
Processed 3950000 files, found 30
Processed 3960000 files, found 30
Processed 3970000 files, found 30

php remove.php infected.txt
...
Processing file 28 of 30 (%93.33)
Processing file 29 of 30 (%96.66)
Processing file 30 of 30 (%100)

Find and delete any php files in upload folder
findwp-content/uploads/ -name "*.php" -type f -delete

remove text and html files
find . -name "*.txt" -type f -delete
find . -name "*.html" -type f -delete

look for .js.php extension files
find . -name "*.js.php" -type f

look for files that are updated in the last 60mins
find -iname "*.php" -type f -amin -60 # access time

Look for commonly injected scripts

find . -name "*.php" -type f -exec grep -q "function setCookie(a,b,c)" {} \; -print
find . -name "*.php" -type f -exec grep -q "@$GLOBALS[$GLOBALS['l8f127f'][89].$GLOBALS['l8f127f'][28].$GLOBALS['l8f127f'][22]" {} \; -print
find . -name "*.php" -type f -exec grep -q "\x47L\x4fBA\x4c\x53" {} \; -print
find . -name "*.php" -type f -exec grep -q "$sab=$_COOKIE;\x0d\x0a$jiu=$sab[qsll]" {} \; -print

Once you have clean all the files and backup, secure the files and directory with the correct permission.

find . -type f -exec chmod 644 {} \;
find . -type d -exec chmod 755 {} \;rm

These are just some of the scripts that I used, I will be updating once I got some new sits to clean up.