[extension] datafilter

Yii 1.1 Extensions
21

It was a bug in CFilterBase::applyCriteria declaration, it should be:




    public function applyCriteria($model, &$criteria)

    {

        return;

    }

This error is reported by PHP only when E_STRICT option is enabled.

I fixed bug and uploaded new version here (I replaced previous v0.3 archives).

22

I have tested the version 0.3, but the demo has still has a large security leak: :(

Normal Url:


http://localhost/dfdemo/?userFieldsSearch=df_users.id&userFieldsSearchText=&groupFilter=1&countryFilter=&cityFilter=

Now we change the url a little bit:


http://localhost/dfdemo/?userFieldsSearch=df_users.id&userFieldsSearchText=&groupFilter=999&countryFilter=999&cityFilter=999);DROP DATABASE `dfdemo`;

As result a normal user deletes the whole database…

Please use binding parameters to solve this problem:

http://www.yiiframew…ding-parameters

Greetings

Anticon

23

Hi, Anticon

Thank you VERY much for your post, because I uploaded wrong (old) version yesterday.

Now I re-uploaded extension and demo application and there are all security fixes as well as a fix I made yesterday.

Sorry to all who downloaded a wrong version and please download it again.

24

Hi seb,

this version looks better. Thank you. :)

Greetings

Anticon

25

I’m migrating legacy application and found problem using datafilter to search a column which content order no. with format OCYY/MM/NNN, the error occurs when i click on next page as follow :

[code] <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

<html><head>

<title>404 Not Found</title>

</head><body>

<h1>Not Found</h1>

<p>The requested URL /pqc/index.php/tAG/admin/TAGFilter[TagFieldsSearch]/t.ORDERNO/TAGFilter[TagFieldsSearchText]/OC08/10/yt0/Go/TAG_page/3 was not found on this server.</p>

<hr>

<address>Apache/2.2.12 (Ubuntu) Server at domain1.com Port 80</address>

</body></html>

/code]

I notice that the problem is related to slash character use in order no. separator.

Any idea how to solve this problem ?

Rgds

Majin

26

Hi,

Very nice extension which saves time!

Although I faced several problems:

  1. After applying filtering, or search it goes to default VIEW(that’s sites/index on default and in your demo config ‘defaultController’=>‘user/admin’,)

  2. After selecting country(in my case organisation) and then city(in my case branch) it throws:


CDbException

Description


CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'BraID' in where clause is ambiguous

Source File


C:\wamp\www\framework\db\CDbCommand.php(372)







21:17:55.734893	trace	system.db.CDbCommand	Querying SQL: SELECT COUNT(*) FROM `course` INNER JOIN `branch` branches ON (`course`.`BraID`=branches.`BraID`) AND (branches.OrgID = :organisationsID) WHERE BraID = :BraID

21:17:55.735400	error	system.db.CDbCommand	Error in querying SQL: SELECT COUNT(*) FROM `course` INNER JOIN `branch` branches ON (`course`.`BraID`=branches.`BraID`) AND (branches.OrgID = :organisationsID) WHERE BraID = :BraID

Second problem might be on my own :slight_smile: just don’t see the possible SQL error …

Might this be because Parent PK(OrgID) is same as Child’s tables FK(OrgID)? But then why courses and branches works ok… :slight_smile: waiting for help.

27

"Page not found" error means the url is incorrect. Are you sure this is related to the datafilter?

Datafilter actually adds a HTML form and submits it to the server.

Please give more details about your issue and it would be good if you reproduce this bug in some simple application (for example in datafilter demo app).

  1. CDataFilter class has formAction property - an url for filter action. By default is is "" (empty string) which means current controller / action. So by default filter request should be sent to the controller/action where CDataFilter object is created.

  2. You should check your WHERE conditions in the SQL code which applies filtering. Now you have "WHERE BraID = :BraID" and it should be whether "WHERE course.BraID = :BraID" or "WHERE branch.BraID = :BraID"

28

Thanks for your reply and support!

1.I am doing this filtering based on your sample and the tables are same ER but the names differ according to:

country-organisation;

city-branch;

user-course;

group-trainer;

Now I set up all model filtering same as in you demo in model Course.php controller courseController.php and Views from your sample according to filtering with auto submit.

Where should I put default controller/action because it redirects to site/index after search or applying filter in course/admin VIEW?

  1. In courseController.php I am using also your modified(only table names differ) sample code:

I believe error appear base on this code portion:


        if($filterName == 'Organisation' || $filterName == 'organisationFilter2') {

            $localCriteria = new CDbCriteria;

            //'null' value is a spectial option for coutryFilter

            if ($filterValue != 'null') {

                $localCriteria->select = 'course.*';

                $localCriteria->join =

                    'INNER JOIN `branch` branches

                    ON (`course`.`BraID`=branches.`BraID`)

                    AND (branches.OrgID = :organizationID) ';

                $localCriteria->params = array(':organizationID'=>$filterValue);

                

            } else {

                $localCriteria->condition = ' BraID is null ';

            }

            $criteria->mergeWith($localCriteria);

        }

Thanks for help!

29

For the first problem the only way I can use datafilter normally is:

in config main.php setting URL manager:

	'urlManager' =&gt; array(


       'urlFormat'=&gt;'path'


  ),

But the problem persist in server because it does not support rewrite mode :slight_smile:

30

Yes, indeed the problem is incorrect url.

Here is my explanation :

I have an sales order table, one of the column is order no. which format is OCYY/MM/NNN where YY=Year, MM=Month, NNN=Sequence.

I want to search all Oct 2008 order so i’m using datafilter to search with keyword “OC08/10” (as you can see the keyword contain “/” character) after click search button it return expected records, but when i click on next page it threw error as in previous post.

I suspect the "/" in the keyword mess up proper search path.

Please advice how to solve it.

thanks

majin

31

Are you tried to set formAction? Code should look like this:




$filters = new CDataFilter(User::model());

        

$filters->addFilter(...);

$filters->formAction = 'user/admin';

Form with filters will be generated like this:




CHtml::form(CHtml::normalizeUrl($this->formAction),$this->formMethod,$this->formOptions);

Here formAction property is passed to CHtml::normalizeUrl.

SQL error is produced because your criteria contains where statement like this ’ BraID = :BraID ’ and you need to use ’ courses.BraID = :BraID ’ (and also ’ courses.BraID is null ').

32

I will test out the 1.problem solution, but with 2. I understand what is wrong, but can not find it in the code… I see your demo code as a transcript because of same situation(just table names are different) and its model.php is basically same… Any suggestions where this SQL statement stumbles in the code ? Thanks again!

33

Where should I edit the code because it adds

WHERE BraID = :BraID in the end of SQL and I can’t change it.

34

So you have search-type filter, correct? And when you enter search string like "OC08/10" then you get exactly this value as a part of the url?

I just tried to reproduce this case on my demo application, I opened "ManagingUser" page and entered "a/b" to the "Search" block. After I press "search" button, I can see "a%2Fb" in the url instead of "a/b". Are you able to reproduce problem with demo app?

P.S. If you are using dropdown filter, then try to urlencode dropdown values.

I think it should be inside conroller’s action. Try to track $criteria object through your code. Most probably is is created inside controller action like this:




    public function actionAdmin()

    {

        //create criteria object  

        $criteria=new CDbCriteria;

        

        ...

        

        //apply filters criteria (invokes code inside your model class) 

        $filters->applyCriteria($criteria);


        ...


        //apply pagination 

        $pages->applyLimit($criteria);


        ...


        // apply sorting

        $sort->applyOrder($criteria);


        ...

        //maybe you applying custom condition to criteria somewhere? 

        $criteria->condition = "BraID = :BraID"; 


        $models=User::model()->findAll($criteria);

    }
35

Hi, I’m trying to solve this issue but only found the WHERE condition in the model course:


	    // Applies filter criteria enterd using DataFilter widget

    public function applyDataFilterCriteria(&$criteria, $filterName, $filterValue)

    {

...


        if($filterName == 'Branch') {

            $localCriteria = new CDbCriteria;

            CDataFilter::setCondition('BraID', $filterValue, $localCriteria);

            $criteria->mergeWith($localCriteria);

        }

...

I’m testing your example and then filtering [+trainer][+ogranisation][branch] it works. Also works when filtering [+trainer][ogranisation][+branch].

Example working SQL when selecting [+trainer][ogranisation][+branch]:


SELECT COUNT(*) FROM `course` WHERE ( TrainID = :TrainID ) AND ( BraID = :BraID )

Its strange error, because I use your sample, only the table names and ID’s names are different…

Could you suggest where the problem is??

Thanks again!

36

Thanks for the reply Seb.

Yes, you are correct about search-type and search string in url. That is my problem the search string is not urlencode as in your demo, and I think your demo should work correctly since there is no "/" of search string in the url as in mine.

Pls shed some light on how to urlencode the search-type string filter ?

rgds

majin

37

Try CDataFilter::setCondition(‘courses.BraID’, $filterValue, $localCriteria);

This is strange. Search type filter is a regular HTML form with dropdown input and text input. After you press "search" button, this form is submitted and, I think, all values should be automatically urlencoded.

In my demo app no

I just made one more test with my demo app:

  1. Created new user with name "test/22/31"

  2. Inside search block selected "Name" in the Field dropdown and entered "test/22" in the Contains field.

  3. Clicked Search button and the user I added was found.

Try this with your local datafilter demo. Maybe an error is because of some yii or environment settings?

38

I fixed demo application to work with Yii 1.1 (changed primary table alias to ‘t’).

New version can be downloaded here.

39

Yes, I tried that and this error occurs:




Error in querying SQL: SELECT COUNT(*) FROM `course` INNER JOIN `branch` branches ON (`course`.`BraID`=branches.`BraID`) AND (branches.OrgID = :organisationID) WHERE [b]course.[/b]PaID = :[b]course.[/b]PaID

Can not find the fix…

40

In my own data, first search page is always no problem, error exist when I clicked on next page or page other than first page.

I try out your demo locally and created 12 new users contain "/" in each name, and it worked perfectly.

I can access to any page without problem.

Then I compared to my code, the different is I’m using zii cGridView. Could it be different pagination in CLinkPager and cGridView cause this problem ? Could you try your demo using cGridView ?

P.S. for your convenience I attached df_user with additional users mentioned above.

thanks,

majin