Sorting and Filtering Records

Question

is there any known pattern/algorithm on how to perform sorting or filtering a list of records (from database) in the correct way? My current attempt involves usage of a form that provides some filtering and sorting options, and then append these criteria and sorting algorithm to my existing SQL. However, I find it can be easily abused that users may get results that they are not suppose to see.

The application that I am building is a scheduler where I store all the events in a database table. Then depending on user's level/role/privilege a different subset of data will be displayed. Therefore my query can be as complicated as

SELECT  * 
FROM    app_event._event_view 
WHERE   ((class_id = (SELECT class_id FROM app_event._ical_class WHERE name = 'PUBLIC') AND class_id != (SELECT class_id FROM app_event._ical_class WHERE name = 'PRIVATE') AND class_id != (SELECT class_id FROM app_event._ical_class WHERE name = 'CONFIDENTIAL')) OR user_id = '1') 
        AND calendar_id IN (SELECT calendar_id FROM app_event.calendar WHERE is_personal = 't') 
        AND calendar_id = (SELECT calendar_id FROM app_event.calendar WHERE name = 'personal') 
        AND state_id IN (1,2,3,4,5,6) AND EXTRACT(year FROM dtstart) = '2008' 
        AND EXTRACT(month FROM dtstart) = '11'

As I am more concern about serving the correct subset of information, performance is not a major concern at the moment (as the sql mentioned was generated by the script, clause by clause). I am thinking of turning all these complicated SQL statements to views, so there will be less chances that the SQL generated is inappropriate.

Answer 1

It's hard to understand that query, because I have to scroll massively and since I don't know the database...

But if the privilegues are "one dimensional", e.g. admins can see everything, power users can see less than admins, guests can see less than power users, etc.: you could probably implement the privilegues as an integer in both the event and the user, then

select from event where conditions AND event.privilegue <= user.privilegue

If you set the privilegue values something like 10 000 (high/admin), 5000, 1(lowest/guest) you can later add more levels in between without changing all your code.

Answer 2

Use ORM tool or use parameters like:

SELECT  * 
FROM    app_event._event_view 
WHERE
    (
        :p_start_year is null or
        (state_id IN (1,2,3,4,5,6) AND EXTRACT(year FROM dtstart) = :p_start_year)
    )
    and
    (
        :p_date_start is null or
        AND EXTRACT(month FROM dtstart) = :p_date_start
    )

Answer 3

First off, this query will look and perform better if you use joins:

SELECT  * 
  FROM    
    app_event._event_view EV
    INNER JOIN app_event.calendar C
        ON EV.calendar_id = C.calendar.id
    INNER JOIN app_event._ical_class IC
        ON C.class_id = EV.class_id
  WHERE   
    C.is_personal = 't'
    AND C.name = 'personal'
    AND state_id IN (1,2,3,4,5,6) 
    AND EXTRACT(year FROM dtstart) = '2008' 
    AND EXTRACT(month FROM dtstart) = '11'
    AND (
        IC.name = 'PUBLIC' -- other two are redundant
        OR user_id = '1'
        )

That's a good start for keeping complexity down. Then, if you want to add additional filters directly to the SQL, you can append more "AND ..." statements at the end. Since all the criteria are connected with AND (the OR is safely contained within parentheses), there's no possibility of someone adding a criterion after these that somehow undoes the ones above - that is, once you've restricted the results with one part of a clause, you can't "un-restrict" it with a later clause if it's strung together with ANDs. (Of course, if those additional clauses go anywhere near user text input, you have to sanitize them to prevent SQL injection attacks.)

The database can filter faster than any other tier, in general, because using a WHERE clause on the database will often allow the database to avoid even reading the unnecessary records off the disk, which is several orders of magnitude slower than anything you can do with the CPU. Sorting is often faster on the DB as well, because the DB can often do the joins in such a way that records are already sorted in the order you want them. So, performance wise, if you can do it on the DB, all the better.

You said performance isn't really the key here, though, so adding filters programatically in the business logic might be fine for you, and easier to read than messing with SQL strings.