Filter the data displayed through sql statement

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
danielefeola
Veteran Member
Posts: 31
Joined: 2013-04-19 13:36

Filter the data displayed through sql statement

Post by danielefeola » 2016-05-24 08:15

Hello everyone!
I have a problem, maybe I do not understand.
I need to filter the data displayed through sql statement.
So far I have used $x->QueryWhere
Users have pointed out to me that they can see even more information!

Question 1: What is the difference between

// Lookup fields that can be used as filterers
...
...
...
$x->QueryWhere='';

and

}elseif($perm[2]==3){ // view all
// no further action
$x->QueryWhere=''; [line added by me]
}elseif($perm[2]==0){ // view none

Either way I can filter, but if I use _view.php?SelectedID=n I see everything!
HELP!

Example
}elseif($perm[2]==3){ // view all
// no further action
$x->QueryWhere='WHERE 1=2';
}elseif($perm[2]==0){ // view none

... tablename_view.php ok I see nothing
instead
... tablename_view.php?SelectedID=3 I see all of ID 3!
HELP!

Many thanks

Daniele

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

Re: Filter the data displayed through sql statement

Post by a.gneady » 2016-05-24 19:24

When editing the where part of the query, you should do it through hooks rather than directly editing the tablename_view.php file. This prevents AppGini from overwriting your changes if you regenerate your code. So, in the hooks/tablename.php file (where tablename is the name of the concerned table), find the tablename_init function, and add this code into it:

Code: Select all

$options->QueryWhere = '/* your where clause here */';
For the exact syntax to use in your where clause, it depends on how you want to filter your data.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

danielefeola
Veteran Member
Posts: 31
Joined: 2013-04-19 13:36

Re: Filter the data displayed through sql statement

Post by danielefeola » 2016-05-25 05:41

Of course I had already used the init hooks, but not solve the very serious problem on data privacy.
That's why I was trying to change the file _view.php

The user 'daniele' can only see some data and not others.
I filter with a sql statement in hooks / tablename.php

tablename_init function (& $ options, $ MemberInfo, & $ args) {
     $ Options-> QueryWhere = 'where 1 = 2';
return TRUE;
}

The user 'daniele' does not see anything, it is right.

The user 'daniele' writes the /tablename_view.php?SelectedID=1 URL and sees all!

The user 'daniele' writes the /tablename_view.php?SelectedID=2 URL and sees all!

The user 'daniele' writes the /tablename_view.php?SelectedID=n URL and sees all!

In this example the user 'daniele' should NOT see anything on tablename.php.

The user 'daniele' belongs to a group who view all of tablename.php (allowWiew = 3) but should see some specific filtered data with sql statement.

How can I solve this very serious problem?
Also with patches or any other script?
Thank you very much

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

Re: Filter the data displayed through sql statement

Post by a.gneady » 2016-05-26 01:04

The user 'daniele' belongs to a group who view all of tablename.php (allowWiew = 3) but should see some specific filtered data with sql statement.
Hmm ... before attempting to find a patch, may I ask, if this is a single user, why not move him to a more restricted group that can't access that table (if that's what you want to do based on the 'where 1=2' clause you used)?
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

danielefeola
Veteran Member
Posts: 31
Joined: 2013-04-19 13:36

Re: Filter the data displayed through sql statement

Post by danielefeola » 2016-05-26 05:35

I can not work with groups.
In my case the structure is similar to this.
GROUP 1
GROUP 1.2
GROUP 1.2.1
GROUP 1.2.2 user daniele is here and where to see only the data of this group
GROUP 1.2.n
GROUP 1.3
GROUP 1.3.1
GROUP 1.3.2
GROUP 1.3.n
GROUP 1.n
GROUP 1.n.1
GROUP 1.n.2
GROUP 1.n.n
GROUP 2
GROUP 2.2
GROUP 2.2.1 user daniele is also here and where to see the data even in this group
GROUP 2.2.2
GROUP 2.2.n
GROUP 2.3
GROUP 2.3.1
GROUP 2.3.2
GROUP 2.3.n
...
...
...

Using a sql query is not difficult.
Each user can only see a few things.
You can not filter for groups because the user is on more groups.
Also those who enter data does not belong to those groups.
Where 1=2 is an example to control.

Thanks!

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

Re: Filter the data displayed through sql statement

Post by a.gneady » 2016-05-26 22:18

Well ... if you want to prevent a specific user from opening the detail view of any record in a specific table, you could add something like this to the tablename_init hook (where tablename is the name of the concerned table):

Code: Select all

$restricted = array('daniele', 'some_other_user', ...);

if(in_array($memberInfo['username'], $restriced)){
    $_POST['SelectedID'] = $_GET['SelectedID'] = $_REQUEST['SelectedID'] = false;
}
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

danielefeola
Veteran Member
Posts: 31
Joined: 2013-04-19 13:36

Re: Filter the data displayed through sql statement

Post by danielefeola » 2016-05-29 14:12

even with this script does not solve the problem.
in this case daniele can not see the details of anything.

danielefeola
Veteran Member
Posts: 31
Joined: 2013-04-19 13:36

Re: Filter the data displayed through sql statement

Post by danielefeola » 2016-05-29 14:38

resolves (still in testing) with change
tablename_dml.php on

// can edit?
if(($arrPerm[3]==1 && $ownerMemberID==getLoggedMemberID()) || ($arrPerm[3]==2 && $ownerGroupID==getLoggedGroupID()) || $arrPerm[3]==3){
$AllowUpdate=1;
}else{
$AllowUpdate=0;
}
$res = sql("select * from `tablename` where `id`='".makeSafe($selected_id)."' and id=$_SESSION[id_tablename]", $eo);
if(!($row = db_fetch_array($res))){
return error_message($Translation['No records found']);
}

Post Reply