0

Joomla 3.9.13

I'm having some issues with a simple select query. This query is generated by the core files, I haven't created any overrides or changed the code.

In administrator/components/com_fields/models/field.php around line 668 the query looks like this;

$query->select(array($query->qn('field_id'), $query->qn('value')))
    ->from($query->qn('#__fields_values'))
    ->where($query->qn('field_id') . ' IN (' . implode(',', ArrayHelper::toInteger($fieldIds)) . ')')
    ->where($query->qn('item_id') . ' = ' . $query->q($itemId));

In my contacts component, I have dumped this query and it produces the following;

SELECT `field_id`,`value` FROM `o3m0z_fields_values` WHERE `field_id` IN (17) AND `item_id` = '119'

The results of this are NULL.

When I copy / paste this query into HeidiSql the results are also zero.

However, whenever I remove the single quotes around 119, I get results!

So the updated query looks like this;

SELECT `field_id`,`value` FROM `o3m0z_fields_values` WHERE `field_id` IN (17) AND `item_id` = 119

Why does the first query fail and the second query succeed?

How can I edit the original Joomla query so that it doesn't add single quotes to the query?

I know this is editing core files which I should never do, but it's causing all sorts of issues when I'm editing contacts with custom fields.

  • After reading stackoverflow.com/q/59409239/2943403, now I am wondering if you have some rogue spaces or invisible characters on your item_id column values. Can you verify that the string length on these values is equal to the number of visible characters in the string? – mickmackusa Dec 20 '19 at 3:40
  • I found an inconsistency in your tests (I'm referring to your StackOverflow post). In your screenshot, you have item_id being a VARCHAR type column, but in your online demo, you have it as an INTEGER db-fiddle.com/f/krkchC29r8eBxDimraey3x/0 Which is it? (oh, I see, that db-fiddle link was originally created by Nick) – mickmackusa Dec 20 '19 at 3:51
  • To completely expose all relevant details, we will need you to post the RDBMS that you are using and we need you to verify that the column values and your incoming numeric values are completely free from invisible characters. var_dump() does a good job of exposing the multibyte length of strings. – mickmackusa Dec 20 '19 at 4:11
0

Perhaps you are running on a PostgreSQL database or some other database which doesn't do a favourable job of converting numeric strings to integers/floats.

This is worth a look: https://stackoverflow.com/a/38528808/2943403

So amend the Joomla query building syntax remove the quoting call, then apply integer casting for security. (This is standard/best practice for securing queries when passing variables carrying integer values into your sql.)

->where($query->qn('item_id') . ' = ' . (int)$itemId);

Just while you are modifying core files (or considering posting an issue to the core developers), the select() call could be refined to makewrite fewer quoting calls by passing an array to qn()...

$query->select($query->qn(['field_id', 'value']))

If you are looking for microoptimisations while building queries with Joomla's query building methods, NONE of the column names nor the table name need to be wrapped in backticks for stability/security/functionality because there are no characters in those names to cause mischief and no reserved keywords are used.


One last observation...

I also don't like to see the database object referred to as $query. As in many other instances, it is clearer to declare the database object as $db and the sql building is done on $query.

This object naming convention is consistently used in the Joomla Documentation:

Just a few examples of mine from JSE:

  • quoteName() runs in recursive manner when an array is passed. It ends up being slower than quoting individual columns separately. You can also avoid useless function calls by using quoteName() directly instead of qn() alias. – Sharky Dec 20 '19 at 6:31
  • The cost in microseconds should be weighed up against the readability, maintainability, and reduced code bloat of making a single quoting call. – mickmackusa Dec 20 '19 at 6:51

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.