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

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
kerelov
Posts: 20
Joined: 2020-04-17 21:20
Location: Bulgaria

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

Post by kerelov » 2021-04-14 09:04

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.

kerelov
Posts: 20
Joined: 2020-04-17 21:20
Location: Bulgaria

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

Post by kerelov » 2021-04-15 08:08

Second app with the same issue. Definitely a bug!

SkayyHH
Veteran Member
Posts: 322
Joined: 2015-04-27 21:18
Location: Germany

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

Post by SkayyHH » 2021-04-15 11:01

Same here. It´s a bug.

User avatar
a.gneady
Site Admin
Posts: 1163
Joined: 2012-09-27 14:46
Contact:

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

Post by a.gneady » 2021-04-22 15:17

Is it possible to list detailed steps to reproduce this issue please?
:idea: Calendar plugin: Organize your data into beautiful calendars.
:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

kerelov
Posts: 20
Joined: 2020-04-17 21:20
Location: Bulgaria

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

Post by kerelov » 2021-04-25 05:21

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.

SkayyHH
Veteran Member
Posts: 322
Joined: 2015-04-27 21:18
Location: Germany

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

Post by SkayyHH » 2021-05-02 18:15

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

Post Reply