Joomla Remote SQL Call

• Download source - 16.5 KB

Table of Contents

• Introduction
• Architecture
• Security
• Point of interest
• Post installation script
• Site part
• Conclusion

The second part of the article, with a VB.NET client, is now available. Part 2

Introduction

This is the first of two articles that show a method to automate Joomla tasks or applications based on it.

I have often worked with hosted Joomla sites, and many times, I had to integrate one or more company applications with it.

A real example is the integration of the company erp with Virtuemart, an e-commerce solution Joomla based: hundreds of products to publish online every day and hundreds of products that change price more than a time in a day.
Moreover, working with hosted site, often, means also to have not a direct database access.

I created a Joomla component that can receive remote commands and issues them to the underlying database, with transaction support.

Architecture

The architecture is quite simple; I created a Joomla component that publishes a page that handles incoming message in XML format. It executes commands contained in the XML message, and sends back a message response.

Message exchange does not apply any standards; no SOAP neither other RPC protocols. I created a proprietary http based protocol.

Security

Page is public. So, without a security check, it could represent a big security hole. For this reason, I introduced a simple security mechanism based on key exchange.

After the component is installed, it generates a unique security token. This security token must be present in the request parameters to be accepted by server.

Point of Interest

Joomla component implements both an admin panel and a site part.
The admin panel shows the security key that must be used, and the site page handles XML request only.

I don’t explain how to create a Joomla component because there are already many how-to about this topic (you can start from here).

The interesting points are the use of the post installation script to generate a unique security key and the site page that handles XML requests.

Post Installation Script

In the Joomla installation package, the XML file named “component.xml” is the file that contains all installation instructions. The instruction <scriptfile>script.php</scriptfile> indicates to the installation process to find a class named component_nameInstallerScript contained in the file script.php located in the root folder of the installation package.

Some well-defined public methods of this class are called during the installation process:

• function install($parent) that is called during installation process. Here, you can specify additional steps to execute in this phase,
• function uninstall($paent) that is called during uninstallation process
• function update($parent) that is called during update process
• function preflight($type, $parent) that is called before install and update
• function postflight($type, $parent) that is called after install and update

You can find more information here.

I use the postflight function to write an XML file containing the security token:

function postflight($type, $parent)
{
    $string = '<sec><token>'.md5(uniqid(rand(), true)).'</token></sec>';
    $xml = new SimpleXMLElement($string);
    $xml->asXML(JPATH_SITE.'/components/com_sqlxml/sec.xml');
}

Site Part

The site part is managed by a controller that handles only XML output. The request must specify the url parameter “format=xml”.
It registers a function named "cmdep.execcmd" that handles the request and prepares the XML response to send back.

The first step of this function is the security check. If the token doesn't match, it replies with an error.

Once security token is validated, the function reads the content of the request and, for each command specified, performs the required operations.

Let's look inside the message structure:

<?xml version="1.0" encoding="utf-16"?>
<msg-req xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <commands>
    <command type="sql" action="select" 
    mandatory="false"><![CDATA[]]></command>
    <command type="sql" action="select" 
    mandatory="false"><![CDATA[]]></command>
  </commands>
</msg-req>

As you can see, msg-req contains one commands collection and then one or more command.
All the process is contained in a transaction; each command can specify if to be part of the transaction or not.

A command, that now can be only an SQL statement, can specify some parameters:

• The "mandatory" parameter indicates if command must terminate with success to complete the transaction. If set to true and the command fails, the transaction rollbacks.
• The "action" parameter indicates what kind of SQL statement is to be executed.
  Managed types are "INSERT", "UPDATE", "DELETE", "SELECT", "CALL".
  There is not a semantic check between the value of the parameter and the real content of the command, this parameter manages only how the SQL command is executed and how it will return the execution results. So, an "INSERT" command will return the last inserted auto generated ID, while an "UPDATE" or "DELETE" command returns the number of affected rows.
• The "save" parameter is used to specify a variable name where the return value is temporarily saved to be used in the next commands.

Look at the following example:

"TABLE1" has 3 fields: "ID" is an auto generated numeric field that is also the primay key, followed by two text fields "FIELD1" and "FIELD2".

"TABLE2" has 3 fields as well: An auto generated "ID", "REF_ID" that is a reference to "ID" field in "TABLE1" and "FIELD1" of type text.

If you need to insert related record in these tables, you have to create 2 INSERT commands, mandatory (so the transaction commits only if both commands execute successfully), the first that save the return value in a variable called "SAVED_ID" and the second that refer to that variable in the form "$[SAVED_ID]" in the next SQL statement:

<command type="sql" action="insert" mandatory="true" 
save="ID"><![CDATA[INSERT INTO TABLE1 (FIELD1, FIELD2) 
VALUES ('VALUE1', 'VALUE2')]]></command>
<command type="sql" action="insert" 
mandatory="true"><![CDATA[INSERT INTO TABLE2 
(REF_ID, FIELD1) VALUES ($[SAVED_ID], 'VALUE2')]]></command>

When the second command is executed, all parameters in the form $[...] are replaced with the corresponding values. So the $[SAVED_ID] value was replaced with the value saved in the first statement.

In PHP, the management of this parameter substitution was implemented with the use of associative array.
Return values are saved into a global array using the value of the "saved" parameter as key:

$res_values = array();
$cmd_attr = $cmd->attributes(); //xml message command attributes
$save = (string)$cmd_attr['save'];
 if ($save!=null) 
       $res_values[$save]=$query_result[0];

Then, all parameters in SQL statement that match $[…] are replaced quering the array:

$tmp = (string)$cmd; //Use a temp string to store the sql command
//use a regular expression to find all $[…] parameters. 
//the first matching group is the complete parameter string "$[named_param]"
//the second matching group is the parater name only "named_param"
$ct = preg_match_all("/\\$\[((?:\[\S*\]|[^\[])*)\]/", $tmp, $arr);
//for each parameter found proceed with values substitution.
for($i=0; $i<$ct; $i++){
  $tmp = str_replace($arr[0][$i], $res_values[$arr[1][$i]], $tmp);
}

Conclusion

This component is useful when you don't have a direct access to the hosted database.

You can automate your Joomla task with any programming languages taking advantage of transaction management.

In the next article, I'll show a VB.NET client that explores some component features.

Updates

09/09/2015 - Published the second part of the article. Part 2