Categories
MySql wordpress

SQL to extract order report

SELECT pm.meta_value AS city,p.ID as order_id,p.post_excerpt as
customer_note,p.post_date as order_date,pm2.meta_value as
suburb,pm3.meta_value as customer_id,
pm4.meta_value as sender_email,
pm5.meta_value as sender_firstname,
pm6.meta_value as sender_lastname
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
LEFT JOIN wp_postmeta pm2 ON p.ID = pm2.post_id
LEFT JOIN wp_postmeta pm3 ON p.ID = pm3.post_id
LEFT JOIN wp_postmeta pm4 ON p.ID = pm4.post_id
LEFT JOIN wp_postmeta pm5 ON p.ID = pm5.post_id
LEFT JOIN wp_postmeta pm6 ON p.ID = pm6.post_id
WHERE p.post_type='shop_order'
AND p.post_status='wc-completed'
AND pm.meta_key='_shipping_address_2' AND pm.meta_value='Durban'
AND pm2.meta_key='_shipping_city'
AND pm3.meta_key='_customer_user'
AND pm4.meta_key='_billing_email'
AND pm5.meta_key='_billing_first_name'
AND pm6.meta_key='_billing_last_name'
ORDER BY p.post_date DESC

This SQL string will extract multiple meta values from an order.

Categories
php Plugin wordpress

WP REST with JWT

Using the WordPress REST API with JWT authentication

Step 1. Install this plugin => https://wordpress.org/plugins/advanced-access-manager/. The free version will suffice. This goes on the site you will be xtracting data from. The REST API must be enabled in the WordPress settings.

Step 2. Configure the plugin and on it’s Settings tab add the Secret as per this page => https://aamplugin.com/article/how-to-authenticate-wordpress-user-with-jwt-token

On the Page pulling the data from the above server, use the following PHP code :

function getToken()
{
	$token="";
	$html="<div>";
	
	$postRequest = array(
		'username' => 'ANY_ADMIN_USERNAME',
		'password' => 'THE_PASSWORD_FOR_SAID_USER'
	);
	$h=http_build_query($postRequest);
	$ch = curl_init("https://YOUR_SERVER_NAME.COM/wp-json/aam/v1/authenticate");
	curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS,$h);
	curl_setopt($ch, CURLOPT_FAILONERROR, true);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_VERBOSE, true);
	
    $server_output = curl_exec ($ch);
	$info = curl_getinfo($ch);
	//$html="<pre>".print_r($info,true)."</pre>";
	//echo $html;
    if ($server_output === false)
	{
        die('Error getting JWT token on WordPress for API integration.');
    }
    $server_output = json_decode($server_output);

    if ($server_output === null && json_last_error() !== JSON_ERROR_NONE)
	{
        die('Invalid response getting JWT token on WordPress for API integration.');
    }
    if (!empty($server_output->token))
	{
        $token = $server_output->token; # Token is here
        curl_close ($ch);
        return $token;
    }
	else
	{
        die('Invalid response getting JWT token on WordPress for API integration.');
    }
    return false;
}

The when you need the token, call it like such :

$mytoken=getToken();

And that’s about it…..

Categories
Linux wordpress

WordPress Security

This applies to self-managed Apache2 servers. Shared servers require different permissions, for example wp-config : set that file’s permissions to 440 or 400.

Site Lockdown

File permissions to lock down website, from the websites home folder. Do this from the root directory for example and you will break your server.


chown root:root  -R * 
find . -type d -exec chmod 755 {} \;
find . -type f -exec chmod 644 {} \;

Change the folder ownership of the site to root

chown root:root -R * 

Change to wp-content/uploads (to allow uploads)

chown www-data:www-data -R * 

To edit any files via ftp change that ownership to the ftpuser:wwwdata

chown username:www-data -R * 

If you get asked for ftp details when trying to upgrade wordpress, or any plugins or themes, you need to add the following to wp-config.php

define('FS_METHOD','direct');

Refer to https://wordpress.org/support/article/hardening-wordpress/ for more details, especially those regarding MySql.

On the server install

Denyhosts

Disable root login

Install rkhunter (root kit hunter) to check for vulnerabilities.

sudo apt-get install rkhunter

Perform check with

sudo rkhunter --check --skip-keypress

or on first run

sudo rkhunter --checkall --skip-keypress

And keep it updated with

sudo rkhunter --update

For Ubuntu server you may have to “fix” /etc/rkhunter.conf

UPDATE_MIRRORS=0 to UPDATE_MIRRORS=1
MIRRORS_MODE=1 to MIRRORS_MODE=0
WEB_CMD="/bin/false" to WEB_CMD=""
Categories
php Plugin wordpress

WordPress Basic Plugin

Below is a very simple working example of a plugin. Create a directory in your plugins directory, create a php file and copy the below into it.

Then from the wordpress plugins menu simply activate it.

Just know that this plugin does absolutely nothing other than to show the basic setup, including public and protected variables.

<?php
/*
	Plugin Name: Plugin Demo
	Plugin URI: https://southcoasthosting.com/
	Description: Just a plugin demo to show how to set up the class and add an admin menu item
	Author: Gavin Simpson
	Version: 1.0
	Author URI: https://southcoasthosting.com
*/

class plugindemo
{
	protected $namespace;
	public $posttype="plugindemo";
	public static function init()
	{
		$class = __CLASS__;
		new $class;
	}
	public function __construct()
	{
		$this->namespace=test;
		add_action( 'admin_menu', array($this,'my_plugin_menu'),10,0);
	}
	public function test_function($namespace)
	{
		$this->namespace=$namespace;
	}
	function my_plugin_menu()
	{
		
		if (current_user_can('administrator'))
		{
			add_menu_page( 'SCHS Menu', 'SCHS Menu', 'manage_options', 'schs-menu', array($this,'my_plugin_menu_page_callback'),null,1);
			add_submenu_page( 'schs-menu', 'SCHS Submenu', 'SCHS Submenu', 'manage_options', 'schs-submenu', array($this,'schs_submenu_page_callback'));
		}
	}
	function my_plugin_menu_page_callback()
	{
		echo "<div class='wrap'>";
		echo "<h1>".esc_html(get_admin_page_title())."</h1>";
		echo "</div>";
	}
	function schs_submenu_page_callback()
	{
		echo "<div class='wrap'>";
		echo "<h1>".esc_html(get_admin_page_title())."</h1>";
		echo "</div>";		
	}
}

add_action('init',array('plugindemo','init'));
?>

Categories
MySql php wordpress

Exporting/Importing Woocommerce Orders with SQL 2020

Covers WordPress version 5.3.2

No matter how you use the SQL, be it on the command line, PhpMyAdmin or PHP, These are just the SQL commands needed.

There are 4 tables invlovled not counting the variable product data. I’ll be adding that as soon as a get a free moment.

  • wp_posts
  • wp_postmeta
  • wp_woocommerce_order_items
  • wp_woocommerce_order_itemmeta

wp_posts are where the main orders are kepts, with the post_id being the actual order ID. The other 3 tables use this id to attach items, customer details, etc to the order.

wp_postmeta contains the customer data.

wp_woocommerce_order_items contains the items that are ordered.

wp_woocommerce_order_itemmeta contains the price, etc of the items ordered.

So, on to the SQL by example

SELECT * FROM wp_posts WHERE post_type="shop_order"  and date(post_date)=date("2020-02-25")

The main thing is the “shop_order” post type. If you leve out the date part of the SQL then obviously you will get all orders in the database. If using PhpMyAdmin you can then simply export the results, os CLI pipe to a file, whatever, saving the data into something like wp_posts.sql

Now assuming a list of orders with post_id’s of 11538, 11541,11542, 11543, 11533, 11534, 11535, 11536, 11537, 11539, 11540 was resturned. We need to get all the info for those numbers starting with wp_postmeta.

SELECT * FROM wp_posts WHERE post_type="shop_order"  and date(post_date)=date("2020-02-25")

Save the results to wp_postmeta.sql.

SELECT * FROM wp_woocommerce_order_items WHERE order_id in (11538,11541,11542,11543,11533,11534,11535,11536,11537,11539,11540)

This will give us the items ordered, so save teh results to wp_woocommerce_order_items.sql.

SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id in (11538,11541,11542,11543,11533,11534,11535,11536,11537,11539,11540)

And save these results to wp_woocommerce_order_itemmeta.sql.

So now we have 4 sql files containing the orders and order data that we want to import, possibly on another server. The assumption at this point is the users exist already, otherwise you will have to export the users as well which is beyond the scope of this blog.

If would/could be a very, very bad idea so simply import as it, as chances are the key values are already in use.

The plan therefore is simple, edit the .sql files and change them to a number higher that what exists in the database you are importing to.

So you would change the post_id in the first file, then edit the second file to point to the new numbers you created, as well as create new meta_id values, and so forth. All the id numbers in the 4 files must match up.

Then you can simply import the 4 files into the new database, and there you go, your orders have been imported.

Summary

SELECT * FROM wp_posts WHERE post_type="shop_order"  and date(post_date)=date("2020-02-25")

SELECT * FROM wp_postmeta WHERE post_id in (11538,11541,11542,11543,11533,11534,11535,11536,11537,11539,11540)

SELECT * FROM wp_woocommerce_order_items WHERE order_id in (11538,11541,11542,11543,11533,11534,11535,11536,11537,11539,11540,11545)

SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id in (11538,11541,11542,11543,11533,11534,11535,11536,11537,11539,11540,11545)

Thank you for your time, I hope this helps.