2

I'm taking it upon myself to become aware of some of the lesser known/used query methods because I like toying with MySQL and I want to implement Joomla calls when possible/sensible -- because they were made to be used.

For this question, I'm referring to:
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query/mysqli.php

The current list of methods are:

  • __toString()
  • processLimit()
  • concatenate()
  • setLimit()
  • regexp()
  • Rand()
  • selectRowNumber()
  • castAsChar()

Now, I've seen __toString() and setLimit() used with some frequency, and I have not yet discovered any hiccups with processLimit() or selectRowNumber() that are not outright typo scenarios. For these reasons, I'll avoid discussion on these four methods.

To phrase as a question:

What kinds of pitfalls should I be aware of while calling: concatenate(), regexp(), rand(), and castAsChar() in sql queries?

3

All of my demonstrations will assume this line is already declared:

$db = JFactory::getDBO();

I will also provide a "guinea pig" table as a demo reference and playground for researchers when sample data is necessary for context. (db-fiddle.com demo)

CREATE TABLE IF NOT EXISTS `#__calendar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `month` varchar(100) NOT NULL,
  `color` varchar(100) NOT NULL,
  `total` int(11) NOT NULL,
  `status` varchar(10) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `month` (`month`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `#__calendar` (`id`, `month`, `color`, `total`, `status`) VALUES
(1, 'January', 'red', 234, 'active'),
(2, 'February', 'red-orange', 998, 'inactive'),
(3, 'March', 'orange', 10, 'active'),
(4, 'April', 'orange-yellow', 718, null),
(5, 'May', 'yellow', 11, 'active'),
(6, 'June', 'yellow-green', 209, 'active'),
(7, 'July', 'green', 315, 'active'),
(8, 'August', 'green-blue', 8, 'inactive'),
(9, 'September', 'blue', 555, 'active'),
(10, 'October', 'blue-violet', 188, 'active'),
(11, 'November', 'violet', 39, 'active'),
(12, 'December', 'violet-red', 711, 'inactive');

| id  | month     | color         | total | status   |
| --- | --------- | ------------- | ----- | -------- |
| 1   | January   | red           | 234   | active   |
| 2   | February  | red-orange    | 998   | inactive |
| 3   | March     | orange        | 10    | active   |
| 4   | April     | orange-yellow | 718   |          |
| 5   | May       | yellow        | 11    | active   |
| 6   | June      | yellow-green  | 209   | active   |
| 7   | July      | green         | 315   | active   |
| 8   | August    | green-blue    | 8     | inactive |
| 9   | September | blue          | 555   | active   |
| 10  | October   | blue-violet   | 188   | active   |
| 11  | November  | violet        | 39    | active   |
| 12  | December  | violet-red    | 711   | inactive |

View on DB Fiddle Here is a list that I have compiled that reveals possible/likely issues and misunderstandings that may be encountered while calling methods from the $query object:


Chaining an Undeclared Object

If you are used to chaining from the $query variable declaration, you will likely hit a snag if you want to call a method from within a chained method.

Issue:

$query = $db->getQuery(true)
    ->select($query->rand());

Error: 0 Call to a member function rand() on null

Instead, you need to first declare $query object as a variable before you can call a (un-chained) method from it.

Solution: Declare $query then chain to your heart's content.

$query = $db->getQuery(true);
$query->select($query->rand());  // output: 0.28479570456295805

Null Parameters With concatenate()

concatenate() conditionally calls one of two MySQL functions. If you provide a non-falsey (not an empty, false, null, or zero value) as the second parameter, CONCAT_WS() will be called, otherwise CONCAT(). Learn the difference. While Joomla tries to provide a safeguard against a trouble-making null value as the separator/glue parameter via if ($separator), it ruins the opportunity to glue columns/text which may or may not be NULL together with an empty space (not NULL). What might cause trouble is if CONCAT() is rendered by the method and one of the values to be glued together is NULL.

Issue: Syntax:

$query = $db->getQuery(true);
$query->select($query->concatenate(["month", "status"]))
    ->from("#__calendar")
    ->where("id = 4");`

or

$query = $db->getQuery(true);
$query->select($query->concatenate(["month", "status"], ""))
    ->from("#__calendar")
    ->where("id = 4");`

Output: NULL instead of: "April" (with empty string glued to end)

Solutions: Learn about NULL-converting techniques and other workarounds here.
There are too many fringe cases to spell out here, but you might wish to employ COALESCE(`column_with_possibly_null_values`,'') or IFNULL(`column_with_possibly_null_values`,'') or avoid the Joomla method entirely and manually code your preferred MySQL function.

Recommendation: I'd actually like the gitters to consider modifying the source code from if ($separator) to if ($separator !== null) or similar to permit the use of an empty string as glue.


Syntax Error With regexp()

Issue: Syntax:

$query = $db->getQuery(true);
$query->select("month")
    ->from("#__calendar")
    ->where($db->qn("month") . $query->regexp("[^elrty]$"));

Error: Query Syntax Error ...near [^elrty]$

Solution: The regex pattern must be single-quoted, so use $db-q() or $db->quote().

$query = $db->getQuery(true);
$query->select("month")
    ->from("#__calendar")
    ->where($db->qn("month") . $query->regexp($db->q("[^elrty]$")));  // output: March

Additionally: If you are going to pass user-supplied / untrusted data into the regex pattern, understand that you will need to escape characters with special meaning to the regex engine. In php, the tool is almost always preg_quote(), but because we are passing the value all the way through to MySQL, some additional adjustment is required (double-backslashing is used for escaping). If you are not overly familiar with MySQL's implementation or regular expressions, be aware that pattern delimiters are not used and that if you are going to use forward slashes in your pattern, you will need to write \\\\ in php for every \ that you intend to deliver.

New Example: If a row had a month value of wh^t? the hey! and you wanted to search for it by providing $search = "wh^t? the" into regexp(), you would need a where clause coded like this:

->where("month " . $query->regexp($db->q(preg_replace('~[-/\\^$*+?.()|[\]{}]~u', '\\\\\\\\$0', $search), false)));
// yes, you really do need all of these backslashes ------------------------------^^^^^^^^

and it would render as WHERE month REGEXP 'wh\\^t\\? the'

Furthermore, if you wanted to include word boundaries on both sides of the search variable in your condition logic, the expression would look like this:

->where("month " . $query->regexp($db->quote('([[:<:]]|^)' . preg_replace('~[-/\\^$*+?.()|[\]{}]~u', '\\\\\\\\$0', $search) . '([[:>:]]|$)', false)));

and it would render as WHERE month REGEXP '([[:<:]]|^)wh\\^t\\? the([[:>:]]|$)'


Passing a Seed to rand()

This is certainly a lesser known bit of functionality and not likely to be implemented for the vast majority of queries seeking randomization, but MySQL's RAND() function accepts an optional parameter to be used as the "seed". Learn more. This is useful if you need a predictable/repeatable sequence of "random numbers" for your query. Unfortunately, the Joomla core doesn't acknowledge any parameters passed to this function and will silently omit the seed value.

Issue: Syntax:

$query = $db->getQuery(true);
$query->select("month")
    ->from("#__calendar")
    ->where("FLOOR(" . $query->rand(2) . "*10) = 9");

Output: [completely random / unpredictable resultset]

Solution: Do not call the rand() method when supplying a seed value.

$query = $db->getQuery(true);
$query->select("month")
    ->from("#__calendar")
    ->where("FLOOR(RAND(2)*10) = 9");  // output: June (*everytime)

Recommendation: I would like to see the source code updated to allow the passing of a seed value. Overall, I find this method to be a waste of programming, because offers less functionality and bloats the query building process with more characters. Do we need a method for every function? e.g. FLOOR(), MAX(), etc? If nothing else is done, the capital R typo should be changed in the source code.

*Full disclosure, I've never used RAND() with a seed in any of my projects -- this is new discovery for me.


Using castAsChar() Without a Length Parameter

Issue: Syntax:

$query = $db->getQuery(true);
$query->select("id, month")
      ->from($db->qn("#__calendar"))
      ->where("id IN (1,2,10,11,12)")
      ->order($query->castAsChar("id"));

Output: [natural sorting (numeric) instead of computer sorting (alphabetically)]

Learn more about natural versus computer sorting. The source code is designed to abort the method's default action if there is no length parameter supplied in the call. The rendered order clause becomes: ORDER BY id <- no trace of CHAR() despite the developer's intention.

Solution: Do not call this method if you are not going to provide a length parameter.

$query->select("id, month")
    ->from($db->qn("#__calendar"))
    ->where("id IN (1,2,10,11,12)")
    ->order("CAST(id AS CHAR)");  // output order: 1, 10, 11, 12, 2

Additionally: This function can be used to truncate a value to a certain amount of characters (multibyte-safe). So, SELECT CAST('âêîôû' AS CHAR(2)) returns âê.

Recommendation: Providing more flexibility (or additional methods) to the types of casting that can be done seems like a good idea. Learn about other types.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.