Page 1 of 1

MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-04-14 09:04
by kerelov
After upgrading from 5.94 to 5.95 in one of my apps where I use users with only group access I receive MYSQL syntax errors in all tables when I try to use default filter, custom filter or initial filter. I've edited datalist.php with the code from v5.94 (viewtopic.php?f=2&t=3347#p11759) and MYSQL error disappears and all works OK. This happens only to users with group access in tables, all admin users can use filters with no problem.

Re: MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-04-15 08:08
by kerelov
Second app with the same issue. Definitely a bug!

Re: MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-04-15 11:01
by SkayyHH
Same here. It´s a bug.

Re: MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-04-22 15:17
by a.gneady
Is it possible to list detailed steps to reproduce this issue please?

Re: MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-04-25 05:21
by kerelov
So the easiest to reproduce is to have a table (for example table with name "notes") where you give full permission to admins group (insert, view for all, edit for all, delete for all) and a second group for example clients which have access to the same table with group level (insert, view for group, edit for group, delete for group).
If you are logged in with a user from the clients group and you have the datalist.php from v5.95 and try to filter the table "notes" for example with the regular filter (something simple like ID equals to 1) and you will get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1
Then you can log out and login with an admin account and try to do the same thing and it will all work ok.
Now if you remove this line from datalist.php

Code: Select all

$QueryHasCustomWhere = (strlen($this->QueryWhere) > 0);
and edit this line

Code: Select all

if($WhereNeedsClosing && !$QueryHasCustomWhere)
to be

Code: Select all

if($WhereNeedsClosing)
it all works ok for both groups admins and clients can use filters with no problems.
Something that I think is connected to this issue is the homeLinks[] and navLinks[] arrays in links-home.php hooh and links-navmenu.php hooks.
If you create a link with a group permission like this:

Code: Select all

'groups' => array('*'),
or

Code: Select all

'groups' => array('Admins'),
custom links are shown OK and everything is fine.
But if you use two groups like this:

Code: Select all

'groups' => array('Admins', 'Lawyers'),
where Lawyers is a group with limited permissions like the clients group from the above example, then home and nav links are not displayed for both groups.

Both problems appeared in two of my apps after update from 5.94 to 5.95.

Re: MYSQL syntax error with filters after the bug fix for QueryWhere in v5.95

Posted: 2021-05-02 18:15
by SkayyHH
It´s easy to reproduce. Log in as not admin member and try to filter with appgini table view filter funktion. E.G. Name = "Smith"

Many greetings, Kai