Bug: Missing quotes if pre-filter with IN condition and additional user filter

Status
Not open for further replies.
VOI

VOI

Member
Hi,

I set up a list with a pre-filter using the IN condition on a dropdown element (lets call it 'status') to filter for multiple values and to avoid using multiple filter conditions nested with OR.

This element is shown in the list and its "Filter type" is set to "dropdown".

If the user tries to filter the list using this dropdown filter (e.g. for the value 'doi1'), a page with an SQL error is displayed.
I believe the reason for this error is the WHERE part created from the user filter:

Code: 
AND (`projects`.`status` IN (doi1))

There should be single quotes around the value selected by the user - right?

My current workaround is to use nested OR filters with EQUALS condition for the list pre-filter instead of the IN Condition. In this case the WHERE statement based on the user filter does not use the IN condition, but the LIKE syntax as follows:

Code: 
AND (`projects`.`status` = 'doi1' OR `projects`.`status` LIKE '[\"doi1\"%' OR `projects`.`status` LIKE '%\"doi1\"%' OR `projects`.`status` LIKE '%\"doi1\"]')

However, I would be glad if I could use the IN condition for pre-filters again.

Chris
 
Are you running 3.1 or a recent GitHub?
There was a discussion about this (with prefilters using dbjoin values != id), I don't know if it has been fixed.

Edit:
just saw you signiture, so it has not been fixed.
 
Didnt do any GitHub Update after updating to 3.1.

The element I am referring to is a normal "dropdown" element, not a databasejoin elment.

Chris
 
No, not sooo recent, 3.1 should be enough.
I think it's the same issue: if the IN values are not integer they must be quoted.
 
Have you tried latest github? We've applied a couple of fixes for quoting of keys in queries since your original post.

-- hugh
 
Update from Github on 11-03-2014 - problem is still there, i.e. the user filter is still using IN instead of LIKE and the single quotes are still missing.

Chris
 
Did a bit of research in the model files and the wiki. This is what I found:

  • the relevant code seeem to be:
    • the function 'getFilterArray' in the list model PHP file 'list.php' used to build the overall query
    • and the function 'getFilterValue' in the element model PHP file 'element.php' used to build the actual query for each element
  • I did not find any code in there which adds any quotes if the IN condition is used. I would expect that to happen in line 4224 of 'element.php'
  • so any user filter on a dropdown element with an IN pre-filter applied creates some WHERE statement like
    Code: 
    AND (`projects`.`status` IN (doi1))
  • on the other hand, regarding pre-filters using the IN condition, according to http://fabrikar.com/forums/index.php?wiki/pre-filters/#notes-for-in-and-not-in values, they CAN be quoted.
  • In my setup I ALWAYS quote any values in the pre-filters if using the IN condition since otherwise I get a SQL error, i.e. the content of the value field in the list pre-filter would be
    Code: 
    'doi1','doi2','doi3'
  • so to make the IN Filter work for both pre-filters and user filters, either the pre-filters would need to be entered without quotes and the code would add quotes for both pre-filters and user filters or the code would have to check around line 4224 of 'element.php' if the value already has been quoted.
  • I tried the last approach by adding the following line before line 4224 of 'element.php':
    Code: 
    $value = explode(",",$value);
if (is_array($value))
{
  for ($i = 0; $i < count($value); $i++)
  {
    $value[$i] = $db->quote(trim(trim($value[$i],"'"),'"'));
  }
} else {
  $value = $db->quote(trim(trim($value,"'"),'"'));
}
    However, after applying this code the list with IN pre-filter on the dropdown element did not load anymore - without any PHP error
Maybe someone has some better ideas how to approach this.

Chris
 
Your line numbering doesn't jive with the latest github.

Can you either update and give the current line number, or paste some of the surrounding code around line 4224 in your copy, so I can be sure of where you are looking?

And yes, that is one of the places I've been testing some fixes. I just want to be sure we are copacetic on which lines we are talking about.

-- hugh
 
OBTW, just FYI ... when I started working through this issue, I created a helper function FabrikString::safeQuote(), which takes either a string or an array of strings, and if necessary db quotes them.

NOTE that it doesn't use "pass by reference" to modify the arg you pass it, so you have to assign the result.

PHP: 
$values = FabrikString::safeQuote($values);

I did it this way because there are times where we need to use a quoted array of names, but we don't want to quote the original data. Hmmm, I just noticed that I used strstr() to test for existing quotes, but that won't work for something like O'Reilly. I just changed that in github to use a regex.

BTW, I just noticed that your code doesn't implode() the array back in to a comma separated string, which is probably why it's failing.

-- hugh
 
OK, can you update to latest github? I may have fixed this, although as I can't duplicate your issue (for reasons I don't understand!) it's hard to tell, but getFilterValue() should now quote values for IN and NOT IN.

-- hugh
 
Hi Hugh

I think your pre-filter fix broke the list filters. Now it returns no results as it should when I click on filters using the latest Github. If I remove the pre-filters from the list works ok.
Thanks.
 
Hi,

did an update as well. For me filtering is working - even with pre-filters applied.

Only thing is that the first time you apply a list filter after opening the list, the page refresh is odd: after the page has finished reloading, the list is displayed without the list filter applied. After a small amount of time '?resetfilters=0&clearordering=0&clearfilters=0' is appended to the URL and after another small amount of time the list is filtered correclty. The last two "updates" are down without any page refresh. I am using IE 10.

Chris
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 63 (members: 2, guests: 61)
    Back
    Top