Click here to Skip to main content
12,549,332 members (48,905 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked

Joomla Remote SQL Call

, 28 Aug 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
This is a Joomla component that allows remote SQL execution using XML message over HTTP.

Table of Contents

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


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.


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.


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);

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="" 
    <command type="sql" action="select" 
    <command type="sql" action="select" 

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" 
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) 

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 $[&hellip;] 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);


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.


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Italy Italy
I'm a senior software developer.
I wrote my first program in basic with commodore 64, that is... a long time ago Wink | ;-)
From that moment, I've learned many programming language and developed many projects.

I've started working as IT consultant in a software factory company that had produced software mostly for banking and financial business.
In this environment I could work on many different hardware platforms, using many different technologies and programming languages.Then, in the era of distributed application, I learnt to make all these different techologies working together.

My interest has always been in software development specially oriented to internet application, but during all this time I've acquired also other skill in system and network administration.

You may also be interested in...


Comments and Discussions

GeneralNice project Pin
lucwuyts29-Aug-15 1:11
memberlucwuyts29-Aug-15 1:11 
GeneralRe: Nice project Pin
FrankNight29-Aug-15 22:27
memberFrankNight29-Aug-15 22:27 
GeneralRe: Nice project Pin
lucwuyts25-Nov-15 2:56
memberlucwuyts25-Nov-15 2:56 
GeneralRe: Nice project Pin
FrankNight11-Dec-15 5:59
professionalFrankNight11-Dec-15 5:59 
GeneralRe: Nice project Pin
lucwuyts25-Aug-16 0:47
memberlucwuyts25-Aug-16 0:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161021.1 | Last Updated 28 Aug 2015
Article Copyright 2015 by FrankNight
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid