How to use SQL statements not supported by JDatabaseQuery

Question

Recently, I have needed to use some SQL statements that are not supported by the JDatabaseQuery class, specifically REPLACE INTO and INSERT IGNORE. The only way that I am able to get something to work is to not use the JDatabaseQuery class at all.

Example:

...
$query = "REPLACE INTO #__" . $table . "(" . implode(',', $fields) . ")";
$query = $query . " VALUES(" . implode(',', $values) . ")";
$db->setQuery($query);
$db->execute();

I seem to recall reading sometime ago that you could pass in your SQL statements, however I can't find whatever it was I was reading and I have spent most of the day trying different approaches that avoid hacking the JDatabaseQuery class and putting in my own methods.

I like the query builder of Joomla and would like to keep things consistent but how do people handle unsupported statements?

Answer 1

As demonstrate by my answer at with insert on duplicate key update - updating multiple rows at once, I recommend using Joomla's secure query building methods, then performing any necessary surgery when calling setQuery().

I reckon this is the best you can do. Just be sure that your surgery CANNOT accidentally "over-replace" or otherwise unintentionally damage any other aspect of your SQL string.

Conversely, your posted technique is vulnerable to injection attacks and/or breakage due to quotes in the elements being imploded (well, as far as I can see your values are not quote-wrapped at all, but they might be earlier in the script).

Answer 2

After more hours trawling through the Joomla libraries to understand how the Query process is working, I can say that converting the $query object to a SQL command string and replacing the current statement with the required statement is the easiest way that I can see to make this work. This is what is covered in the linked answer by @mickmackusa.

From Mick's answer he has offered these suggestions, however there is more discussion worth reading there if you need to suffix another statement.

$db->setQuery(substr_replace($query, 'REPLACE', 0, 8));    // swap INSERT for REPLACE
// in other words: $db->setQuery(str_replace("\r\nINSERT", "REPLACE", $query));
// or:             $db->setQuery(preg_replace("~^\s+INSERT~", "REPLACE", $query));
// Joomla puts \r\n at the start of the query; see via var_dump($db) after setQuery()

For anyone else that has found themselves here, I will try to explain what the process is and why it is difficult, possibly impossible, to do it any other way.

The Long Answer:

When you create a a new Query, $db->getQuery(), an object is created with a lot of predetermined empty elements by the Class JDatabaseQuery in libraries/joomla/database/query.php. When you begin to invoke the methods of the Query object, eg.

$query = $db-setQuery();
$query->insert('#__tablename');
$query->columns($columns);

Those values are stored in their respective elements. You can only have one of either Insert, Delete, Select or Update statements per Query object.

For example the insert('#__tablename') method will add 'insert' to the type element and update the name and elements with 'INSERT INTO' and '#__tablename'. The value in the 'type' element is important as you will see shortly.

[sql:protected] => 
[type:protected] => insert
[element:protected] => 
[select:protected] => 
[delete:protected] => 
[update:protected] => 
[insert:protected] => JDatabaseQueryElement Object
    (
        [name:protected] => INSERT INTO
        [elements:protected] => Array
            (
                [0] => #__tablename
            )

        [glue:protected] => ,
    )

As you can see these elements are all 'protected' which prevents you from changing their value from outside the Query object. If there was a public method inside the class JDatabaseQuery that allowed you to update elements then you could replace 'INSERT INTO' with a 'REPLACE INTO' but alas, here we are.

At this point you have an Object with some values in elements, but that is a long way from a valid SQL command that can executed; it needs to be 'converted'.

The $db->setQuery($query) method will assign the Query object as an object to $this->sql. If it is a string it will also get assigned to $this->sql, e.g. $db->query('SELECT * FROM #__tablename'), and even $db->query('Fred and Barney') will get duplicated to $this->sql.

Inside the $query and $this->sql objects is a magic method called __toString that automatically gets called whenever an object is referenced as a string, hold that thought. The __toString method steps through the object looking at the 'type'(i.e.insert,select,update,delete) element and from that constructs the SQL statements into a string almost ready to be passed to the database.

The __toString is the second reason why providing anything other the predefined statements is not allowed as its converting process is fairly narrow in scope. If we could change the element value of INSERT INTO to a REPLACE INTO, or INSERT IGNORE INTO, then it would actually manage to process it correctly. However it is not able to handle extra statements like ON DUPLICATE KEY UPDATE. You can see for yourself at /joomla/database/query.php what is going on with the supported statements in the switch/case processing.

The last piece of the process was trying to work out when the __toString is actioned to create the SQL command string. Turns out that in normal processing, if the string has not already been created, it is the call to the function replacePrefix() (which replaces the '#_' with the actual prefix) that causes the __toString() to fire up, $this->replacePrefix((string) $this->sql);, note the '(string)'.

Therefore if you have been keeping up, you can create your SQL command string before the $db->setQuery(), during the db->setQuery or leave it for the replacePrefix() to make it happen. If you want to change anything in your SQL that you can't do with the JDatabaseQuery object methods then you need to choose either of the first two options as shown in the examples from @mickmackusa at the top of this answer.

Beyond my knowledge at the moment however I think that by being able to override or extend Class JDatabaseQuery so as to allow either changing of the elements of the object and/or improving the __toString method to handle other variations of the Insert statements it could be possible to handle more statements.