There are some considerations when using DBTable or derived components like DBQuery.

Filter, Fixed filter and SubFilter

These elements can be a bit confusing. PHsPeed, loads data from the main query into a temporary table. The Filter is used to filter the main query. After that, you have a temporary result table which you can reuse. It has disadvantages, like the overhead, but it has also numerous advantages. It prevent that you have to reload the full query each time, especially when the database server is not on the same machine as the web server. And it allows faster access as the number of records is smaller, making the user experience faster. 

The result set is a temporary table which is reused as long as you are within a certain PHP module. Sometimes that is not desired, especially when you are using a lookup. For tables that are used in a lookup it is required that the result cache is set to 'no'. PHsPeed will attempt to set this property automatically, but you can verify its setting when the result is unexpected.

To filter the result set, you need to use the SubFilter. It has exactly the same function as filter, only it works on the temporary table.

The fixed filter had a different function and it works on the main table. The main difference with filter is that it is not used internally and is always applied. It is especially meant for fixed filtering, like in situations where you have to show a list of employees belonging to a department of the requesting user. Because the fixed filter is always applied, the end user is not able to find data outside its own range. For that reason this value should be set by your application, and not by any form field. 

As of version 2.3 there are two new events that can be used to set a filter or fixed filter. These events are invoked, just after the applicable filter has been generated. &$filter will contain the original filter that can be modified:

OnSetFilter:                Set the filter.

OnSetFixedFilter:         Set the fixed filter.

Example:

function dbtable_1_onSetFixedFilter($app, &$filter)

{

  $filter='cust_id='.getSessionVar('custid','');

}

The original filter is passed, so you can also append arguments to the filter. 

Considerations regarding SQL injection  

PHsPeed has a number of measures to avoid issues like XSS or SQL injection. However, it cannot protect you when you introduce these in your custom code. The filter is one of those occasions where you can easily make a mistake:

Suppose the following:

function btn_1_onClick($app)

{

  $empno=$edit_1->value;

  $$dbtable_1->setFilter('empno='".$empno.'");

}

The setFilter defines and executes a filter on a query, which by itself is perfectly fine. It is used internally a lot. But if you use it in a sample like above then you have introduces a SQL Injection issue. The SQL statement build will look like something like this:

select field, field, field, from mytable where empno=$empno

If the user enters an employee number, then the result will be as expected. But what if the user enters something like '10001 or (1=10)' ? then the generated SQL will look like:

select field, field, field, from mytable where empno=$empno or (1=1)

Causing all records to be shown, no matter what empno is entered. That could have severe issues!

In PHP it is advised to use PDO to bind variables so that the application is protected against SQL injection, and that is exactly what PHsPeed  does. But using PDO is no guarantee that you cannot introduce SQL injection if you do not follow 'the rules'. So how to approach a filter in a safe way? Look at the following:

We have defined a filter using a variable as value. (do not use : as this is bound to cloud variables which might give odd results). 

Next:

  function btn_1_onClick($app) {  
      $$dbtable_1->prepareFilter();  
      $$dbtable_1->setFilterIntegerField(':empno', $$edit_1->value);  
      $$dbtable_1->executeFilter();
  }

First the filter is prepared. The old results are cleared, and the filter statement is set in place. Next the value is applied by binding the variable. Then the filter is executed. The efffect is that when a user applies  '10001 or (1=10)' then only the numeric part is used, the rest ignored. The result will be the one record that is requested. Actually this is similar to the way you apply variables to the PDO sql statements. 

To set filters you can use 

    public function setFilterStringField($field, $value)      
    public function setFilterNumberField($field, $value)       
    public function setFilterIntegerField($field, $value)     

The same is valid for the sub filter and fixed filter. (To avoid potential issues we have added the same methods to the fixed filter property, however it is advised to only use it for its purpose)

   prepareSubFilter                prepareFixedFilter
   setSubFilterIntegerField        setFixedFilterIntegerField
   setSubFilterNumberField          setFixedFilterNumberField
   setSubFilterStringField        setFixedFilterStringField
   excecuteSubFilter                executeFixedFilter